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