一些应用需求要求插入大额的数据到SQL server数据库中。这时候再用INSERT语句并不是一个高效率的方法。SQL Server提供了一个批量插入数据的类:SqlBulkCopy。本文用一个例子来阐述这个 类怎么在你的程序中使用。
例子:
SqlConnection sourceConn
=
new
SqlConnection
( " Data Source=./sqlexpress;
initial catalog = northwind;integrated security = true " );
sourceConn.Open();
SqlCommand cmd = new
SqlCommand( " select * from employees " , sourceConn);
SqlDataReader reader = cmd.ExecuteReader();
SqlConnection destConn = new
SqlConnection( " Data Source=./sqlexpress;
initial catalog = northwind;integrated security = true " );
destConn.Open();
SqlBulkCopy bcp = new SqlBulkCopy(destConn);
bcp.DestinationTableName = " Employees " ;
bcp.ColumnMappings.Add( " LastName " , " LastName " );
bcp.ColumnMappings.Add( " FirstName " , " FirstName " );
bcp.WriteToServer(reader);
destConn.Close();
reader.Close();
sourceConn.Close();
( " Data Source=./sqlexpress;
initial catalog = northwind;integrated security = true " );
sourceConn.Open();
SqlCommand cmd = new
SqlCommand( " select * from employees " , sourceConn);
SqlDataReader reader = cmd.ExecuteReader();
SqlConnection destConn = new
SqlConnection( " Data Source=./sqlexpress;
initial catalog = northwind;integrated security = true " );
destConn.Open();
SqlBulkCopy bcp = new SqlBulkCopy(destConn);
bcp.DestinationTableName = " Employees " ;
bcp.ColumnMappings.Add( " LastName " , " LastName " );
bcp.ColumnMappings.Add( " FirstName " , " FirstName " );
bcp.WriteToServer(reader);
destConn.Close();
reader.Close();
sourceConn.Close();
这里,我们创建了两个SqlConnection对象。一个用来从源数据库取数据,另一个是用来打开目的数据库的。在上面的例子中目的和源数据库的连接是相同的,但是他们也是可以不同的连接。
然后我们从Employees表中用SqlDataReader类来取数据。SqlBulkCopy类的构造函数接受参数为目的数据库连接的对象。DestinationTableName 属性用来指定目的数据库中表的名字。如果目的和源数据表的各列不同,你需要用ColumnMappings来指定各列的映射。最后,你需要调用WriteToServer方法,然后传递DataReader来把数据插入到目的数据库中。你同样可以用DataTable来代替DataReader来保存那些需要插入的记录。