ThreadPool.QueueUserWorkItem与BeginExecuteNonQuery [英] ThreadPool.QueueUserWorkItem versus 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);

推荐答案

BeginExecuteNonQuery不运行存储的原因吗?程序?我的代码错了吗?

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

可能您没有在连接字符串中添加 Asynchronous Processing = True .

Probably you didn't add the Asynchronous Processing=True in the connection string.

另外-可能存在以下情况:当来自sql的响应准备就绪时-asp.net响应已经发送.

Also - there could be a situation that when the reponse from sql is ready - the asp.net response has already sent.

这就是您需要使用的原因:

that's why you need to use : Page.RegisterASyncTask (+AsyncTimeout)

(如果您使用Webform异步页面，则应在page指令中添加: Async ="True" )

(if you use webform asynchronous pages , you should add in the page directive : Async="True")

p.s.此行:

System.Threading.ThreadPool.QueueUserWorkItem 很危险.您应该注意，响应尚未发送.

System.Threading.ThreadPool.QueueUserWorkItem is dangerouse in asp.net apps. you should take care that the response is not already sent.

这篇关于ThreadPool.QueueUserWorkItem与BeginExecuteNonQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！