title: How to execute SQL transaction Async in ASP.NET webservice
body: <p>I have webservice with many connections to it.
  I used this code:</p>
  
  <pre><code>IAsyncResult result = sqlCommand.BeginExecuteNonQuery();
  while (!result.IsCompleted)
  {
     System.Threading.Thread.Sleep(500);
  }
  sqlCommand.EndExecuteNonQuery(result);
  </code></pre>
  
  <p>I think this is not a best method because I call <code>Sleep()</code>.
  PS. This method will be slow down performance of WebService and server</p>
  
  <p><strong>UPDATE2:</strong>
  i try to describe my code more:
  i have WebClient and 2 event (<code>ProgressChanged</code> and <code>DownloadCompleted</code>)</p>
  
  <pre><code>[WebMethod]
  public void DonwloadFromRemoteServer(string uniqueId, string url)
  {
      if (!Directory.Exists(uniqueId))
          Directory.CreateDirectory(uniqueId);
  
      WebClient wc = new WebClient();
  
      wc.DownloadProgressChanged += (sender, args) =&gt; wc_DownloadProgressChanged(sender, args, uniqueId, Path.GetFileName(url));
  
      wc.DownloadFileCompleted += (sender, args) =&gt; wc_DownloadFileCompleted(sender, args, uniqueId, Path.GetFileName(url));
      wc.DownloadFileAsync(new Uri(url), String.Format("{0}\\{1}", uniqueId, Path.GetFileName(url)));
  }
  
  void wc_DownloadProgressChanged(object sender, DownloadProgressChangedEventArgs e, string uniqueId, string fileName)
  {
      SqlConnection connection = new SqlConnection("Data Source=.\\SQLSERVER;Initial Catalog=XRingtoneDB;Integrated Security=True;Asynchronous Processing=true");
      connection.Open();
      SqlCommand sqlCommand = new SqlCommand();
      sqlCommand.Connection = connection;
      sqlCommand.CommandText = String.Format("IF NOT EXISTS(SELECT uniqueId FROM downloads WHERE uniqueID = '{0}') " +
                                         "INSERT INTO downloads VALUES ('{0}', '{1}', '{2}') " +
                                         "IF EXISTS(SELECT uniqueId FROM downloads WHERE uniqueID = '{0}') " +
                                         "Update downloads " +
                                         "set progress='{2}' " +
                                         "where uniqueId='{0}' ", uniqueId, fileName, e.BytesReceived);
  
      AsyncCallback callback = ((result) =&gt;
      {
          sqlCommand.EndExecuteNonQuery(result);
          connection.Close();
      });
  
      sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);
  }
  
  void wc_DownloadFileCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e, string uniqueId, string fileName)
  {
      SqlConnection connection = new SqlConnection("Data Source=.\\SQLSERVER;Initial Catalog=XRingtoneDB;Integrated Security=True;Asynchronous Processing=true");
      connection.Open();
      SqlCommand sqlCommand = new SqlCommand();
      sqlCommand.Connection = connection;
  
      sqlCommand.CommandText = String.Format("update downloads " +
                                                  "set progress='Completed' " +
                                                  "where uniqueId='{0}' and fileName='{1}'", uniqueId, fileName);
  
      AsyncCallback callback = ((result) =&gt;
      {
          sqlCommand.EndExecuteNonQuery(result);
          sqlCommand.Connection.Close();
      });
      sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);
  }
  </code></pre>
  
  <p><code>ProgressChanged</code> works fine but <code>DownloadCompleted</code> only works in Debug mode.
  I think it's happened because I need timeout or wait something between these calls.</p>
  
  <p><strong>Update3:</strong>
  Sometime i have TWO same row in DB after execute download! <em>confused</em>
  And did I need to close all connections?</p>