Opérations de transaction et de copie en bloc

Les opérations de copie en bloc peuvent être effectuées comme des opérations isolées ou dans le cadre d'une transaction à plusieurs étapes. Cette dernière option vous permet d’effectuer plusieurs opérations de copie en bloc au sein de la même transaction, ainsi que d’effectuer d’autres opérations de base de données (telles que des insertions, des mises à jour et des suppressions) tout en étant en mesure de valider ou de restaurer l’intégralité de la transaction.

Par défaut, une opération de copie en bloc s’exécute sans transaction que le client puisse annuler. Pour restaurer tout ou partie d’une copie après une erreur, spécifiez SqlBulkCopyOptions.UseInternalTransaction pour les transactions par lot, utilisez un(e) Transaction existant(e), ou inscrivez la connexion dans SqlTransaction.

Exécution d’une opération de copie en bloc non transactionnelle

L’application console suivante montre ce qui se passe quand une opération de copie en bloc non transactionnelle rencontre une erreur au cours de l’opération.

Dans l’exemple, la table source et la table de destination incluent chacune une Identity colonne nommée ProductID. Le code prépare d’abord la table de destination en supprimant toutes les lignes, puis en insérant une seule ligne dont ProductID l’existence est connue dans la table source. Par défaut, une nouvelle valeur pour la colonne Identity est générée dans la table de destination pour chaque ligne ajoutée. Dans cet exemple, quand la connexion est ouverte, une option est définie qui oblige le processus de chargement en bloc à utiliser à la place les valeurs Identity de la table source.

L’opération de copie en bloc est exécutée avec la propriété BatchSize définie avec la valeur 10. Quand l'opération rencontre la ligne non valide, une exception est générée. Dans ce premier exemple, l'opération de copie en bloc est non transactionnelle. Tous les lots copiés avant l'erreur sont validés. Le lot contenant la clé dupliquée est restauré et l'opération de copie en bloc est interrompue avant le traitement des autres lots.

var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
           new(connectionString))
{
    sourceConnection.Open();

    //  Delete all from the destination table.
    SqlCommand commandDelete = new()
    {
        Connection = sourceConnection,
        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()
    {
        Connection = sourceConnection,
        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(
        "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
        sourceConnection);
    long countStart = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Starting row count = {countStart}");

    //  Get data from the source table as a SqlDataReader.
    SqlCommand commandSourceData = new(
        "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(
               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)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader.Close();
        }
    }

    // Perform a final count on the destination
    // table to see how many rows were added.
    long countEnd = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Ending row count = {countEnd}");
    Console.WriteLine($"{countEnd - countStart} rows were added.");
    Console.WriteLine("Press Enter to finish.");
    Console.ReadLine();
}

Imports Microsoft.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As 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.
            Dim commandInsert As 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.
            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 the KeepIdentity option.
            Using bulkCopy As SqlBulkCopy = 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 already been copied.
                Try
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                Finally
                    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
        Throw New NotImplementedException()
    End Function
End Module

Exécution d’une opération de copie en bloc dédiée dans une transaction

Pour autoriser SqlBulkCopy la gestion d’une transaction distincte pour chaque lot, spécifiez UseInternalTransaction. Utilisez une chaîne de connexion ou une connexion existante sans transaction active.

Transmettez cette option au constructeur SqlBulkCopy. La copie effectue un commit de chaque lot terminé indépendamment plutôt que de traiter toute l’opération comme une transaction.

L’application console suivante est similaire à l’exemple précédent, à une exception près : dans cet exemple, l’opération de copie en bloc gère ses propres transactions. Tous les lots copiés avant l'erreur sont validés. Le lot contenant la clé dupliquée est restauré et l'opération de copie en bloc est interrompue avant le traitement des autres lots.

var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
           new(connectionString))
{
    sourceConnection.Open();

    //  Delete all from the destination table.
    SqlCommand commandDelete = new()
    {
        Connection = sourceConnection,
        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()
    {
        Connection = sourceConnection,
        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(
        "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
        sourceConnection);
    long countStart = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Starting row count = {countStart}");

    //  Get data from the source table as a SqlDataReader.
    SqlCommand commandSourceData = new(
        "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(
               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)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader.Close();
        }
    }

    // Perform a final count on the destination
    // table to see how many rows were added.
    long countEnd = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Ending row count = {countEnd}");
    Console.WriteLine($"{countEnd - countStart} rows were added.");
    Console.WriteLine("Press Enter to finish.");
    Console.ReadLine();
}

Imports Microsoft.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As 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.
            Dim commandInsert As 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.
            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.
            ' 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 bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString, _
             SqlBulkCopyOptions.UseInternalTransaction Or _
             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 already been copied.
                Try
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                Finally
                    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
        Throw New NotImplementedException()
    End Function
End Module

Utilisation des transactions existantes

Transmettez l’objet existant SqlTransaction au SqlBulkCopy constructeur pour inclure la copie dans cette transaction. La copie en bloc ne valide pas ou n’annule pas la transaction. Votre application contrôle la transaction, y compris toute autre opération de base de données qu’elle comprend. Si la connexion a une transaction active, mais que vous transmettez une référence de transaction Null, le fournisseur lève une exception.

Pour annuler l’intégralité de la copie après une erreur ou l’inclure dans une opération plus grande que vous pouvez annuler, fournissez l’objet SqlTransaction au constructeur SqlBulkCopy.

L’application console suivante est similaire au premier exemple (non transactionnelle), à une exception près : dans cet exemple, l’opération de copie en bloc est incluse dans une transaction externe plus vaste. Quand l'erreur de violation de clé primaire se produit, toute la transaction est annulée et aucune ligne n'est ajoutée à la table de destination.

var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
           new(connectionString))
{
    sourceConnection.Open();

    //  Delete all from the destination table.
    SqlCommand commandDelete = new()
    {
        Connection = sourceConnection,
        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()
    {
        Connection = sourceConnection,
        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(
        "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
        sourceConnection);
    long countStart = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Starting row count = {countStart}");

    //  Get data from the source table as a SqlDataReader.
    SqlCommand commandSourceData = new(
        "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(connectionString))
    {
        destinationConnection.Open();

        using (SqlTransaction transaction =
                   destinationConnection.BeginTransaction())
        {
            using (SqlBulkCopy bulkCopy = new(
                       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)
                {
                    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 = Convert.ToInt32(
        commandRowCount.ExecuteScalar());
    Console.WriteLine($"Ending row count = {countEnd}");
    Console.WriteLine($"{countEnd - countStart} rows were added.");
    Console.WriteLine("Press Enter to finish.");
    Console.ReadLine();
}

Imports Microsoft.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As 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.
            Dim commandInsert As 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.
            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 inside the transaction.
            Using destinationConnection As SqlConnection = _
               New SqlConnection(connectionString)
                destinationConnection.Open()

                Using transaction As SqlTransaction = _
                  destinationConnection.BeginTransaction()

                    Using bulkCopy As SqlBulkCopy = 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 ex As Exception
                            Console.WriteLine(ex.Message)
                            transaction.Rollback()

                        Finally
                            reader.Close()
                        End Try
                    End Using
                End Using
            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
        Throw New NotImplementedException()
    End Function
End Module

Voir aussi

• Opérations de copie en bloc dans SQL Server
• Vue d'ensemble d’ADO.NET