SqlBulkCopy 建構函式

定義

初始化 SqlBulkCopy 類別的新執行個體。Initializes a new instance of the SqlBulkCopy class.

多載

SqlBulkCopy(SqlConnection)

使用 SqlConnection 的指定開放執行個體,初始化 SqlBulkCopy 類別的新執行個體Initializes a new instance of the SqlBulkCopy class using the specified open instance of SqlConnection.

SqlBulkCopy(String)

根據所提供的 connectionString 初始化並開啟 SqlConnection 的新執行個體。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 這個建構函式會使用 SqlConnection 初始化 SqlBulkCopy 類別的新執行個體。The constructor uses the SqlConnection to initialize a new instance of the SqlBulkCopy class.

SqlBulkCopy(String, SqlBulkCopyOptions)

基於已提供的 connectionString,初始化和開啟 SqlConnection 的新執行個體。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 建構函式會使用 SqlConnection,初始化 SqlBulkCopy 類別的新執行個體。The constructor uses that SqlConnection to initialize a new instance of the SqlBulkCopy class. SqlConnection 執行個體會根據 copyOptions 參數中所提供的選項操作。The SqlConnection instance behaves according to options supplied in the copyOptions parameter.

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用已提供之現有開啟的 SqlBulkCopy 執行個體,初始化 SqlConnection 的新執行個體。Initializes a new instance of the SqlBulkCopy class using the supplied existing open instance of SqlConnection. SqlBulkCopy 執行個體會根據 copyOptions 參數中提供的選項進行運作。The SqlBulkCopy instance behaves according to options supplied in the copyOptions parameter. 如果提供了非 null 的 SqlTransaction,則複製作業會在該交易執行。If a non-null SqlTransaction is supplied, the copy operations will be performed within that transaction.

SqlBulkCopy(SqlConnection)

使用 SqlConnection 的指定開放執行個體,初始化 SqlBulkCopy 類別的新執行個體Initializes a new instance of the SqlBulkCopy class using the specified open instance of 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.SqlBulkCopy
Public Sub New (connection As SqlConnection)

參數

connection
SqlConnection

已開啟的 SqlConnection 執行個體,用於執行大量複製作業。The already open SqlConnection instance that will be used to perform the bulk copy operation. 如果您的連接字串不使用 Integrated Security = true,您便可以使用 SqlCredential 來傳遞使用者 ID 和密碼,這種方式比在連接字串中以文字指定使用者 ID 和密碼來得更加安全。If your connection string does not use Integrated Security = true, you can use SqlCredential to pass the user ID and password more securely than by specifying the user ID and password as text in the connection string.

範例

下列主控台應用程式示範如何使用已開啟的連接來大量載入資料。The following console application demonstrates how to bulk load data using a connection that is already open. 在這個範例中,SqlDataReader 用於從 SQL Server AdventureWorks 資料庫的 Production.Product 資料表,將資料複製到同一資料庫中的類似資料表中。In this example, a SqlDataReader is used to copy data from the Production.Product table in the SQL Server AdventureWorks database to a similar table in the same database. 此範例僅供示範之用。This example is for demonstration purposes only. 您不會使用在 SqlBulkCopy 生產應用程式的相同資料庫中,將資料從一個資料表移到另一個。You would not use SqlBulkCopy to move data from one table to another in the same database in a production application. 請注意,來源資料不一定要位於 SQL Server 上;您可以使用任何可讀取 IDataReader 或載入至的資料來源 DataTableNote that the source data does not have to be located on SQL Server; you can use any data source that can be read to an IDataReader or loaded to a DataTable.

重要

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. 這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。This code is provided to demonstrate the syntax for using SqlBulkCopy only. 如果來源和目的地資料表位於相同的 SQL Server 實例中,則使用 Transact-sql INSERT ... SELECT 語句來複製資料會更容易且更快速。If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT ... SELECT statement to copy the data.

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 ,所以連接會在實例關閉後保持開啟狀態 SqlBulkCopyBecause the connection is already open when the SqlBulkCopy instance is initialized, the connection remains open after the SqlBulkCopy instance is closed.

如果 connection 引數為 null, ArgumentNullException 就會擲回。If the connection argument is null, an ArgumentNullException is thrown.

另請參閱

適用於

SqlBulkCopy(String)

根據所提供的 connectionString 初始化並開啟 SqlConnection 的新執行個體。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 這個建構函式會使用 SqlConnection 初始化 SqlBulkCopy 類別的新執行個體。The constructor uses the SqlConnection to initialize a new instance of the SqlBulkCopy class.

public:
 SqlBulkCopy(System::String ^ connectionString);
public SqlBulkCopy (string connectionString);
new System.Data.SqlClient.SqlBulkCopy : string -> System.Data.SqlClient.SqlBulkCopy
Public Sub New (connectionString As String)

參數

connectionString
String

字串,定義為 SqlBulkCopy 執行個體使用而開啟的連接。The string defining the connection that will be opened for use by the SqlBulkCopy instance. 如果您的連接字串不使用 Integrated Security = true,您可以使用 SqlBulkCopy(SqlConnection)SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)SqlCredential 傳遞使用者 ID 和密碼,比起在連接字串中將使用者 ID 和密碼指定為文字更安全。If your connection string does not use Integrated Security = true, you can use SqlBulkCopy(SqlConnection) or SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) and SqlCredential to pass the user ID and password more securely than by specifying the user ID and password as text in the connection string.

範例

下列主控台應用程式示範如何使用指定為字串的連接來大量載入資料。The following console application demonstrates how to bulk load data by using a connection specified as a string. 當實例關閉時,會自動關閉連接 SqlBulkCopyThe connection is automatically closed when the SqlBulkCopy instance is closed.

在此範例中,來源資料會先從 SQL Server 資料表讀取至 SqlDataReader 實例。In this example, the source data is first read from a SQL Server table to a SqlDataReader instance. 來源資料不一定要位於 SQL Server 上;您可以使用任何可讀取 IDataReader 或載入至的資料來源 DataTableThe source data does not have to be located on SQL Server; you can use any data source that can be read to an IDataReader or loaded to a DataTable.

重要

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. 這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。This code is provided to demonstrate the syntax for using SqlBulkCopy only. 如果來源和目的地資料表位於相同的 SQL Server 實例中,則使用 Transact-sql INSERT ... SELECT 語句來複製資料會更容易且更快速。If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT ... SELECT statement to copy the data.

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

備註

在大量複製作業結束時,會自動關閉連接。The connection is automatically closed at the end of the bulk copy operation.

如果 connectionString 為 null, ArgumentNullException 則會擲回。If connectionString is null, an ArgumentNullException is thrown. 如果 connectionString 是空字串, ArgumentException 則會擲回。If connectionString is an empty string, an ArgumentException is thrown.

另請參閱

適用於

SqlBulkCopy(String, SqlBulkCopyOptions)

基於已提供的 connectionString,初始化和開啟 SqlConnection 的新執行個體。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 建構函式會使用 SqlConnection,初始化 SqlBulkCopy 類別的新執行個體。The constructor uses that SqlConnection to initialize a new instance of the SqlBulkCopy class. SqlConnection 執行個體會根據 copyOptions 參數中所提供的選項操作。The SqlConnection instance behaves according to options supplied in the copyOptions parameter.

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.SqlBulkCopy
Public Sub New (connectionString As String, copyOptions As SqlBulkCopyOptions)

參數

connectionString
String

字串,定義為 SqlBulkCopy 執行個體使用而開啟的連接。The string defining the connection that will be opened for use by the SqlBulkCopy instance. 如果您的連接字串不使用 Integrated Security = true,您可以使用 SqlBulkCopy(SqlConnection)SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)SqlCredential 傳遞使用者識別碼和密碼,這種方式比在連接字串中以文字指定使用者識別碼和密碼來得更加安全。If your connection string does not use Integrated Security = true, you can use SqlBulkCopy(SqlConnection) or SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) and SqlCredential to pass the user ID and password more securely than by specifying the user ID and password as text in the connection string.

copyOptions
SqlBulkCopyOptions

SqlBulkCopyOptions 列舉的值組合,會決定要將哪些資料來源資料列複製到目的地資料表。A combination of values from the SqlBulkCopyOptions enumeration that determines which data source rows are copied to the destination table.

範例

下列主控台應用程式示範如何使用指定為字串的連接來執行大量載入。The following console application demonstrates how to perform a bulk load by using a connection specified as a string. 當您載入目的地資料表時,選項會設定為使用來源資料表之 identity 資料行中的值。An option is set to use the value in the identity column of the source table when you load the destination table. 在此範例中,來源資料會先從 SQL Server 資料表讀取至 SqlDataReader 實例。In this example, the source data is first read from a SQL Server table to a SqlDataReader instance. 來源資料表和目的地資料表各自包含識別欄位。The source table and destination table each include an Identity column. 根據預設,會針對加入的每個資料列,在目的地資料表中產生Identity資料行的新值。By default, a new value for the Identity column is generated in the destination table for each row added. 在此範例中,當開啟連接時,會設定一個選項,強制大量載入進程改為使用來源資料表的識別值。In this example, an option is set when the connection is opened that forces the bulk load process to use the Identity values from the source table instead. 若要查看選項如何變更大量載入的運作方式,請使用 dbo 執行範例 。BulkCopyDemoMatchingColumns資料表是空的。To see how the option changes the way the bulk load works, run the sample with the dbo.BulkCopyDemoMatchingColumns table empty. 從來源載入所有資料列。All rows load from the source. 然後再次執行範例,而不清空資料表。Then run the sample again without emptying the table. 系統會擲回例外狀況,而且程式碼會將訊息寫入主控台,通知您因為 primary key 條件約束違規而無法加入資料列。An exception is thrown and the code writes a message to the console notifying you that rows weren't added because of primary key constraint violations.

重要

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. 這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。This code is provided to demonstrate the syntax for using SqlBulkCopy only. 如果來源和目的地資料表位於相同的 SQL Server 實例中,則使用 Transact-sql INSERT ... SELECT 語句來複製資料會更容易且更快速。If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT ... SELECT statement to copy the data.

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

備註

您可以在主題中取得所有大量複製選項的詳細資訊 SqlBulkCopyOptionsYou can obtain detailed information about all the bulk copy options in the SqlBulkCopyOptions topic.

另請參閱

適用於

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用已提供之現有開啟的 SqlBulkCopy 執行個體,初始化 SqlConnection 的新執行個體。Initializes a new instance of the SqlBulkCopy class using the supplied existing open instance of SqlConnection. SqlBulkCopy 執行個體會根據 copyOptions 參數中提供的選項進行運作。The SqlBulkCopy instance behaves according to options supplied in the copyOptions parameter. 如果提供了非 null 的 SqlTransaction,則複製作業會在該交易執行。If a non-null SqlTransaction is supplied, the copy operations will be performed within that transaction.

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.SqlBulkCopy
Public Sub New (connection As SqlConnection, copyOptions As SqlBulkCopyOptions, externalTransaction As SqlTransaction)

參數

connection
SqlConnection

已開啟的 SqlConnection 執行個體,用於執行大量複製作業。The already open SqlConnection instance that will be used to perform the bulk copy. 如果您的連接字串不使用 Integrated Security = true,您便可以使用 SqlCredential 來傳遞使用者 ID 和密碼,這種方式比在連接字串中以文字指定使用者 ID 和密碼來得更加安全。If your connection string does not use Integrated Security = true, you can use SqlCredential to pass the user ID and password more securely than by specifying the user ID and password as text in the connection string.

copyOptions
SqlBulkCopyOptions

SqlBulkCopyOptions 列舉的值組合,會決定要將哪些資料來源資料列複製到目的地資料表。A combination of values from the SqlBulkCopyOptions enumeration that determines which data source rows are copied to the destination table.

externalTransaction
SqlTransaction

現有的 SqlTransaction 執行個體,其下會發生大量複製。An existing SqlTransaction instance under which the bulk copy will occur.

備註

如果選項包含 UseInternalTransaction ,而且 externalTransaction 引數不是 null,則會擲回InvalidArgumentExceptionIf options include UseInternalTransaction and the externalTransaction argument is not null, an InvalidArgumentException is thrown.

如需示範如何 SqlBulkCopy 在交易中使用的範例,請參閱交易和大量複製作業For examples demonstrating how to use SqlBulkCopy in a transaction, see Transaction and Bulk Copy Operations.

另請參閱

適用於