C# 如何将 sqlparameter 传递给 IN()？

How to pass sqlparameter to IN()?

提问by chobo

For some reason the Sqlparameter for my IN() clause is not working. The code compiles fine, and the query works if I substitute the parameter with the actual values

由于某种原因，我的 IN() 子句的 Sqlparameter 不起作用。代码编译得很好，如果我用实际值替换参数，查询就可以工作

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) 

采纳答案by Christian Specht

You have to create one parameter for each value that you want in the INclause.

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

The SQL needs to look like this:

SQL 需要如下所示：

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

So you need to create the parameters andthe INclause in the foreachloop.
Something like this (out of my head, untested):

所以，你需要创建的参数，并在IN在第foreach循环。
像这样的东西（在我的脑海里，未经测试）：

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++;
}

回答by John Pick

SQL Server sees your INclause as:

SQL Server 将您的IN子句视为：

IN ('a,b,c')

What it needs to look like is:

它需要看起来像：

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

There is a better way to do what you're trying to do.

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

• If the user id's are in the DB, then the INclause should be changed to a subquery, like so:

  IN (SELECT UserID FROM someTable WHERE someConditions)

• This is a hack -- it doesn't work well with indexes, and you have to be careful it works right with your data, but I've used it successfully in the past:

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

• 如果用户 ID 在数据库中，则该IN子句应更改为子查询，如下所示：

  IN (SELECT UserID FROM someTable WHERE someConditions)

• 这是一个技巧——它不适用于索引，你必须小心它适用于你的数据，但我过去曾成功使用过它：

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

回答by Dmitriy Khaykin

If you are using SQL 2008, you can create a stored procedure which accepts a Table Valued Parameter (TVP) and use ADO.net to execute the stored procedure and pass a datatable to it:

如果您使用的是 SQL 2008，您可以创建一个接受表值参数 (TVP) 的存储过程，并使用 ADO.net 执行存储过程并将数据表传递给它：

First, you need to create the Type in SQL server:

首先，您需要在 SQL 服务器中创建类型：

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

Then, you need to write a stored procedure which accepts this type as a parameter:

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

CREATE PROCEDURE [dbo].[usp_DoSomethingWithTableTypedParameter]
(
   @UserIdList udt_UserId READONLY
)
AS
BEGIN

        SELECT userId, username 
        FROM Users 
        WHERE userId IN (SELECT UserId FROM @UserIDList) 

END

Now from .net, you cannot use LINQ since it does not support Table Valued Parameters yet; so you have to write a function which does plain old ADO.net, takes a DataTable, and passes it to the stored procedure: I've written a generic function I use which can do this for any stored procedure as long as it takes just the one table-typed parameter, regardless of what it is;

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

    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;
        }
    }

Then you can write DAL functions which use this utility function with actual names of stored procedures; to build on the example in your question, here is what the code would look like:

然后，您可以编写 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;
    }

Note the "connection" parameter above - I actually use this type of function in a partial DataContext class to extend LINQ DataContext with my TVP functionality, and still use the (using var context = new MyDataContext()) syntax with these methods.

注意上面的“连接”参数——我实际上在部分 DataContext 类中使用这种类型的函数来扩展 LINQ DataContext 与我的 TVP 功能，并且仍然使用 (using var context = new MyDataContext()) 语法与这些方法。

This will only work if you are using SQL Server 2008 - hopefully you are and if not, this could be a great reason to upgrade! Of course in most cases and large production environments this is not that easy, but FWIW I think this is the best way of doing this if you have the technology available.

这仅在您使用 SQL Server 2008 时有效 - 希望您是，如果不是，这可能是升级的一个很好的理由！当然，在大多数情况下和大型生产环境中，这并不容易，但 FWIW 我认为如果您拥有可用的技术，这是最好的方法。

回答by mrogunlana

Possible "cleaner" version:

可能的“更清洁”版本：

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