Operace transakcí a hromadného kopírování

Stáhnout ADO.NET

Operace hromadného kopírování lze provádět jako izolované operace nebo jako součást transakce s více kroky. Tato druhá možnost umožňuje provádět více než jednu operaci hromadného kopírování ve stejné transakci, stejně jako provádět další databázové operace, jako jsou vložení, aktualizace a odstranění, zatímco stále můžete potvrdit nebo vrátit zpět celou transakci.

Ve výchozím nastavení se operace hromadného kopírování provádí jako izolovaná operace. Operace hromadného kopírování probíhá bez transakce bez příležitosti k vrácení zpět. Pokud potřebujete vrátit zpět celou hromadnou kopii nebo její část, když dojde k chybě, můžete:

• Použijte transakci spravovanou pomocí SqlBulkCopy.

• Provedení operace hromadného kopírování v rámci existující transakce

• Zaregistruj se do System.TransactionsTransaction.

Provádění neprovedené operace hromadného kopírování

Následující konzolová aplikace ukazuje, co se stane, když hromadná kopírovací operace bez transakcí narazí na chybu v průběhu operace.

V příkladu zdrojová tabulka a cílová tabulka obsahují sloupec Identity s názvem Idproduktu. Kód nejprve připraví cílovou tabulku odstraněním všech řádků a následným vložením jednoho řádku, jehož IDproduktu je známo, že ve zdrojové tabulce existuje. Ve výchozím nastavení se v cílové tabulce pro každý přidaný řádek vygeneruje nová hodnota sloupce Identity. V tomto příkladu se při otevření připojení nastaví možnost, která vynutí proces hromadného načítání dat, aby použil hodnoty Identity ze zdrojové tabulky místo nich.

Operace hromadného kopírování se provede s vlastností BatchSize nastavenou na hodnotu 10. Když operace narazí na neplatný řádek, vyvolá se výjimka. V tomto prvním příkladu je operace hromadného kopírování provedena mimo transakci. Všechny dávky, které jsou zkopírovány až do bodu chyby, jsou potvrzeny. Dávka obsahující duplicitní klíč se vrátí zpět a operace hromadného kopírování se zastaví, než zpracuje všechny zbývající dávky.

Poznámka:

Tato ukázka se nespustí, pokud jste nevytvořili pracovní tabulky, jak je popsáno v nastavení ukázky hromadného kopírování. Tento kód je k dispozici k předvedení syntaxe pouze pro použití sqlBulkCopy . Pokud jsou zdrojové a cílové tabulky umístěny ve stejné instanci SQL Serveru, je jednodušší a rychlejší použít příkaz Transact-SQL INSERT ... SELECT ke zkopírování dat.

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;";
    }
}

Provedení vyhrazené operace hromadného kopírování v transakci

Ve výchozím nastavení je operace hromadného kopírování vlastní transakcí. Pokud chcete provést vyhrazenou operaci hromadného kopírování, vytvořte instanci SqlBulkCopy s připojovacím řetězcem nebo použijte existující SqlConnection objekt bez aktivní transakce. Operace hromadného kopírování vytvoří transakci v každém scénáři a pak potvrdí nebo vrátí zpět transakci.

V konstruktoru třídy UseInternalTransaction můžete explicitně zadat volbu SqlBulkCopy k provedení operace hromadného kopírování ve vlastní transakci. Každá dávka operace se provede v rámci samostatné transakce.

Poznámka:

Vzhledem k tomu, že různé dávky se provádějí v různých transakcích, pokud dojde k chybě během operace hromadného kopírování, všechny řádky v aktuální dávce budou vráceny zpět, ale řádky z předchozích dávek zůstanou v databázi.

Následující konzolová aplikace je podobná předchozímu příkladu s jednou výjimkou: V tomto příkladu operace hromadného kopírování spravuje své vlastní transakce. Všechny dávky, které jsou zkopírovány až do bodu chyby, jsou potvrzeny. Dávka obsahující duplicitní klíč se vrátí zpět a operace hromadného kopírování se zastaví, než zpracuje všechny zbývající dávky.

Důležité

Tato ukázka se nespustí, pokud jste nevytvořili pracovní tabulky, jak je popsáno v nastavení ukázky hromadného kopírování. Tento kód je k dispozici k předvedení syntaxe pouze pro použití sqlBulkCopy . Pokud jsou zdrojové a cílové tabulky umístěny ve stejné instanci SQL Serveru, je jednodušší a rychlejší použít příkaz Transact-SQL INSERT ... SELECT ke zkopírování dat.

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;";
    }
}

Použití existujících transakcí

Existující SqlTransaction objekt můžete zadat jako parametr v konstruktoru SqlBulkCopy. V této situaci se operace hromadného kopírování provádí v existující transakci a ve stavu transakce se neprovádí žádná změna – transakce není potvrzena ani přerušena. Tato metoda umožňuje aplikaci zahrnout operaci hromadného kopírování do transakce s jinými databázovými operacemi. Pokud však nezadáte SqlTransaction objekt nebo předáte hodnotu null a připojení má aktivní transakci, vyvolá se výjimka.

Pokud potřebujete vrátit zpět celou operaci hromadného kopírování, protože dojde k chybě, nebo pokud by se hromadná kopie měla provést jako součást většího procesu, který lze vrátit zpět, můžete poskytnout SqlTransaction objekt SqlBulkCopy konstruktoru.

Následující konzolová aplikace je podobná prvnímu příkladu (bez transakce), s jednou výjimkou: v tomto příkladu je operace hromadného kopírování zahrnuta do větší externí transakce. Když dojde k chybě porušení primárního klíče, celá transakce se vrátí zpět a do cílové tabulky se nepřidají žádné řádky.

Důležité

Tato ukázka se nespustí, pokud jste nevytvořili pracovní tabulky, jak je popsáno v nastavení ukázky hromadného kopírování. Tento kód je k dispozici k předvedení syntaxe pouze pro použití sqlBulkCopy . Pokud jsou zdrojové a cílové tabulky umístěny ve stejné instanci SQL Serveru, je jednodušší a rychlejší použít příkaz Transact-SQL INSERT ... SELECT ke zkopírování dat.

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;";
    }
}

Související obsah

• Operace hromadného kopírování na SQL Serveru