Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Bulksgewijs kopiëren kan worden uitgevoerd als geïsoleerde bewerkingen of als onderdeel van een transactie met meerdere stappen. Met deze laatste optie kunt u meer dan één bulkkopiebewerking uitvoeren binnen dezelfde transactie, en andere databasebewerkingen uitvoeren, zoals invoegingen, updates en verwijderingen, terwijl u de hele transactie nog steeds kunt doorvoeren of terugdraaien.
Standaard wordt een bulksgewijze kopieerbewerking uitgevoerd als een geïsoleerde bewerking. De bulk kopieerbewerking vindt plaats op een niet-transactiegebonden manier, zonder de mogelijkheid om deze ongedaan te maken. Als u een bulkkopie of een deel ervan wilt terugdraaien wanneer er een fout optreedt, kunt u het volgende stappen ondernemen.
SqlBulkCopyEen beheerde transactie gebruiken
De bulksgewijze kopieerbewerking uitvoeren binnen een bestaande transactie
Zelf inschrijven in een System.TransactionsTransaction.
Een niet-getransacteerde bulkkopiebewerking uitvoeren
In de volgende consoletoepassing ziet u wat er gebeurt wanneer een bulkkopiebewerking zonder transactie halverwege een fout tegenkomt.
In het voorbeeld bevatten de brontabel en doeltabel elk een Identity kolom met de naam ProductID. De code bereidt eerst de doeltabel voor door alle rijen te verwijderen en vervolgens één rij in te voegen waarvan de product-id bekend is in de brontabel. Standaard wordt een nieuwe waarde voor de Identity kolom gegenereerd in de doeltabel voor elke toegevoegde rij. In dit voorbeeld wordt bij het openen van de verbinding een optie ingesteld die het bulklaadproces dwingt om de Identity waarden uit de brontabel te gebruiken.
De bulksgewijze kopieerbewerking wordt uitgevoerd met de BatchSize eigenschap ingesteld op 10. Wanneer de bewerking de ongeldige rij tegenkomt, wordt er een uitzondering gegenereerd. In dit eerste voorbeeld wordt de bulksgewijze kopieerbewerking niet uitgevoerd. Alle batches die tot het punt van de fout worden gekopieerd, worden vastgelegd. De batch met de dubbele sleutel wordt teruggedraaid en de bulkkopiebewerking wordt gestopt voordat de resterende batches worden verwerkt.
Opmerking
Dit voorbeeld wordt alleen uitgevoerd als u de werktabellen hebt gemaakt, zoals beschreven in de voorbeeldinstallatie voor bulksgewijs kopiëren. Deze code wordt verstrekt om alleen de syntaxis voor het gebruik van SqlBulkCopy te demonstreren. Als de bron- en doeltabellen zich in hetzelfde SQL Server-exemplaar bevinden, is het eenvoudiger en sneller om een Transact-SQL-instructie INSERT ... SELECT te gebruiken om de gegevens te kopiëren.
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;";
}
}
Een toegewezen bulkkopiebewerking uitvoeren in een transactie
Standaard is een bulkkopiebewerking een eigen transactie. Wanneer u een toegewezen bulkkopiebewerking wilt uitvoeren, maakt u een exemplaar van SqlBulkCopy met een verbindingsreeks of gebruikt u een bestaand SqlConnection object zonder actieve transactie. Met de bulkkopiebewerking wordt in elk scenario een transactie gemaakt en vervolgens de transactie doorgevoerd of teruggedraaid.
U kunt expliciet de UseInternalTransaction optie in de SqlBulkCopy klasseconstructor opgeven om een bulkkopiebewerking uit te voeren in een eigen transactie. Elke batch van de bewerking wordt uitgevoerd binnen een afzonderlijke transactie.
Opmerking
Aangezien verschillende batches in verschillende transacties worden uitgevoerd, zullen als er een fout optreedt tijdens de bulkkopiebewerking, alle rijen in de huidige batch worden teruggedraaid, maar rijen uit vorige batches blijven in de database.
De volgende consoletoepassing is vergelijkbaar met het vorige voorbeeld, met één uitzondering: In dit voorbeeld beheert de bulkkopiebewerking zijn eigen transacties. Alle batches die tot het punt van de fout worden gekopieerd, worden vastgelegd. De batch met de dubbele sleutel wordt teruggedraaid en de bulkkopiebewerking wordt gestopt voordat de resterende batches worden verwerkt.
Belangrijk
Dit voorbeeld wordt alleen uitgevoerd als u de werktabellen hebt gemaakt, zoals beschreven in de voorbeeldinstallatie voor bulksgewijs kopiëren. Deze code wordt verstrekt om alleen de syntaxis voor het gebruik van SqlBulkCopy te demonstreren. Als de bron- en doeltabellen zich in hetzelfde SQL Server-exemplaar bevinden, is het eenvoudiger en sneller om een Transact-SQL-instructie INSERT ... SELECT te gebruiken om de gegevens te kopiëren.
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;";
}
}
Bestaande transacties gebruiken
U kunt een bestaand SqlTransaction object opgeven als parameter in een SqlBulkCopy constructor. In dit geval wordt de bulkkopiebewerking uitgevoerd in de bestaande transactie en wordt er geen wijziging aangebracht in de transactiestatus. De bewerking wordt niet doorgevoerd of afgebroken. Met deze methode kan een toepassing de bulksgewijze kopieerbewerking opnemen in een transactie met andere databasebewerkingen. Als u echter geen object opgeeft SqlTransaction en een null-verwijzing doorgeeft en de verbinding een actieve transactie heeft, wordt er een uitzondering gegenereerd.
Als u de volledige bulkkopiebewerking wilt terugdraaien omdat er een fout optreedt of als de bulkkopie moet worden uitgevoerd als onderdeel van een groter proces dat kan worden teruggedraaid, kunt u een SqlTransaction object aan de SqlBulkCopy constructor opgeven.
De volgende consoletoepassing is vergelijkbaar met het eerste voorbeeld (niet-transactie), met één uitzondering: in dit voorbeeld is de bulkkopiebewerking opgenomen in een grotere, externe transactie. Wanneer de fout bij schending van de primaire sleutel optreedt, wordt de hele transactie teruggedraaid en worden er geen rijen toegevoegd aan de doeltabel.
Belangrijk
Dit voorbeeld wordt alleen uitgevoerd als u de werktabellen hebt gemaakt, zoals beschreven in de voorbeeldinstallatie voor bulksgewijs kopiëren. Deze code wordt verstrekt om alleen de syntaxis voor het gebruik van SqlBulkCopy te demonstreren. Als de bron- en doeltabellen zich in hetzelfde SQL Server-exemplaar bevinden, is het eenvoudiger en sneller om een Transact-SQL-instructie INSERT ... SELECT te gebruiken om de gegevens te kopiëren.
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;";
}
}