/// <summary>
/// 生成拼接sql参数列表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <SqlParameter> ParseToSqlParameters(CategoriesSearchEntity 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 QueryCategoriesTable(CategoriesSearchEntity 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);
}
private DataTable GetQueryData(bool isDownload)
{
BCtrl_Categories bll = new BCtrl_Categories();
int totalcnt = 0;
CategoriesSearchEntity entity = new CategoriesSearchEntity();
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
if (!string.IsNullOrEmpty(_StrCategoriesName))
{
entity.CalendarTypeName = _StrCategoriesName;
}
if (_StrParentCalendarID > 0)
{
entity.ParentCalendarTypeID = _StrParentCalendarID;
}
else
{
entity.ParentCalendarTypeID = -1;
}
entity.UseDBPagination = !isDownload;
entity.OrderfieldType = OrderFieldType.Asc;
DataTable table = bll.QueryCategoriesTable(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(table);
}
/// <summary>
/// 设置表关联
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPTable(CategoriesSearchEntity entity)
{
StringBuilder sbtable = new StringBuilder();
//基本表
sbtable.Append(" M_CalendarType ");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 设置显示字段
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPFields(CategoriesSearchEntity entity)
{
StringBuilder sbfileds = new StringBuilder();
if (entity.UseDBPagination)
{
sbfileds.Append(@" CalendarTypeID, CalendarTypeName,CalendarTypeKind,ParentCalendarTypeID,Dismiss,Descripition ");
}
else
{
throw new NotImplementedException();
}
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPWhere(CategoriesSearchEntity entity)
{
StringBuilder sbwhere = new StringBuilder(" ParentCalendarTypeID is not null ");
if (!string.IsNullOrEmpty(entity.CalendarTypeName))
{
sbwhere.Append(" AND [CalendarTypeName] like '%" + entity.CalendarTypeName + "%'");
}
if (entity.ParentCalendarTypeID != 0)
{
sbwhere.Append(" AND [ParentCalendarTypeID] =" + entity.ParentCalendarTypeID);
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
/// <summary>
/// 设置排序
/// </summary>
/// <param name="enity"></param>
/// <returns></returns>
private SqlParameter CPOrder(CategoriesSearchEntity enity)
{
StringBuilder sborder = new StringBuilder();
if (enity.OrderfieldType == OrderFieldType.Desc)
{
sborder.Append(" CalendarTypeID DESC ");
}
else
{
sborder.Append(" CalendarTypeID ASC ");
}
return(new SqlParameter("@OrderField", sborder.ToString()));
}
/// <summary>
/// 一级分类管理信息
/// </summary>
/// <param name="entity"></param>
/// <param name="totalcnt"></param>
/// <returns></returns>
public DataTable QueryCategoriesTable(CategoriesSearchEntity entity, out int totalcnt)
{
return(categories.QueryCategoriesTable(entity, out totalcnt));
}
