/// <summary>
/// 执行分页查询的核心方法：支持单表和多表分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">执行条件</param>
/// <returns></returns>
public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition)
{
PagedSqlDataResult <T> pageData = null;
if (null == condition)
{
return(pageData);
}
string errMsg = "";
if (!condition.IsValid(out errMsg))
{
throw new Exception("分页查询错误：" + errMsg);
}
//记录打出日志
this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition);
try
{
if (condition.PageNumber <= 0)
{
condition.PageNumber = 1;
}
//判断结果集类型
if (null == condition.TableOptions)
{
//简单sql语句类型判断
if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from"))
{
condition.TableOptions = PageTableOptions.SqlScripts;
}
else
{
condition.TableOptions = PageTableOptions.TableOrView;
}
}
//动态查询需要包装外层
if (condition.TableOptions == PageTableOptions.SqlScripts)
{
//condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''");
string sqlCmd = condition.TableNameOrSqlCmd.Trim();
if (sqlCmd[sqlCmd.Length - 1] == ';')
{
sqlCmd = sqlCmd.TrimEnd(';');
}
condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd);
}
//# 分页sql 字符串
string countSql = string.Format("select count(*) as TotalRows, 0 as TotalPages from {0} {1} ;", condition.TableNameOrSqlCmd, condition.ConditionWhere);
string bodySql = string.Concat(
"select "
, condition.SelectFields
, " from "
, condition.TableNameOrSqlCmd
, condition.ConditionWhere
, " order by "
, condition.SortField.IsNullOrEmpty() ? condition.PrimaryKey : condition.SortField
, condition.IsDesc ? " DESC " : " ASC "
, " limit "
, (condition.PageNumber - 1) * condition.PageSize
, ','
, condition.PageSize
);
string pagerSql = string.Concat(countSql, bodySql, ";");
//string
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
//多部分结果
var multiResult = conn.QueryMultiple(pagerSql, condition.SqlParameters);
if (null != multiResult)
{
pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息
if (pageData.TotalRows > 0)
{
//计算分页总页数
if (pageData.TotalRows < condition.PageSize)
{
pageData.TotalPages = 1;
}
else
{
if (pageData.TotalRows % condition.PageSize > 0)
{
pageData.TotalPages = pageData.TotalRows / condition.PageSize + 1;
}
else
{
pageData.TotalPages = pageData.TotalRows / condition.PageSize;
}
}
}
if (null != pageData)
{
pageData.DataList = multiResult.Read <T>().AsList();//结果行
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(pageData);
}
/// <summary>
/// 执行分页查询的核心方法：支持单表和多表分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">执行条件</param>
/// <returns></returns>
public override PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition)
{
PagedSqlDataResult <T> pageData = null;
if (null == condition)
{
return(pageData);
}
string errMsg = "";
if (!condition.IsValid(out errMsg))
{
throw new Exception("分页查询错误：" + errMsg);
}
//记录打出日志
this.SqlOutPutToLogAsync(Contanst.PageSql_Call_Name, condition);
try
{
//判断结果集类型
if (null == condition.TableOptions)
{
//简单sql语句类型判断
if (condition.TableNameOrSqlCmd.ToLower().Contains("select") && condition.TableNameOrSqlCmd.ToLower().Contains("from"))
{
condition.TableOptions = PageTableOptions.SqlScripts;
}
else
{
condition.TableOptions = PageTableOptions.TableOrView;
}
}
//动态查询需要包装外层
if (condition.TableOptions == PageTableOptions.SqlScripts)
{
//condition.TableNameOrSqlCmd = condition.TableNameOrSqlCmd.Replace("'", "''");
condition.TableNameOrSqlCmd = string.Format(" ( {0} ) as tmpTable ", condition.TableNameOrSqlCmd);
}
//完整的sql参数转化
var fullPagerSqlParas = new DynamicParameters();
fullPagerSqlParas.Add("@PageIndex", condition.PageNumber - 1);
fullPagerSqlParas.Add("@PageSize", condition.PageSize);
fullPagerSqlParas.Add("@PrimaryKey", condition.PrimaryKey); //主键
fullPagerSqlParas.Add("@TableNameOrSqlCmd", condition.TableNameOrSqlCmd); //将查询结果结合作为分页的表数据
fullPagerSqlParas.Add("@SortField", condition.SortField);
fullPagerSqlParas.Add("@SelectFields", condition.SelectFields);
fullPagerSqlParas.Add("@ConditionWhere", condition.ConditionWhere);
fullPagerSqlParas.Add("@IsDesc", condition.IsDesc == true ? 1 : 0);
string pageSqlTemplate = PagerSQLProcedure.PAGE_SQL_CORE;
string pagerSql = string.Empty;
if (null != condition.SqlParameters)
{
//参数列表字符串--声明
//参数列表 -逗号分隔
string[] sqlParaToken = null;
string parasStr = this.GetParamSqlTokenToSqlParas(condition.TableNameOrSqlCmd, out sqlParaToken);
string paraKeyValueString = string.Empty;
string paraDeinfine = this.GetSqlServerParamDefineString(condition.SqlParameters, sqlParaToken, out paraKeyValueString);
string defWithWraper = string.Format("N'{0}'", paraDeinfine);
string defDotWraper = string.Format("N',{0}'", paraDeinfine);
if (!string.IsNullOrEmpty(paraDeinfine))
{
pagerSql = string.Format(pageSqlTemplate, " ", defWithWraper, defDotWraper, paraKeyValueString);//需要带参数
}
else
{
pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数
}
//将参数整体注入到动态参数
fullPagerSqlParas.AddDynamicParams(condition.SqlParameters);
}
else
{
//fullPagerSqlParas.Add("@IsParamQuery", false);
pagerSql = string.Format(pageSqlTemplate, "--", "N''", "N''", " ");//不用带参数
}
//string
using (var conn = DatabaseFactory.GetDbConnection(this.DbConfig))
{
//多部分结果
var multiResult = conn.QueryMultiple(pagerSql, fullPagerSqlParas);
if (null != multiResult)
{
pageData = multiResult.ReadFirstOrDefault <PagedSqlDataResult <T> >();//分页信息
if (null != pageData)
{
pageData.DataList = multiResult.Read <T>().AsList();//结果行
}
}
//---------废弃的存储过程----------
////////////////////var dataList = conn.Query<T>(pagerSql, fullPagerSqlParas, commandType: CommandType.Text).AsList();
////////////////////if (dataList.IsNotEmpty())
////////////////////{
//////////////////// pageData = new PagedSqlDataResult<T>(dataList);
//////////////////// //查询完毕后 根据输出参数 返回总记录数 总页数
//////////////////// pageData.TotalRows = fullPagerSqlParas.Get<int>("@TotalRecords");
//////////////////// pageData.TotalPages = fullPagerSqlParas.Get<int>("@TotalPageCount");
////////////////////}
}
}
catch (Exception ex)
{
throw ex;
}
return(pageData);
}
public abstract PagedSqlDataResult <T> PageQuery <T>(PagedSqlCondition condition) where T : class;
