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!