SQL Parameters Inside A Loop

Question

i have a list that i am pulling things out of to insert into a database. This is not going to be a web app so i have just been doing as follows:

string sqlStorage = (null,"asd"),

for (int i = 1; i < listsize; )
{

sqlStorage = sqlStorage + "(null,someVariableFromLoop)";

i++

}

string connString = "Server=localhost;...........";

MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO table1 VALUES " + tempSQLStorage;

etcetc...

However

"someVariableFromLoop"

is a large amount of text which includes all kinds of horrible code breaking characters. quotation marks etc etc.

So i looked into parameters (the way i should be doing SQL i know, i know), however i was unable to find a way to store these parameters inside the loop. i dont want to hit the DB every single iteration. I had a go at something along the lines of

"@variable"+i.toString();

but could not get it to work at all.

So does anyone have any idea how i would go about storing the parameters and the execute the query? Thanks in advance!

The first reason I would look to parameters is to increase security. If you were accepting input from the UI and shoving it directly to the database, you were wide open to SQL injection attacks. — Eric J., Jul 03 '14 at 18:40
Assuming this is SQL Server, you should be looking at either table-valued parameters or (if its a very large number of lines), the SQLBulkCopy api. — RBarryYoung, Jul 03 '14 at 18:42
@EricJ. 100% agreed. This however is just a locally hosted dirty little script to process some data i have. — loveforfire33, Jul 03 '14 at 18:43
Your `for` loop is rather strange, the usual way to write it is `for (int i = 0; i < listsize; i++)` and not use `i++` inside the loop. — Andrew Morton, Jul 03 '14 at 18:46

score 1 · Accepted Answer · edited May 23 '17 at 11:57

So i looked into parameters (the way i should be doing SQL i know, i know), however i was unable to find a way to store these parameters inside the loop. i dont want to hit the DB every single iteration. I had a go at something along the lines of "@variable"+i.toString(); but could not get it to work at all.

Well, what was the error you received? Because that's the way you do it. Here's an example for MSSQL and I know the technique works, because I've done similar before:

int i = 0;
List<string> clauses = new List<string>() {"(@key0, @value0)"};
List<SqlParameter> paramList = new List<SqlParameter> {
    new SqlParameter("@key0", DBNull.Value), 
    new SqlParameter("@value0", "asd")
};
for (i = 1; i < listSize; i++) {
    clauses.Add("(@key" + i + ", @value" + i + ")");
    paramList.Add(new SqlParameter("@key" + i, someKey));
    paramList.Add(new SqlParameter("@value" + i, someValue);
}
SqlConnection conn = new SqlConnection(connString);
SqlCommand command = new SqlCommand(conn, @"INSERT INTO table1 VALUES " + String.Join(", ", clauses);
foreach(SqlParameter param in paramList) command.Parameters.Add(param);
command.ExecuteNonQuery();

Note, above code is quick and dirty. Obviously using statements and various other best practices should be incorporated as well for production code.

Also look at this: How do you use the MySql IN clause. It has an example of dynamically creating and passing parameters to the query, but for an SELECT...IN clause vs. INSERT...VALUES.

score 0 · Answer 2 · edited Sep 12 '17 at 20:20

0

To ensure secure code (and avoid malformed queries), use SQL Command objects with Parameters. There is nothing horribly wrong with executing the command once for every record - a little extra overhead for round-trips over the network, but if the text is long you might have to do this anyway since queries do have a character limit.

edited Sep 12 '17 at 20:20

StayOnTarget

11,743
10
52
81

answered Jul 03 '14 at 18:48

SlimsGhost

2,849
1
10
16

Disagree about negligible overhead - network communications with a database are not negligible. – zimdanen Jul 03 '14 at 19:15
Assuming connection pooling, and execution plan reuse, could you help me understand what additional overhead there would be from issuing one insert with several value lists versus several inserts with one value list each? Unless someone tells me otherwise, I will continue thinking that the network comms are nearly identical (apart from multiple instances of the INSERT keyword and the field list). If I'm wrong, please explain. – SlimsGhost Jul 03 '14 at 19:20
The roundtrip itself is what adds the performance hit. If OP is inserting, say, 100 items, doing 100 roundtrips is going to take a noticeably-longer time than doing one. – zimdanen Jul 03 '14 at 19:29
1

Fair enough. I wish I had a benchmark handy to see what the multiple roundtrips add *percentage-wise* to the overall execution time. I suspect it to be small, but definitely not zero, so I've edited my answer above. – SlimsGhost Jul 03 '14 at 19:51

SQL Parameters Inside A Loop

2 Answers2

Linked