In sql, how to provide a string array to a Dapper query for the IN clause?

c# dapper

Question

I have a string array, I need to pass this to a dapper query in the select statement. How to pass the string array in 'WHERE IN' clause of select query?

The column accountnumber is basically a varchar column in the SQL database.

This is my method:

public static List<TokenFile> GetTokenFileRecords(string[] accountNumbers)
{
    // string sqlQuery = "Select * from [PaymentSolutions].[dbo].[Tokenfile] where ACCOUNTNUM= @accountNumbers"

    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(SqlHelper.ConnectToDB("PayFileconnString")))
    {
         var tokenFileOutput = connection.Query<TokenFile>("Select * from [PaymentSolutions].[dbo].[Tokenfile] where ACCOUNTNUM in ('" + @accountNumbers + "')", new { Accountnum = accountNumbers })
                                         .ToList();
         return tokenFileOutput;
    }
}
1
-1
2/8/2019 12:54:59 PM

Accepted Answer

Dapper supports this directly. For example...

string sql = "SELECT * FROM SomeTable WHERE id IN @ids"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});
353
1/5/2018 9:39:54 AM

Popular Answer

Directly from the GitHub project homepage:

Dapper allow you to pass in IEnumerable and will automatically parameterize your query.

connection.Query<int>(
    @"select * 
      from (select 1 as Id union all select 2 union all select 3) as X 
      where Id in @Ids", 
    new { Ids = new int[] { 1, 2, 3 });

Will be translated to:

select * 
from (select 1 as Id union all select 2 union all select 3) as X 
where Id in (@Ids1, @Ids2, @Ids3)

// @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow