Run stored procedure asynchronously

Question

I've been playing around with the code below. When the button is clicked, the idea is that a stored procedure runs and updates a table with a random number of dummy records (for now, whilst I'm playing, anyway).

protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=MATT\\RICHARDSON2008R2;Initial Catalog=Minerva;User ID=User;Password=password; Asynchronous Processing=True");
            SqlCommand cmd = new SqlCommand("exec UpdateRandomData '" + UpdateID.Text + "'",conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

The stored procedure I've written adds 100000 rows using a loop (to simulate a procedure that can take some time to run):-

ALTER procedure [dbo].[UpdateRandomData]
    @updateID varchar(50)
as
declare @count int = 1;
declare @theDate datetime = getdate();
declare @total int  =FLOOR(rand() * 100000);

while @count < 100001
begin
    insert into RandomData (randnumber, updateID, updatedDate)
    values 
    (FLOOR(rand() * 1001), @updateID, @theDate)
    set @count += 1;
end
GO

When I run the above C# code I get a timeout before the stored procedure finishes running, so I tried cmd.ExecuteNonQueryAsync(); instead:-

conn.Open();
cmd.ExecuteNonQueryAsync();
conn.Close();

The problem with this is that it doesn't seem to work the way I expect and I only ever add one row to the table from my stored procedure.

Can anybody point me in the right direction why this isn't working as I want?

Solution 1

Running this asynchronously is not the solution. You have to change the timeout. You can adjust the SqlCommand's timeout. It's an integer representing a number of seconds to wait for a response. I typically set it like this to avoid confusion:

cmd.CommandTimeout = TimeSpan.FromMinutes(30).TotalSeconds;

To wait indefinitely, set the Command Timeout to zero. I think you'll also need to set the ConnectionTimeout property of your SqlConnection.

Also, you should be using the using pattern for both your SqlConnection and SqlCommand objects.

Solution 2

You mean this?:

using System.Threading.Tasks;

.

protected void Button1_Click(object sender, EventArgs e)
    {
        Task.Run(() => {
                   SqlConnection conn = new SqlConnection("Data Source=MATT\\RICHARDSON2008R2;Initial Catalog=Minerva;User ID=User;Password=password; Asynchronous Processing=True");
                   SqlCommand cmd = new SqlCommand("exec UpdateRandomData '" + UpdateID.Text + "'",conn);

                   conn.Open();
                   cmd.ExecuteNonQuery();
                   conn.Close();
              })
    }

Solution 3

One way to achieve that would be using sp_start_job.

This allows you execute sp and not wait for it to be finished.

Comments

[Oguz Ozgul]

I think you call ExecuteNonQueryAsync() but right after that close the connection. If you send the final version of the code, we can make sure. When you call an async method, what you should do is to implement a callback (AsyncCallback) method which will be invoked when the operation is completed, or to get the IAsyncResult return value and to check for its IsCompleted property during regular intervals

[Oguz Ozgul]

@mat-richardson here is the documentation for the most modern way of implementing an asynchronous operation. Bu I have to agree with the comments on the timeout. If the stored procedure will execute for 10 minutes, you should set the timeout to zero regardless of you calling the stored procedure synchronously or asynchronously.

[Mat Richardson]

I don't really want to change the timeout. I'm simulating this as a test, but the final code will likely use a stored procedure that could take up to 10 minutes to run. I don't really want the user to have to wait through that. Instead I want them to be able to kick the procedure off and then go about their business.

[Niels V]

But the command runs in a connection. And even with async, when you close the connection the command is killed as well. When the application is still running, that isn't a problem, take a look f.i. to the AdoNetSample on msdn.microsoft.com/en-us/library/7b6f9k7k%28v=vs.110%29.aspx‌​. Otherwise you need some agent or service functionality to run the process (sp_start_job with SQL Agent, or a custom service that process the job).