I am facing a problem with sqlbulk . I have a list of PersonModel and trying to insert or save the data into SQL server but i am getting an exeption private static List PersonModels { get; set; } AsDataTable is an Extention Method its throwing an exception on transaction.Commit().... System.InvalidOperationException "This SqlTransaction has completed; it is no longer usable " using (SqlConnection connection = new SqlConnection(CS)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)) { bulkCopy.BatchSize = 100; bulkCopy.DestinationTableName = "dbo.Person" ; try { bulkCopy.WriteToServer(PersonModels.AsDataTable()); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine( "Rollback Exception Type: {0}" , ex.GetType()); Console.WriteLine( " Message: {0}" , ex.Message); } } transaction.Commit(); }

Hey check for any long running processes executed from your app against the DB. For example you may be calling a stored procedure or query which does not have enough time to finish. Also check the below link https://www.c-sharpcorner.com/UploadFile/8a67c0/how-to-handle-no-longer-usable-sql-transaction-in-C-Sharp/

Answer

This SqlTransaction has completed; it is no longer usable ?

Abdalla Omran

2.4k

I am facing a problem with sqlbulk .

I have a list of PersonModel and trying to insert or save the data into SQL server but i am getting an exeption

private static List<PersonModel> PersonModels { get; set; }

AsDataTable is an Extention Method

its throwing an exception on transaction.Commit().... System.InvalidOperationException "This SqlTransaction has completed; it is no longer usable "

using (SqlConnection connection = new SqlConnection(CS))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = 100;
bulkCopy.DestinationTableName = "dbo.Person";
try
{
bulkCopy.WriteToServer(PersonModels.AsDataTable());
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine("Rollback Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
}
}
transaction.Commit();
}