如何将sqlparameter传递给IN()

本文关键字：IN sqlparameter | 更新日期: 2023-09-27 18:25:16

由于某些原因，我的IN（）子句的Sql参数不起作用。代码编译得很好，如果我用实际值替换参数，查询就会工作

StringBuilder sb = new StringBuilder();
            foreach (User user in UserList)
            {
                sb.Append(user.UserId + ",");
            }
            string userIds = sb.ToString();
            userIds = userIds.TrimEnd(new char[] { ',' });

SELECT userId, username 
FROM Users 
WHERE userId IN (@UserIds)

如何将sqlparameter传递给IN()

您必须为IN子句中所需的每个值创建一个参数。

SQL需要如下所示：

SELECT userId, username 
FROM Users 
WHERE userId IN (@UserId1, @UserId2, @UserId3, ...)

因此，您需要在foreach循环中创建参数和IN子句
类似这样的事情（在我的脑海中，未经测试）：

StringBuilder sb = new StringBuilder();
int i = 1;
foreach (User user in UserList)
{
    // IN clause
    sb.Append("@UserId" + i.ToString() + ",");
    // parameter
    YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), user.UserId);
    i++;
}

如果您使用SQL 2008，您可以创建一个接受表值参数（TVP）的存储过程，并使用ADO.net执行存储过程并向其传递数据表：

首先，您需要在SQL server中创建Type:

CREATE TYPE [dbo].[udt_UserId] AS TABLE(
    [UserId] [int] NULL
)

然后，您需要编写一个存储过程，它接受这种类型作为参数：

CREATE PROCEDURE [dbo].[usp_DoSomethingWithTableTypedParameter]
(
   @UserIdList udt_UserId READONLY
)
AS
BEGIN
        SELECT userId, username 
        FROM Users 
        WHERE userId IN (SELECT UserId FROM @UserIDList) 
END

现在从.net，您不能使用LINQ，因为它还不支持表值参数；因此，您必须编写一个函数，该函数执行普通的旧ADO.net，获取DataTable，并将其传递给存储过程：我已经编写了一个通用函数，我使用它可以对任何存储过程执行此操作，只要它只获取一个表类型的参数，而不管它是什么；

    public static int ExecStoredProcWithTVP(DbConnection connection, string storedProcedureName, string tableName, string tableTypeName, DataTable dt)
    {
        using (SqlConnection conn = new SqlConnection(connection.ConnectionString))
        {
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p = cmd.Parameters.AddWithValue(tableName, dt);
            p.SqlDbType = SqlDbType.Structured;
            p.TypeName = tableTypeName;
            conn.Open();
            int rowsAffected = cmd.ExecuteNonQuery(); // or could execute reader and pass a Func<T> to perform action on the datareader;
            conn.Close();
            return rowsAffected;
        }
    }

然后，您可以编写DAL函数，该函数使用该实用程序函数和存储过程的实际名称；为了建立在您问题中的示例之上，以下是代码的样子：

    public int usp_DoSomethingWithTableTypedParameter(List<UserID> userIdList)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(int));
        foreach (var userId in updateList)
        {
            dt.Rows.Add(new object[] { userId });
        }
        int rowsAffected = ExecStoredProcWithTVP(Connection, "usp_DoSomethingWithTableTypedParameter", "@UserIdList", "udt_UserId", dt);
        return rowsAffected;
    }

注意上面的"connection"参数-我实际上在一个分部DataContext类中使用了这种类型的函数来扩展我的TVP功能的LINQ DataContext，并且仍然在这些方法中使用（使用var context=new MyDataContext（））语法。

只有当您使用SQL Server 2008时，这才会起作用——希望您是，如果不是，这可能是升级的好理由！当然，在大多数情况下和大型生产环境中，这并不是那么容易，但FWIW我认为，如果你有可用的技术，这是最好的方法。

可能的"清洁剂"；版本：

StringBuilder B = new StringBuilder();
for (int i = 0; i < UserList.Count; i++)
     YourCommand.Parameters.AddWithValue($"@UserId{i}", UserList[i].UserId);
B.Append(String.Join(",", YourCommand.Parameters.Select(x => x.Name)));

SQL Server将您的IN子句视为：

IN ('a,b,c')

它需要看起来像：

IN ('a','b','c')

有一种更好的方法来做你想做的事情。

  • 如果用户id在DB中，那么IN子句应该改为子查询，如下所示：

    IN (SELECT UserID FROM someTable WHERE someConditions)

  • 这是一个破解——它不能很好地处理索引，你必须小心它能正确地处理你的数据，但我过去曾成功地使用过它：

    @UserIDs LIKE '%,' + UserID + ',%' -- also requires @UserID to begin and end with a comma

相关文章：