c# - 多个数据库插入的控制台应用程序上的 ThreadAbortException

Question

如果我尝试使用控制台应用程序中的线程一次执行 >100 个数据库插入，我会收到以下错误。根据当前架构，我需要一次插入一条记录。对于较少数量的记录 (10-30)，错误不会发生。一次插入这么多记录是否会造成此问题？

代码是这样的:

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

}

错误:

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)

Answer 1

您将耗尽连接池资源。在循环外更改分配和创建。

更新

我更新了答案以表明需要在 finally 语句中显式关闭 SqlDatabase 打开的连接:

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