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

Hi John -

 

Here is a sample for you:

 

public static void TestSP()

{

    string connectionString = "{{Your connection string here}}";   

 

    //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

    {

        using (SqlConnection 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;

 

            int retValue = command.ExecuteNonQuery();

 

            Console.WriteLine(

                "retValue = {0}\n" +

                "@RETURN_VALUE = {1}\n" +

                "@ErrorDesc = '{2}'\n" +

                "@ErrorNo = {3}\n" +

                "@RowsAffected = {4}"

                ,

                retValue,

                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);

    }

}

 

Result:

 

retValue = -1

@RETURN_VALUE = 1234

@ErrorDesc = 'SUCCESS, UserName = SP_PARAMS_TEST'

@ErrorNo = 1

@RowsAffected = 503

 

--

Shamil