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?

Answer 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.

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

Answer 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.