I am attempting to parameterize multiple values that are going into my query, but keep getting errors. This is what I am currently working on, in which I am getting a "must declare the scalar variable" error on the userClientIds parameter. Can someone help me figure out what I am missing.

 public async Task<IEnumerable<SharedUser>> GetUsersForSharing(Guid userId, Guid 
 templateId, string? searchedEmpName, string? searchedEmpNumber)
 {
     // this is a list of ints, which will need passed into the WHERE IN clause below
     var userClientIds = userClients.Select(client => client.ClientId).ToList();

     var sql = $@"SELECT DISTINCT
            UserId, 
            ClientId,
            FullName,
            EmployeeNumber
        FROM dbo.ClientUser

        WHERE 
            UserId <> @UserId
            AND ClientId in (
                @userClientIds
            )";

        if(searchedEmpName != null)
        {
            sql  = $@"AND FullName LIKE '%@searchedEmpName%'";
        }
        if(searchedEmpNumber != null)
        {
            sql  = $@"AND EmployeeNumber LIKE '%@searchedEmpNumber%'";
        }
        
        using(var conn = _connectionFactory.GetDbConnection())
        {
            var parameters = new DynamicParameters();
            parameters.Add("@userId", userId.ToString());
            parameters.Add("@userClientIds", new[] { userClientIds });
            parameters.Add("@searchedEmpName", searchedEmpName);
            parameters.Add("@searchedEmpNumber", searchedEmpNumber);
            conn.Open();
            var result = await conn.QueryAsync<SharedUser>(sql, new { parameters });
            return result;
        }
}

CodePudding user response：

You just need to pass the whole list to DyanmicParameters without a containing array, and Dapper will inject it for you.

Some more notes:

• Pass the dynamic parameters directly to Query, not inside a contatining array.
• Use of DISTINCT is a code-smell: why does your tabel have duplicates in the first place? Perhaps you should improve your tabel design.
• userId.ToString() why? If it's a Guid keep it as such.
• The LIKE parameters are not going to work like that. instead you need to concatenate them within SQL $@"AND FullName LIKE '%' @searchedEmpName '%' etc.
• Dapper will open and close the connection for you automatically.

public async Task<IEnumerable<SharedUser>> GetUsersForSharing(Guid userId, Guid 
 templateId, string? searchedEmpName, string? searchedEmpNumber)
 {
     var userClientIds = userClients.Select(client => client.ClientId).ToList();

     var sql = $@"
SELECT
  UserId, 
  ClientId,
  FullName,
  EmployeeNumber
FROM dbo.ClientUser
WHERE 
  UserId <> @UserId
  AND ClientId in (
  @userClientIds
)
";

        if(searchedEmpName != null)
        {
            sql  = $@"AND FullName LIKE '%'   @searchedEmpName   '%'
";
        }
        if(searchedEmpNumber != null)
        {
            sql  = $@"AND EmployeeNumber LIKE '%'   @searchedEmpNumber   '%'
";
        }
        
        using(var conn = _connectionFactory.GetDbConnection())
        {
            var parameters = new DynamicParameters();
            parameters.Add("@userId", userId);
            parameters.Add("@userClientIds", userClientIds);
            parameters.Add("@searchedEmpName", searchedEmpName);
            parameters.Add("@searchedEmpNumber", searchedEmpNumber);
            var result = await conn.QueryAsync<SharedUser>(sql, parameters);
            return result;
        }
}

If you have a big list then do not do the above code, as performance will be terrible. Instead use a table valued parameter, and pass it with .AsTableValuedParameter

CodePudding user response：

Create client id to comma separated string;

var clientIds = String.Join(",", userClientIds.Select(i => i.ToString()).ToArray())

Then add parameter:

parameters.Add("@userClientIds", clientIds);