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

Hi Shamil,

I assume that the parameters on the C# side have to be in the same order / name / datatype / size as 
the parameters in the stored procedure?

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