Retrieving output from stored procedure in c#

Question

I am calling a stored procedure written at sql server, in my c# service. But I am again and again facing exception:

InvalidCastException was unhandled by user code: Specified cast is not valid

Code:

public function(Data dt)
{
    con = new SqlConnection(constring);
    string brand = dt.brand;
    cmd = new SqlCommand("execute pro100 @brand, @check", con);

    SqlParameter param = new SqlParameter("@check", SqlDbType.Int);
    param.Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@brand", brand);
    cmd.Parameters.Add(param);
    con.Open();
    cmd.ExecuteNonQuery();

    int result = (int)cmd.Parameters["@check"].Value; // Exception is here
    con.Close();
    return result;
}

My stored procedure is as follows This is the stored proc

ALTER PROCEDURE [dbo].[pro100]
@brand varchar(20), 
@check int output
as
update carlog set minex=1000 where brand=@brand;
select @check=id from carlog where brand=@brand;
return @check

Could someone suggest the possible solution maybe?

Answer 1

Here it is a solution that ignores exception handling:

public function(Data dt)
{
    con = new SqlConnection(constring);

    cmd = new SqlCommand("pro100", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@brand", dt.brand);
    cmd.Parameters.Add("@check", SqlDbType.Int).Direction = ParameterDirection.Output;

    con.Open();
    cmd.ExecuteNonQuery();   
    int result = Convert.ToInt32(cmd.Parameters["@check"].Value);
    con.Close();
    return result;
}

Answer 2

I always re-use the variable that holds the parameter for output parameters, like this:-

public function(Data dt)
{
    con = new SqlConnection(constring);
    string brand = dt.brand;
    cmd = new SqlCommand("execute pro100", con);

    SqlParameter param = new SqlParameter("@check", SqlDbType.Int);
    param.Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@brand", brand);
    cmd.Parameters.Add(param);
    con.Open();
    cmd.ExecuteNonQuery();

    int? result = (int?)param.Value; // Exception was here
    con.Close();
    return result;
}

But you may also need to handle null values being returned from your stored procedure - by never returning a null - or by casting in C# to a type that can hold nulls (as I have above). I also dropped the parameter list from the command text - as the parameters are being added in code to the parameter collection.