[dba-VB] Async calling SP with params from C# sample

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Fri Nov 27 16:02:17 CST 2009


Another sample - async call (partially used MSDN sample):

public static void runTest()
{
    SPTester o = new SPTester();
    o.TestSP();

    while (!o.Completed) System.Threading.Thread.Sleep(1000);    
}

public class SPTester
{
    public bool Completed { get; set; }

    private SqlConnection _connection; 
    public void TestSP()
    {
        Completed = false;

        string connectionString = 
            "Data Source=HAMBURG\\SQL2005;"+
            "Initial Catalog=X;"+
            "User Id=sa;Password=Y;"+
            "Asynchronous Processing=true";

        //CREATE Procedure [dbo].[TestParams] 
        //   @Username varchar(20), 
        //   @ErrorDesc varchar(100) output, 
        //   @ErrorNo int output, 
        //   @RowsAffected int output AS
        //DECLARE @lErrorNo INTEGER	
        //DECLARE @lRowsAffected INTEGER	
        //Begin
        // set @ErrorDesc = 'SUCCESS' + ', UserName = ' +
IsNull(@USerName,'?')
        // set @ErrorNo = 1 -- OK
        // set @RowsAffected = 503
        // RETURN 1234
        //end
        try
        {
            _connection = new SqlConnection(connectionString);
            {
                _connection.Open();

                SqlCommand command = new SqlCommand("TestParams",
_connection);
                command.CommandType =
System.Data.CommandType.StoredProcedure;
                //   @Username varchar(20), 
                command.Parameters.Add(
                    new SqlParameter("@Username",
System.Data.SqlDbType.VarChar, 20));
                command.Parameters["@UserName"].Value = "SP_PARAMS_TEST";
                //   @ErrorDesc varchar(100) output, 
                command.Parameters.Add(
                    new SqlParameter("@ErrorDesc",
System.Data.SqlDbType.VarChar, 100));
                command.Parameters["@ErrorDesc"].Direction =
System.Data.ParameterDirection.Output;
                //   @ErrorNo int output, 
                command.Parameters.Add(
                    new SqlParameter("@ErrorNo", System.Data.SqlDbType.Int,
4));
                command.Parameters["@ErrorNo"].Direction =
System.Data.ParameterDirection.Output;
                //   @RowsAffected int output AS
                command.Parameters.Add(
                    new SqlParameter("@RowsAffected",
System.Data.SqlDbType.Int, 4));
                command.Parameters["@RowsAffected"].Direction =
System.Data.ParameterDirection.Output;
                // RETURN
                command.Parameters.Add(
                    new SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4));
                command.Parameters["@RETURN_VALUE"].Direction =
System.Data.ParameterDirection.ReturnValue;

                AsyncCallback callback = new AsyncCallback(handleCallback);
                command.BeginExecuteNonQuery(callback, command);

            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            if (_connection != null)
            {
                _connection.Close();
                _connection = null;
            }
        }
    }

    private void handleCallback(IAsyncResult result)
    {
        try
        {
            SqlCommand command = (SqlCommand)result.AsyncState;
            int rowCount = command.EndExecuteNonQuery(result);

            Console.WriteLine(
                "rowCount = {0}\n" +
                "@RETURN_VALUE = {1}\n" +
                "@ErrorDesc = '{2}'\n" +
                "@ErrorNo = {3}\n" +
                "@RowsAffected = {4}"
                ,
                rowCount,
                command.Parameters["@RETURN_VALUE"].Value.ToString(),
                command.Parameters["@ErrorDesc"].Value.ToString(),
                command.Parameters["@ErrorNo"].Value.ToString(),
                command.Parameters["@RowsAffected"].Value.ToString()
                );

        }
        catch (SqlException ex)
        {
            Console.WriteLine("T-SQL: {0}", ex.Message);
        }
        catch (Exception ex)
        {
            Console.WriteLine("{0}", ex.Message);
        }
        finally
        {
            Completed = true;

            if (_connection != null)
            {
                _connection.Close();
                _connection = null;
            }
        }
    }

}

--
Shamil





More information about the dba-VB mailing list