How to add a collections of parameters to an SqlCommand before query execution in C#?

Question

I have this code where I want to add a collections of Parameters to an SqlCommand.

public void AddParameters(Dictionary<string, object> parameters)
{
    if (parameters == null)
        return;

    foreach(KeyValuePair<string, object> keyValue in parameters)
    {
        sqlCommand.Parameters.AddWithValue(keyValue.Key, keyValue.Value);
    }
}

I want to make it more efficienty without using a loop because I can have a very large collection of parameters. Is there any way to add the entire collection with a single function?

The "single function" would have a loop inside it. So in what way is it different? — user5014677, Jul 04 '17 at 10:05
You are optimizing wrong things. Don't care about micro seconds but code safety. Using `AddWithValue` isn't safe and can cause performance(or other) problems because the type of the parameter must be derived from the value. — Tim Schmelter, Jul 04 '17 at 10:06
I have another loop before calling this function to build the dictionary of my parameters, so I try to find an optimized function to add parameters in SqlCommand without using another loop. — Riccardo, Jul 04 '17 at 10:13
Thank you Tim Schmelter, what can I use instad of AddWithValue function? Now in another function I'm using SqlParameter class and I add this to the collection Parameters of SqlCommand — Riccardo, Jul 04 '17 at 10:16

score 3 · Answer 1 · answered Jul 04 '17 at 10:16

The only way to "optimize" that scenario is to already have a DbCommand instance sat around with the correct command-text / type, and the parameters already added, and you simply set the .Value of each in turn before executing it against the connection. However, this demands careful synchronization if you are using multiple connections / threads, so would need to be done carefully. Ultimately, anything you do here is going to be nothing compared to the latency of talking to a database, so: it is very likely that this is simply premature optimization and yak shaving - meaning: you might feel like you've achieved something useful here, but all you're actually doing is spending your time.

score 1 · Answer 2 · answered Jul 04 '17 at 10:20

I don't like several things

why you need a public method to add parameters to a private SqlCommand which should not be a field at all but which should be created where you are using it. This is too error-prone.
You are optimizing wrong things. Don't care about micro seconds but code safety. Using AddWithValue isn't safe and can cause performance(or other) problems because the type of the parameter must be derived from the value

If at all you could use such a method but with little benefit:

void AddParameters(params SqlParameter[] parameters)
{
    if (parameters == null || parameters.Length == 0)
        return;

    sqlCommand.Parameters.AddRange(parameters);
}

The params keyword allows to use it in this way:

SqlParameter pararmeter = new SqlParameter("@Column", SqlDbType.Int);
pararmeter.Value = 1234;
AddParameters(pararmeter);

Deepak Mishra · Answer 3 · 2017-07-04T10:30:59.580

0

You can use AddRange method to improve the performance slightly:

 public void AddParameters(SqlParameter[]  parameters)
    {
        if (parameters == null)
            return;
        sqlCommand.Parameters.AddRange(parameters);

    }

edited Jul 04 '17 at 10:30

answered Jul 04 '17 at 10:18

Deepak Mishra

2,984
1
26
32

@MarcGravell not sure...just saw this question..https://stackoverflow.com/questions/9836471/why-is-addrange-faster-than-using-a-foreach-loop – Deepak Mishra Jul 04 '17 at 10:20
2

indeed, but there's two problems here; a: the moment we add db latency into the mix, everything else we do will be **irrelevant marginal data**, and b: I would wager that you've actually **added** time here (not removed it), via the `ToArray` - so now it is looping *twice* instead of once, *and* doing a heap alloc / copy. – Marc Gravell Jul 04 '17 at 10:28
@MarcGravell: Corrected. – Deepak Mishra Jul 04 '17 at 10:31

How to add a collections of parameters to an SqlCommand before query execution in C#?

3 Answers3