/// <summary>
/// 按数据库取得分页取得数据
/// </summary>
/// <param name="page">分页实体</param>
/// <param name="columns">查询的列</param>
/// <param name="tableSql">查询的表体</param>
/// <param name="cond">查询的条件</param>
/// <param name="DBName">数据库名称</param>
/// <param name="totalRows">OUT->总数据行数</param>
/// <returns>返回查询出来的数据以及输入总数据行数</returns>
public static DataSet GetTableForPagination(Pagination page, string columns, string tableSql, string cond, DBConfigEnum DBName, out int totalRows)
{
int pageIndex = page.NowPage;
int pageSize = page.PageSize;
totalRows = 0;
if (string.IsNullOrWhiteSpace(page.Sort))
{
page.Sort = DefaultSortStr;
page.SortType = "";
}
else if (string.IsNullOrWhiteSpace(page.SortType))
{
page.SortType = " asc ";
}
if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(tableSql))
{
throw new Exception("传入的列名与表名均不能为空");
}
string sql = $@"select * from (
select ROW_NUMBER() OVER(order by {page.Sort} {page.SortType} ) rowid,{columns} from {tableSql} where 1=1 {cond}
) t where 1=1 and rowid between {((pageIndex - 1) * pageSize) + 1} and {pageIndex * pageSize}";
string countSql = $@"select Count(1) counts from (
select {columns} from {tableSql} where 1=1 {cond}
) t ";
DataSet ds = ExecuteSql(sql, DBName);
using (var conn = new DBConfig().InitConn(DBName.ToString())) {
totalRows = conn.ExecuteScalar <int>(countSql);
}
return(ds);
}
/// <summary>
/// 按数据库名称并根据传入的参数进行查询
/// </summary>
/// <param name="columns">查询的列名</param>
/// <param name="tableSql">查询的表体</param>
/// <param name="cond">条件，以and|or连接字开始</param>
/// <param name="sort">排序字段</param>
/// <param name="DBName">数据库名称</param>
/// <param name="rowCount">查询结果的行数</param>
/// <returns>返回一个DataSet并带出查询的行数</returns>
public static DataSet GetTable(string columns, string tableSql, string cond, string sort, DBConfigEnum DBName, out int rowCount)
{
if (string.IsNullOrWhiteSpace(sort))
{
sort = DefaultSortStr;
}
if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(tableSql) || string.IsNullOrWhiteSpace(sort))
{
throw new Exception("空值的参数设定");
}
string sql = $@"select * from (
select ROW_NUMBER() over( order by {sort}) rowid, {columns} from {tableSql}
where 1=1 {cond}
) t ";
string countSql = $@"select Count(1) from (
select ROW_NUMBER() over( order by {sort}) rowid, {columns} from {tableSql}
where 1=1 {cond}
) t ";
DataSet ds = new DataSet();
ds = ExecuteSql(sql, DBName);
using (var conn = new DBConfig().InitConn(DBName.ToString())) {
rowCount = conn.ExecuteScalar <int>(countSql);
}
return(ds);
}
