Separate function for each stored procedure or generalized function to call any procedure?

Question

In our project's current state, we have at least one method used to call each stored procedure that we have created on the database server. Since these methods are pretty lengthy, involving creating each SqlParameter and then passing them to the database connection, I was considering creating a method that would generalize the process for our needs.

This is what I've come up with so far:

public static void UpdateTableTest(string procedureName, params object[] paramList)
    {
        if (sessionID == -1)
            GetActiveSession();

        SqlConnection scn = new SqlConnection(GetConnectionString());

        try
        {
            scn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = scn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedureName;
            SqlCommandBuilder.DeriveParameters(cmd);
            for (int i = 0; i < paramList.Length; i++)
            {
                cmd.Parameters[i+1].Value = paramList[i];
            }

            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            scn.Close();
        }
    }

Aside from problems that I will run in to for different query types, and for query types that I will need to return some data from, is there anything inherently wrong with this method of executing stored procedures? Should I stick with an individual method for each stored procedure?

Get rid of that try/catch block (it just messes up your call stack), and instead put your SqlConnection and SqlCommand into using blocks. — John Saunders
– John Saunders, Commented Feb 27, 2014 at 20:53
The only critique I would offer here is that maintenance wise you may run into issues where order of parameters in cmd, and order of parameters of the function has to match, and it's not very explicit here. Perhaps receive a dictionary where key is the name of parameter, and then assign by names would protect you here a bit more. Greatest con here I think is that you're having to make a roundtrip to DB every time just to get a list of parameters - expensive. You should know and supply parameters apriori to save the roundtrip. It would be hard to fix it l8r if you implement this. — LB2
– LB2, Commented Feb 27, 2014 at 21:02
@LB2 I see what you're saying about the round trip. Do you think the proper way to call each stored procedure from my C# is to have them in their own separate methods? — wibs
– wibs, Commented Feb 27, 2014 at 21:08
I think having common function is not wrong, but I would probably do the following: Expose helper method to get IDbParameter (in case you switch databases later), have callers of the function set up parameters to avoid roundtrip, then pass a list of these parameters so you can run it through this common code above). Of course you can do it layered way allowing caller be both lazy or explicit if you want to extend this capability. — LB2
– LB2, Commented Feb 27, 2014 at 21:14

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can consider using Enterprise library or

SQLHelpers

available online.

example

DataSet dsUserInfo = new DataSet();    
dsUserInfo = SqlHelper.ExecuteDataSet("Select * from UserInfo", CommandType.Text);    
MyDataGridview.DataSource = dsUserInfo.Tables[0];

Or if you want to get rid of this even, you can go for ORM like EF suggested by John

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 27, 2014 at 20:55

Shantanu Gupta

21.2k56 gold badges187 silver badges293 bronze badges

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

2 Comments

John Saunders Over a year ago

Or better still, using an ORM like Entity Framework.

wibs Over a year ago

Thank you, this will be much more flexible for my needs.

Collectives™ on Stack Overflow

Separate function for each stored procedure or generalized function to call any procedure?

1 Answer 1

SQLHelpers

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related