CODEWITHSUNDEEP
dapper
dmuun
dmuun

Reputation: 1228

Dapper.net dynamic OR query

New to using Dapper and need a little help. I have a .csv file that I will read to dynamically generate a select statement.

The .csv file contents look like this:

id,fromDate,toDate
1,20100101,20110101
2,20110101,20120101
3,20100101,20120101
etc...

The query I need to dynamically generate should be:

SELECT * FROM targetTable WHERE (id = 1 AND cDate BETWEEN 20100101 AND 20110101) OR (id = 2 AND cDate BETWEEN 20110101 AND 20120101) OR 
OR (id = 3 AND cDate BETWEEN 20100101 AND 20120101)

How can I do this with Dapper?

Upvotes: 0

Views: 276

Answers (1)

stefankmitph
stefankmitph

Reputation: 3306

Why not simply building your query?

Assuming you have all your data in this form after reading the csv and push it into classes:

public class Data
{
    public int ID { get; set; }
    public int FromDate { get; set; }
    public int ToDate { get; set; }
}

I'd just simply pass the query string:

List<string> lines = new List<string>();
foreach (var data in listData) // listData ... List<Data>
{
    string line = string.Format("(id = {0} and cdate between {1} and {2})", data.ID,data.FromDate, data.ToDate);
    lines.Add(line);
}

string additionalQuery = string.Join(" OR ", myLines);

and then...

var result = sqlConnection.Query<MyQueryData>(string.Format("SELECT * FROM targetTable WHERE {0}", additionalQuery));

Will work just fine...

Upvotes: 1

Related Questions