CODEWITHSUNDEEP
c#sqldatetimefilterdapper
Jocelyn
Jocelyn

Reputation: 153

C# - SQL filter Date two times with Dapper

I want to receive the data of a defined period, using two DateTimes.

For that I have the following sql query:

 public List<Deliveries> GetDeliveriesFilter(DateTime date1, DateTime date2, string text)
 {
     string q = @"SELECT ...,
                  d.DesiredDate AS DateOfDelivery,
                  ....
                  WHERE  d.DesiredDate >= @date1 AND  d.DesiredDate <= @date2
AND p.Name COLLATE latin1_german1_ci LIKE @text
                  ...";
 
       var result = db.Query<Deliveries>(q, new
                {
                    DateOfDelivery = "%" + date1 + "%",
                    DateOfDelivery = "%" + date2 + "%",
                    Name = "%" + text + "%"
                    ...
                });
                return result.ToList();
 }

But I can not declare DateOfDelivery two times. How can I handle my sql query that the date1 and date2 is set for the delivery date?

With other parameters it is well working.

Kind regards.

Upvotes: 0

Views: 1040

Answers (1)

Palle Due
Palle Due

Reputation: 6312

You misunderstood something about parameters in SQL/Dapper. The properties of the object you pass in, need to correspond exactly to the parameters you define. And you cannot surround a Datetime parameter with '%'-characters, that doesn't make sense. The following will select an interval between the two dates, where Name contains the text1 parameter.

public List<Deliveries> GetDeliveriesFilter(DateTime date1, DateTime date2, string text1)
{
    string q = @"SELECT ...,
                 d.DesiredDate AS DateOfDelivery,
                 ....
                 WHERE  d.DesiredDate >= @date1 AND  d.DesiredDate <= @date2
                  AND p.Name COLLATE latin1_german1_ci LIKE @text
                  ...";
 
    var result = db.Query<Deliveries>(q, new
                {
                    date1,
                    date2,
                    text = "%" + text1 + "%"
                    ...
                });
    return result.ToList();
}

I utilize that when creating a dynamic object, property names can be taken from the variables you pass to it, so the dynamic will have three properties; date1, date2 and text, just like your SQL query..

Upvotes: 1

Related Questions