/// <summary> /// 如果有Sql语句,直接根据Sql生成分页 /// </summary> /// <param name="gkey"></param> /// <param name="parameters"></param> /// <returns></returns> private IPager BuildSqlPager(DataGateKey gkey, IDictionary <string, object> parameters) { var mainModel = GetMainTable(gkey); string filter = FormatFilter(gkey.Filter, mainModel); if (!filter.IsEmpty()) { filter = " where " + filter; } string sql = $"{gkey.Sql}{filter}"; int pageSize = CommOp.ToInt(GetValueRemoveKey(parameters, "pageSize")); if (pageSize <= 0) { pageSize = Consts.DefaultPageSize; } DBPagerInfo pager = new DBPagerInfo { Query = sql, KeyId = mainModel.PrimaryKey.FixDbName, PageIndex = Math.Max(1, CommOp.ToInt(GetValueRemoveKey(parameters, "pageIndex"))) - 1, PageSize = pageSize, OrderBy = gkey.OrderBy, }; return(pager); }
//单表的分页 private IPager BuildPager(DataGateKey gkey, IDictionary <string, object> parameters) { var tableMeta = gkey.MainTable; string filter = FormatFilter(gkey.Filter, tableMeta); if (!filter.IsEmpty()) { filter = " where " + filter; } string sql = gkey.Sql; if (sql.IsEmpty()) { sql = $"select {gkey.QueryFieldsTerm} from {tableMeta.FixDbName}{filter}"; } int pageSize = CommOp.ToInt(GetValueRemoveKey(parameters, "pageSize")); if (pageSize <= 0) { pageSize = Consts.DefaultPageSize; } DBPagerInfo pager = new DBPagerInfo { Query = sql, KeyId = $"{gkey.TableJoins[0].Alias ?? tableMeta.FixDbName}.{tableMeta.PrimaryKey.FixDbName}", PageIndex = Math.Max(1, CommOp.ToInt(GetValueRemoveKey(parameters, "pageIndex"))) - 1, PageSize = pageSize, OrderBy = gkey.OrderBy, }; return(pager); }
public IDataReader ExecPageReader(DBPagerInfo pager, params IDataParameter[] sp) { string sql = "SELECT COUNT(*) FROM (" + pager.Query + ")c"; pager.RecordCount = CommOp.ToInt(Helper.ExecGetObject(sql, sp)); sql = String.Format(@"SELECT * FROM(SELECT A.*, rownum r FROM({0} ORDER BY {1})A WHERE rownum <= {2})B WHERE r>{3}", pager.Query, pager.OrderBy, pager.StartIndex + pager.PageSize, pager.StartIndex); return(Helper.ExecReader(sql, sp)); }
public IDataReader ExecPageReader(DBPagerInfo pager, params IDataParameter[] sp) { if (pager.OrderBy.IsEmpty()) { throw new ArgumentException("pager.OrderBy"); } string sql = "SELECT COUNT(*) FROM (" + pager.Query + ")c"; pager.RecordCount = CommOp.ToInt(Helper.ExecGetObject(sql, sp)); sql = String.Format(@"{0} ORDER BY {1} LIMIT {2} OFFSET {3}", pager.Query, pager.OrderBy, pager.PageSize, pager.StartIndex); return(Helper.ExecReader(sql, sp)); }
public void OracleTestPageReader() { OracelMDATestImportFromDataTable(); DBPagerInfo pageInfo = new DBPagerInfo { Query = "SELECT * FROM BIZ_CUSTOMER", PageIndex = 1, PageSize = 2, OrderBy = "ID" }; var reader = helper.ExecPageReader(pageInfo); int cnt = 0; while (reader.Read()) { cnt++; } Assert.AreEqual(cnt, 1); }
public void SQLiteTestPageReader() { SQLiteTestImportFromDataTable(); DBPagerInfo pageInfo = new DBPagerInfo { Query = "SELECT * FROM Biz_Customer", PageIndex = 1, PageSize = 2, OrderBy = "ID" }; var reader = helper.ExecPageReader(pageInfo); int cnt = 0; while (reader.Read()) { cnt++; } Assert.AreEqual(cnt, 1); }
public void TestPageReader() { TestImportFromDataTable(); DBPagerInfo pageInfo = new DBPagerInfo { Query = "SELECT * FROM Biz_Customer", KeyId = "ID", PageIndex = 1, PageSize = 2, OrderBy = "ID" }; DBHelper helper = new DBHelper("DefaultConnection"); var reader = helper.ExecPageReader(pageInfo); int cnt = 0; while (reader.Read()) { cnt++; } Assert.AreEqual(cnt, 1); }