上篇博客中介绍了批量导入Excel到数据库,其本质为将Excel转为DataTable再将DataTable 的数据取出来,一条条的插入到数据库中。下面我们介绍一种比较优化的方法:将整个DataTable导入数据库中。
首先,将Excel中的数据取出,存入DataTable中,代码如下:
/// <summary> /// 传入excel路径,转换为datatable /// </summary> /// <param name="url"></param> /// <returns></returns> public DataTable CreateExcelDataSource(string url) { //定义一个DataTable数据表 DataTable dt = null; //获得excel数据 string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; //从Excel表的Sheet1单元格获取数据 string strSql = "select * from [Sheet1$]"; OleDbConnection oleConn = new OleDbConnection(connetionStr); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr); try { //把Excel数据填充给DataTable dt = new DataTable(); oleAdapter.Fill(dt); //返回数据表 return dt; } catch (Exception ex) { throw ex; } finally { oleAdapter.Dispose(); oleConn.Close(); oleConn.Dispose(); //删除上传的Excel文件(因为该文件的存在会占用多余的网站空间) if (File.Exists(url)) { File.Delete(url); } } }
我们获得了可以存入SQLServer数据库中的DataTable了,按照之前的想法,是用循环将DataTable中的数据取出来,一条条的插入数据库;对于数据量小的数据源来说,这种做法还可以接受,但是当导入的数据相当多是,这种做法的性能是可想而知的。那么我们来看一下,如何用更优的方法解决这个问题。
批量导入代码如下:
#region 批量导入DataTable /// <summary>批量导入DataTable /// 批量导入DataTable /// </summary> /// <param name="dt">DataTable数据表</param> /// <param name="tableName">表名</param> /// <param name="dtColum">数据列集合</param> /// <return>Boolean值:true成功,false失败</return> public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum) { //打开数据库 GetConn(); try { //声明SqlBulkCopy ,using释放非托管资源 using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn)) { //一次批量的插入的数据量 sqlBC.BatchSize = 1000; //超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除 sqlBC.BulkCopyTimeout = 60; //设置要批量写入的表 sqlBC.DestinationTableName = tableName; for (int i = 0; i < dtColum.Count; i++) { sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString()); } //批量写入 sqlBC.WriteToServer(dt); } return true; } catch { return false; } finally { //关闭数据库 sqlConn.Close(); } } #endregion
下面我们来比较一下两种导入方式的特点。
非批量导入的特点,非批量导入表结构不必相同,只需要把每一条数据提取出来,循环执行写入方法,写入数据库中;而批量导入呢?则需要表结构相同,表结构相同则可以只调用一次系统封装好的方法ColumnMappings将表中的数据导入,非常方便。当系统需要动态生成表,并且导入数据时,非批量导入实现起来就会很困难。简单来说非批量导入需要知道表结构并且多次调用同一方法;批量导入表结构需要相同,并且只调用一次方法就可以了。
两种方法各有利弊,若导入数据量很小并且不必每个字段都导入的话,非批量导入可以胜任;若导入的数据量很大,不清楚表结构,只要导入所有数据即可的话,批量导入就很有优势。具体情况具体分析选择合适的方法。