public DataTable QueryByDividPage(ref Common.DividPage page, string sql)
{
if (page == null)
{
page = new Common.DividPage();
}
if (page.CurrentPageNumber == 0)
{
page.CurrentPageNumber = 1;
}
int RecordCounts = 0;
int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber;
int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts;
string strSqlRecordCounts = "select count(*) as recordcounts from (" + sql + ") as a";
using (IDataReader dr = dbFactory.ExecuteReader(strSqlRecordCounts))
{
if (dr.Read())
{
RecordCounts = int.Parse(dr["recordcounts"].ToString());
}
}
//string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS PageRowNumber , * From (@Tables) ) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
string strSql = "Select * From (@Tables) as a Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
strSql = strSql.Replace("@TopNumber", TopNumber.ToString());
//strSql = strSql.Replace("@Sort", Sort.ToString());
//strSql = strSql.Replace("@Fields", Fields.ToString());
strSql = strSql.Replace("@Tables", sql.ToString());
//strSql = strSql.Replace("@Filter", Filter.ToString());
strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString());
DataTable dt = dbFactory.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
page.RecordCounts = RecordCounts;
if (page.RecordCounts > 0)
{
page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts;
}
else
{
page.PagesCount = 0;
page.CurrentPageRecordCounts = 0;
}
return(dt);
}
public static OracleDataReader QueryByDividPage2(ref Common.DividPage page, string Tables)
{
if (page == null)
{
page = new Common.DividPage();
}
if (page.CurrentPageNumber == 0)
{
page.CurrentPageNumber = 1;
}
int RecordCounts = 0;
int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber;
int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts;
string strSqlRecordCounts = "select count(*) as recordcounts from (" + Tables + ")";
using (OracleDataReader dr = OracleDBHelper.ExecuteReader(strSqlRecordCounts))
{
if (dr.Read())
{
RecordCounts = int.Parse(dr["recordcounts"].ToString());
}
}
//string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS PageRowNumber , * From (@Tables) ) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
string strSql = "Select * From (@Tables) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
strSql = strSql.Replace("@TopNumber", TopNumber.ToString());
//strSql = strSql.Replace("@Sort", Sort.ToString());
//strSql = strSql.Replace("@Fields", Fields.ToString());
strSql = strSql.Replace("@Tables", Tables.ToString());
//strSql = strSql.Replace("@Filter", Filter.ToString());
strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString());
OracleDataReader dR = OracleDBHelper.ExecuteReader(strSql);
page.RecordCounts = RecordCounts;
if (page.RecordCounts > 0)
{
page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts;
}
else
{
page.PagesCount = 0;
page.CurrentPageRecordCounts = 0;
}
return(dR);
}
public bool GetModelListByPage(ref List <TBase_Model> modelList, User.UserModel user, TBase_Model model, ref Common.DividPage page, ref string strError)
{
try
{
modelList = db.GetModelListByPage(user, model, ref page);
return(true);
}
catch (Exception ex)
{
strError = "获取" + GetModelChineseName() + "列表失败！" + ex.Message + "\r\n" + ex.TargetSite;
return(false);
}
}
/// <summary>
/// 分页获取modellist的记录 condition:多条件的情况下
/// </summary>
public virtual List <TBase_Model> GetModelListByPage(User.UserModel user, TBase_Model model, ref Common.DividPage page, string condition = "")
{
try
{
CanQuery(model);
using (IDataReader reader = common_FactoryDB.QueryByDividPage(ref page, GetViewName(), GetFilterSql(user, model), GetFieldsSql(), GetOrderBySql(), condition))
{
return(ToModels(reader));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="RecordCounts"></param>
/// <param name="Tables"></param>
/// <param name="Filter"></param>
/// <param name="PageSize"></param>
/// <param name="PageNumber"></param>
/// <param name="Fields"></param>
/// <param name="Sort"></param>
/// <returns></returns>
public IDataReader QueryByDividPage(ref Common.DividPage page, string Tables, string Filter = "", string Fields = "*", string Sort = "", string condition = "") // string Sort = "Order by ID Desc")
{
if (Fields.Trim() == "*")
{
if (Tables.Contains(")N"))
{
string temtable = Tables.Substring(Tables.Length - 1, 1);
if (Fields.Trim() == "*")
{
Fields = temtable + '.' + Fields;
}
}
else
{
Fields = Tables + '.' + Fields;
}
}
if (page == null)
{
page = new Common.DividPage();
}
if (page.CurrentPageNumber == 0)
{
page.CurrentPageNumber = 1;
}
int RecordCounts = 0;
int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber;
int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts;
string strSqlRecordCounts = "select count(*) as recordcounts from " + Tables + " " + Filter + "" + condition;
using (IDataReader dr = dbFactory.ExecuteReader(strSqlRecordCounts))
{
if (dr.Read())
{
RecordCounts = int.Parse(dr["recordcounts"].ToString());
}
}
// string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS PageRowNumber , @Fields From @Tables @Filter ) Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
//string strSql = "select * from (select top @TopNumber ROW_NUMBER() OVER (@Sort) AS ROWNUM , @Fields from @Tables @Filter @Condition ) t ";
string strSql = "select * from (select top @TopNumber ROW_NUMBER() OVER (@Sort) AS ROWNUM , @Fields from @Tables @Filter ) t ";
strSql += " where ROWNUM > @WhereNumber";
strSql = strSql.Replace("@TopNumber", TopNumber.ToString());
strSql = strSql.Replace("@Sort", Sort.ToString());
strSql = strSql.Replace("@Fields", Fields.ToString());
strSql = strSql.Replace("@Tables", Tables.ToString());
strSql = strSql.Replace("@Filter", Filter.ToString());
strSql = strSql.Replace("@Condition", condition.ToString());
strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString());
IDataReader dR = dbFactory.ExecuteReader(strSql);
page.RecordCounts = RecordCounts;
if (page.RecordCounts > 0)
{
page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts;
}
else
{
page.PagesCount = 0;
page.CurrentPageRecordCounts = 0;
}
return(dR);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="page">分页方式</param>
/// <param name="Tables">表名(视图名)</param>
/// <param name="Filter">筛选条件</param>
/// <param name="Fields">显示列</param>
/// <param name="Sort">排序方式</param>
/// <returns>查询结果</returns>
//public static SqlDataReader QueryByDividPage(ref Common.DividPage page, string Tables, string Filter = "", string Fields = "*", string Sort = "Order by ID Desc")
//{
// if (Fields.Trim() == "*") Fields = Tables + '.' + Fields;
// if (!string.IsNullOrEmpty(Filter.Trim()) && Filter.ToLower().IndexOf("where") < 0) Filter = "where " + Filter;
// if (page == null) page = new Common.DividPage();
// if (page.CurrentPageNumber <= 0) page.CurrentPageNumber = 1;
// int RecordCounts = 0;
// int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber;
// int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts;
// string strSqlRecordCounts = "Select Count(1) As recordcounts From " + Tables + " " + Filter;
// try
// {
// using (SqlDataReader dr = OperationSql.ExecuteReader(CommandType.Text, strSqlRecordCounts))
// {
// if (dr.Read())
// {
// RecordCounts = int.Parse(dr["recordcounts"].ToString());
// }
// }
// }
// catch (Exception ex)
// {
// throw new Exception(ex.Message);
// }
// finally
// {
// }
// if (page.CurrentPageShowCounts < 0) page.CurrentPageShowCounts = TopNumber = RecordCounts;
// string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS ROWNUMBER , @Fields From @Tables @Filter ) a Where ROWNUMBER <= @TopNumber And ROWNUMBER > @WhereNumber ";
// strSql = strSql.Replace("@TopNumber", TopNumber.ToString());
// strSql = strSql.Replace("@Sort", Sort.ToString());
// strSql = strSql.Replace("@Fields", Fields.ToString());
// strSql = strSql.Replace("@Tables", Tables.ToString());
// strSql = strSql.Replace("@Filter", Filter.ToString());
// strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString());
// SqlDataReader dR = OperationSql.ExecuteReader(CommandType.Text, strSql);
// page.RecordCounts = RecordCounts;
// if (page.RecordCounts > 0)
// {
// page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts;
// if (page.CurrentPageShowCounts == 1) page.CurrentPageRecordCounts = 1;
// else if (page.CurrentPageNumber < page.PagesCount) page.CurrentPageRecordCounts = page.CurrentPageShowCounts;
// else page.CurrentPageRecordCounts = RecordCounts % page.CurrentPageShowCounts;
// }
// else
// {
// page.PagesCount = 0;
// page.CurrentPageRecordCounts = 0;
// }
// return dR;
//}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="page">分页方式</param>
/// <param name="Tables">表名(视图名)</param>
/// <param name="Filter">筛选条件</param>
/// <param name="Fields">显示列</param>
/// <param name="Sort">排序方式</param>
/// <returns>查询结果</returns>
public static SqlDataReader QueryByDividPage(ref Common.DividPage page, string Tables, string Filter = "", string Fields = "*", string Sort = "Order by ID Desc")
{
try
{
if (Fields.Trim() == "*")
{
Fields = Tables + '.' + Fields;
}
if (!string.IsNullOrEmpty(Filter.Trim()) && Filter.ToLower().IndexOf("where") < 0)
{
Filter = "where " + Filter;
}
if (page == null)
{
page = new Common.DividPage()
{
CurrentPageShowCounts = -1
}
}
;
if (page.CurrentPageNumber <= 0)
{
page.CurrentPageNumber = 1;
}
int RecordCounts = 0;
int TopNumber = page.CurrentPageShowCounts * page.CurrentPageNumber;
int WhereNumber = (page.CurrentPageNumber - 1) * page.CurrentPageShowCounts;
string strSqlRecordCounts = "Select Count(1) As recordcounts From " + Tables + " " + Filter;
if (strSqlRecordCounts.ToLower().IndexOf("group by") >= 0)
{
strSqlRecordCounts = string.Format("Select Count(1) As recordcounts from ({0}) t", strSqlRecordCounts);
}
try
{
using (SqlDataReader dr = OperationSql.ExecuteReader(CommandType.Text, strSqlRecordCounts))
{
if (dr.Read())
{
RecordCounts = int.Parse(dr["recordcounts"].ToString());
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
}
if (page.CurrentPageShowCounts < 0)
{
page.CurrentPageShowCounts = TopNumber = RecordCounts;
}
//if (Filter.ToLower().IndexOf("group by") < 0)
//{
// if (!string.IsNullOrEmpty(Filter.Trim())) Filter = string.Format("{0} and rownum <= {1}", Filter, (TopNumber + 10));
// else Filter = string.Format("where rownum <= {1}", Filter, (TopNumber + 10));
//}
string strSql = "Select * From (Select ROW_NUMBER() OVER(@Sort) AS PageRowNumber , @Fields From @Tables @Filter ) t Where PageRowNumber <= @TopNumber And PageRowNumber > @WhereNumber ";
strSql = strSql.Replace("@TopNumber", TopNumber.ToString());
strSql = strSql.Replace("@Sort", Sort.ToString());
strSql = strSql.Replace("@Fields", Fields.ToString());
strSql = strSql.Replace("@Tables", Tables.ToString());
strSql = strSql.Replace("@Filter", Filter.ToString());
strSql = strSql.Replace("@WhereNumber", WhereNumber.ToString());
SqlDataReader dR = OperationSql.ExecuteReader(CommandType.Text, strSql);
page.RecordCounts = RecordCounts;
if (page.RecordCounts > 0)
{
if (page.CurrentPageShowCounts <= 0)
{
page.PagesCount = 0;
}
else
{
page.PagesCount = (RecordCounts + page.CurrentPageShowCounts - 1) / page.CurrentPageShowCounts;
}
if (page.CurrentPageNumber < page.PagesCount)
{
page.CurrentPageRecordCounts = page.CurrentPageShowCounts;
}
else
{
page.CurrentPageRecordCounts = RecordCounts % page.CurrentPageShowCounts;
}
if (page.CurrentPageRecordCounts == 0)
{
page.CurrentPageRecordCounts = page.CurrentPageShowCounts;
}
}
else
{
page.PagesCount = 0;
page.CurrentPageRecordCounts = 0;
}
return(dR);
}
catch (Exception ex)
{
string strError = string.Empty;
//Common_Func.IsOracleError(ex.Message, ref strError);
throw new Exception(strError);
}
}
