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