C# ASP.NET: create general method to call Stored Procedure with any number of parameters

Question

In the project I'm working to in this period I have to work a lot with stored procedures in SQL Server, actually I use this method (using Dapper):

public static foo_type call_stored_procedure(string stored_procedure_name, string ConnectionString, type_param_1 param1, type_param_2 param2)
{
    using (var connection = new SqlConnection(ConnectionString))
    {
         connection.Open();
         var server_return = connection.Query<foo_type>(stored_procedure_name,
                        new { param1 = param1, param2 = param2 }, commandType: CommandType.StoredProcedure);

         if (server_return != null)
             return server_return;
     }

     return null;
 }

This method has the following two problems:

Can return only object of foo_type type
Accept only two parameters

I'd like to make this method general, so

I need to pass all the params of the stored procedure
I'd like to tell the function the type of the objects that will be returned from the stored procedure

The function declaration will be something like:

public static <T> call_stored_procedure(string stored_procedure_name, string ConnectionString, <T> type, <T> params...)

Is there a way to do something like this in C#?

Read about method overloading. of course it is not an answer to your question but method caller feels like calling same method — shreesha
– shreesha, Commented Aug 13, 2015 at 12:55

Alex Lebedev · Accepted Answer · 2015-08-13 13:22:43Z

1

Try this:

public T CallStoredProcedure<T>(connectionString, procName, params object[] parameters)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        var server_return = connection.Query<T> (procName, parameters, commandType:  CommandType.StoredProcedure);
        if (server_return != default(T))
            return server_return;
        }
        return default(T);
}

answered Aug 13, 2015 at 13:22

Alex Lebedev

6095 silver badges14 bronze badges

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

Comments

marc_s · Accepted Answer · 2016-03-09 06:37:02Z

0

Try passing a Dictionary<string,object> as a parameter and return object.

It is similar to how ASP.NET MVC allows any number of parameters to be sent to an Action. Your stored procedure will retrieve the parameters it needs from the dictionary by their key.

edited Mar 9, 2016 at 6:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 13, 2015 at 12:55

Shayno

897 bronze badges

Collectives™ on Stack Overflow

C# ASP.NET: create general method to call Stored Procedure with any number of parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related