asynchronous function not executing sql CLR stored procedure

Question

I have a CLR stored procedure that i want to execute asynchronously from C#.

the code is as follow:

private delegate void GeneratePayrollDelegate(string payProcessID);

public void GeneratePayroll(string payProcessID)
    {
        GeneratePayrollDelegate del = new GeneratePayrollDelegate(GeneratePayrollAsync);
        del.BeginInvoke(payProcessID, null, null);
    }

public void GeneratePayrollAsync(string payProcessID)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(DLConnectionStringHelper.GetConnectionString() + "; async=true;"))
            {
                using (SqlCommand cmd = new SqlCommand("proc_GeneratePayroll", connection))
                {                        
                    cmd.CommandTimeout = 3600;
                    cmd.CommandType = CommandType.StoredProcedure;

                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }
        catch (Exception ex) { _Exceptions.ManageExceptions(ex); }
    }

This stored procedure executes successfully if it runs from sql.

When it executes from this code above, it gives no row inside the CLR stored procedure when trying to retrieve the row by the ID sent as parameter.

Need help!

In the CLR proc, one of the functions is taking some required information related to this payprocessID, it returns no rows. when i try to retrieve row at postion 0, it gives range exception — Boomer
– Boomer, Commented Jun 6, 2011 at 9:28
So, it still executes successfully but you get unexpected results? It would be better to edit the question. — Jodrell
– Jodrell, Commented Jun 6, 2011 at 9:34

Bryan · Accepted Answer · 2011-09-20 15:26:06Z

you are calling cmd.ExecuteNonQuery(); you should call BeginExecuteReader instead to get result as sample code can be like this

private void Asynchronous(IAsyncResult asyncResult)
{
                System.Data.SqlClient.SqlDataReader reader;
                try
                {
                    System.Data.SqlClient.SqlCommand command =
                       asyncResult.AsyncState as System.Data.SqlClient.SqlCommand;
                    reader = command.EndExecuteReader(asyncResult);
                    while (reader.Read())
                    {

                    }
                    reader.Close();
                }
                catch
                {
                }
}

public void GeneratePayrollAsync(string payProcessID)
{
    try
    {
        using (SqlConnection connection = new SqlConnection("ConnectionString"))
        {
            using (SqlCommand command = new SqlCommand("proc_GeneratePayroll", connection))
            {
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                //Set Your stored procedure parameter here
                connection.Open();
                command.BeginExecuteReader(Asynchronous, command, CommandBehavior.Default);

            }
        }
    }
    catch (Exception ex) {  }
}

Collectives™ on Stack Overflow

asynchronous function not executing sql CLR stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related