So, want to make a multi-row insert query, and I need to replace the keys with the values inside a loop where I have the values.
It was working by hardcoding the values into the query string, but I need to do it by using the "cmd.Parameters.AddValue() or cmd.Parameters.AddWithValue()" as I need to prevent SQL Injection.
So, my code is something like this:
string query = "insert into dbo.Foo (column1, column2, column3) values ";
SqlCommand cmd
foreach (line in rowsArray) {
cmd.Parameters.Clear();
cmd = new SqlCommand(query, cnn); //So, the problem is this override
query += "(@key1, @key2, @key3), ";
cmd.Parameters.AddWithValue("@key1", line.value1);
cmd.Parameters.AddWithValue("@key2", line.value2);
cmd.Parameters.AddWithValue("@key3", line.value3);
}
query = query.Substring(0, query.Length-2); //Last comma
cmd.ExecuteNonQuery();
cnn.Close();
I want to ExecuteNonQuery(); outside the loop, to make just one insert.
Any ideas?
I thought about making a loop where I add the keys in the string with a identifier and then replacing all of them iterating another loop with the same id's, but I don't see that very efficient or a good practice.
I finnaly decided to make two loops as It worked better than expected.
I thought about making a loop where I add the keys in the string with a identifier and then replacing all of them iterating another loop with the same id's, but I don't see that very efficient or a good practice.
With this approach I finish the string query and then I add the values. I give the keys ids so I can replace them with the values in order in the next loop using the same id.
string query = "insert into dbo.Foo (column1, column2, column3) values ";
int id = 0;
foreach (line in rowsArray) {
query += "(@key1"+id+", @key2"+id+", @key3"+id+"), ";
id++;
}
query = query.Substring(0, query.Length-2); //Last comma
SqlCommand cmd = new SqlCommand(query, cnn);
id = 0;
foreach (line in rowsArray) {
cmd.Parameters.AddWithValue("@key1"+id, line.value1);
cmd.Parameters.AddWithValue("@key2"+id, line.value2);
cmd.Parameters.AddWithValue("@key3"+id, line.value3);
}
cmd.ExecuteNonQuery();
cnn.Close();
Also, mention the existance of SqlBulkCopy, which allows to upload DataTables and will result in a cleaner code.