/// <summary> /// 生成拼接sql参数列表 /// </summary> /// <param name="entity"></param> /// <returns></returns> public List <SqlParameter> ParseToSqlParameters(searchFestivalEntity 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> /// <param name="totalcnt"></param> /// <returns></returns> public DataTable GetFestivalList(searchFestivalEntity entity, out int totalcnt) { DataTable table = null; totalcnt = 0; try { SqlParameter[] prms = ParseToSqlParameters(entity).ToArray(); if (entity.UseDBPagination) { table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0]; totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString()); } else { throw new NotImplementedException(); } } catch (Exception ex) { totalcnt = -1; LogUtil.WriteLog(ex); } return(table); }
/// <summary> /// 设置表关联 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPTable(searchFestivalEntity entity) { StringBuilder sbtable = new StringBuilder(); //基本表 sbtable.Append(@" M_Festival AS f LEFT JOIN dbo.M_EventItem AS e ON e.FestivalID = f.FestivalID"); return(new SqlParameter("@TableName", sbtable.ToString())); }
/// <summary> /// 查询条件 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPWhere(searchFestivalEntity entity) { StringBuilder sbwhere = new StringBuilder("1=1"); if (!string.IsNullOrEmpty(entity.StartTime)) { sbwhere.Append("and f.StartTime>='" + entity.StartTime + " 00:00:00'"); } if (!string.IsNullOrEmpty(entity.EndTime)) { sbwhere.Append("and f.EndTime<='" + entity.EndTime + " 23:59:59'"); } return(new SqlParameter("@sqlWhere", sbwhere.ToString())); }
/// <summary> /// 设置排序 /// </summary> /// <param name="enity"></param> /// <returns></returns> private SqlParameter CPOrder(searchFestivalEntity enity) { StringBuilder sborder = new StringBuilder(); if (enity.OrderfieldType == OrderFieldType.Desc) { sborder.Append(" f.EndTime DESC "); } else { sborder.Append(" f.EndTime ASC "); } return(new SqlParameter("@OrderField", sborder.ToString())); }
/// <summary> /// 设置显示字段 /// </summary> /// <param name="entity"></param> /// <returns></returns> private SqlParameter CPFields(searchFestivalEntity entity) { StringBuilder sbfileds = new StringBuilder(); if (entity.UseDBPagination) { sbfileds.Append(@" f.FestivalID, f.FestivalName,f.FestivalShortName,convert(varchar(11),f.StartTime,120) as StartTime ,convert(varchar(11),f.EndTime,120) as EndTime,f.FestivalType,f.FestivalWeight ,e.EventItemGUID,e.Title"); } else { throw new NotImplementedException(); } return(new SqlParameter("@Fields", sbfileds.ToString())); }
private DataTable GetQueryData(bool isDownload) { int totalcnt = 0; BCtrl_Festival bll = new BCtrl_Festival(); searchFestivalEntity entity = new searchFestivalEntity(); if (!string.IsNullOrEmpty(txtStartTime)) { entity.StartTime = txtStartTime; } if (!string.IsNullOrEmpty(txtEndTime)) { entity.EndTime = txtEndTime; } entity.PageSize = base.PageSize; entity.PageIndex = base.PageIndex; entity.UseDBPagination = !isDownload; entity.OrderfieldType = OrderFieldType.Desc; DataTable db = bll.GetFestivalList(entity, out totalcnt); base.TotalRecords = totalcnt; return(db); }
public DataTable GetFestivalList(searchFestivalEntity entity, out int totalcnt) { return(fesobj.GetFestivalList(entity, out totalcnt)); }