private PetaPoco.Sql GetSql_CommentPageIndexInParentComments(long parentId, SortBy_Comment sortBy) { PetaPoco.Sql sql = PetaPoco.Sql.Builder; sql.Where("parentId=@0", parentId); if (sortBy == SortBy_Comment.DateCreated) { sql.OrderBy("id"); } else { sql.OrderBy("id desc"); } return(sql); }
private PetaPoco.Sql GetSql_CommentPageIndex(string tenantType, long commentedObjectId, SortBy_Comment sortBy) { PetaPoco.Sql sql = PetaPoco.Sql.Builder; sql.Where("tenantTypeId=@0", tenantType); sql.Where("commentedObjectId=@0", commentedObjectId); sql.Where("parentId=0"); if (sortBy == SortBy_Comment.DateCreated) { sql.OrderBy("id"); } else { sql.OrderBy("id desc"); } return(sql); }
/// <summary> /// 主要查詢方法 /// </summary> /// <param name="page">頁數(預設為 1)</param> /// <param name="ExportMode">匯出模式。(預設為 false)若為真,則取消分頁。</param> private void Query(long page = 1, bool ExportMode = false) { sql.Append(String.Format("SELECT * FROM {0} WHERE 1=1", TableName)); #region 將搜尋控制項的值轉換為 SQL Statement BackendSearchControl.ConvertControlToSQL(sql, SearchItems, plSearch, this.ViewState); #endregion sql.OrderBy("sID DESC"); if (!ExportMode) { var data = db.Page <DataModel_a12SupauCheckin>(page, PageSize, sql); patwGridView1.DataSource = data.Items; patwGridView1.DataBind(); AspNetPager1.PageSize = (int)data.ItemsPerPage; AspNetPager1.RecordCount = (int)data.TotalItems; lbTotal.Text = "依據條件,目前共有 " + data.TotalItems.ToString() + " 筆"; } else { var data = db.Query <DataModel_a12SupauCheckin>(sql); patwGridView1.DataSource = data; patwGridView1.DataBind(); AspNetPager1.Visible = false; lbTotal.Text = "依據條件,目前共有 " + data.Count() + " 筆"; } }
/// <summary> /// 从MicroblogQuery构建PetaPoco.Sql的where条件 /// </summary> /// <param name="query">MicroblogQuery查询条件</param> /// <param name="sql">PetaPoco.Sql对象</param> private void BuildSqlWhere_MicroblogQuery(MicroblogQuery query, ref PetaPoco.Sql sql) { if (sql == null) { sql = PetaPoco.Sql.Builder; } if (query.UserId.HasValue) { sql.Where("UserId = @0", query.UserId); } if (!string.IsNullOrEmpty(query.TenantTypeId)) { sql.Where("TenantTypeId = @0", query.TenantTypeId); } if (query.OwnerId.HasValue) { sql.Where("OwnerId = @0", query.OwnerId); } if (query.isOriginal.HasValue && query.isOriginal.Value) { sql.Where("OriginalMicroblogId = 0"); } else if (query.MediaType.HasValue) { switch (query.MediaType) { case MediaType.Image: sql.Where("HasPhoto = 1"); break; case MediaType.Video: sql.Where("HasVideo = 1"); break; case MediaType.Audio: sql.Where("HasMusic = 1"); break; } } if (!string.IsNullOrEmpty(query.Keyword)) { sql.Where("Body like @0", "%" + StringUtility.StripSQLInjection(query.Keyword) + "%"); } if (query.StartDate.HasValue) { sql.Where("DateCreated >= @0", query.StartDate); } //reply:已修改 if (query.EndDate.HasValue) { sql.Where("DateCreated < @0", query.EndDate.Value.AddDays(1)); } if (query.AuditStatus.HasValue) { sql.Where("AuditStatus = @0", (int)query.AuditStatus); } sql.OrderBy("MicroblogId desc"); }