Pass multiple parameters into Dapper query using QueryAsync

Question

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

I'm not sure, but `userClientIds` is already an array does it (`new[] { userClientIds }`) not create nested array? Would you try to with only `userClientIds`. — harpal, Sep 01 '22 at 04:37
And also the same with `new { parameters }`, `parameters` is already an object. — harpal, Sep 01 '22 at 04:42
Refer this https://stackoverflow.com/a/12724304/9695286. You should have query like `AND ClientId in @userClientIds` & add parameter as `parameters.Add("@userClientIds", userClientIds);`. — Karan, Sep 01 '22 at 04:45
This looks wrong: `parameters.Add("@userClientIds, new[] { userClientIds });`. The variable `userClientIds` is already a collection (a list). You are passing an array of lists. Try making `userClientIds` an array instead of a list and just pass that in — Flydog57, Sep 01 '22 at 04:47
Also, you may need to correct few things. Your query `sql += $@"AND FullName LIKE '%@searchedEmpName%'";` will not replace your parameter value since it will be not be consider as variable because it is wrapped within single quotes. — Karan, Sep 01 '22 at 04:48
Also it would be better to add a space at beginning when you do `sql += ` like `sql += $@" AND FullName LIKE '%@searchedEmpName%'";` Otherwise it may end up creating incorrect query like `AND ClientId in @userClientIdsAND EmployeeNumber LIKE` which will treat `@userClientIdsAND` as parameter. — Karan, Sep 01 '22 at 04:50

Charlieface · Answer 1 · 2022-09-01T19:37:29.507

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

You must remove the parenthesis () of ClientId in (@userClientIds) otherwise you get a syntax error.

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

First, create a table type

CREATE TYPE dbo.IdList (Id int PRIMARY KEY)

Then pass it like this

var table = new DataTable { Columns = {
    { "Id", typeof(int) },
} };
foreach (var id in userClientIds)
    table.Rows.Add(id);

parameters.Add("@userClientIds", table.AsTableValuedParameter("dbo.IdList"));

And the syntax in SQL is

WHERE 
  UserId <> @UserId
  AND ClientId in (SELECT uci.Id FROM @userClientIds uci)

I made these changes and am now getting a syntax error near the userId variable in the sql. The userId is a guid and is a uniqueidentifier in the db. I have tried with userId.toString() and also putting quotes around the userId param, with no luck. Any suggestions? — BenTen, Sep 01 '22 at 15:26
No you don't want strings, keep it as a Guid. `parameters.Add("@userId", userId);` will pass it as a `uniqueidentifier`, then you can just do `WHERE UserId <> @UserId` — Charlieface, Sep 01 '22 at 15:28
still getting syntax errors. the userClientIds parameter is type IEnumerable without adding toList() and the ClientId column in sql is an int. Would I pass that in as an IEnumerable or a list? Also, does the WHERE IN look correct like it is? — BenTen, Sep 01 '22 at 16:18
I am really confused because the specific syntax error I am getting is "Invalid syntax near ",". I do not know where the comma is that would be causing this. The sql ran fine before parameterizing, so I know the query itself is fine. — BenTen, Sep 01 '22 at 16:23
Take the `()` out so it's just `AND ClientId in @userClientIds`. See also new edit — Charlieface, Sep 01 '22 at 19:37

score -1 · Answer 2 · answered Sep 01 '22 at 06:20

-1

Create client id to comma separated string;

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

Then add parameter:

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

answered Sep 01 '22 at 06:20

MD. RAKIB HASAN

3,670
4
22
35

Pass multiple parameters into Dapper query using QueryAsync

2 Answers2

Linked