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?

Turbot · Accepted Answer · 2011-12-07 01:40:08Z

4

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.

edited Dec 7, 2011 at 1:40

answered Dec 6, 2011 at 3:05

Turbot

5,2511 gold badge24 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TheX Over a year ago

I also tried this method, but sql query runs only one time. PS. My method with Sleep() will be slow down performance of WebService and server?

Turbot Over a year ago

what do you mean by sql query runs only one time. Yes, dealing with large amount of data, you don't want to wait(using sleep() in your case) until entire result is processed and handling it require large amount of RAM. This is just a better designed to utilize asynchronous pattern to data process as soon as it become available in Callback function.

TheX Over a year ago

i mean that when i view my DB in Server Managament, there is only first run of sql query. it's doesn't updated anymore.

TheX Over a year ago

please see Update i question. Thanks.

Turbot Over a year ago

could you post the webclient code snippet to show how you setup your DownloadCompleted event? Seem like the problem you have is related to the webclient function rather than BeginExecuteNonQuery

competent_tech · Accepted Answer · 2011-12-06 16:34:50Z

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

edited Dec 6, 2011 at 16:34

answered Dec 6, 2011 at 3:08

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

7 Comments

TheX Over a year ago

i know it. but how? please see Update in question.

TheX Over a year ago

do you see my update? i use your method but my second query (when call DonwloadCompleted()) didn't run. I think about synchro method but i'm not sure. if 1000 users call query at one time???

competent_tech Over a year ago

@TheX: Can you show the exact code that isn't working? I suspect that there is a disconnect between what you have and what I think you have. Also, your code should work just fine if 1000 users call it simultaneously. However, I would strongly recommend that you change your dynamic SQL to a stored procedure, which will greatly reduce the total amount of work that has to be done each time a query is executed.

TheX Over a year ago

please see updates... I post all My code. I don't know Stored Procedure yet but i try when code start works fine) Now code works only with my first method (with Sleep() call).

competent_tech Over a year ago

@TheX: All of your code looks correct, but I can easily see various conditions where updating the database asynchronously will cause problems because you don't have a guaranteed order of execution. For example, you could get the progresschanged and completed events very close together and it could be possible for the download sql update to finish executing asynchronously faster than the progress, which means that the download sql would execute, then the progress sql. I think that you would be much better off switching to synchronous database updates.

|

Collectives™ on Stack Overflow

How to execute SQL transaction Async in ASP.NET webservice

2 Answers 2

5 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related