Click here to Skip to main content
15,899,124 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My stored procedure has an output parameter: @msg out
How can I retrieve this using ado.net?
DAL Class
C#
[DataObjectMethod(DataObjectMethodType.Insert)]
public static int Insert(object Fname, object Lname, object UserName, object Password, object Mobile, object RoleId,object Pic,object Date,object Active, out string msg)
{
    int Result = 0;
    SqlParameter[] parameters = new SqlParameter[]
    {
        new SqlParameter("Fname",Fname),
        new SqlParameter("Lname",Lname),
        new SqlParameter("UserName",UserName),
        new SqlParameter("Password",Password),
        new SqlParameter("Mobile",Mobile),
        new SqlParameter("fk_RoleId",RoleId),
        new SqlParameter("Pic",Pic),
        new SqlParameter("Date",Date),
        new SqlParameter("Active",Active),
        //new SqlParameter()
    };

    //SqlParameter Outparam = new SqlParameter("msg", SqlDbType.NVarChar);
    //Outparam.Direction = ParameterDirection.Output;
    //parameters[8].Value =  new SqlParameter("msg", Outparam);

    Result = ExecuteNonQuery("sp_Insert_User", parameters);
    return Result;

}


Base Class
VB
/// <summary>
       /// Get a stored procedure name and parameters and return the number of rows affected
       /// </summary>
       /// <param name="commandText">Name of StoredProcedure</param>
       /// <param name="parameters">Array of SqlParameter</param>
       public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
       {
           cmd = new SqlCommand(commandText, con);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddRange(parameters);

           if (con.State == ConnectionState.Closed)
               con.Open();

           int rowsAffected = cmd.ExecuteNonQuery();

           if (con.State == ConnectionState.Open)
               con.Close();

           return rowsAffected;
       }
Posted
Comments
Herman<T>.Instance 8-May-14 10:53am    
Strange question. You ask in the code for the number of rows involved. What message do you expect?

Try This :-

SqlParameter[] parameters = new SqlParameter[]
    {
        new SqlParameter("Fname",Fname),
        new SqlParameter("Lname",Lname),
        new SqlParameter("UserName",UserName),
        new SqlParameter("Password",Password),
        new SqlParameter("Mobile",Mobile),
        new SqlParameter("fk_RoleId",RoleId),
        new SqlParameter("Pic",Pic),
        new SqlParameter("Date",Date),
        new SqlParameter("Active",Active),
        new SqlParameter("msg", SqlDbType.NVarChar) { Direction = ParameterDirection.Output,Value=0 }
                                       
    };
              
             Result = ExecuteNonQuery("sp_Insert_User", parameters);
          
            Result= Convert.ToInt32(sqlparams[9].Value);
            return Result;



Hope it will works for you.
 
Share this answer
 
Comments
Maciej Los 8-May-14 15:56pm    
+5!
Assuming that msg is a varchar or similar then try the following ...

C#
SqlParameter Outparam = new SqlParameter("msg", SqlDbType.NVarChar);
Outparam.Direction = ParameterDirection.Output;

SqlParameter[] parameters = new SqlParameter[]
{
    new SqlParameter("Fname",Fname),
    new SqlParameter("Lname",Lname),
    new SqlParameter("UserName",UserName),
    new SqlParameter("Password",Password),
    new SqlParameter("Mobile",Mobile),
    new SqlParameter("fk_RoleId",RoleId),
    new SqlParameter("Pic",Pic),
    new SqlParameter("Date",Date),
    new SqlParameter("Active",Active),
    Outparam
};

string msg;
int Result = ExecuteNonQuery("sp_Insert_User", parameters, out msg);
// now have rows affected AND the message

Note that I changed your ExecuteNonQuery function as follows
public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters, out string msg)
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand(commandText, con);

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddRange(parameters);

    if (con.State == ConnectionState.Closed)
        con.Open();

    int rowsAffected = cmd.ExecuteNonQuery();
    msg =  (string)cmd.Parameters["msg"].Value;

    if (con.State == ConnectionState.Open)
        con.Close();

    return rowsAffected;
}

Warning - I have not been able to test this code properly
 
Share this answer
 
Comments
Jagbir Saini 8-May-14 11:18am    
We can get the value without changing base class function also.
CHill60 8-May-14 11:37am    
How? The name "msg" implies that it is a message (i.e. text), but the ExecuteNonQuery function is returning an int. OP is already getting the number of rows affected so the implication is that they also want the msg output. Can't do that without changing that function???
Well I'm being silly!! D'er ... from the parameter array CHill :headslap:
Jagbir Saini 8-May-14 11:44am    
yup.
CHill60 8-May-14 12:10pm    
... I'm awake now :-)
Jagbir Saini 8-May-14 12:45pm    
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900