CODEWITHSUNDEEP
c#multithreading
user1052927
user1052927

Reputation: 41

ThreadAbortException on a console app on multiple database inserts

If I try to execute >100 database inserts one at a time using a thread in a console app, I get following error. As per the current architecture I need to insert records one at a time. For lesser number of records(10-30),the error doesn't happen. Is inserting so many records one at a time creating this issue?

The code is something like:

foreach (MyObject myObject in myObjectCollection)
{
   var database = new SqlDatabase(connectionString);
   using (DbCommand command = database.GetStoredProcCommand(storedProcedureName))
   { 
         // Create parameters from myObject
         // Add parameters to the command object         
         database.ExecuteNonQuery (command);
   }    

}

The error:

System.Threading.ThreadAbortException: Thread was being aborted.    
 at SNIReadSync(SNI_Conn* , SNI_Packet** , Int32 )    
 at SNINativeMethodWrapper.SNIReadSync(SafeHandle pConn, IntPtr& packet, Int32 timeout)    
 at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)    
 at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()    
 at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()    
 at System.Data.SqlClient.TdsParserStateObject.ReadByte()    
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    
 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    
 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    
 at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)    
 at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)

Upvotes: 4

Views: 2221

Answers (5)

jdearana
jdearana

Reputation: 1099

In case you are using EF, here a method to run any DB procedure ensuring that the connection is closed:

void EnsureClose<TObjectContext>(Action<TObjectContext> dbAction) where TObjectContext : ObjectContext, new()
    {
        using (var objContext = new TObjectContext())
        {
            try
            {
                dbAction(objContext);
            }
            finally
            {
                objContext.Connection.Close();
            }
        }
    }

Upvotes: 0

xofz
xofz

Reputation: 5660

Is the thread a background thread? If so, Main() might be returning during execution, which will abort background threads.

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Do you happen to have Asynchronous Processing=true (or just async=true) in your connection string? If you do, try removing it.

Also, did you try implementing this using "pure" ADO.NET?

Upvotes: 0

competent_tech
competent_tech

Reputation: 44941

You are going to run out of connection pool resources. Change the allocation and creation outside of the loop.

Update

I have updated the answer to show that explicit close of the connection that SqlDatabase opens needs to be performed in a finally statement:

   SqlDatabase database = new SqlDatabase(connectionString);
   try {
      using (DbCommand command = database.GetStoredProcCommand(storedProcedureName)) {
         // Create parameters from myObject
        foreach (MyObject myObject in myObjectCollection)
        { 
             // Add parameters to the command object         
             database.ExecuteNonQuery (command);
       }
      } 
   } finally {
      if (database != null) {
         // Do whatever is necessary here to explicitly close the connection to the database
      }
   }

Upvotes: 2

Stelzi79
Stelzi79

Reputation: 585

Is it possible you triggered a DataBaseTimeout? Try to put a higher TimoutNumber in the client.

Upvotes: 0

Related Questions