CODEWITHSUNDEEP
c#sql
user3808188
user3808188

Reputation: 577

How to add multiple parameters to SQL command in one statement?

I have six lines of parameters like this:

    cmd.Parameters.AddWithValue("@variable1", myvalue1);
    cmd.Parameters.AddWithValue("@variable2", myvalue2);
    cmd.Parameters.AddWithValue("@variable3", myvalue3);

and so on.

Is there any way to compress this a bit without directly inserting in the cmd.CommandText?

Edit: I guess I could have used a good old fashioned array. I've decided to stick with this though.

Upvotes: 12

Views: 44677

Answers (4)

Larry
Larry

Reputation: 18051

I took the question literally: "...in one statement" :)

Steve code is nice but it can be simplified a bit more using the most canonical SqlParameter constructor and implicit arrays declaration:

cmd.Parameters.AddRange(new []
    {
        new SqlParameter("@variable1", myValue1),
        new SqlParameter("@variable2", myValue2),
        new SqlParameter("@variable3", myValue3),
    });

Upvotes: 11

warren.sentient
warren.sentient

Reputation: 573

I think this will read very nicely as a one liner like this:

Usage:

// One liner to create and set SqlCommand parameters
cmd.SetParameters(Parameter("@variable1", myvalue1), Parameter("@variable2", myvalue2), Parameter("@variable3", myvalue3));

To support the one liner you need to create a function to wrap the Sql Parameter as a semantic bundle (Tuple like) as follows:

public SqlParameter Parameter(string name, object value)
{
    return new SqlParameter(name, value);
}

Create a static class with an extension method to give us the syntactic sugar we are looking for. Notice the use of the params keyword which allows the multiple parameters in the above call to SetParameters.

public static class SqlDataUtils
{
    public static void SetParameters(this SqlCommand command, params SqlParameter[] parameters)
    {
        command.Parameters.AddRange(parameters);
    }
}

This answer is inspired by the accepted answer to Key value pairs in C# Params by Bryan Watts

Upvotes: 3

Steve
Steve

Reputation: 216363

As far as I know, your code is the most compact possible in term of lines count, however you could use the List<SqlParameter> with the object initializer syntax to have just one line terminated by a semicolon to build your parameter list, then pass that list as the array of parameters expected by the AddRange method

 List<SqlParameter> prm = new List<SqlParameter>()
 {
     new SqlParameter("@variable1", SqlDbType.Int) {Value = myValue1},
     new SqlParameter("@variable2", SqlDbType.NVarChar) {Value = myValue2},
     new SqlParameter("@variable3", SqlDbType.DateTime) {Value = myValue3},
 };
 cmd.Parameters.AddRange(prm.ToArray());

Notice that with this approach you need to define correctly the datatype of the parameter. In my example I have used some arbitrary types to show the correct syntax

A bit off-topic, by I think that in this general context is interesting to point out that AddWithValue is not to be considered when you want to get the best performance possible.

In this article on MSDN How data access code affects database perfomance is well explained why one should avoid the AddWithValue method for performance reasons.
In short, using AddWithValue could be a problem for the Sql Server Optimizer because the parameters of type string are passed with the size equal to the current length of the string. But this will force the Sql Server Optimizer to discard the query plan created for a previous identical call but with a string of different length.
It is better to call the SqlParameter constructor specifying the type and the size of the parameter and don't worry how to compress the size of the calls.

Upvotes: 14

Taylor Brown
Taylor Brown

Reputation: 1775

Just for argument's sake, using the code example you gave where the stored proc variables are literally named variabe1, variable2, etc... you could do something like this:

string[] myValues = new string[] { "myvalue1", "myvalue2", "myvalue3", "myvalue4", "myvalue5", "myvalue6" };
for (int i = 0; i < 6; i++) { cmd.Parameters.AddWithValue("@variable" + (i + 1),myValues[i]); }

2 lines of ugly code... LOL

A loop like this may come in handy if you had say 25 - 50 values, though I don't see that very often. And you could use 2 arrays one for the variable names and one for the values, as long as the indexes match up, then this would work:

string[] myVarNames = new string[] { "variable1", "variable2", "variableThree", "variable4our", "variableFIVE", "variableSIX" };
string[] myValues = new string[] { "myvalue1", "myvalue2", "myvalue3", "myvalue4", "myvalue5", "myvalue6" };

for (int i = 0; i < 6; i++)
{
    cmd.Parameters.AddWithValue("@" + myVarNames[i], myValues[i]);
}

Upvotes: 2

Related Questions