c#ADO.NET / 用 SqlBulkCopy 类,海量批写入数据库

SqlBulkCopy 类，使您可以用其他源的数据有效批量加载 SQL Server 表。

Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable instance 或可使用 DataReader 读取数据。

----------------------------------------------------------------

示例一:
同上方 msdn 连接的写法，不透过 DataTable 暂存所有要批写入的数据，而是直接用 
DataReader + SqlBulkCopy 类，即时海量地批写入 :

简易示例

using(SqlConnection Conn = new SqlConnection(strConnString)) 
{ 
    Conn.Open(); 
    SqlCommand Comm = new SqlCommand("SELECT EmployeeID, LastName, FirstName FROM Employees", Conn); 

    SqlDataReader dr; 
    
    dr = Comm.ExecuteReader(); 
    using (SqlConnection bcConn = new SqlConnection(strConnString)) 
    { 
        bcConn.Open(); 
        using (SqlBulkCopy bc = new SqlBulkCopy(bcConn)) 
        { 
          //要存入的数据库
            bc.DestinationTableName = "dbo.TestEmployees"; 

          //字段对应
           bc.ColumnMappings.Add("EmployeeID", "EmployeeID"); 
          bc.ColumnMappings.Add("LastName", "LastName"); 
          bc.ColumnMappings.Add("FirstName", "FirstName"); 
          try 
          { 
          　  for (int i = 0; i < 1000; i++) 
              　 bc.WriteToServer(dr); 
          } 
          catch (Exception ex) 
          { 
              Console.WriteLine(ex.Message); 
          } 
          if (dr != null) 
              dr.Close(); 
        } 
    }
}

在發現了 SqlBulkCopy 以後，發現它更是威力強大，現在就來介紹 SqlBulkCopy 的猛，如下例 :
示例二 :
(此例为先将要批写入的所有数据，暂存在一个 DataTable 里)

进阶示例

 //一开始我们先产生一个 DataTable，用来装我们要写入的数据
 DataTable dt = new DataTable();
 dt.Columns.Add("id", typeof(int));
 dt.Columns.Add("name", typeof(string));
 
 //因为 SqlBulkCopy 的猛，就是海量地快速一次性写入能力，我们來跑 10 万条吧
 int i;
 for (i = 0; i < 100000; i++)
 {
     DataRow dr = dt.NewRow();
     dr["name"] = i.ToString();
     dt.Rows.Add(dr);
 }
 
 //声明连接字符串
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
 
 conn.Open();

 //声明 SqlBulkCopy
 using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
 {
       //設置一个批，写入多少条记录
       sqlBC.BatchSize = 1000;

       //設置逾時的秒数
       sqlBC.BulkCopyTimeout = 60;
 
       //設置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表後，呼叫事件处理函数
       sqlBC.NotifyAfter = 10000;
       sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

       //設置要写入的数据库
       sqlBC.DestinationTableName = "dbo.Table1";
              
       //对应数据行
       sqlBC.ColumnMappings.Add("id", "id");
       sqlBC.ColumnMappings.Add("name", "name");
 
       //开始写入
       sqlBC.WriteToServer(dt);
 }
 
 conn.Dispose();
 
 void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
 {
     Response.Write("---<br/>");
 } 

• 測試環境：SQL2005 Express
• 測試資料量：10 万条
• 測試次數：10 次
• 平均秒數：2.3532 秒

结论：太可怕啦，之前的写法，如果真的要写入 10 万条這种海量的数据，都需花费一分钟左右，但使用了 SqlBulkCopy 卻只要短短的兩秒钟。下表看的出來，如果记录条数很少，就沒必要使用 SqlBulkCopy 了。

• 寫入十万条记录 10 次的平均秒數
  • 使用SqlBulkCopy：2.2051
  • 使用AddWithValue：63.418
• 寫入一万条记录 10 次的平均秒數
  • 使用SqlBulkCopy：0.2188
  • 使用AddWithValue：6.3856
• 寫入一千条记录 10 次的平均秒數
  • 使用SqlBulkCopy：0.0187
  • 使用AddWithValue：0.5805
• 寫入一百条记录 10 次的平均秒數
  • 使用SqlBulkCopy：0.0062
  • 使用AddWithValue：0.0353
• 寫入十条记录 10 次的平均秒數
  • 使用SqlBulkCopy：0.004
  • 使用AddWithValue：0.004