ThreadPool.QueueUserWorkItem与BeginExecuteNonQuery

本文介绍了ThreadPool.QueueUserWorkItem与BeginExecuteNonQuery的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

由于计划对数据库表执行清理任务，我有时使用简单的插入方法有时会超时而遇到问题.该任务每十分钟运行一次，在执行过程中，由于等待操作超时"，我的代码经常在事件日志中记录错误.

I have a problem at work with a simple insert method occasionally timing out due to a scheduled clean-up task on a database table. This task runs every ten minutes and during its execution my code often records an error in the event log due to 'the wait operation timed out'.

我正在考虑的解决方案之一是使调用存储过程的代码异步，为了做到这一点，我首先开始查看BeginExecuteNonQuery方法.

One of the solutions I'm considering is to make the code calling the stored procedure asynchronous, and in order to do this I first started looking at the BeginExecuteNonQuery method.

我尝试使用BeginExecuteNonQuery方法，但是发现它经常根本不插入行.我使用的代码如下:

I've tried using the BeginExecuteNonQuery method but have found that it quite often does not insert the row at all. The code I've used is as follows:

SqlConnection conn = daService.CreateSqlConnection(dataSupport.DBConnString);
SqlCommand command = daService.CreateSqlCommand("StoredProc");

try {
    command.Connection = conn;

    command.Parameters.AddWithValue("page", page);
    command.Parameters.AddWithValue("Customer", customerId);

    conn.Open();

    command.BeginExecuteNonQuery(delegate(IAsyncResult ar) {
        SqlCommand c = (SqlCommand)ar.AsyncState;

        c.EndExecuteNonQuery(ar);
        c.Connection.Close();
    }, command);

} catch (Exception ex) {
     LogService.WriteExceptionEntry(ex, EventLogEntryType.Error);
} finally {
     command.Connection.Close();
     command.Dispose();
     conn.Dispose();
}

很明显，我不希望即时插入，但希望在五分钟后将其插入到低使用率的开发数据库中.

Obviously, I'm not expecting an instant insert but I am expecting it to be inserted after five minutes on a low usage development database.

我现在已切换到以下代码，它可以执行插入操作:

I've now switched to the following code, which does do the insert:

System.Threading.ThreadPool.QueueUserWorkItem(delegate {
      using (SqlConnection conn = daService.CreateSqlConnection( dataSupport.DBConnString)) {
         using (SqlCommand command = daService.CreateSqlCommand("StoredProcedure")) {
             command.Connection = conn;

             command.Parameters.AddWithValue("page", page);
             command.Parameters.AddWithValue("customer", customerId);

             conn.Open();
             command.ExecuteNonQuery();
         }
      }
  });

我有几个问题，其中一些是假设:

由于我的insert方法的签名无效，因此我假定调用它的代码不等待响应.这是正确的吗?

As my insert method's signature is void, I'm presuming code that calls it doesn't wait for a response. Is this correct?

BeginExecuteNonQuery不运行存储过程是否有原因?我的代码错了吗?

Is there a reason why BeginExecuteNonQuery doesn't run the stored procedure? Is my code wrong?

最重要的是，如果我使用QueueUserWorkItem(或行为良好的BeginExecuteNonQuery)，我是否正确地认为这将获得期望的结果?就是说，在预定任务运行时尝试运行存储过程，将看到代码在任务完成后执行，而不是当前超时?

Most importantly, if I use the QueueUserWorkItem (or a well-behaved BeginExecuteNonQuery) am I right in thinking this will have the desired result? Which is, that an attempt to run the stored procedure whilst the scheduled task is running will see the code executing after the task completes, rather than its current timing out?

修改

这是我现在用来回应收到的评论和答案的版本.

This is the version I'm using now in response to the comments and answers I've received.

SqlConnection conn = daService.CreateSqlConnection(
              string.Concat("Asynchronous Processing=True;",
              dataSupport.DBConnString));
SqlCommand command = daService.CreateSqlCommand("StoredProc");

command.Connection = conn;

command.Parameters.AddWithValue("page", page);
command.Parameters.AddWithValue("customer", customerId);

conn.Open();
command.BeginExecuteNonQuery(delegate(IAsyncResult ar) {
      SqlCommand c = (SqlCommand)ar.AsyncState;

      try {
          c.EndExecuteNonQuery(ar);
      } catch (Exception ex) {
           LogService.WriteExceptionEntry(ex, EventLogEntryType.Error);
      } finally {
          c.Connection.Close();
          c.Dispose();
          conn.Dispose();
      }

 }, command);

ueUserWorkItem与BeginExecuteNonQu

问题描述

推荐答案