最近公司需要優化導入的問題，由於之前使用的方式是生成 Insert 語句插入資料庫，數據量小的時候還行，但是隨著發展數據量漸漸大了，之前的方法性能就跟不上了，於是發現了 SqlBulkCopy 這個類。 使用 SqlBulkCopy 類只能向 SQL Server 表寫入數據。但是，數據源不限於 S ...
最近公司需要優化導入的問題，由於之前使用的方式是生成 Insert 語句插入資料庫，數據量小的時候還行，但是隨著發展數據量漸漸大了，之前的方法性能就跟不上了，於是發現了 SqlBulkCopy 這個類。
使用 SqlBulkCopy 類只能向 SQL Server 表寫入數據。但是，數據源不限於 SQL Server；可以使用任何數據源，只要數據可載入到 DataTable 實例或可使用 IDataReader 實例讀取數據。
public class Conn { private static string StrConn { get { return ConfigurationManager.ConnectionStrings["StrConn"].ToString(); //return ConfigurationManager.AppSettings["StrConn"].ToString(); } } public static SqlConnection SqlConn { get { return new SqlConnection(StrConn); } } } public class SqlHelper { public DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); SqlConnection conn = null; SqlDataAdapter sda = null; try { conn = Conn.SqlConn; sda = new SqlDataAdapter(sql, conn); conn.Open(); sda.Fill(dt); } catch (Exception ex) { } finally { if (conn != null) { conn.Close(); conn.Dispose(); } if (sda != null) { sda.Dispose(); } } return dt; } public DataSet GetDataSet(string sql) { DataSet ds = new DataSet(); SqlConnection conn = null; SqlDataAdapter sda = null; try { conn = Conn.SqlConn; sda = new SqlDataAdapter(sql, conn); conn.Open(); sda.Fill(ds); } catch (Exception ex) { } finally { if (conn != null) { conn.Close(); conn.Dispose(); } if (sda != null) { sda.Dispose(); } } return ds; } /// <summary> /// 使用事務插入方法 /// </summary> /// <param name="dt">源數據</param> /// <param name="tableName">目標表名</param> public void InsertO(DataTable dt, string tableName) { using (SqlConnection conn = Conn.SqlConn) { using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.Default | SqlBulkCopyOptions.UseInternalTransaction)) { try { //設置目標表名，即資料庫表名 sqlBuleCopy.DestinationTableName = tableName; //設置每一批次的行數，即達到指定的行數就插入一次資料庫 sqlBuleCopy.BatchSize = 100000; //設置超時之前完成的時間（秒） sqlBuleCopy.BulkCopyTimeout = 3600; for (int i = 0; i < dt.Columns.Count; i++) { //設置源數據列與目標表的列的映射關係，第一個參數為源數據列，第二個參數為目標表列 sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } sqlBuleCopy.WriteToServer(dt); } catch (Exception) { } } } } /// <summary> /// 未使用事務插入方法 /// </summary> /// <param name="dt">源數據</param> /// <param name="tableName">目標表名</param> public void InsertT(DataTable dt, string tableName) { using (SqlConnection conn = Conn.SqlConn) { using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn)) { try { conn.Open(); //設置目標表名，即資料庫表名 sqlBuleCopy.DestinationTableName = tableName; //設置每一批次的行數，即達到指定的行數就插入一次資料庫 sqlBuleCopy.BatchSize = 100000; //設置超時之前完成的時間（秒） sqlBuleCopy.BulkCopyTimeout = 3600; for (int i = 0; i < dt.Columns.Count; i++) { //設置源數據列與目標表的列的映射關係，第一個參數為源數據列，第二個參數為目標表列 sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); } sqlBuleCopy.WriteToServer(dt); } catch (Exception) { conn.Close(); conn.Dispose(); } finally { conn.Close(); conn.Dispose(); } } } } }
我的源數據是使用 Excel 導入的數據，導入的方法就不說了，不是這裡的重點，之後我會專門總結一下 Excel 導入的方法。然後查詢目標表需要插入數據的欄位，修改源數據表的欄位名和類型，然後調用批量插入的方法。
protected void btnImport_Click(object sender, EventArgs e) { try { //獲取導入的數據 DataSet ds = BI.ExecleDs(savePath, ""); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; //查詢目標表需要插入的欄位 string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo "; DataTable dt1 = sqlhelper.GetDataTable(sql); if (dt1 != null) { for (int i = 0; i < dt1.Columns.Count; i++) { //修改源數據表的欄位類型和欄位名稱 dt.Columns[i].DataType = dt1.Columns[i].DataType; dt.Columns[i].ColumnMapping = dt1.Columns[i].ColumnMapping; dt.Columns[i].ColumnName = dt1.Columns[i].ColumnName; } sqlhelper.InsertO(dt, "UserInfo"); } } } catch (Exception ex) { throw; } }
以上這種修改數據類型的方法，如果碰到數據類型不一致並且 DataTable 有數據的時候，會報錯，不能修改有數據的列的數據類型。（好像是廢話，嘿嘿，沒有數據和有數據時不能修改數據類型，這完全是沒用的。）
所以就有了下麵的方法，先實例化一個新的 DataTable，然後複製目標表的架構，然後再把數據保存到新的 DataTable 中。
protected void btnImport_Click(object sender, EventArgs e) { try { //獲取導入的數據 DataSet ds = BI.ExecleDs(savePath, ""); if (ds != null && ds.Tables.Count > 0) { DataTable dt = ds.Tables[0]; //查詢目標表需要插入的欄位 string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo "; DataTable dt1 = sqlhelper.GetDataTable(sql); DataTable dt2 = new DataTable(); if (dt1 != null) { //複製目標表的架構 dt2 = dt1.Clone(); for (int i = 0; i < dt1.Rows.Count; i++) { DataRow dr = dt2.NewRow(); dr = dt1.Rows[i]; dt2.Rows.Add(dr.ItemArray); } sqlhelper.InsertO(dt2, "UserInfo"); } } } catch (Exception ex) { throw; } }
如果源數據表的列和目標表的列的順序或列名不相同，那就必須使用 ColumnMappings.Add() 方法設置映射關係。
參考：
http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html
