CODEWITHSUNDEEP
c#.netsqlasynchronoussqlclr
Boomer
Boomer

Reputation: 1478

asynchronous function not executing sql CLR stored procedure

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!

Upvotes: 0

Views: 1591

Answers (1)

DeveloperX
DeveloperX

Reputation: 4683

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) {  }
}

Upvotes: 1

Related Questions