ADO.NET将多个值插入SQL参数 [英] ADO.NET insert multiple values to SQL parameter

问题描述

我收到了 Dictionary< string，string> ，并希望将其值转发到 SqlParameter 。那有可能吗？这就是我这样做的方式，但我收到一个错误，即列名与表定义不匹配。

I am receiving a Dictionary<string, string> and would like to forward its values to the DB inside SqlParameter. Is that even possible? This is the way I did it, and I am getting an error that column name doesn't match table definition.

SqlParameter param = new SqlParameter();
param.ParameterName = "@Values";

var sb = new StringBuilder();
foreach (var item in data)
{
    sb.Append("'" + item.Value + "', ");
}
param.Value = sb.ToString().TrimEnd(',');

string insertString = $"insert into {tableName} values (@Values)";
SqlCommand command = new SqlCommand(insertString, connection);

command.Parameters.Add(param);
command.ExecuteNonQuery();

推荐答案

Sql服务器无法解释您所使用的单个变量作为多个值传递。

可以使用多个变量生成查询，也可以使用表值参数。

对于第一个选项，必须更改构建查询的方式：

Sql server can't interpret the single variable you are passing as multiple values.
You can either generate your query with multiple variables, or use a table valued parameter.
For the first option, you must change the way you build your query:

var command = new SqlCommand();

var insertString = $"insert into {tableName} values (";
var sb = new StringBuilder(insertString);
int i = 0;
foreach (var item in data)
{
    sb.Append("@P").Append(i).Append(",");
    command.Parameters.Add("@P" + i, SqlDbType.VarChar).Value = item.Value;
    i++;
}
command.Connection = connection;
command.CommandText = sb.ToString().TrimEnd(",") + ");";
command.ExecuteNonQuery();

注意：代码未经测试，可能存在一些错误。

Note: Code was not tested, there might be some errors.

对于第二个选项，您必须使用存储过程。我从未尝试过将表值参数传递给内联查询，而且我认为这是不可能的。

这篇文章（也在Alex K的评论中链接）解释了如何做到这一点。

For the second option, You must use a stored procedure. I've never tried to pass table valued parameters to an inline query and I don't think it's possible.
This post (also linked in Alex K's comment) explains how to do that.

这篇关于ADO.NET将多个值插入SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！