Parameterize insert of multiple rows

Question

Is there any way to parameterize an SQL INSERT statement (in C#), which inserts multiple rows? Currently I can think of only one way, to generate a statement for inserting mulitple rows, but that is quite open to SQL injection:

string sql = " INSERT INTO  my_table"
           + " (a, b, c)"
           + " VALUES";

// Add each row of values to the statement
foreach (var item in collection) {
    sql = sql
        + String.Format(" ({0}, {1}, {2}),",
              aVal, bVal, cVal);
}

// Remove the excessive comma
sql = sql.Remove(sql.Length - 1);

What is the smarter/safer way to do this?

Answer 1

You could add paramaters inside the loop, like:

using (var comm = new SqlCommand()) {
        var counter = 0;
         foreach (var item in collection) {
            sql = sql + String.Format(" (@a{0}, @b{0}, @c{0}),"  counter);

            comm.Parameters.AddWithValue("@a" + counter, aVal); 
            comm.Parameters.AddWithValue("@b" + counter, bVal);
            comm.Parameters.AddWithValue("@c" + counter, cVal);
            counter++;
        }
    }

But I really wouldn't do a multi-row insert like this. IIRC the maximum amount of parameters in a query is about 2100, and this could get very big very fast. As you're looping through a collection anyway, you could just send it to the database in your loop, something like:

using (var con = new SqlConnection("connectionString here"))
{
   con.Open();
   var sql = "INSERT INTO  my_table (a, b, c) VALUES (@a,@b,@c);"

   using (var comm = new SqlCommand(sql, con))
   {
       comm.Parameters.Add("@a", SqlDbType.Int);
       comm.Parameters.Add("@b", SqlDbType.NVarChar);
       comm.Parameters.Add("@c", SqlDbType.Int);
       foreach (var item in collection) {
       {
           comm.Parameters["@a"].Value = aVal;
           comm.Parameters["@b"].Value = bVal;
           comm.Parameters["@b"].Size = bVal.Length;
           comm.Parameters["@c"].Value = cVal;

           comm.ExecuteNonQuery();
       }
   }
}

The statement is prepared only once (and faster than a huge statement with 100's of parameters), and it doesn't fail all records when one record fails (add some exception handling for that). If you want to fail all when one record fails, you could wrap the thing up in a transaction.

Edit: Ofcourse, when you regularly have to input 1000's of rows, this approach isn't the most efficient either, and your DBA might start to complain. There are other approaches to this problem to remove the strain from the database: for example, create a stored procedure in your database that will insert the data from an xml document, or use Table Valued Parameters. NYCdotNet wrote 2 nice blogs about these options, which I won't recreate here, but they're worth exploring (I'll paste some code below from the blog, as per guidelines, but credit where it's due: NYCdotNet) XML document approach Table Valued Parameters

The "meat" from the blog about TVP (in VB.NET but that shouldn't matter):

So I created this "generic" table-valued type:

 CREATE TYPE dbo.UniqueIntegerList AS TABLE

 (

        TheInteger INT NOT NULL

     PRIMARY KEY (TheInteger)

 );

Creating the Save Stored Procedure

Next, I created a new stored procedure which would accept my new Table-Valued Type as a parameter.

 CREATE PROC DoTableValuedParameterInsert(@ProductIDs
 dbo.UniqueIntegerList READONLY)

 AS BEGIN



        INSERT INTO ProductsAccess(ProductID)

        SELECT TheInteger AS [ProductID]

        FROM @ProductIDs;



 END

In this procedure, I am passing in a parameter called @ProductIDs. This is of type "dbo.UniqueIntegerList" which I just created in the previous step. SQL Server looks at this and says "oh I know what this is - this type is actually a table". Since it knows that the UniqueIntegerList type is a table, I can select from it just like I could select from any other table-valued variable. You have to mark the parameter as READONLY because SQL 2008 doesn't support updating and returning a passed table-valued parameter.

Creating the Save Routine

Then I had to create a new save routine on my business object that would call the new stored procedure. The way you prepare the Table-Valued parameter is to create a DataTable object with the same column signature as the Table-Valued type, populate it, and then pass it inside a SqlParameter object as SqlDbType.Structured.

 Public Sub SaveViaTableValuedParameter()



   'Prepare the Table-valued Parameter'

  Dim objUniqueIntegerList As New DataTable

  Dim objColumn As DataColumn =
  objUniqueIntegerList.Columns.Add("TheInteger", _

  System.Type.GetType("System.Int32"))

   objColumn.Unique = True



   'Populate the Table-valued Parameter with the data to save'

   For Each Item As Product In Me.Values

     objUniqueIntegerList.Rows.Add(Item.ProductID)

   Next



   'Connect to the DB and save it.'

   Using objConn As New SqlConnection(DBConnectionString())

     objConn.Open()

     Using objCmd As New SqlCommand("dbo.DoTableValuedParameterInsert")

       objCmd.CommandType = CommandType.StoredProcedure

       objCmd.Connection = objConn

       objCmd.Parameters.Add("ProductIDs", SqlDbType.Structured)



       objCmd.Parameters(0).Value = objUniqueIntegerList



       objCmd.ExecuteNonQuery()

     End Using

     objConn.Close()

   End Using

 End Sub