sfaust sfaust - 9 months ago 46
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 Source

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