12

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.

4
  • i'm not sure why you are trying to compress this, but i'm pretty sure the way you are doing it is the most straightforward and compact way. Commented Jul 23, 2014 at 18:48
  • 1
    There is no way to make this shorter. Why do you want to? You could use a completely different library or something that automates it, but it wouldn't be cheaper, only look different. Commented Jul 23, 2014 at 18:50
  • 1
    Just curiosity honestly. Turns out there isn't a shorter way! Commented Jul 23, 2014 at 18:54
  • 1
    As far as I'm aware there isn't a way to shorten this. Six lines is not bad, but if your parameter really began to grow large you could consider some sort of looping algorithm if you felt it necessary. Otherwise, I would stick to this and use copy and paste as your friends. Commented Jul 23, 2014 at 19:00

4 Answers 4

14

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.

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

4 Comments

I'm afraid I fail to see how this is more compressed than the original code.
Not compressed it is just one line instead of three lines (excluding the last AddRange of course)
Oh well literally there are more lines but if you count the semicolons... Just kidding. The origina OP code is the most compact possible also looking at the IL code produced.
@Steve If myValue1 was an array, how would I implement that using your same notation?
11

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),
    });

3 Comments

Unless one of that values is an integer with a value of zero. See stackoverflow.com/questions/9999751/…
You are right. Casting an hard coded 0 as object new SqlParameter("@param", (object)0) would do the trick, but I agree it is not nice.
No I was not. Thanks for the link, it is as interesting as discouraging: "Always specifying the right data type, length, precision, and scale will ensure that you're not doing data type coercion at execution time." - ARGH I'm way too lazy! But I will remember this for extreme situations where performances are critical.
3

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

Comments

2

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]);
}

4 Comments

This could inspire an interesting solution for OleDB and ODBC, which have unnamed ordered parameters.
Yeah I've used the second option extensively in the past
Instead of two arrays, using a Dictionary<string, object> would remove the worry of mismatched indices.
@ShooShoSha I went to rewrite using a dictionary, but realized it did not satisfy the OP requirement of "compressing" the code... I think using a dictionary would actually add more lines of code to the OP question. This must have been the reason I gave an array solution as opposed to the dictionary when I did this 3 years ago. OP decided to go with his original code, which is probably the better solution anyway in my opinion. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.