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
– harpal, Commented Sep 1, 2022 at 4:37
And also the same with new { parameters }, parameters is already an object. — harpal
– harpal, Commented Sep 1, 2022 at 4:42
Refer this stackoverflow.com/a/12724304/9695286. You should have query like AND ClientId in @userClientIds & add parameter as parameters.Add("@userClientIds", userClientIds);. — Karan
– Karan, Commented Sep 1, 2022 at 4: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
– Flydog57, Commented Sep 1, 2022 at 4: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
– Karan, Commented Sep 1, 2022 at 4:48

Charlieface · Accepted Answer · 2022-09-01 19:37:29Z

2

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)

edited Sep 1, 2022 at 19:37

answered Sep 1, 2022 at 11:33

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BenTen Over a year ago

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?

Charlieface Over a year ago

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

BenTen Over a year ago

still getting syntax errors. the userClientIds parameter is type IEnumerable<int> 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 Over a year ago

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.

Charlieface Over a year ago

Take the () out so it's just AND ClientId in @userClientIds. See also new edit

MD. RAKIB HASAN · Accepted Answer · 2022-09-01 06:20:03Z

-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 1, 2022 at 6:20

MD. RAKIB HASAN

3,9564 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Pass multiple parameters into Dapper query using QueryAsync

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related