/// <summary> /// 查询并且返回DataSet(游标分页) /// </summary> /// <param name="sql">要查询的SQL语句</param> /// <param name="objPage">分页对象</param> /// <param name="oper">数据库对象</param> /// <param name="curType">映射的实体类型(如果用回数据库的原列名,则此为null)</param> /// <returns></returns> public static DataTable QueryDataTable(string sql, PageContent objPage, DataBaseOperate oper, Type curType) { DataTable ret = new DataTable(); ParamList lstParams = new ParamList(); lstParams.AddNew("@sql", DbType.AnsiString, sql); lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1); lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize); lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords); IDataReader reader = null; try { InitProc(oper); reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null); if (reader.NextResult())//第二个结果集为查询结果 { if (curType == null) { ret = CacheReader.GenerateDataTable(reader, "newDt", false); } else { ret = CacheReader.GenerateDataTable(reader, "newDt", curType, false); } } if (reader.NextResult())//第三个结果集为总行数 { if (reader.Read()) { int totalRecord = reader.GetInt32(0); objPage.TotalRecords = totalRecord; //int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize); //objPage.TotalPage = totalPage; if (objPage.CurrentPage >= objPage.TotalPage - 1) { objPage.CurrentPage = objPage.TotalPage - 1; } } } } finally { reader.Close(); } return(ret); }
/// <summary> /// 查询并且返回DataSet(游标分页) /// </summary> /// <param name="sql">要查询的SQL语句</param> /// <param name="lstParam">参数集合</param> /// <param name="objPage">分页对象</param> /// <param name="oper">数据库对象</param> /// <param name="curType">映射的实体类型(如果用回数据库的原列名,则此为null)</param> /// <returns></returns> public static DataTable QueryDataTable(string sql, ParamList lstParam, PageContent objPage, DataBaseOperate oper, Type curType, Dictionary <string, bool> cacheTables) { objPage.TotleRecords = CutPageSqlCreater.GetTotleRecord(lstParam, oper, sql, objPage.MaxSelectRecords, cacheTables); long totlePage = (long)Math.Ceiling((double)objPage.TotleRecords / (double)objPage.PageSize); objPage.TotlePage = totlePage; if (objPage.CurrentPage >= objPage.TotlePage - 1) { objPage.CurrentPage = objPage.TotlePage - 1; } if (objPage.CurrentPage >= objPage.TotlePage - 1) { objPage.CurrentPage = objPage.TotlePage - 1; } DataTable ret = new DataTable(); IDataReader reader = null; try { string qsql = CutPageSqlCreater.GetCutPageSql(sql, objPage); reader = oper.Query(qsql, lstParam, cacheTables); if (curType == null) { ret = CacheReader.GenerateDataTable(reader, "newDt", false); } else { ret = CacheReader.GenerateDataTable(reader, "newDt", curType, false); } } finally { reader.Close(); //oper.CloseDataBase(); } return(ret); }