Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }