string strConnection = ConfigurationManager.AppSettings["conStr"].ToString();//讀取Web.config文件中的數(shù)據(jù)庫(kù)連接字符串
SqlConnection sourceconnection = new SqlConnection(strConnection);//數(shù)據(jù)的連接方式是SQL Server
sourceconnection.Open();//打開(kāi)數(shù)據(jù)庫(kù)連接
SqlCommand cmd = new SqlCommand("Select * from MSreplication_options");//通過(guò)命令來(lái)讀取SQL語(yǔ)句
cmd.Connection = sourceconnection;//獲取連接方式
SqlDataReader reader = cmd.ExecuteReader();//開(kāi)始執(zhí)和結(jié)果集，獲取DataReader記錄集
//連接目標(biāo)數(shù)據(jù)庫(kù)連接，并且打開(kāi)數(shù)據(jù)庫(kù)連接方式，在此由于調(diào)用同一個(gè)數(shù)據(jù)庫(kù)，連接字符串沒(méi)有變
SqlConnection destinationConnection = new SqlConnection(strConnection);
destinationConnection.Open();
//調(diào)用SqlBulkCopy類(lèi)的方法
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection);
//獲取目標(biāo)表的名稱(chēng)
bulkCopy.DestinationTableName = "destination";
//寫(xiě)入DataReader對(duì)象
bulkCopy.WriteToServer(reader);
//關(guān)閉各個(gè)對(duì)象
reader.Close();
sourceconnection.Close();
destinationConnection.Close(); ID (OrderID): int
Name (ShipName): nvarchar(40)
Address (ShipAddress): nvarchar(60)
City (ShipCity): nvarchar(15)
然后在Northwind數(shù)據(jù)中創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，如下：
CREATE PROCEDURE dbo.SelectOrders
AS
SELECT OrderID, ShipName, ShipAddress, ShipCity
FROM Orders private void btnStart_Click(object sender, EventArgs e)
{
String sourceConnectionString =
"Data Source=127.0.0.1;Initial Catalog=Northwind;Integrated Security=True";
String destinationConnectionString =
"Data Source=127.0.0.1;;Initial Catalog=SqlBulkCopySample;Integrated Security=True";
DataTable data = SelectDataFromSource(sourceConnectionString);//獲取數(shù)據(jù)
CopyDataToDestination(destinationConnectionString, data);//復(fù)制數(shù)據(jù)
} private DataTable SelectDataFromSource(String connectionString)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SelectOrders", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data.Load(reader);
}
return data;
} private void CopyDataToDestination(String connectionString, DataTable table)
{
SqlBulkCopyColumnMapping mapping1 =
new SqlBulkCopyColumnMapping("OrderID", "ID");
SqlBulkCopyColumnMapping mapping2 =
new SqlBulkCopyColumnMapping("ShipName", "Name");
SqlBulkCopyColumnMapping mapping3 =
new SqlBulkCopyColumnMapping("ShipAddress", "Address");
SqlBulkCopyColumnMapping mapping4 =
new SqlBulkCopyColumnMapping("ShipCity", "City");
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 5;
bulkCopy.ColumnMappings.Add(mapping1);
bulkCopy.ColumnMappings.Add(mapping2);
bulkCopy.ColumnMappings.Add(mapping3);
bulkCopy.ColumnMappings.Add(mapping4);
bulkCopy.DestinationTableName = "tblOrder";
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
bulkCopy.NotifyAfter = 200;
bulkCopy.WriteToServer(table);
} private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show
(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
} private DataTable SelectDataFromSource(String connectionString)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SelectOrders", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data.Load(reader);
}
return data;
} private void CopyDataToDestination(String connectionString, DataTable table)
{
SqlBulkCopyColumnMapping mapping1 =
new SqlBulkCopyColumnMapping("OrderID", "ID");
SqlBulkCopyColumnMapping mapping2 =
new SqlBulkCopyColumnMapping("ShipName", "Name");
SqlBulkCopyColumnMapping mapping3 =
new SqlBulkCopyColumnMapping("ShipAddress", "Address");
SqlBulkCopyColumnMapping mapping4 =
new SqlBulkCopyColumnMapping("ShipCity", "City");
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 5;
bulkCopy.ColumnMappings.Add(mapping1);
bulkCopy.ColumnMappings.Add(mapping2);
bulkCopy.ColumnMappings.Add(mapping3);
bulkCopy.ColumnMappings.Add(mapping4);
bulkCopy.DestinationTableName = "tblOrder";
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
bulkCopy.NotifyAfter = 200;
bulkCopy.WriteToServer(table);
} private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show
(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
}聯(lián)系客服
