Exemple #1
0
        /// <summary>
        /// 获取查询的CPQuery对象
        /// </summary>
        /// <param name="sql">查询SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="pagingInfo">分页信息</param>
        /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
        /// <returns></returns>
        private static CPQuery GetCPQuery(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }

            if (string.IsNullOrEmpty(pagingInfo.OrderSeq))
            {
                throw new MyException("请在分页实体中指定 排序字段");
            }

            // 格式化数据库实例名
            if (tables != null && tables.Length > 0)
            {
                sql = string.Format(sql, tables);
            }

            // 生成 SELECT 命令
            string selectCommandText = string.Format(@"select * from ( 
                            select ROW_NUMBER() OVER(ORDER BY {3}) AS [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86],* 
                            from ({0}) as a1) as t1 where [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] > ({1} * {2}) 
                            and [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] <= ({1} * ({2}+1)) order by [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86]", sql, pagingInfo.PageSize, pagingInfo.PageIndex, pagingInfo.OrderSeq);

            CPQuery query1 = CPQuery.From(selectCommandText, parameters);
            // 生成 COUNT 命令
            string  getCountText = string.Format(@"select count(*) from ({0}) as t1", sql);
            CPQuery query2       = CPQuery.From(getCountText, parameters);

            pagingInfo.TotalRecords = query2.ExecuteScalar <int>();

            return(query1);
        }
Exemple #2
0
        /// <summary>
        /// 获取有效的CPQuery对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="pagingInfo"></param>
        /// <param name="tables"></param>
        /// <returns></returns>
        public static CPQuery GetValidCPQuery(string sql, SqlParameter[] parameters, StandardPagingInfo pagingInfo, params object[] tables)
        {
            CPQuery query = GetCPQuery(sql, parameters, pagingInfo, tables);

            if (pagingInfo.AutoValidPage)
            {
                if (query.FillDataTable().Rows.Count == 0 && pagingInfo.PageIndex > 0)
                {
                    //如果当前查询的页没有数据,但是设置的是允许自动定位页,则查找到最后一个有效的页面进行查询
                    int len = (int)Math.Ceiling((double)pagingInfo.TotalRecords / pagingInfo.PageSize);
                    pagingInfo.PageIndex = (len - 1) < 0 ? 0 : (len - 1);
                    query = GetCPQuery(sql, parameters, pagingInfo, tables);
                }
            }
            return(query);
        }
Exemple #3
0
        /// <summary>
        /// 获取CPQuery
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="pagingInfo">标准分页实体</param>
        /// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
        /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
        /// <returns>CPQuery</returns>
        private static CPQuery GetSpecialQuery(string sql, object parameters, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }

            if (string.IsNullOrEmpty(pagingInfo.OrderSeq))
            {
                throw new MyException("请在分页实体中指定 排序字段");
            }

            // 格式化数据库实例名
            if (tables != null && tables.Length > 0)
            {
                sql = string.Format(sql, tables);
            }

            var extCols    = "";
            var joinTables = "";

            if (dict != null && dict.Count > 0)
            {
                extCols    = "," + dict.Keys.Join(" , ");
                joinTables = dict.Values.Join(" ");
            }

            // 生成 SELECT 命令
            var selectCommandText = string.Format(@"SELECT  a.* {4}
                                        FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY {3} ) AS [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] ,
                                                            *
                                                    FROM      ({0}) AS a
                                                ) AS a {5}
                                        WHERE   [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] > ( {1} * {2} )
                                                AND [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] <= ( {1} * ( {2} + 1 ) ) order by [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86]", sql, pagingInfo.PageSize, pagingInfo.PageIndex, pagingInfo.OrderSeq, extCols, joinTables);

            CPQuery query1 = CPQuery.From(selectCommandText, parameters);
            // 生成 COUNT 命令
            string  getCountText = string.Format(@"select count(*) from ({0}) as t1", sql);
            CPQuery query2       = CPQuery.From(getCountText, parameters);

            pagingInfo.TotalRecords = query2.ExecuteScalar <int>();

            return(query1);
        }
Exemple #4
0
 /// <summary>
 /// 获取分页数据表(指定数据库连接字符串)
 /// </summary>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="strConn">数据库连接字符串</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>分页数据表</returns>
 public static DataTable GetData(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, params object[] tables)
 {
     using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
     {
         return(GetValidCPQuery(sql, parameters, pagingInfo, tables).FillDataTable());
     }
 }
Exemple #5
0
 /// <summary>
 /// 获取实体分页列表
 /// </summary>
 /// <typeparam name="T">实体类型</typeparam>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>实体分页列表</returns>
 public static List <T> GetList <T>(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables) where T : class, new()
 {
     return(GetValidCPQuery(sql, parameters, pagingInfo, tables).ToList <T>());
 }
Exemple #6
0
 /// <summary>
 /// 获取分页数据表
 /// </summary>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>分页数据表</returns>
 public static DataTable GetData(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables)
 {
     return(GetValidCPQuery(sql, parameters, pagingInfo, tables).FillDataTable());
 }
Exemple #7
0
 /// <summary>
 /// 获取实体分页列表(指定数据库连接字符串)
 /// </summary>
 /// <typeparam name="T">实体类型</typeparam>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="strConn">数据库连接字符串</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>实体分页列表</returns>
 public static List <T> GetSpecialList <T>(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables) where T : class, new()
 {
     return(GetSpecialData(sql, parameters, pagingInfo, dict, tables).ToList <T>());
 }
Exemple #8
0
 /// <summary>
 /// 获取数据集分页列表(指定数据库连接字符串)
 /// </summary>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="strConn">数据库连接字符串</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>数据集分页列表</returns>
 public static DataTable GetSpecialData(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
 {
     using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
     {
         return(GetSpecialQuery(sql, parameters, pagingInfo, dict, tables).FillDataTable());
     }
 }
Exemple #9
0
 /// <summary>
 /// 获取特殊数据集分页列表
 /// </summary>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>分页数据集</returns>
 public static DataTable GetSpecialData(string sql, object parameters, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
 {
     return(GetSpecialQuery(sql, parameters, pagingInfo, dict, tables).FillDataTable());
 }
Exemple #10
0
 /// <summary>
 /// 获取实体分页列表(指定数据库连接字符串)
 /// </summary>
 /// <typeparam name="T">实体类型</typeparam>
 /// <param name="sql">执行SQL</param>
 /// <param name="parameters">参数</param>
 /// <param name="strConn">数据库连接字符串</param>
 /// <param name="pagingInfo">分页信息(OrderSeq传递排序字段:(Oid desc,Name asc))</param>
 /// <param name="tables">联合查询用到的数据库实例名,可以用ConnStringHelper.GetDBName(string key)</param>
 /// <returns>实体分页列表</returns>
 public static List <T> GetList <T>(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, params object[] tables) where T : class, new()
 {
     using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
     {
         return(GetValidCPQuery(sql, parameters, pagingInfo, tables).ToList <T>());
     }
 }
        /// <summary>
        /// 获取实体分页列表
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="pageInfo">标准分页信息</param>
        /// <returns>实体分页列表</returns>
        public List <T> ToPageList(StandardPagingInfo pageInfo)
        {
            //--需要配置的SQL语句
            //select row_number() over (order by UpCount asc) as RowIndex,
            //    Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
            //from   CaoItem
            //where CreateTime < @CreateTime

            //--在运行时,将会生成下面二条SQL

            //select * from (
            //select row_number() over (order by UpCount asc) as RowIndex,
            //    Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
            //from   CaoItem
            //where CreateTime < @CreateTime
            //) as t1
            //where  RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))

            //select  count(*) from   ( select
            //-- 去掉 select row_number() over (order by UpCount asc) as RowIndex,
            //    Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
            //from   CaoItem as p
            //where CreateTime < @CreateTime
            //) as t1


            // 为了方便得到 count 的语句,先直接定位 ") as RowIndex,"
            // 然后删除这之前的部分,将 select  count(*) from   (select 加到SQL语句的前面。
            // 所以,这里就检查SQL语句是否符合要求。

            if (string.IsNullOrEmpty(pageInfo.OrderSeq))
            {
                pageInfo.OrderSeq = _sqlInfo.PrimaryKey;
            }

            string xmlCommandText = _query.ToString().Replace("select", "select ROW_NUMBER() OVER ( ORDER BY " + pageInfo.OrderSeq + " ) as RowIndex, ");

            Match match = _pagingRegex.Match(xmlCommandText);

            if (match.Success == false)
            {
                throw new InvalidOperationException("XML中配置的SQL语句不符合分页语句的要求。");
            }
            int p = match.Index;

            // 获取命令参数数组
            SqlParameter[] parameters1 = _query.Command.Parameters.Cast <SqlParameter>().ToArray();
            _query.Command.Parameters.Clear();  // 断开参数对象与原命令的关联。

            // 克隆参数数组,因为参数对象只能属于一个命令对象。
            SqlParameter[] parameters2 = (from pp in parameters1
                                          select new SqlParameter
            {
                ParameterName = pp.ParameterName,
                SqlDbType = pp.SqlDbType,
                Size = pp.Size,
                Scale = pp.Scale,
                Value = pp.Value,
                Direction = pp.Direction
            }).ToArray();



            // 生成 SELECT 命令
            string selectCommandText = string.Format(@"select * from ( {0} ) as t1 where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))", xmlCommandText);

            Panto.Map.Extensions.DAL.CPQuery query1 = Panto.Map.Extensions.DAL.CPQuery.From(selectCommandText, parameters1);

            query1.Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@PageIndex",
                SqlDbType     = System.Data.SqlDbType.Int,
                Value         = pageInfo.PageIndex
            });
            query1.Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@PageSize",
                SqlDbType     = System.Data.SqlDbType.Int,
                Value         = pageInfo.PageSize
            });



            // 生成 COUNT 命令
            string getCountText = string.Format("select  count(*) from   (select {0}  ) as t1",
                                                xmlCommandText.Substring(p + match.Length));

            Panto.Map.Extensions.DAL.CPQuery query2 = Panto.Map.Extensions.DAL.CPQuery.From(getCountText, parameters2);


            // 执行二次数据库操作(在一个连接中)
            using (Panto.Map.Extensions.DAL.ConnectionScope scope = new Panto.Map.Extensions.DAL.ConnectionScope())
            {
                List <T> list = query1.ToList <T>();
                pageInfo.TotalRecords = query2.ExecuteScalar <int>();

                return(list);
            }
        }