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 1140 posts 3225 karma points MVP 3x 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 1140 posts 3225 karma points MVP 3x 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; }
    }
    
Please Sign in or register to post replies

Write your reply to:

Draft