How to execute SQL transaction Async in ASP.NET webservice

Question

I have webservice with many connections to it. I used this code:

IAsyncResult result = sqlCommand.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
   System.Threading.Thread.Sleep(500);
}
sqlCommand.EndExecuteNonQuery(result);

I think this is not a best method because I call Sleep(). PS. This method will be slow down performance of WebService and server

UPDATE2: i try to describe my code more: i have WebClient and 2 event (ProgressChanged and DownloadCompleted)

[WebMethod]
public void DonwloadFromRemoteServer(string uniqueId, string url)
{
    if (!Directory.Exists(uniqueId))
        Directory.CreateDirectory(uniqueId);

    WebClient wc = new WebClient();

    wc.DownloadProgressChanged += (sender, args) => wc_DownloadProgressChanged(sender, args, uniqueId, Path.GetFileName(url));

    wc.DownloadFileCompleted += (sender, args) => 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) =>
    {
        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) =>
    {
        sqlCommand.EndExecuteNonQuery(result);
        sqlCommand.Connection.Close();
    });
    sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);
}

ProgressChanged works fine but DownloadCompleted only works in Debug mode. I think it's happened because I need timeout or wait something between these calls.

Update3: Sometime i have TWO same row in DB after execute download! confused And did I need to close all connections?

Answer 1

you could use AsyncCallback instead of Sleep()

AsyncCallback callback = new AsyncCallback(HandleCallback);
sqlcommand.BeginExecuteNonQuery(callback, sqlcommand);

handles the Async state using callback.

private void HandleCallback(IAsyncResult result)
{
  SqlCommand command = (SqlCommand)result.AsyncState;
  command.EndExecuteNonQuery(result);
  ....
}

UPDATE 2

the codes overall are good however , are you allowed multiples files download in one unique id ? if you are downloading different files with same unique Id It could update the downloads table which reporting incomplete progress.

Not sure what you mean DownloadCompleted only work in debug mode however you could use a private variable in your WebMethod to control flow. Speaking with that, since you are using DB to reporting status changed but not inserting large amount of data you should consider using sync queries so that it could better archive the result.

Answer 2

You should call the overload of BeginExecuteNonQuery that accepts a callback and in the callback end the query.

Update

If you have additional code that needs to be executed after the database execution has completed, then this code needs to be started in the callback.

For example, if you currently have:

sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);
DoSomeAdditionalWorkNow();

DoSomeAdditionalWorkNow will not wait until the query has completed and if it depends on data updated in the command, you will have problems.

This can be remedied by moving the call to the additional work method into the callback as such:

AsyncCallback callback = ((result) =>
{
    sqlCommand.EndExecuteNonQuery(result);
    connection.Close();
    DoSomeAdditionalWorkNow();
});
sqlCommand.BeginExecuteNonQuery(callback, sqlCommand);

However

I think that you are overcomplicating your programming life. The queries that you are executing are not long running and you would be perfectly fine executing them with the synchronous version:

sqlCommand.ExecuteNonQuery();