我正在 asp.net mvc 中创建一个 Web 应用程序,我有一个如下所示的查询
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>();
}
注意:角色 ID 始终类似于 (7,8,9),并且是数据库中int
列。
我在这行代码上收到此转换错误:
WHERE UserId = @SP_UserId AND RoleId IN (@SP_RoleId))
这是错误:
将 nvarchar 值"7,9,10"转换为数据类型 int 时转换失败。
如何防止此错误?
问题代码中的以下行:
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
子句。
如果传入IEnumerable
,Dapper 可以转换IN
子句的值。
更改代码行,如下所示:
p.Add("@SP_RoleId", new[] {7,8,9}, dbType: DbType.Int32, direction: ParameterDirection.Input);
无需在array
中使用转换字符串或任何字符串split()
函数
如果您有逗号分隔字符串,则可以按照以下步骤进行检查,
- 如果您有
@SP_RoleId = "7, 8, 9"
- 您可以按如下
方式转换此字符串@SP_RoleId = ",7,8,9,"
(',' + ltrim(rtrim( @SP_RoleId )) + ','
) - 现在使用
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>();
}