SqlBulkCopy 构造函数
定义
重要
一些信息与预发行产品相关，相应产品在发行之前可能会进行重大修改。 对于此处提供的信息，Microsoft 不作任何明示或暗示的担保。
初始化 SqlBulkCopy 类的新实例。
重载
| 名称 | 说明 |
|---|---|
| SqlBulkCopy(SqlConnection) |
使用 SqlBulkCopy 的打开的指定实例初始化 SqlConnection 类的新实例。 |
| SqlBulkCopy(String) |
基于提供的 SqlConnection 初始化并打开 |
| SqlBulkCopy(String, SqlBulkCopyOptions) |
基于提供的 SqlConnection 初始化并打开 |
| SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) |
使用提供的现有打开实例初始化类的新 SqlBulkCopy 实例 SqlConnection。 该 SqlBulkCopy 实例的行为取决于参数中 |
SqlBulkCopy(SqlConnection)
使用 SqlBulkCopy 的打开的指定实例初始化 SqlConnection 类的新实例。
public:
SqlBulkCopy(System::Data::SqlClient::SqlConnection ^ connection);public SqlBulkCopy(System.Data.SqlClient.SqlConnection connection);new System.Data.SqlClient.SqlBulkCopy : System.Data.SqlClient.SqlConnection -> System.Data.SqlClient.SqlBulkCopyPublic Sub New (connection As SqlConnection)参数
- connection
- SqlConnection
将用于执行大容量复制操作的已打开 SqlConnection 实例。 如果连接字符串不使用 Integrated Security = true，则可以使用 SqlCredential 比在连接字符串中将用户 ID 和密码指定为文本更安全地传递用户 ID 和密码。
示例
以下控制台应用程序演示如何使用已打开的连接批量加载数据。 在此示例中，SqlDataReader 用于将数据从 SQL Server AdventureWorks 数据库的 Production.Product 表复制到相同数据库的一个类似的表中。 此示例仅用于演示目的。 你不会用于 SqlBulkCopy 将数据从一个表移到生产应用程序中同一数据库中的另一个表。 请注意，源数据不必位于 SQL Server 上;可以使用可读取到或加载到 a IDataReaderDataTable.
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();
// 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;";
}
}
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 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
注解
由于初始化实例时 SqlBulkCopy 连接已打开，因此连接在实例关闭后 SqlBulkCopy 保持打开状态。
如果参数为 connection null，则会引发一个 ArgumentNullException 参数。
另请参阅
适用于
SqlBulkCopy(String)
基于提供的 SqlConnection 初始化并打开 connectionString 的新实例。 该构造函数使用 SqlConnection 来初始化 SqlBulkCopy 类的新实例。
public:
SqlBulkCopy(System::String ^ connectionString);public SqlBulkCopy(string connectionString);new System.Data.SqlClient.SqlBulkCopy : string -> System.Data.SqlClient.SqlBulkCopyPublic Sub New (connectionString As String)参数
- connectionString
- String
定义将打开以供实例使用的 SqlBulkCopy 连接的字符串。 如果你的连接字符串不使用 Integrated Security = true，则可以使用 SqlBulkCopy(SqlConnection) 或 SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) 和 SqlCredential将用户 ID 和密码指定为连接字符串中的文本来更安全地传递用户 ID 和密码。
示例
以下控制台应用程序演示如何使用指定为字符串的连接批量加载数据。 当实例关闭时 SqlBulkCopy ，连接会自动关闭。
在此示例中，源数据首先从 SQL Server 表读取到 SqlDataReader 实例。 源数据不必位于 SQL Server 上;可以使用可读取到或加载到 a IDataReaderDataTable.
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
注解
该连接在大容量复制操作结束时自动关闭。
如果 connectionString 为 null，则会引发一个 ArgumentNullException 。 如果 connectionString 为空字符串，则会引发一个 ArgumentException 字符串。
另请参阅
适用于
SqlBulkCopy(String, SqlBulkCopyOptions)
基于提供的 SqlConnection 初始化并打开 connectionString 的新实例。 构造函数使用该 SqlConnection 构造函数初始化类的新实例 SqlBulkCopy 。 该 SqlConnection 实例的行为取决于参数中 copyOptions 提供的选项。
public:
SqlBulkCopy(System::String ^ connectionString, System::Data::SqlClient::SqlBulkCopyOptions copyOptions);public SqlBulkCopy(string connectionString, System.Data.SqlClient.SqlBulkCopyOptions copyOptions);new System.Data.SqlClient.SqlBulkCopy : string * System.Data.SqlClient.SqlBulkCopyOptions -> System.Data.SqlClient.SqlBulkCopyPublic Sub New (connectionString As String, copyOptions As SqlBulkCopyOptions)参数
- connectionString
- String
定义将打开以供实例使用的 SqlBulkCopy 连接的字符串。 如果你的连接字符串不使用 Integrated Security = true，则可以使用 SqlBulkCopy(SqlConnection) 或 SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) 和 SqlCredential将用户 ID 和密码指定为连接字符串中的文本来更安全地传递用户 ID 和密码。
- copyOptions
- SqlBulkCopyOptions
枚举中的 SqlBulkCopyOptions 值的组合，用于确定将哪些数据源行复制到目标表。
示例
以下控制台应用程序演示如何使用指定为字符串的连接执行大容量加载。 加载目标表时，选项设置为使用源表的标识列中的值。 在此示例中，源数据首先从 SQL Server 表读取到 SqlDataReader 实例。 每个源表和目标表都包含一个“标识”列。 默认情况下，在目标表中为添加的每一行生成 标识 列的新值。 在此示例中，打开连接时会设置一个选项，该连接强制大容量加载进程改用源表中的 Identity 值。 若要查看选项如何更改大容量加载的工作方式，请使用 dbo 运行示例。BulkCopyDemoMatchingColumns 表为空。 从源加载所有行。 然后再次运行示例，而不清空表。 引发异常，代码会向控制台写入一条消息，通知你由于主键约束冲突而未添加行。
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();
// Create the SqlBulkCopy object using a connection string
// and the KeepIdentity option.
// 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, SqlBulkCopyOptions.KeepIdentity))
{
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
' Create the SqlBulkCopy object using a connection string
' and the KeepIdentity option.
' 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, SqlBulkCopyOptions.KeepIdentity)
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
注解
可以获取有关主题中 SqlBulkCopyOptions 所有大容量复制选项的详细信息。
另请参阅
适用于
SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)
使用提供的现有打开实例初始化类的新 SqlBulkCopy 实例 SqlConnection。 该 SqlBulkCopy 实例的行为取决于参数中 copyOptions 提供的选项。 如果提供非 null SqlTransaction ，则复制操作将在该事务中执行。
public:
SqlBulkCopy(System::Data::SqlClient::SqlConnection ^ connection, System::Data::SqlClient::SqlBulkCopyOptions copyOptions, System::Data::SqlClient::SqlTransaction ^ externalTransaction);public SqlBulkCopy(System.Data.SqlClient.SqlConnection connection, System.Data.SqlClient.SqlBulkCopyOptions copyOptions, System.Data.SqlClient.SqlTransaction externalTransaction);new System.Data.SqlClient.SqlBulkCopy : System.Data.SqlClient.SqlConnection * System.Data.SqlClient.SqlBulkCopyOptions * System.Data.SqlClient.SqlTransaction -> System.Data.SqlClient.SqlBulkCopyPublic Sub New (connection As SqlConnection, copyOptions As SqlBulkCopyOptions, externalTransaction As SqlTransaction)参数
- connection
- SqlConnection
将用于执行大容量复制的已打开 SqlConnection 实例。 如果连接字符串不使用 Integrated Security = true，则可以使用 SqlCredential 比在连接字符串中将用户 ID 和密码指定为文本更安全地传递用户 ID 和密码。
- copyOptions
- SqlBulkCopyOptions
枚举中的 SqlBulkCopyOptions 值的组合，用于确定将哪些数据源行复制到目标表。
- externalTransaction
- SqlTransaction
发生大容量复制的现有 SqlTransaction 实例。
注解
如果选项包含 UseInternalTransaction 且 externalTransaction 参数不为 null，则会引发 InvalidArgumentException 。
有关演示如何在事务中使用的 SqlBulkCopy 示例，请参阅 事务和大容量复制操作。