我们经常要在一个表中将数据迁移到另一个表,当然,用的方法十分多了。在.net 2.0中,提供了一个sqlbulkcopy类,也可以实现如下的操作,下面简单介绍下。比如一个表如下
CREATE TABLE Person3
(
PersonID int IDENTITY(1,1) PRIMARY KEY,
Name nvarchar(200),
Email nvarchar(200),
Picture image
)
INSERT INTO Person3(Name,Email,Picture)
SELECT Name,Email,Picture FROM Person
假设person表已经存在了,则上面的语句可以往person3表中插入数据(在sql server 2005中执行)。现在我们使用下面的代码来实现
string connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
SqlConnection myNewConnection = new SqlConnection(connectionString);
myNewConnection.Open();
SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection);
bulk.DestinationTableName = "[Person3]";
try
{
bulk.WriteToServer(dr);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
myNewConnection.Close();
dr.Close();
myConnection.Close();
bulk.Close();
}
SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection);
bulk.DestinationTableName = "[Person3]";
其中,将mynewconnection作为参数传到 sqlbulkcopy类的构造参数中去,并指定目标迁移的表名是person3.之后,再使用 bulk.WriteToServer(dr);就可以迁移了。
而上面的person表和person3的结构是完全相同的,那么如果结构不同的,怎么办呢?下面举例子说明,创建一个表person2
CREATE TABLE Person2
(
PersonID int IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(200),
LastName nvarchar(200),
Email nvarchar(200),
Picture image
)
如果我们按上面将person表迁移到person2表中去,将会出错,因为字段不同,而我们将采用下面的代码
string connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
SqlConnection myNewConnection = new SqlConnection(connectionString);
myNewConnection.Open();
SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection);
bulk.DestinationTableName = "[Person2]";
bulk.ColumnMappings.Add("Name", "LastName");
bulk.ColumnMappings.Add("Email", "Email");
bulk.ColumnMappings.Add("Picture", "Picture");
try
{
bulk.WriteToServer(dr);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
myNewConnection.Close();
dr.Close();
myConnection.Close();
bulk.Close();
}
可以看到,这里使用 bulk.ColumnMappings.Add(“。。。”,“。。。”)来强制规定,源目标字段和哪一个目标表的字段相匹配了。据说用sqlbulkcopy类,在数据多的情况下,性能是十分好的,呵呵。