Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Bjarne Fyrstenborg 1155 posts 3349 karma points MVP 4x c-trib
    Oct 04, 2018 @ 12:18
    Bjarne Fyrstenborg
    0

    Query data between two dates with PetaPoco

    I have tried quering the data in the database - or more specific in uCommerce_PurchaseOrder, where I need to get orders within a daterange, where the column CompletedDate is a datetime allowing null values.

    var sqlQuery = "SELECT * FROM uCommerce_PurchaseOrder o";
    var sql = new Sql(sqlQuery);
    
    Sql filterSql = new Sql();
    
    var pageIndex = 0;
    var pageSize = 100;
    
    var start = new DateTime(2018, 1, 1);
    var end = DateTime.Now;
    
    Sql filterSql = new Sql();
    
    filterSql.Where("o.CompletedDate IS NOT NULL")
             .Where("o.CompletedDate >= @start", new { start = start }, DatabaseContext.SqlSyntax)
             .Where("o.CompletedDate <= @end", new { end = end }, DatabaseContext.SqlSyntax);
    
    sql.Append(filterSql);
    
    // Get page of results and total count
    var pagedResult = _db.Page<OrderModel>(pageIndex + 1, pageSize, sql.SQL);
    int totalRecords = Convert.ToInt32(pagedResult.TotalItems);
    

    With the .Where() clauses for start and end date it doesn't return any results, but without these it does.

    I have tried with a different format as well without luck:

    filterSql.Append("WHERE o.CompletedDate IS NOT NULL");
    filterSql.Append("AND o.CompletedDate >= @start AND o.CompletedDate <= @end",
        new
        {
            start = start,
            end = end
        }
    );
    

    Any idea what I am missing in the queries?

    /Bjarne

  • Bjarne Fyrstenborg 1155 posts 3349 karma points MVP 4x c-trib
    Oct 06, 2018 @ 15:55
    Bjarne Fyrstenborg
    0

    I found another way to solve this, but I don't know why regular SQL expression didn't seem to work with the datetime parameters.

    public class MyClass
    {
        private int[] _excludeOrderStatusIds = new int[] { 1, 2 };
    
        public void Get()
        {
            var d1 = DateTime.Now.AddMonths(-3);
            var d2 = DateTime.Now;
    
            var tbl = Database.PocoData.ForType(typeof(OrderDto)).TableInfo.TableName;
    
            var sqlOrders = new Sql().Select("uCommerce_PurchaseOrder.OrderId, uCommerce_PurchaseOrder.OrderNumber, uCommerce_PurchaseOrder.CreatedDate, uCommerce_PurchaseOrder.CompletedDate, uCommerce_PurchaseOrder.OrderStatusId, uCommerce_PurchaseOrder.CustomerId, uCommerce_Customer.FirstName, uCommerce_Customer.LastName")
                                    .From<OrderDto>(DatabaseContext.SqlSyntax)
                                    .InnerJoin<CustomerDto>(DatabaseContext.SqlSyntax)
                                    .On<OrderDto, CustomerDto>(DatabaseContext.SqlSyntax, left => left.CustomerId, right => right.CustomerId)
                                    .Where<OrderDto>(x => x.CompletedDate >= d1 && x.CompletedDate <= d2, DatabaseContext.SqlSyntax)
                                    .Where("uCommerce_PurchaseOrder.OrderStatusId NOT IN (@ids)", new { ids = _excludeOrderStatusIds });
    
            var r = _db.Page<OrderDto>(1, 100, sqlOrders);
            var c = Convert.ToInt32(r.TotalItems);
        }
    }
    
    
    [TableName("uCommerce_PurchaseOrder")]
    [PrimaryKey("OrderId", autoIncrement = true)]
    [ExplicitColumns]
    internal class OrderDto
    {
        [Column("OrderId")]
        public int OrderId { get; set; }
    
        [Column("OrderNumber")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string OrderNumber { get; set; }
    
        [Column("CustomerId")]
        [ForeignKey(typeof(CustomerDto), Column="CustomerId")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public int CustomerId { get; set; }
    
        [Column("OrderStatusId")]
        public int OrderStatusId { get; set; }
    
        [Column("CreatedDate")]
        public DateTime CreatedDate { get; set; }
    
        [Column("CompletedDate")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public DateTime? CompletedDate { get; set; }
    
        [ResultColumn]
        public CustomerDto CustomerDto { get; set; }
    }
    
    [TableName("uCommerce_Customer")]
    [PrimaryKey("CustomerId", autoIncrement = true)]
    [ExplicitColumns]
    internal class CustomerDto
    {
        [Column("CustomerId")]
        public int CustomerId { get; set; }
    
        [Column("FirstName")]
        public string FirstName { get; set; }
    
        [Column("LastName")]
        public string LastName { get; set; }
    
        [Column("EmailAddress")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string EmailAddress { get; set; }
    
        [Column("PhoneNumber")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string PhoneNumber { get; set; }
    
        [Column("MobilePhoneNumber")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string MobilePhoneNumber { get; set; }
    
        [Column("MemberId")]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string MemberId { get; set; }
    }
    
  • Colin Wiseman 8 posts 118 karma points
    29 days ago
    Colin Wiseman
    0

    Just in case someone else queries this too, I used the standard BETWEEN clause in SQL:

    .Where(@"EventsId IN (@eventsIds) AND ( (StartDAte BETWEEN @1 AND @2) OR (EndDate BETWEEN @1 AND @2) OR (StartDate <= @1 AND EndDate >= @2))",
                                    new { eventsIds =  eventsIds },
                                    fromDate.Date,
                                    toDate.Date)); //  + 1 to make sure the we find a full day.
    

    This finds all events that start or end within the date range and for events that span the selected date range.

Please Sign in or register to post replies

Write your reply to:

Draft