Operasi transaksi dan penyalinan massal

Mengunduh ADO.NET

Operasi penyalinan massal dapat dilakukan sebagai operasi terisolasi atau sebagai bagian dari transaksi beberapa langkah. Opsi terakhir ini memungkinkan Anda melakukan lebih dari satu operasi penyalinan massal dalam transaksi yang sama, serta melakukan operasi database lainnya seperti penyisipan, pembaruan, dan penghapusan, sambil tetap dapat menerapkan atau mengembalikan seluruh transaksi.

Secara default, operasi penyalinan massal dilakukan sebagai operasi terisolasi. Operasi penyalinan massal terjadi dengan cara yang tidak ditransaksikan, tanpa kesempatan untuk memutarnya kembali. Jika Anda perlu mengembalikan semua atau sebagian salinan massal saat terjadi kesalahan, Anda dapat:

• SqlBulkCopyGunakan transaksi -managed

• Lakukan operasi penyalinan massal dalam transaksi yang ada

• Daftarkan dalam System.TransactionsTransaction.

Melakukan operasi penyalinan massal yang tidak ditransaksikan

Aplikasi Konsol berikut menunjukkan apa yang terjadi ketika operasi penyalinan massal yang tidak ditransaksikan mengalami kesalahan sebagian melalui operasi.

Dalam contoh, tabel sumber dan tabel tujuan masing-masing menyertakan kolom Identitybernama ProductID. Kode pertama-tama menyiapkan tabel tujuan dengan menghapus semua baris lalu menyisipkan satu baris yang ProductID-nya diketahui ada dalam tabel sumber. Secara default, nilai baru untuk kolom Identity dihasilkan dalam tabel tujuan untuk setiap baris yang ditambahkan. Dalam contoh ini, opsi diatur saat koneksi dibuka yang memaksa proses pemuatan massal untuk menggunakan nilai Identity dari tabel sumber sebagai gantinya.

Operasi penyalinan massal dijalankan dengan properti BatchSize diatur ke 10. Ketika operasi menemukan baris yang tidak valid, pengecualian akan dilemparkan. Dalam contoh pertama ini, operasi penyalinan massal tidak ditransaksikan. Semua batch yang disalin hingga titik kesalahan dilakukan. Batch yang berisi kunci duplikat digulung balik, dan operasi penyalinan massal dihentikan sebelum memproses batch yang tersisa.

Catatan

Sampel ini tidak akan berjalan kecuali Anda telah membuat tabel kerja seperti yang dijelaskan dalam penyiapan contoh penyalinan massal. Kode ini disediakan untuk mendemonstrasikan sintaks untuk menggunakan SqlBulkCopy saja. Jika tabel sumber dan tujuan terletak di instans SQL Server yang sama, akan lebih mudah dan lebih cepat untuk menggunakan pernyataan INSERT ... SELECT Transact-SQL untuk menyalin data.

using Microsoft.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();

            //  Delete all from the destination table.         
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key         
            //  when all rows from source are bulk copied.         
            //  Note that this technique will only be successful in          
            //  illustrating the point if a row with ProductID = 446           
            //  exists in the AdventureWorks Production.Products table.          
            //  If you have made changes to the data in this table, change         
            //  the SQL statement in the code to add a ProductID that         
            //  does exist in your version of the Production.Products         
            //  table. Choose any ProductID in the middle of the table         
            //  (not first or last row) to best illustrate the result.         
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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 the KeepIdentity option. 
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    // Print the number of rows processed using the 
                    // RowsCopied property.
                    Console.WriteLine("{0} rows were processed.",
                        bulkCopy.RowsCopied);
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            // Note that for this scenario, the value will 
            // not be equal to the RowsCopied property.
            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;";
    }
}

Melakukan operasi penyalinan massal khusus dalam transaksi

Secara default, operasi penyalinan massal adalah transaksinya sendiri. Saat Anda ingin melakukan operasi penyalinan SqlBulkCopy massal khusus, buat instans dengan string koneksi, atau gunakan objek yang ada SqlConnection tanpa transaksi aktif. Operasi penyalinan massal membuat transaksi di setiap skenario lalu menerapkan atau menggulung balik transaksi.

Anda dapat secara eksplisit menentukan UseInternalTransaction opsi di SqlBulkCopy konstruktor kelas untuk menjalankan operasi penyalinan massal dalam transaksinya sendiri. Setiap batch operasi akan dijalankan dalam transaksi terpisah.

Catatan

Karena batch yang berbeda dijalankan dalam transaksi yang berbeda, jika terjadi kesalahan selama operasi penyalinan massal, semua baris dalam batch saat ini akan digulung balik, tetapi baris dari batch sebelumnya akan tetap berada dalam database.

Aplikasi konsol berikut mirip dengan contoh sebelumnya, dengan satu pengecualian: Dalam contoh ini, operasi penyalinan massal mengelola transaksinya sendiri. Semua batch yang disalin hingga titik kesalahan dilakukan. Batch yang berisi kunci duplikat digulung balik, dan operasi penyalinan massal dihentikan sebelum memproses batch yang tersisa.

Penting

Sampel ini tidak akan berjalan kecuali Anda telah membuat tabel kerja seperti yang dijelaskan dalam penyiapan contoh penyalinan massal. Kode ini disediakan untuk mendemonstrasikan sintaks untuk menggunakan SqlBulkCopy saja. Jika tabel sumber dan tujuan terletak di instans SQL Server yang sama, akan lebih mudah dan lebih cepat untuk menggunakan pernyataan INSERT ... SELECT Transact-SQL untuk menyalin data.

using Microsoft.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();

            //  Delete all from the destination table.         
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key         
            //  when all rows from source are bulk copied.         
            //  Note that this technique will only be successful in          
            //  illustrating the point if a row with ProductID = 446           
            //  exists in the AdventureWorks Production.Products table.          
            //  If you have made changes to the data in this table, change         
            //  the SQL statement in the code to add a ProductID that         
            //  does exist in your version of the Production.Products         
            //  table. Choose any ProductID in the middle of the table         
            //  (not first or last row) to best illustrate the result.         
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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.
            // Note that when specifying the UseInternalTransaction
            // option, you cannot also specify an external transaction.
            // Therefore, you must use the SqlBulkCopy construct that
            // requires a string for the connection, rather than an
            // existing SqlConnection object. 
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity |
                       SqlBulkCopyOptions.UseInternalTransaction))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    // Print the number of rows processed using the 
                    // RowsCopied property.
                    Console.WriteLine("{0} rows were processed.",
                        bulkCopy.RowsCopied);
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            // Note that for this scenario, the value will 
            // not be equal to the RowsCopied property.
            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;";
    }
}

Menggunakan transaksi yang ada

Anda dapat menentukan objek SqlTransaction yang ada sebagai parameter dalam konstruktor SqlBulkCopy. Dalam situasi ini, operasi penyalinan massal dilakukan dalam transaksi yang ada, dan tidak ada perubahan yang dilakukan pada status transaksi - tidak dilakukan atau dibatalkan. Metode ini memungkinkan aplikasi untuk menyertakan operasi penyalinan massal dalam transaksi dengan operasi database lainnya. Namun, jika Anda tidak menentukan objek SqlTransaction dan meneruskan referensi null, dan koneksi memiliki transaksi aktif, pengecualian akan dilemparkan.

Jika Anda perlu mengembalikan seluruh operasi penyalinan massal karena terjadi kesalahan, atau jika salinan massal harus dijalankan sebagai bagian dari proses yang lebih besar yang dapat digulung balik, Anda dapat memberikan objek SqlTransaction ke konstruktor SqlBulkCopy.

Aplikasi konsol berikut mirip dengan contoh pertama (tidak ditransaksikan), dengan satu pengecualian: dalam contoh ini, operasi penyalinan massal disertakan dalam transaksi eksternal yang lebih besar. Ketika kesalahan pelanggaran kunci primer terjadi, seluruh transaksi digulung balik dan tidak ada baris yang ditambahkan ke tabel tujuan.

Penting

Sampel ini tidak akan berjalan kecuali Anda telah membuat tabel kerja seperti yang dijelaskan dalam penyiapan contoh penyalinan massal. Kode ini disediakan untuk mendemonstrasikan sintaks untuk menggunakan SqlBulkCopy saja. Jika tabel sumber dan tujuan terletak di instans SQL Server yang sama, akan lebih mudah dan lebih cepat untuk menggunakan pernyataan INSERT ... SELECT Transact-SQL untuk menyalin data.

using Microsoft.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();

            //  Delete all from the destination table.         
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key         
            //  when all rows from source are bulk copied.         
            //  Note that this technique will only be successful in          
            //  illustrating the point if a row with ProductID = 446           
            //  exists in the AdventureWorks Production.Products table.          
            //  If you have made changes to the data in this table, change         
            //  the SQL statement in the code to add a ProductID that         
            //  does exist in your version of the Production.Products         
            //  table. Choose any ProductID in the middle of the table         
            //  (not first or last row) to best illustrate the result.         
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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 inside the transaction. 
            using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();

                using (SqlTransaction transaction =
                           destinationConnection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                               destinationConnection, SqlBulkCopyOptions.KeepIdentity,
                               transaction))
                    {
                        bulkCopy.BatchSize = 10;
                        bulkCopy.DestinationTableName =
                            "dbo.BulkCopyDemoMatchingColumns";

                        // Write from the source to the destination.
                        // This should fail with a duplicate key error.
                        try
                        {
                            bulkCopy.WriteToServer(reader);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            // Print the number of rows processed using the 
                            // RowsCopied property.
                            Console.WriteLine("{0} rows were processed.",
                                bulkCopy.RowsCopied);
                            Console.WriteLine(ex.Message);
                            transaction.Rollback();
                        }
                        finally
                        {
                            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;";
    }
}

Langkah berikutnya

• Operasi penyalinan massal di SQL Server