0

I made a generic function to call stored procedures from C#. The code looks like this:

public static void executeStoredProcedure(string SPName, Dictionary<string, object> parameters, string connectionStringName)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(SPName, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> kvp in parameters)
                    cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

And the code when I use it goes like this:

Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("UserId", UserId);
parameters.Add("UserName", UserName);
GlobalClass.executeStoredProcedure("UpdateUserName", parameters, "userDB");

How do I modify this to be able to work with a stored procedure that has an output parameter, and store the output in a C# variable?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
user7792598
  • 177
  • 1
  • 6
  • 17
  • 1
    Possible duplicate of [Using stored procedure output parameters in C#](http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp) – Rick S Apr 26 '17 at 15:47
  • what is your output value type? and how do you want to store the return values? (i.e. List, array etc.) – DaniDev Apr 26 '17 at 16:30

3 Answers3

2

You can use SqlParameter array instead of Dictionary<string, object>. For example

SqlParameter[] parameters = new SqlParameter[] 
{
new SqlParameter() {ParameterName = "@UserId", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value= UserId},
new SqlParameter() {ParameterName = "@UserName", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = UserName},
new SqlParameter() {ParameterName = "@OutValue", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output},
};
GlobalClass.executeStoredProcedure("UpdateUserName", parameters, "userDB");

To store output in C# variable use

int OutVal = Convert.ToInt32(cmd.Parameters["@OutValue"].Value);

This value you can return as the executeStoredProcedure function result or assign this value to an external variable.

Alexander
  • 4,420
  • 7
  • 27
  • 42
0

To get the output you have to specify an output parameter with the return direction instead of the default input direction.

SqlParameter retVal = new SqlParameter("@retVal", SqlDbType.YOURTYPE);
retVal.Direction = ParameterDirection.ReturnValue;

parameters.Add(parm);

//your code
cmd.ExecuteNonQuery();
object val = retVal.Value;
//your code

Then, pass a bool to your executeStoredProcedure and return an object OR create a new method that has a return value and takes a bool for returning a value.

interesting-name-here
  • 1,851
  • 1
  • 20
  • 33
0

I have a method similar. I used to List return type and I get sp result with dynamic expandoObject. When I searching this issue, that answer helped me.

Community
  • 1
  • 1
is_oz
  • 813
  • 1
  • 8
  • 27