在C#中的SQL语句中使用int[]中的值

本文关键字：int 中的 SQL 语句 | 更新日期: 2023-09-27 18:20:05

我想要运行以下SQL语句：

string sql = @"DECLARE @a udt;" +
              "INSERT INTO @a (id) VALUES @params;";

我有以下整数数组：

int[] array1 = {10,20,30,40,50,60};

重要的是不要让上面的数组大小在我的程序中是动态的。

我想将数组中的值添加到SQL语句中的@params中，以便在SqlCommand中执行的SQL看起来如下：

sql = @"DECLARE @a udt;" +
       "INSERT INTO @a (id) VALUES (10),(20),(30),(40),(50),(60);" +
       "EXEC sp @a;";

我尝试了以下方法，并不断获得例外：

SqlConnection con = new SqlConnection("connectionString");
SqlCommand cmnd = new SqlCommand(sql, con);
for (int i = 0; i < array1.Count; i++)
{
    cmnd.Parameters.AddWithValue("@params" , array1[i]);
}
con.open().
cmnd.ExecuteNonQuery();

请有人解释一下我做错了什么。我还需要确保防止SQL注入。

在C#中的SQL语句中使用int[]中的值

您已经接近解决方案，但需要显式引用sql字符串中的每个数组索引参数。例如：

    List<int> tests = new List<int>() { 10, 11, 12};
    var command = new MySqlCommand();
    List<string> parameterNames = new List<string>();
    for (int i = 0; i < tests.Count; i++)
    {
            string parameterName = String.Format("@test{0}", i);
            command.Parameters.AddWithValue(parameterName, tests[i]);
            parameterNames.Add("(" + parameterName + ")");
    }
    string insertSql = @"insert into test(id) values {0}";
    command.CommandText = String.Format(insertSql, String.Join(", ", parameterNames));
    command.Connection = connection;
    int result = command.ExecuteNonQuery();

生成的sql命令文本将是：

"insert into test(id) values (@test0), (@test1), (@test2)"

根据@params参数加入Int数组进行格式化：

int[] intArr = { 10, 20, 30 };
string intStr = string.Join(",", intArr.Select(i => "(" + i.ToString() + ")"));

intStr的形式为(10),(20),(30)，您可以将其传递给sql。创建动态语句并执行它。

-- declared here for sample
declare @params varchar(50) = '(10),(20),(30)'
declare @sql varchar(max) = 
    'declare @a table (id int)' + ';' +
    'insert into @a (id) values ' + @params + ';' +
    'exec sp @a'
exec (@sql)

您可以为values子句生成动态SQL。这将导致一些性能开销，具体取决于此代码的运行频率，因为计划缓存重用性较差。

我会将数组作为分隔字符串传递给过程，并使用SQL字符串拆分器将字符串转换为表。这允许您将单个参数传递给过程，并可能绕过UDT。

有多种字符串拆分器可用于网络搜索。你发现任何使用循环的方法都会很慢，但会起作用。我个人更喜欢Jeff Moden的内联表值函数[DimitedSplit8K]（需要免费注册，但我认为这很值得），它可能是最快的TSQL字符串拆分器。

任何拆分器的用法都类似于此：

DECLARE @a udt;
INSERT INTO @a([id])
SELECT [Item]
FROM [dbo].[DelimitedSplit8K](@params)

这将把{10,20,30,40,60}转换为每行一个值的表。

另一种用法是加入：

SELECT 
    [col1],
    [col2],
    [ItemNumber] -- position in array
FROM [dbo].[MyTable] A
INNER JOIN [dbo].[DelimitedSplit8K](@params) B
ON       A.[col3] = B.[Item] -- join on array value
相关文章：