SQLBULKCOPY批量插入数据使用

protected void Button1_Click(object sender, EventArgs e)

{ 

  

     DateTime beginTime = DateTime.Now;

     Response.Write("开始时间：" + beginTime.ToString("yyyy年MM月dd日：HH:mm:ss:fff")); 

  

     //构造一个Datatable存储将要批量导入的数据

     DataTable dt = new DataTable();

    dt.Columns.Add("id", typeof(string));

    dt.Columns.Add("name", typeof(string)); 

  

    // 见识下SqlBulkCopy强悍之处，来个十万条数数据试验

    int i;

    for (i = 0; i < 100000; i++)

    {

        DataRow dr = dt.NewRow();

        dr["name"] = i.ToString();

        dt.Rows.Add(dr);

    } 

  

    string str = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString.ToString();

    //声明数据库连接

    SqlConnection conn = new SqlConnection(str); 

  

    conn.Open();

    //声明SqlBulkCopy ,using释放非托管资源

    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.text"; 

  

        //自定义的datatable和数据库的字段进行对应

        sqlBC.ColumnMappings.Add("id", "tel");

        sqlBC.ColumnMappings.Add("name", "neirong"); 

  

        //批量写入

        sqlBC.WriteToServer(dt);

    }

    conn.Dispose();

    Response.Write("<br/>"); 

  

  

  

    DateTime endTime = DateTime.Now;

    Response.Write("结束时间：" + endTime.ToString("yyyy年MM月dd日：HH:mm:ss:fff"));

    TimeSpan useTime = endTime-beginTime;//使用时间

    Response.Write("<br/>插入时间："+ useTime.TotalSeconds.ToString()+"秒"); 

  

}

//响应时事件

void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

{

    Response.Write("<br/> OK! ");

}

 

 

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

在做大批量数据插入的时候，如果用Insert into ... values (...)这种方式的话效率极低，这里介绍两种性能比较好的批量插入方法。

1. 使用SqlBulkCopy

private static long SqlBulkCopyInsert() 
{ 
Stopwatch stopwatch = new Stopwatch(); 
stopwatch.Start(); 
DataTable dataTable = GetTableSchema(); 
string passportKey; 
for (int i = 0; i < count; i++) 
{ 
passportKey = Guid.NewGuid().ToString(); 
DataRow dataRow = dataTable.NewRow(); 
dataRow[0] = passportKey; 
dataTable.Rows.Add(dataRow); 
} 
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); 
sqlBulkCopy.DestinationTableName = "Passport"; 
sqlBulkCopy.BatchSize = dataTable.Rows.Count; 
SqlConnection sqlConnection = new SqlConnection(connectionString); 
sqlConnection.Open(); 
if (dataTable!=null && dataTable.Rows.Count!=0) 
{ 
sqlBulkCopy.WriteToServer(dataTable); 
} 
sqlBulkCopy.Close(); 
sqlConnection.Close(); 
stopwatch.Stop(); 
return stopwatch.ElapsedMilliseconds; 
}  

 

　　使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制。这里我们先要建好一个DataTable（最好是通过DataAdapter来灌数据得到，因为这样出来的DataTable就已经有跟数据表相同的列定义，可以免去之后Mapping Column的步骤），把要插入的数据加进这个DataTable中，然后用SqlBulkCopy的实例来插入到数据库中。经过测试，SqlBulkCopy方法比直接用Sql语句插入数据的效率高出将近25倍。

 

　　另外批量导入SQL、MYSQL等数据是同样的for循环，使用拼出来的sql或者使用参数的方式传递或者使用事务等不同方式的传递效率都不同。如果不使用SqlBulkCopy的方式的话，我测试下来做快递是用一次事务来操作为最快。因为10000次的循环如果是每次提交，那么都有链接和停止数据库的操作，或者说他包含了1000次的小事务处理。如果外面就一个事务的话效率肯定会高。

 

 

 

使用中，我也碰到了一些“莫名其妙”的问题，在此记下，以备查询。

1. SqlBulkCopy可以将一个DataTable对象插入到指定名称的数据表中，但是，这个DataTable的架构必须和数据库表一样，尤其要注意一点，DataTable中列的顺序必须和数据库表一样，而且不允许间隔。比如：数据库表中有A、B、C三列，其中B列有默认值，这时用于插入的DataTable不能只有A、C两列，如果只有这两列，就会把DataTable里C列的数据插入数据库表B列里，而把DBNull插入C列里。如果是C列有默认值，DataTable可以只有A、B两列。
2. SqlBulkCopu的默认超时时间是30秒，30x4000=1.2万，无法满足我一次性插入单张表的需要，改成3600秒。