DKR DKR - 1 month ago 9
C# Question

How to get values of output parameters in c# while executing SP using ExecuteScalar()

I am trying to execute a stored procedure in c#.

Stored procedure performs insert operation and returns values of one of the columns as output parameter.

I need to execute it from c# and get back value of output param.

Below is what i have tried so far.

SqlParameter[] associateParams = new SqlParameter[10];
{
associateParams[0]=new SqlParameter("@orgName", newAssociate.OrgName);
associateParams[1]=new SqlParameter("@createdBy", newAssociate.Email);
associateParams[2]=new SqlParameter("@userName", newAssociate.UserName);
associateParams[3]=new SqlParameter("@workEmail", newAssociate.Email);
associateParams[4]=new SqlParameter("@password", newAssociate.Password);
associateParams[5]=new SqlParameter("@teamStrength", "0");
associateParams[6]=new SqlParameter("@projName", newAssociate.ProjName);
associateParams[7]=new SqlParameter("@userType", "Associate");
associateParams[8] = new SqlParameter("@userSalt", SqlDbType.VarChar, 400);
associateParams[8].Direction = ParameterDirection.Output;
associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int);
associateParams[9].Direction = ParameterDirection.Output;

}

using (SqlCommand cmd = con.CreateCommand())
{
log.Debug("In command is called");
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;
cmd.Parameters.AddRange(param);

log.Debug("Command is called");
try
{
if (con.State != ConnectionState.Open)
{
con.Open();

log.Debug("Con is open");
}
cmd.ExecuteScalar();
log.Debug(cmd.Parameters["@userSalt"].Value.ToString());
log.Debug(cmd.Parameters["@activationKey"].Value.ToString());


Executing above, performs insert successfully but returns null for output params values.

Can anyone suggest what I am missing here.

Thanks

Answer

try like this when you define output parameters:

         associateParams[8] = new SqlParameter("@userSalt", SqlDbType.VarChar, 400);
         associateParams[8].Value = "";
         associateParams[8].Direction = ParameterDirection.Output;
         associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int);                 
         associateParams[9].Value = 0;
         associateParams[9].Direction = ParameterDirection.Output;

let me know if this helps.

UPDATE: here is my own method

 cmd.Parameters.Add(new SqlParameter("@userSalt", SqlDbType.VarChar, 400));
 cmd.Parameters["@userSalt"].Value = "";
 cmd.Parameters["@userSalt"].Direction = ParameterDirection.Output;

UPDATE1: because you dont use ExecuteNonQuery. Change cmd.ExecuteScalar() to cmd.ExecuteNonQuery()