SqlBulkCopy是.net2.0的新特性,平时用的很少,但是其功能却是非常强大,对于批量插入数据性能非常优越
代码
///
<summary>
/// bulk插入
/// </summary>
private void BulkInsert()
{
SqlConnection sqlcon = new SqlConnection( " Data Source=LocalHost;Integrated Security=SSPI;Initial Catalog=xiaotest; " );
DateTime beginTime = DateTime.Now;
DataTable dt = new DataTable();
dt.Columns.Add( " n " , typeof ( string ));
dt.Columns.Add( " name " , typeof ( string ));
for ( int i = 1 ; i < 1000 ; i ++ )
{
DataRow r = dt.NewRow();
r[ " n " ] = i;
r[ " name " ] = " xiao " ;
dt.Rows.Add(r);
}
sqlcon.Open();
using (SqlBulkCopy bulk = new SqlBulkCopy( " Data Source=LocalHost;Integrated Security=SSPI;Initial Catalog=xiaotest; " ))
{
bulk.BatchSize = 1000 ;
bulk.DestinationTableName = " test2 " ;
bulk.ColumnMappings.Add( " n " , " n " );
bulk.ColumnMappings.Add( " name " , " name " );
bulk.WriteToServer(dt);
}
DateTime endTime = DateTime.Now;
TimeSpan useTime = endTime - beginTime;
dt.Dispose();
time = " 使用时间 " + useTime.TotalSeconds.ToString() + " 秒 " ;
sqlcon.Close();
sqlcon.Dispose();
/// bulk插入
/// </summary>
private void BulkInsert()
{
SqlConnection sqlcon = new SqlConnection( " Data Source=LocalHost;Integrated Security=SSPI;Initial Catalog=xiaotest; " );
DateTime beginTime = DateTime.Now;
DataTable dt = new DataTable();
dt.Columns.Add( " n " , typeof ( string ));
dt.Columns.Add( " name " , typeof ( string ));
for ( int i = 1 ; i < 1000 ; i ++ )
{
DataRow r = dt.NewRow();
r[ " n " ] = i;
r[ " name " ] = " xiao " ;
dt.Rows.Add(r);
}
sqlcon.Open();
using (SqlBulkCopy bulk = new SqlBulkCopy( " Data Source=LocalHost;Integrated Security=SSPI;Initial Catalog=xiaotest; " ))
{
bulk.BatchSize = 1000 ;
bulk.DestinationTableName = " test2 " ;
bulk.ColumnMappings.Add( " n " , " n " );
bulk.ColumnMappings.Add( " name " , " name " );
bulk.WriteToServer(dt);
}
DateTime endTime = DateTime.Now;
TimeSpan useTime = endTime - beginTime;
dt.Dispose();
time = " 使用时间 " + useTime.TotalSeconds.ToString() + " 秒 " ;
sqlcon.Close();
sqlcon.Dispose();
经过1000条数据的对比测试,一般性的循环插入与sqlbulk插入的时间对比为:
一般插入:使用时间0.5200008秒
使用builk插入:使用时间0.02秒
性能非常优越