Matthew Matthew - 17 days ago 5
ASP.NET (C#) Question

Passing 0-n parameters to SQL Stored Procedure executer method

I am creating a method that can be called from anywhere in my application that will take in the named of a stored procedure and a list of parameters to pass to it.

In doing this I ran across the

Parameters.AddWithValue
command, but also ran across a blog posts and some SO posts that say this is bad due to conversion issues. They all recommended to add parameters using

Parameters.Add(PARAMETER, SqlDbType.TYPE);


but the problem with this is if I have a method like mine how do I properly use the
Parameters.Add
method when I don't know what type the parameters are when they come in? What is a good way to address this, or am I being overly paranoid and just should stick with
Parameters.AddWithValue
?

For reference here is the base method right now that I am attempting to update so it can handle parameters

public static DataTable ExecuteDynamicsStoredProc(string procedureName)
{
var dataTable = new DataTable();

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DynamicsDB"].ToString()))
{
using (var command = new SqlCommand("c2s_ProjectPerformanceReport", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;

var dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;

dataAdapter.Fill(dataTable);

return dataTable;
}
}
}

Answer

You can make the method accept a SqlParameter[] and use command.Parameters.AddRange().

public static DataTable ExecuteDynamicsStoredProc(string procedureName, SqlParameter[] args) {
    var dataTable = new DataTable();

    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DynamicsDB"].ToString())) {
        using (var command = new SqlCommand(procedureName, connection)) { //use passed in proc name
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(args); //add all the parameters
            var dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = command;
            dataAdapter.Fill(dataTable);
            return dataTable;
        }
    }
}

public static void ExecuteProcOne(string name, int age, bool alive) {            
    SqlParameter p1 = new SqlParameter("name", name);
    SqlParameter p2 = new SqlParameter("age", age);
    SqlParameter p3 = new SqlParameter("alive", alive);

    var result = ExecuteDynamicsStoredProc("ExecuteProcOne", new SqlParameter[] { p1, p2, p3 });
}

Use methods like ExecuteProcOne() to handle the individual procedures with their respective datatypes. You can extract this out further to make a method return the SqlParameter[].

This way you can just call MyProcName and you know what parameters you need from intellisense.

Comments