如何正确清理(或参数化)SQL压缩插入语句 [英] How to properly sanitize (or parameterize) SQL Compressed Insert Statement

问题描述

编辑请注意，该语句不是单个插入.它使用foreach块插入多个值数组.

EDIT Notice that the statement isn't a single insert. It inserts multiple value arrays using the foreach block.

StringBuilder sbSQL = 
  new StringBuilder(
    "INSERT INTO [table] ([fk_id], [Description], [Title] ) VALUES");

foreach(var item in items) {
    sbSQL.AppendFormat("({0},'{1}','{2}'),", 
      item.fk_id, item.description, item.title)
}

myDataContext.ExecuteCommand(sbSQL.Remove(sbSQL.Length - 1, 1).ToString());
myDataContext.SubmitChanges();

我真的很想使用这种方法，因为它加快了数据库插入2次的速度.但是我不知道该如何对它进行参数化.

I really would like to use this method, as it sped up DB inserts 2 times. But I don't know how I am supposed to paramaterize it.

推荐答案

我开始写评论，但这太长了.

I started writing a comment, but this was too long.

LINQ-to-SQL中的参数化查询对您来说应该很好.像这样:

The parameterized queries in LINQ-to-SQL should do just fine for you. Something like this:

StringBuilder sbSQL = new StringBuilder(
       "INSERT INTO [table] ([fk_id], [Description], [Title] ) VALUES");

int paramNum = 0;
List<object> paramValues = new List<object>();

foreach(var item in items) 
{
    sbSQL.AppendFormat("({{{0}}},{{{1}}},{{{2}}}),", 
        paramNum, 
        paramNum + 1, 
        paramNum + 2);

    paramValues.Add(item.fk_id);
    paramValues.Add(item.description);
    paramValues.Add(item.title);

    paramNum += 3;
}

myDataContext.ExecuteCommand(
    sbSQL.Remove(sbSQL.Length - 1, 1).ToString(), 
    paramValues.ToArray());

ExecuteCommand 函数仅接受您的SQL命令，并使用与标准.NET字符串格式函数兼容的令牌({0})进行组合，然后将您传递的值转换为参数，并使用其生成的参数名称代替令牌.您可以根据自己的喜好编写命令并排列参数.它不会检查或解析命令.

The ExecuteCommand function just takes your SQL command, complete with tokens ({0}) compatible with standard .NET string formatting functions, then turns the values you pass into parameters and uses its generated parameter names in place of the tokens. It's up to you to write the command and arrange the parameters however you like; it's not going to inspect or parse the command.

这篇关于如何正确清理(或参数化)SQL压缩插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！