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

Shamil,

I executed my first SP using your example code (first light as they say in the astronomy world).

Man oh man is it ugly though.  Tons of code to get one lonely little SP to execute (NOT your fault!!!).

Thank you so much for the examples, I can definitely take it from here.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> 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
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>