How To Call A SQL Server Stored Procedure And Use Output Paramaters In C#



TODO:

Have you ever wanted to call a stored procedure, and use an output parameter in C#?

 

SOLUTION:

 

using (SqlConnection myConnection = new SqlConnection(<YOUR CONNECTION STRING>))
{
    string _Number = "D3R42ed"; //test number to lookup
    long _ReturnId = -1;  //test value for the output param

    //do the command stuff now
    using (SqlCommand UserInfoCmd = new SqlCommand())
    {
        UserInfoCmd.CommandType = CommandType.StoredProcedure;
        UserInfoCmd.CommandTimeout = 30;
                       
        UserInfoCmd.CommandText = "usp_myStoredProcedure";  //your stored procedure name here
        UserInfoCmd.Parameters.Add("@Number", System.Data.SqlDbType.VarChar, 25).Value = _Number;  //set the number parameter
        
        SqlParameter outparm = UserInfoCmd.Parameters.Add("@ReturnValue", System.Data.SqlDbType.BigInt, 0); //Add the parameter
        outparm.Direction = ParameterDirection.Output;  //tell it, it is an OUTPUT param
        
        myConnection.Open();  //open the connection
        UserInfoCmd.Connection = myConnection;  //set the connection

        //harvest abstract here
        using (SqlDataReader myReader = UserInfoCmd.ExecuteReader())  //now execute the command
        {
            //see if we can read
            if (myReader.Read())
            {
                string SomeName = myReader.IsDBNull(0) ? "" : myReader.GetSqlString(0).Value.Trim();  //get some test value from the SP select statement

                myReader.Close();  //VERY IMPORTANT, CLOSE BEFORE ACCESSING OUTPUT PARAMS
                _ReturnId = Convert.ToInt64(UserInfoCmd.Parameters["@ReturnValue"].Value);  //now get the output param value            
            }
        }
    }
}

 

 

NOTES:

Be sure to close the SqlDataReader before accessing the output parameters!