How to make a generic function for stored procedure with output?

Question

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?

Possible duplicate of Using stored procedure output parameters in C# — Rick S
– Rick S, Commented Apr 26, 2017 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
– DaniDev, Commented Apr 26, 2017 at 16:30

Alexander · Accepted Answer · 2017-04-26 16:31:09Z

3

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.

answered Apr 26, 2017 at 16:31

Alexander

4,5527 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

interesting-name-here · Accepted Answer · 2017-04-26 15:48:09Z

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.

answered Apr 26, 2017 at 15:48

interesting-name-here

1,8901 gold badge22 silver badges35 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:02:14Z

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.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 26, 2017 at 16:12

is_oz

1,0031 gold badge13 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to make a generic function for stored procedure with output?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related