/// <summary> /// 生成拼接sql参数列表 /// </summary> /// <param name="entity"></param> /// <returns></returns> public List <SqlParameter> ParseToSqlParameters(EventItemGroupSearchEntity entity) { List <SqlParameter> paraList = new List <SqlParameter>(); //table paraList.Add(CPTable(entity)); //fields paraList.Add(CPFields(entity)); //filter_SqlWhere paraList.Add(CPWhere(entity)); //order paraList.Add(CPOrder(entity)); //pagesize paraList.Add(new SqlParameter("@pageSize", entity.PageSize)); //pageindex paraList.Add(new SqlParameter("@pageIndex", entity.PageIndex)); paraList.Add(new SqlParameter() { ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output }); return(paraList); }
/// <summary> /// 设置表关联 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPTable(EventItemGroupSearchEntity entity) { StringBuilder sbtable = new StringBuilder(); //基本表 sbtable.Append(" M_EventItemGroups "); return(new SqlParameter("@TableName", sbtable.ToString())); }
/// <summary> /// 设置显示字段 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPFields(EventItemGroupSearchEntity entity) { StringBuilder sbfileds = new StringBuilder(); if (entity.UseDBPagination) { sbfileds.Append(" GroupEventID,GroupEventName,CONVERT(VARCHAR(10),CreatedTime,121) AS CreatedTime,CONVERT(VARCHAR(16),PublishTime,121) AS PublishTime,GroupState"); } else { throw new NotImplementedException(); } return(new SqlParameter("@Fields", sbfileds.ToString())); }
/// <summary> /// 设置排序 /// </summary> /// <param name="enity"></param> /// <returns></returns> private SqlParameter CPOrder(EventItemGroupSearchEntity enity) { StringBuilder sborder = new StringBuilder(); if (enity.OrderfieldType == OrderFieldType.Desc) { sborder.AppendFormat(" GroupEventID DESC"); } else { sborder.AppendFormat(" GroupEventID ASC"); } return(new SqlParameter("@OrderField", sborder.ToString())); }
private DataTable GetQueryData(bool isDownload) { int totalcnt = 0; DataTable table = null; BCtrl_EventItemGroup bll = new BCtrl_EventItemGroup(); EventItemGroupSearchEntity entity = new EventItemGroupSearchEntity(); entity.GroupEventID = GroupEventID; // entity.EventItemIDs = ArticleIds; entity.PageSize = base.PageSize; entity.PageIndex = base.PageIndex; table = bll.QueryGroupRelViewList(entity, out totalcnt); base.TotalRecords = totalcnt; return(table); }
public DataTable QueryGroupTable(EventItemGroupSearchEntity searchEntity, out int totalcnt) { DataTable table = null; totalcnt = 0; try { SqlParameter[] prms = ParseToSqlParameters(searchEntity).ToArray(); table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0]; totalcnt = Convert.ToInt32(prms[prms.Length - 1].Value); } catch (Exception ex) { LogUtil.WriteLog(ex); return(null); } return(table); }
private DataTable GetQueryData(bool isDownload) { BCtrl_EventItemGroup bll = new BCtrl_EventItemGroup(); int totalcnt = 0; EventItemGroupSearchEntity entity = new EventItemGroupSearchEntity(); entity.GroupEventName = GroupEventName; entity.GroupState = GroupState; entity.PageSize = base.PageSize; entity.PageIndex = base.PageIndex; entity.UseDBPagination = !isDownload; DataTable table = bll.QueryGroupTable(entity, out totalcnt); base.TotalRecords = totalcnt; return(table); }
public DataTable QueryGroupRelViewList(EventItemGroupSearchEntity searchEntity, out int totalcnt) { DataTable table = null; totalcnt = 0; try { List <SqlParameter> prmslist = new List <SqlParameter>(); prmslist.Add(new SqlParameter("@TableName", " M_V_EventItemGroupRel ")); string fields = @" Title, EventGroupID,EventItemID,DisplayOrder,FirstTypName,SecondTypeName,GroupState,CarouselPictureID, IsSingleGroupState,PictureID"; prmslist.Add(new SqlParameter("@Fields", fields)); prmslist.Add(new SqlParameter("@OrderField", " DisplayOrder ASC")); string wheresql = " EventGroupID=" + searchEntity.GroupEventID; prmslist.Add(new SqlParameter("@sqlWhere", wheresql)); prmslist.Add(new SqlParameter("@pageIndex", searchEntity.PageIndex)); prmslist.Add(new SqlParameter("@pageSize", searchEntity.PageSize)); prmslist.Add(new SqlParameter() { ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output }); SqlParameter[] prms = prmslist.ToArray(); table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0]; totalcnt = Convert.ToInt32(prms[prms.Length - 1].Value); } catch (Exception ex) { LogUtil.WriteLog(ex); return(null); } return(table); }
/// <summary> /// 查询条件 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPWhere(EventItemGroupSearchEntity entity) { StringBuilder sbwhere = new StringBuilder(" 1=1 "); sbwhere.Append(" AND GroupState=" + entity.GroupState); if (!string.IsNullOrEmpty(entity.GroupEventName)) { sbwhere.Append(" AND GroupEventName like '" + entity.GroupEventName.Trim() + "%'"); } if (entity.PublishStartTime.HasValue) { sbwhere.Append(" AND PublishTime >= '" + entity.PublishStartTime.Value.Date + "'"); } if (entity.PublishEndTime.HasValue) { sbwhere.Append(" AND PublishTime <= '" + entity.PublishEndTime.Value.AddDays(1).Date.AddSeconds(-1) + "' "); } return(new SqlParameter("@sqlWhere", sbwhere.ToString())); }
public DataTable QueryGroupRelViewList(EventItemGroupSearchEntity searchEntity, out int totalcnt) { return(dao.QueryGroupRelViewList(searchEntity, out totalcnt)); }