單一大量複製作業 (ADO.NET)
更新： November 2007
執行 SQL Server 大量複製作業的最簡單方法是：針對資料庫執行單一作業。預設會將大量複製作業做為隔離的作業來執行：複製作業會以非交易性方式發生，並且無法復原。
.gif "注意事項") 注意事項： |
|---|
如果需要在發生錯誤時，復原全部或部分大量複製，您可以使用 SqlBulkCopy 管理的交易，或在現有交易內執行大量複製作業。如果將連接 (以隱含或明確的方式) 登記到 System.Transactions 交易中，則 SqlBulkCopy 也會使用 System.Transactions。 如需詳細資訊，請參閱 交易和大量複製作業 (ADO.NET)。 |
執行大量複製作業的一般步驟如下：
連接至來源伺服器，並取得要複製的資料。如果可從 IDataReader 或 DataTable 物件擷取資料，則資料也可來自其他來源。
連接至目的伺服器 (除非您要讓 SqlBulkCopy 建立連接)。
建立 SqlBulkCopy 物件，設定所有必要的屬性。
設定 DestinationTableName 屬性，以表示用於大量插入作業的目標資料表。
呼叫其中一個 WriteToServer 方法。
視需要，選擇性地更新屬性，並重新呼叫 WriteToServer。
呼叫 Close，或將大量複製作業包裝至 Using 陳述式內。
.gif "注意事項") 警告： |
|---|
建議來源與目標資料行的資料型別相符。如果資料型別不相符，則 SqlBulkCopy 會嘗試使用 Value 所使用的規則，將每個來源值轉換為目標資料型別。轉換可能會影響效能，亦可能導致意外的錯誤。例如，Double 資料型別通常可轉換為 Decimal 資料型別，但並非始終如此。 |
範例
下列主控台應用程式示範如何使用 SqlBulkCopy 類別來載入資料。在此範例中，會使用 SqlDataReader 從 SQL Server 2005 AdventureWorks 資料庫中的 Production.Product 資料表，將資料複製到相同資料庫中的類似資料表。
| 重要事項： |
|---|
除非您已依照大量複製範例設定 (ADO.NET)所述來建立工作資料表，否則此範例將無法執行。這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。如果來源及目標資料表位於相同的 SQL Server 執行個體 (Instance) 中，則使用 Transact-SQL INSERT _ SELECT 陳述式來複製資料會較為簡易快速。 |
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Open the destination connection. In the real world you would
' not use SqlBulkCopy to move data from one table to the other
' in the same database. This is for demonstration purposes only.
Using destinationConnection As SqlConnection = _
New SqlConnection(connectionString)
destinationConnection.Open()
' Set up the bulk copy object.
' The column positions in the source data reader
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoMatchingColumns"
Try
' Write from the source to the destination.
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)
Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Using
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the sourceConnection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);" & _
"Integrated Security=true;" & _
"Initial Catalog=AdventureWorks;"
End Function
End Module
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();
// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
使用 Transact-SQL 及命令類別來執行大量複製作業
當使用 .NET Framework 1.1 或更早版本 (不支援 SqlBulkCopy 類別) 時，您也可以使用 SqlCommand 物件執行 Transact-SQL BULK INSERT 陳述式。請注意，使用此技術與使用 SQL Server 的 .NET Framework 資料提供者所提供的大量複製功能完全不相關。
下列範例說明如何使用 ExecuteNonQuery 方法執行 BULK INSERT 陳述式。
| 注意事項： |
|---|
資料來源的檔案路徑是與伺服器相對的。伺服器處理序必須存取該路徑，才能順利完成大量複製作業。 |
Using connection As SqlConnection = New SqlConnection(connectionString)
Dim queryString As String = _
"BULK INSERT Northwind.dbo.[Order Details] FROM " & _
"'f:\mydata\data.tbl' WITH (FORMATFILE='f:\mydata\data.fmt' )"
connection.Open()
SqlCommand command = New SqlCommand(queryString, connection);
command.ExecuteNonQuery()
End Using
using (SqlConnection connection = New SqlConnection(connectionString))
{
string queryString = "BULK INSERT Northwind.dbo.[Order Details] " +
"FROM 'f:\mydata\data.tbl' " +
"WITH ( FORMATFILE='f:\mydata\data.fmt' )";
connection.Open();
SqlCommand command = new SqlCommand(queryString, connection);
command.ExecuteNonQuery();
}