将 SqlParameters 的动态数组传递到 SQL 字符串 IN 子句

Question

我有一种方法可以编写一个SqlCommand包含动态参数列表的方法。我的挑战是传递每个new SqlParameter (@Param0, value0), new sqlParameter (@Param1, value1)... 可能是另外 50 个 SQL 参数。它可以作为硬编码字符串传递，但可以sb.ToString()理解地传递将不起作用（因为逗号 - 它们是新参数）。如何编写循环或类似内容以传递正确数量的新参数？到目前为止，我的尝试如下：public ViewResult Index(int? ID){    using (var context = new Projects201819Context())       if (ID == null)       {           var sqlCommand = new SqlCommand();           // Array of item numbers - will change and can be longer/shorter, as required.           var SQL0 = "SELECT * FROM [database] WHERE material_id IN ({0})";           var idList = new List<int> { 11, 53, 125};           int[] idListArray = idList.ToArray();           var idParameterList = new List<string>();           var index = 0;           int IL = idList.Count;           // Create a SqlParameter for each element in the array called "@idParam0", "@idParam1"... and add to list idParameterList           foreach (var id in idList)           {               var paramName = "@idParam" + index;               sqlCommand.Parameters.AddWithValue(paramName, id);               idParameterList.Add(paramName);               index++;           }有 id 的地方很好，没有给出（的 else 部分if (id == null)）。关键是SPName- 这成功地将项目添加到newSPList列表中并string.join返回我需要的确切字符串（HELLO）但我不能将这个长字符串作为单独的参数传递 - 完全有道理 - 我只是不知道如何解决它！感谢您的支持！

Answer 1

让 SQL Server 完成所有肮脏的工作。像这样的东西。

var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split('{0}',','))";

var idList = new List<int> { 11, 53, 125};

int[] idListArray = idList.ToArray();

sqlCommand.CommandText = String.Format(SQL0, string.Join(",", idListArray));

// now execute the command

编辑

更安全、更高效的方式。

var SQL0 = "SELECT * FROM [database] WHERE material_id IN (select value from string_split(@ids,','))";

var idList = new List<int> { 11, 53, 125};

int[] idListArray = idList.ToArray();

sqlCommand.CommandText = SQL0;

sqlCommand.Parameters.Add("@ids", SqlDbTypes.VarChar, -1).Value = string.Join(",", idListArray);

// now execute the command

Answer 2

您不能以这种方式传递参数数组。EF6 中的SqlQuery有一个重载，它接受 SqlParameter 数组作为第二个参数。

你所要做的就是：

SqlParameter[] pms = sqlCommand.Parameters.Cast<SqlParameter>().ToArray();

var datainput = context.materials.SqlQuery(sqlCommand.CommandText, pms);

当然，这也意味着您当前的许多代码是不必要的，您可以将其丢弃

例如，您可以在没有仅用于存储参数和命令文本的 SqlCommand 对象的情况下编写此代码。

var SQL0 = "SELECT * FROM [database] WHERE material_id IN ({0})";

var idList = new List<int> { 11, 53, 125 };

var idParameterList = new List<string>();

var pms = new List<SqlParameter>();

int count = 1;

foreach (var id in idList)

{

    var paramName = "@idParam" + count++;

    SqlParameter p = new SqlParameter(paramName, SqlDbType.Int);

    p.Value = id;

    pms.Add(p);

    idParameterList.Add(paramName);

}

string cmdText = String.Format(SQL0, string.Join(",", idParameterList));

var datainput = context.materials.SqlQuery(cmdText, pms);