/// <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); }
/// <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); }