What is the best code to call stored procedure with many parameters.

Question

Hi fellows,

I have realized that I have a very large method, which creates SqlParameter objects and adds them to SqlCommand (cmd). For instance:

SqlParameter itemType = new SqlParameter
{
    ParameterName = "ItemType",
    Direction = ParameterDirection.Input,
    SqlDbType = SqlDbType.Int,
    Value = (int)item.ItemType
};

cmd.Parameters.Add(itemType);

A stored procedure has a lot of parameters by design and the design cannot be changed.

I do not like to have several pagedowns of code to create the parameters. It is hard to read/support. Of course I can use regions here, but the question is about the code.

Do you have any ideas how can I improve my code?

Currently, I see only one way here: I need to use custom attributes to specify parameter name, direction and db type for each item property. In this case, I need to use reflection and I am not sure that it is the best choice.

That do you think?

Thank you.

Answer 1

Erland Sommerkog's article Arrays and Lists in SQL Server 2005 "describes a number of different ways to do this, both good and bad."

Duplicate of: Since there is no Sqlserver array parameter, what’s the best way to proceed?

Answer 2

May be I understood your question wrongly, but why not just abstract away the code that instantiates the parameter and adds it to a command object to a helper function?

Something like this...

AddSqlParam("ParamName1",SqlDbType.Int,val1,ParameterDirection.Input, command); AddSqlParam("ParamName2",SqlDbType.String,val2,ParameterDirection.Input, command);

You can further refactor this by having commonly used parameters into little helper functions. For example most databases have an ID column.

So you could have a method like AddIdParam(command);

I hope I conveyed my point.

Answer 3

This is a little bit more condensed format you could use

SqlCommand command = new SqlCommand();

command.Parameters.Add(new SqlParameter("@param1", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value1";

command.Parameters.Add(new SqlParameter("@param2", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value2";

command.Parameters.Add(new SqlParameter("@param3", SqlDbType.NVarChar, 255));
command.Parameters[command.Parameters.Count - 1].Value = "value3";

Answer 4

Another way to do this is to make the SqlCommand a member field of the class, and have a member method to initialize it.

This is similar to the code that Visual Studio generates when you create a Component, then drag a SqlCommand onto the design surface. If you configure the SqlCommand with a parameterized query or stored procedure with parameters, then it will generate code to create the SqlParameter objects and add them to the Parameters property.

To use thie SqlCommand later in your code, you would do:

m_Command.Parameters["@ParamName"].Value = value;