sfaust sfaust - 1 month ago 9
SQL Question

Add multiple SQL values with same parameterized query?

I'm fairly new to SQL and trying to figure out the best way to add some predefined data. I figured out from searching around here that I should used a parameterized command to avoid a sql injection attack which isn't a huge concern in this case but I would like to avoid the possibility and learn to do it right... Anyway here is the code I have right now:

using (SqlTransaction trans = connection.BeginTransaction())
{
foreach (IEnumerable<string> row in table.RowData)
{
using (SqlCommand sql = new SqlCommand("INSERT INTO " + table.Title
+ " (" + string.Join(", ", table.Headers)
+ ") VALUES (" + string.Join(", ", table.Headers.Select(x => "@" + x)) + ");", connection, trans))
{

for (int i = 0; i < table.Headers.Count(); i++)
{
if (string.IsNullOrEmpty(row.ElementAt(i)))
{ sql.Parameters.AddWithValue("@" + table.Headers.ElementAt(i), DBNull.Value); }
else
{ sql.Parameters.AddWithValue("@" + table.Headers.ElementAt(i), row.ElementAt(i)); }
}
sql.ExecuteNonQuery();
}
}
trans.Commit();
}


This seems to work and all the data gets in there but it 'feels' inefficient to me. I'm wrapping it in a transaction so there is only one commit, but it's creating the parameters every time and just setting different values for each row.

Is there a way to make this use the same parameters but just set different values per row? Or is this the best way to do this and I should not worry about it?

Thanks in advance for any help you can give.

Answer

We can do what you want by parsing the headers into parameters it a pre-processing step. I have also removed the explicit transaction because every single insert already gets an implicit transaction by default (why pay the performance penalty of two transactions?).

using (var command = new SqlCommand()) {
    command.CommandText =
        "INSERT INTO " + table.Title + " ("
      + string.Join(", ", table.Headers)
      + ") VALUES ("
      + string.Join(", ", table.Headers.Select(x => "@" + x))
      + ");";
    command.Connection = connection;

    foreach (var header in table.Headers) {
        command.Parameters.Add("@" + header);
    }

    foreach (var row in table.RowData) {
        for (var i = 0; i < table.Headers.Count(); i++) {
            if (!string.IsNullOrEmpty(row.ElementAt(i))) {
                command.Parameters["@" + table.Headers.ElementAt(i)] = row.ElementAt(i);
            }
            else {
                command.Parameters["@" + table.Headers.ElementAt(i)] = DBNull.Value;
            }
        }

        command.ExecuteNonQuery();
    }
}