Подтвердить что ты не робот

Как передать sqlparameter в IN()?

По какой-то причине Sqlparameter для моего предложения IN() не работает. Код компилируется отлично, и запрос работает, если я подставляю параметр с фактическими значениями

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)

Ответ 1

Вам нужно создать один параметр для каждого значения, которое вы хотите в предложении IN.

SQL должен выглядеть следующим образом:

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

Итак, вам нужно создать параметры и в 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++;
}

Ответ 2

Если вы используете SQL 2008, вы можете создать хранимую процедуру, которая принимает параметр с табличными значениями (TVP), и использовать ADO.net для выполнения хранимой процедуры и передачи ему datatable:

Сначала вам нужно создать сервер типа SQL:

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, так как он еще не поддерживает параметры таблицы Table; поэтому вам нужно написать функцию, которая выполняет простой старый 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 для расширения LINQ DataContext с помощью моей функции TVP и по-прежнему использую синтаксис (используя var context = new MyDataContext()) с этими методами.

Это будет работать, только если вы используете SQL Server 2008 - надеюсь, вы есть, а если нет, это может стать хорошей причиной для обновления! Конечно, в большинстве случаев и в больших производственных средах это не так просто, но FWIW я считаю, что это лучший способ сделать это, если у вас есть доступная технология.

Ответ 3

Возможная "более чистая" версия:

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

Ответ 4

SQL Server видит ваше предложение IN как:

IN ('a,b,c')

На что он должен выглядеть:

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

Существует лучший способ сделать то, что вы пытаетесь сделать.

Если идентификатор пользователя находится в БД, то предложение IN должно быть изменено на подзапрос, например:

IN (SELECT UserID FROM someTable WHERE someConditions)
Это взлом - он не очень хорошо работает с индексами, и вы должны быть осторожны, он работает правильно с вашими данными, но я успешно использовал его в прошлом:

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