Ошибка преобразования при преобразовании varchar в тип данных int: SQL

Question

Я создаю веб-приложение в asp.net MVC У меня есть запрос, который выглядит как ниже

using (SqlConnection conn = new SqlConnection(_connStr))
{
    conn.Open();

    var p = new DynamicParameters();
    p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);
    p.Add("@SP_UserId", userId, dbType: DbType.Int32, direction: ParameterDirection.Input);

    var obj = conn.Query<PendingKmsRequest>(sql: "SELECT [f].[id] AS [FileId],[fvr].[Id] AS [RequestId], [au].[Name]"
                        + ", [fvr].[RequestByUserId], [fvr].[FromDate], [fvr].[ToDate],[f].[Title], [fvr].[Status], [fvr].[StatusRemarks]"
                        + "FROM [dbo].[File] AS[f]"
                        + "INNER JOIN [dbo].[FileViewRequest] AS [fvr] ON [f].[CurrentFileVersionId] = [fvr].[FileVersionId]"
                        + "INNER JOIN [Access].[User] AS [au] ON [fvr].[RequestByUserId] = [au].[Id]"
                        + "WHERE ([fvr].[Status] = 'P' OR ([fvr].[Status] = 'A' AND [fvr].[StatusByUserId] = @SP_UserId AND GETDATE() BETWEEN [fvr].[FromDate] AND [fvr].[ToDate]))"
                        + "AND (SELECT 1 FROM [Access].[UserRoleMap] WHERE UserId=@SP_UserId AND RoleId IN(@SP_RoleId)) = 1", param: p, commandType: CommandType.Text);

    if (obj != null && obj.Count() > 0)
        return obj.ToList();
    else
        return new List<PendingKmsRequest>();
}

ПРИМЕЧАНИЕ. Идентификатор роли всегда похож на (7,8,9), и это int столбец в базе данных.

Я получаю эту ошибку преобразования в этой строке кода:

 WHERE UserId = @SP_UserId AND RoleId IN (@SP_RoleId))

Это ошибка:

Ошибка преобразования при преобразовании значения nvarchar '7,9,10' в тип данных int.

Как я могу предотвратить эту ошибку?

Answer 1

Следующая строка в вашем коде вопроса:

p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);

Значение "7,8,9" это строка и тип параметра DbType.String Строка также.

Но вы сказали, что это int в вашей базе данных. Это несоответствие.

Далее ваш запрос:

WHERE UserId = @SP_UserId AND RoleId IN (@SP_RoleId))

Запрос использует IN пункт.

Dapper может конвертировать ваше значение для IN оговорка, если передать в IEnumerable,

Измените строку кода, как показано ниже:

p.Add("@SP_RoleId", new[] {7,8,9}, dbType: DbType.Int32, direction: ParameterDirection.Input);

Answer 2

Нет необходимости использовать строку преобразования в array или любая строка split() функция

Если у вас есть строка с запятой, вы можете проверить это, как показано ниже,

1. Если у вас есть @SP_RoleId = "7, 8, 9"
2. Вы можете конвертировать эту строку, как показано ниже
   @SP_RoleId = ",7,8,9," (',' + ltrim(rtrim( @SP_RoleId )) + ',')
3. Сейчас использую Like Проверять ,UserId,

Обновленный код, как показано ниже,

using (SqlConnection conn = new SqlConnection(_connStr))
{
    conn.Open();

    var p = new DynamicParameters();
    p.Add("@SP_RoleId", "7,8,9", dbType: DbType.String, direction: ParameterDirection.Input);
    p.Add("@SP_UserId", userId, dbType: DbType.Int32, direction: ParameterDirection.Input);

    var obj = conn.Query<PendingKmsRequest>(sql: "SELECT [f].[id] AS [FileId],[fvr].[Id] AS [RequestId], [au].[Name]"
                        + ", [fvr].[RequestByUserId], [fvr].[FromDate], [fvr].[ToDate],[f].[Title], [fvr].[Status], [fvr].[StatusRemarks]"
                        + "FROM [dbo].[File] AS[f]"
                        + "INNER JOIN [dbo].[FileViewRequest] AS [fvr] ON [f].[CurrentFileVersionId] = [fvr].[FileVersionId]"
                        + "INNER JOIN [Access].[User] AS [au] ON [fvr].[RequestByUserId] = [au].[Id]"
                        + "WHERE ([fvr].[Status] = 'P' OR ([fvr].[Status] = 'A' AND [fvr].[StatusByUserId] = @SP_UserId AND GETDATE() BETWEEN [fvr].[FromDate] AND [fvr].[ToDate]))"
                        + "AND (SELECT 1 FROM [Access].[UserRoleMap] WHERE ',' +  lTrim(rTrim(@SP_RoleId)) + ',' like '%,' + lTrim(rTrim(UserId) + ',%' " // Updated line
                        + "AND RoleId IN(@SP_RoleId)) = 1", param: p, commandType: CommandType.Text);

    if (obj != null && obj.Count() > 0)
        return obj.ToList();
    else
        return new List<PendingKmsRequest>();
}