循环内的SQL参数 [英] SQL Parameters Inside A Loop

问题描述

我有一个列表，我正在拉出要插入数据库的内容.这不会是一个Web应用程序，所以我一直在做如下操作:

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...

但是

"someVariableFromLoop"

"someVariableFromLoop"

是大量文本，其中包含各种可怕的代码破译字符.引号等.

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

所以我研究了参数(我应该知道，我应该做的SQL方式)，但是我找不到在循环内存储这些参数的方法.我不想每次迭代都击中数据库.我喜欢

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

"@variable"+i.toString();

但根本无法正常工作.

那么没有人知道我将如何存储参数并执行查询吗?预先感谢！

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

推荐答案

所以我调查了参数(我应该知道，我应该做的SQL方式)，但是我找不到存储方法.这些参数在循环内.我不想每次迭代都击中数据库.我尝试了一些类似的事情 "@variable" + i.toString(); 但根本无法正常工作.

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.

那么，您收到的错误是什么?因为那是您这样做的方式.这是MSSQL的示例，我知道该技术有效，因为我之前做过类似的事情:

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

请注意，上面的代码既快速又肮脏.显然，对于生产代码，using语句和各种其他最佳实践也应纳入其中.

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

也请看一下:如何使用MySql IN子句.它有一个动态创建参数并将其传递给查询的示例，但是对于SELECT ... IN子句还是INSERT ... VALUES.

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.

这篇关于循环内的SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！