Starting a stored procedure asynchronously

Question

I played with the code below. When the button is pressed, the idea is that the stored procedure starts and updates the table with a random number of dummy records (for now, during the game, 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 wrote adds 100,000 lines using a loop (to simulate a procedure that may take some time to start): -

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 completes, so I tried cmd.ExecuteNonQueryAsync();instead: -

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

The problem is that it does not work as I expect, and I only ever add one row to the table from my stored procedure.

Can someone point me in the right direction, why is this not working the way I want?

Answer 1

. . - SqlCommand. , . , :

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

, " " "0". , ConnectionTimeout SqlConnection.

, using SqlConnection SqlCommand.

Answer 2

?:

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

sp_start_job.

sp .