SqlBulkCopy.WriteToServer 方法
定义
重要
一些信息与预发行产品相关，相应产品在发行之前可能会进行重大修改。 对于此处提供的信息，Microsoft 不作任何明示或暗示的担保。
将所有行从数据源复制到由对象属性DestinationTableName指定的SqlBulkCopy目标表。
重载
| 名称 | 说明 |
|---|---|
| WriteToServer(DataTable, DataRowState) |
仅复制与所提供的 DataTable 行状态匹配的行，该行的状态与 DestinationTableName 由对象属性 SqlBulkCopy 指定的目标表匹配。 |
| WriteToServer(IDataReader) |
将提供的 IDataReader 中的所有行复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。 |
| WriteToServer(DataTable) |
将提供的 DataTable 中的所有行复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。 |
| WriteToServer(DbDataReader) |
将所有行从提供的DbDataReader数组复制到由对象属性DestinationTableName指定的SqlBulkCopy目标表。 |
| WriteToServer(DataRow[]) |
将所有行从提供的DataRow数组复制到由对象属性DestinationTableName指定的SqlBulkCopy目标表。 |
注解
如果禁用 WriteToServer 了多个活动结果集（MARS），则使连接变得繁忙。 如果启用了 MARS，则可以在同一连接中与其他命令交错调用 WriteToServer 。
WriteToServer(DataTable, DataRowState)
仅复制与所提供的 DataTable 行状态匹配的行，该行的状态与 DestinationTableName 由对象属性 SqlBulkCopy 指定的目标表匹配。
public:
void WriteToServer(System::Data::DataTable ^ table, System::Data::DataRowState rowState);public void WriteToServer(System.Data.DataTable table, System.Data.DataRowState rowState);member this.WriteToServer : System.Data.DataTable * System.Data.DataRowState -> unitPublic Sub WriteToServer (table As DataTable, rowState As DataRowState)参数
- rowState
- DataRowState
枚举中的 DataRowState 值。 仅将与行状态匹配的行复制到目标。
示例
以下控制台应用程序演示如何仅批量加载与指定状态匹配的行 DataTable 。 在这种情况下，仅添加未更改的行。 目标表是 AdventureWorks 数据库中的表。
在此示例中，在运行时创建 a DataTable ，并向其中添加了三行。
WriteToServer在执行方法之前，将编辑其中一行。 使用 WriteToServer 参数调用 DataRowState.UnchangedrowState 该方法，因此仅将两个未更改的行批量复制到目标。
Important
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，则使用 Transact-SQL INSERT ... SELECT 语句复制数据更容易、更快。
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Make a change to one of the rows in the DataTable.
DataRow row = newProducts.Rows[0];
row.BeginEdit();
row["Name"] = "AAA";
row.EndEdit();
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write unchanged rows from the source to the destination.
bulkCopy.WriteToServer(newProducts, DataRowState.Unchanged);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// 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 DataTable MakeTable()
// Create a new DataTable named NewProducts.
{
DataTable newProducts = new DataTable("NewProducts");
// Add three column objects to the table.
DataColumn productID = new DataColumn();
productID.DataType = System.Type.GetType("System.Int32");
productID.ColumnName = "ProductID";
productID.AutoIncrement = true;
newProducts.Columns.Add(productID);
DataColumn productName = new DataColumn();
productName.DataType = System.Type.GetType("System.String");
productName.ColumnName = "Name";
newProducts.Columns.Add(productName);
DataColumn productNumber = new DataColumn();
productNumber.DataType = System.Type.GetType("System.String");
productNumber.ColumnName = "ProductNumber";
newProducts.Columns.Add(productNumber);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = productID;
newProducts.PrimaryKey = keys;
// Add some new rows to the collection.
DataRow row = newProducts.NewRow();
row["Name"] = "CC-101-WH";
row["ProductNumber"] = "Cyclocomputer - White";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-BK";
row["ProductNumber"] = "Cyclocomputer - Black";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-ST";
row["ProductNumber"] = "Cyclocomputer - Stainless";
newProducts.Rows.Add(row);
newProducts.AcceptChanges();
// Return the new DataTable.
return newProducts;
}
private static string GetConnectionString()
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using connection As SqlConnection = _
New SqlConnection(connectionString)
connection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
connection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Create a table with some rows.
Dim newProducts As DataTable = MakeTable()
' Make a change to one of the rows in the DataTable.
Dim row As DataRow = newProducts.Rows(0)
row.BeginEdit()
row("Name") = "AAA"
row.EndEdit()
' Set up the bulk copy object.
' Note that the column positions in the source DataTable
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connection)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
Try
' Write unchanged rows from the source to the destination.
bulkCopy.WriteToServer(newProducts, DataRowState.Unchanged)
Catch ex As Exception
Console.WriteLine(ex.Message)
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 Sub
Private Function MakeTable() As DataTable
' Create a new DataTable named NewProducts.
Dim newProducts As DataTable = _
New DataTable("NewProducts")
' Add three column objects to the table.
Dim productID As DataColumn = New DataColumn()
productID.DataType = System.Type.GetType("System.Int32")
productID.ColumnName = "ProductID"
productID.AutoIncrement = True
newProducts.Columns.Add(productID)
Dim productName As DataColumn = New DataColumn()
productName.DataType = System.Type.GetType("System.String")
productName.ColumnName = "Name"
newProducts.Columns.Add(productName)
Dim productNumber As DataColumn = New DataColumn()
productNumber.DataType = System.Type.GetType("System.String")
productNumber.ColumnName = "ProductNumber"
newProducts.Columns.Add(productNumber)
' Create an array for DataColumn objects.
Dim keys(0) As DataColumn
keys(0) = productID
newProducts.PrimaryKey = keys
' Add some new rows to the collection.
Dim row As DataRow
row = newProducts.NewRow()
row("Name") = "CC-101-WH"
row("ProductNumber") = "Cyclocomputer - White"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-BK"
row("ProductNumber") = "Cyclocomputer - Black"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-ST"
row("ProductNumber") = "Cyclocomputer - Stainless"
newProducts.Rows.Add(row)
newProducts.AcceptChanges()
' Return the new DataTable.
Return newProducts
End Function
Private Function GetConnectionString() As String
' To avoid storing the connection 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
注解
只有处于自变量中DataTable指示状态且rowState尚未删除的行才会复制到目标表。
当大容量复制操作正在进行时，关联的目标 SqlConnection 正忙于为它提供服务，并且无法对连接执行其他操作。
集合 ColumnMappings 从 DataTable 列映射到目标数据库表。
另请参阅
适用于
WriteToServer(IDataReader)
将提供的 IDataReader 中的所有行复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。
public:
void WriteToServer(System::Data::IDataReader ^ reader);public void WriteToServer(System.Data.IDataReader reader);member this.WriteToServer : System.Data.IDataReader -> unitPublic Sub WriteToServer (reader As IDataReader)参数
- reader
- IDataReader
IDataReader其行将复制到目标表。
示例
以下控制台应用程序演示如何从 . SqlDataReader. 目标表是 AdventureWorks 数据库中的表。
Important
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，则使用 Transact-SQL INSERT ... SELECT 语句复制数据更容易、更快。
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();
// Set up the bulk copy object using a connection string.
// In the real world you would not use SqlBulkCopy to move
// data from one table to the other in the same database.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
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;";
}
}
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
' Set up the bulk copy object using a connection string.
' In the real world you would not use SqlBulkCopy to move
' data from one table to the other in the same database.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
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 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
注解
复制操作从读取器中的下一个可用行开始。 大多数情况下，读取器只是通过 ExecuteReader 或类似的调用返回的，因此下一个可用行是第一行。 若要处理多个结果，请调用 NextResult 数据读取器并再次调用 WriteToServer 。
请注意，使用 WriteToServer 修改读取器的状态。 该方法将调用 Read ，直到返回 false、操作中止或发生错误。 这意味着在操作完成后，数据读取器可能处于不同的状态（ WriteToServer 可能位于结果集的末尾）。
当大容量复制操作正在进行时，关联的目标 SqlConnection 正忙于为它提供服务，并且无法对连接执行其他操作。
集合 ColumnMappings 从数据读取器列映射到目标数据库表。
另请参阅
适用于
WriteToServer(DataTable)
将提供的 DataTable 中的所有行复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。
public:
void WriteToServer(System::Data::DataTable ^ table);public void WriteToServer(System.Data.DataTable table);member this.WriteToServer : System.Data.DataTable -> unitPublic Sub WriteToServer (table As DataTable)参数
示例
以下控制台应用程序演示如何从中 DataTable大容量加载数据。 目标表是 AdventureWorks 数据库中的表。
在此示例中，在运行时创建 a DataTable ，并且是操作的 SqlBulkCopy 源。
Important
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，则使用 Transact-SQL INSERT ... SELECT 语句复制数据更容易、更快。
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(newProducts);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// 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 DataTable MakeTable()
// Create a new DataTable named NewProducts.
{
DataTable newProducts = new DataTable("NewProducts");
// Add three column objects to the table.
DataColumn productID = new DataColumn();
productID.DataType = System.Type.GetType("System.Int32");
productID.ColumnName = "ProductID";
productID.AutoIncrement = true;
newProducts.Columns.Add(productID);
DataColumn productName = new DataColumn();
productName.DataType = System.Type.GetType("System.String");
productName.ColumnName = "Name";
newProducts.Columns.Add(productName);
DataColumn productNumber = new DataColumn();
productNumber.DataType = System.Type.GetType("System.String");
productNumber.ColumnName = "ProductNumber";
newProducts.Columns.Add(productNumber);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = productID;
newProducts.PrimaryKey = keys;
// Add some new rows to the collection.
DataRow row = newProducts.NewRow();
row["Name"] = "CC-101-WH";
row["ProductNumber"] = "Cyclocomputer - White";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-BK";
row["ProductNumber"] = "Cyclocomputer - Black";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-ST";
row["ProductNumber"] = "Cyclocomputer - Stainless";
newProducts.Rows.Add(row);
newProducts.AcceptChanges();
// Return the new DataTable.
return newProducts;
}
private static string GetConnectionString()
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using connection As SqlConnection = _
New SqlConnection(connectionString)
connection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
connection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Create a table with some rows.
Dim newProducts As DataTable = MakeTable()
' Note that the column positions in the source DataTable
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connection)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
Try
' Write from the source to the destination.
bulkCopy.WriteToServer(newProducts)
Catch ex As Exception
Console.WriteLine(ex.Message)
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 Sub
Private Function MakeTable() As DataTable
' Create a new DataTable named NewProducts.
Dim newProducts As DataTable = _
New DataTable("NewProducts")
' Add three column objects to the table.
Dim productID As DataColumn = New DataColumn()
productID.DataType = System.Type.GetType("System.Int32")
productID.ColumnName = "ProductID"
productID.AutoIncrement = True
newProducts.Columns.Add(productID)
Dim productName As DataColumn = New DataColumn()
productName.DataType = System.Type.GetType("System.String")
productName.ColumnName = "Name"
newProducts.Columns.Add(productName)
Dim productNumber As DataColumn = New DataColumn()
productNumber.DataType = System.Type.GetType("System.String")
productNumber.ColumnName = "ProductNumber"
newProducts.Columns.Add(productNumber)
' Create an array for DataColumn objects.
Dim keys(0) As DataColumn
keys(0) = productID
newProducts.PrimaryKey = keys
' Add some new rows to the collection.
Dim row As DataRow
row = newProducts.NewRow()
row("Name") = "CC-101-WH"
row("ProductNumber") = "Cyclocomputer - White"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-BK"
row("ProductNumber") = "Cyclocomputer - Black"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-ST"
row("ProductNumber") = "Cyclocomputer - Stainless"
newProducts.Rows.Add(row)
newProducts.AcceptChanges()
' Return the new DataTable.
Return newProducts
End Function
Private Function GetConnectionString() As String
' To avoid storing the connection 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
注解
除已删除的行外，所有 DataTable 行都会复制到目标表。
当大容量复制操作正在进行时，关联的目标 SqlConnection 正忙于为它提供服务，并且无法对连接执行其他操作。
集合 ColumnMappings 从 DataTable 列映射到目标数据库表。
另请参阅
适用于
WriteToServer(DbDataReader)
将所有行从提供的DbDataReader数组复制到由对象属性DestinationTableName指定的SqlBulkCopy目标表。
public:
void WriteToServer(System::Data::Common::DbDataReader ^ reader);public void WriteToServer(System.Data.Common.DbDataReader reader);member this.WriteToServer : System.Data.Common.DbDataReader -> unitPublic Sub WriteToServer (reader As DbDataReader)参数
- reader
- DbDataReader
DbDataReader其行将复制到目标表。
适用于
WriteToServer(DataRow[])
将所有行从提供的DataRow数组复制到由对象属性DestinationTableName指定的SqlBulkCopy目标表。
public:
void WriteToServer(cli::array <System::Data::DataRow ^> ^ rows);public void WriteToServer(System.Data.DataRow[] rows);member this.WriteToServer : System.Data.DataRow[] -> unitPublic Sub WriteToServer (rows As DataRow())参数
示例
以下控制台应用程序演示如何从 DataRow 数组大容量加载数据。 目标表是 AdventureWorks 数据库中的表。
在此示例中，在运行时创建一个 DataTable 。 从 DataTable 要复制到目标表的单个行中选择。
Important
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一 SQL Server 实例中，则使用 Transact-SQL INSERT ... SELECT 语句复制数据更容易、更快。
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Get a reference to a single row in the table.
DataRow[] rowArray = newProducts.Select(
"Name='CC-101-BK'");
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write the array of rows to the destination.
bulkCopy.WriteToServer(rowArray);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// 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 DataTable MakeTable()
// Create a new DataTable named NewProducts.
{
DataTable newProducts = new DataTable("NewProducts");
// Add three column objects to the table.
DataColumn productID = new DataColumn();
productID.DataType = System.Type.GetType("System.Int32");
productID.ColumnName = "ProductID";
productID.AutoIncrement = true;
newProducts.Columns.Add(productID);
DataColumn productName = new DataColumn();
productName.DataType = System.Type.GetType("System.String");
productName.ColumnName = "Name";
newProducts.Columns.Add(productName);
DataColumn productNumber = new DataColumn();
productNumber.DataType = System.Type.GetType("System.String");
productNumber.ColumnName = "ProductNumber";
newProducts.Columns.Add(productNumber);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = productID;
newProducts.PrimaryKey = keys;
// Add some new rows to the collection.
DataRow row = newProducts.NewRow();
row["Name"] = "CC-101-WH";
row["ProductNumber"] = "Cyclocomputer - White";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-BK";
row["ProductNumber"] = "Cyclocomputer - Black";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-ST";
row["ProductNumber"] = "Cyclocomputer - Stainless";
newProducts.Rows.Add(row);
newProducts.AcceptChanges();
// Return the new DataTable.
return newProducts;
}
private static string GetConnectionString()
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a connection to the AdventureWorks database.
Using connection As SqlConnection = _
New SqlConnection(connectionString)
connection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
connection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Create a table with some rows.
Dim newProducts As DataTable = MakeTable()
' Get a reference to a single row in the table.
Dim rowArray() As DataRow = newProducts.Select( _
"Name='CC-101-BK'")
' Note that the column positions in the source DataTable
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(connection)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
Try
' Write the array of rows to the destination.
bulkCopy.WriteToServer(rowArray)
Catch ex As Exception
Console.WriteLine(ex.Message)
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 Sub
Private Function MakeTable() As DataTable
' Create a new DataTable named NewProducts.
Dim newProducts As DataTable = _
New DataTable("NewProducts")
' Add three column objects to the table.
Dim productID As DataColumn = New DataColumn()
productID.DataType = System.Type.GetType("System.Int32")
productID.ColumnName = "ProductID"
productID.AutoIncrement = True
newProducts.Columns.Add(productID)
Dim productName As DataColumn = New DataColumn()
productName.DataType = System.Type.GetType("System.String")
productName.ColumnName = "Name"
newProducts.Columns.Add(productName)
Dim productNumber As DataColumn = New DataColumn()
productNumber.DataType = System.Type.GetType("System.String")
productNumber.ColumnName = "ProductNumber"
newProducts.Columns.Add(productNumber)
' Create an array for DataColumn objects.
Dim keys(0) As DataColumn
keys(0) = productID
newProducts.PrimaryKey = keys
' Add some new rows to the collection.
Dim row As DataRow
row = newProducts.NewRow()
row("Name") = "CC-101-WH"
row("ProductNumber") = "Cyclocomputer - White"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-BK"
row("ProductNumber") = "Cyclocomputer - Black"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "CC-101-ST"
row("ProductNumber") = "Cyclocomputer - Stainless"
newProducts.Rows.Add(row)
newProducts.AcceptChanges()
' Return the new DataTable.
Return newProducts
End Function
Private Function GetConnectionString() As String
' To avoid storing the connection 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
注解
当大容量复制操作正在进行时，关联的目标 SqlConnection 正忙于为它提供服务，并且无法对连接执行其他操作。
集合 ColumnMappings 从 DataRow 列映射到目标数据库表。
