IDBCommand Parameters for multi-line insert

Question

I want to create a multi-line insert statement along these lines:

insert into foo (
    value1,
    value2,
    value3
) values 
( "1", "2", "3"),
( "4", "5", "6"),
( "7", "8", "9");

where the number of rows to be inserted is variable. Is there a way to construct this query using IDBCommand parameterization?

iamkrillin · Accepted Answer · 2013-02-07T20:26:41.030

1

Something like this should work fine.

IDbCommand cmd = new SqlCommand();
StringBuilder sb = new StringBuilder();
sb.Append("insert into foo(col, col2, col3), values");
int parms = 0;

for(int i = 0 ; i<3; i++)
{
    sb.AppendFormat("( @{0}, @{1}, @{2}),", parms, parms + 1, parms + 2);
    cmd.Parameters.Add(new SqlParameter((parms++).ToString(), ""));
    cmd.Parameters.Add(new SqlParameter((parms++).ToString(), ""));
    cmd.Parameters.Add(new SqlParameter((parms++).ToString(), ""));
}
sb.Append(";");
cmd.Parameters;
cmd.CommandText = sb.ToString().Replace(",;", ";");

edited Feb 07 '13 at 20:26

answered Feb 07 '13 at 20:19

iamkrillin

6,798
1
24
51

Is there a limit to how many parameters one can add to a statement? – Jeremy Holovacs Feb 07 '13 at 23:00
I believe that SQL server has a limit in the 200-300 range – iamkrillin Feb 08 '13 at 04:32

C.Evenhuis · Answer 2 · 2013-02-07T20:33:13.397

0

There is, to my knowledge, no way to supply a variable number of parameters to an IDbCommand at once. You can create the IDbCommand once and reuse it for each set of parameter values though:

using (IDbCommand command = myConn.CreateCommand())
{
    command.CommandText = "insert into foo (value1, value2, value3) values (@v1, @v2, @v3)";
    IDbDataParameter param1 = command.CreateParameter() { ParameterName = "@v1" };
    // param2, param3
    foreach (string[] row in records)
    {
        param1.Value = row[0];
        // param2, param3
        command.ExecuteNonQuery();
    }
}

Especially within a transaction this should fast if performance was your concern. See this thread for a discussion on performance of one big concatenated insert vs. multiple ExecuteNonQuerys in a transaction.

edited Feb 07 '13 at 20:33

answered Feb 07 '13 at 20:06

C.Evenhuis

25,996
2
58
72

Won't that require me to make a query per line though? Blech. – Jeremy Holovacs Feb 07 '13 at 20:11
I've updated my answer to show how only the parameter values need to be updated each time, not the entire command. I haven't tested the code, may contain a typo or two. – C.Evenhuis Feb 07 '13 at 20:15

IDBCommand Parameters for multi-line insert

2 Answers2