/// <summary>
/// 生成SQL语句
/// </summary>
/// <param name="list">参数列表</param>
/// <param name="oper">连接对象</param>
/// <param name="objCondition">条件对象</param>
/// <param name="objPage">分页记录类</param>
/// <returns></returns>
public static string CreatePageSql(ParamList list, DataBaseOperate oper,
SelectCondition objCondition, PageContent objPage, bool useCache)
{
if (objPage.CurrentPage < 0 || objPage.PageSize <= 0)//初始化页数
{
return("");
}
//string sql = "select " + objCondition.SqlParams + " from " + DbAdapterLoader.CurrentDbAdapter.FormatTableName(objCondition.Tables) + " where " + objCondition.Condition + " order by " + objCondition.Orders;
//StringBuilder sql = new StringBuilder(5000);
//sql.Append("select ");
//sql.Append(objCondition.SqlParams.ToString());
//sql.Append(" from ");
//sql.Append(DbAdapterLoader.CurrentDbAdapter.FormatTableName(objCondition.Tables.ToString()));
//if (objCondition.Condition.Length > 0)
//{
// sql.Append(" where ");
// sql.Append(objCondition.Condition.ToString());
//}
//if (objCondition.GroupBy.Length > 0)
//{
// sql.Append(" group by ");
// sql.Append(objCondition.GroupBy.ToString());
//}
//if (objCondition.Orders.Length>0)
//{
// sql.Append(" order by ");
// sql.Append(objCondition.Orders.ToString());
//}
//if (objCondition.Having.Length > 0)
//{
// sql.Append(" having ");
// sql.Append(objCondition.Having.ToString());
//}
string sql = objCondition.GetSelect();
if (objPage.IsFillTotalRecords)
{
objPage.TotalRecords = GetTotalRecord(list, oper, objCondition.GetSelect(false), objPage.MaxSelectRecords,
(useCache?objCondition.CacheTables:null));//获取总记录数
//long totalPage = (long)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
//objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
}
}
return(GetCutPageSql(sql, objPage));
}
/// <summary>
/// 执行sql语句，分页返回DataSet
/// </summary>
/// <param name="BQL">sql语句</param>
public DataSet QueryDataSet <E>(BQLQuery BQL, bool useCache)
{
AbsCondition con = ToCondition(BQL, null, true, typeof(E));
DataSet ds = null;
Dictionary <string, bool> cacheTables = null;
if (useCache)
{
cacheTables = con.CacheTables;
}
con.Oper = _oper;
if (con.DbParamList != null)
{
ds = _oper.QueryDataSet(con.GetSql(useCache), con.DbParamList, cacheTables);
}
else
{
SelectCondition sCon = con as SelectCondition;
DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), sCon.PageContent, _oper, null);
dt.TableName = "newTable";
ds = new DataSet();
ds.Tables.Add(dt);
}
return(ds);
}
/// <summary>
/// 获取top的查询字符串
/// </summary>
/// <param name="sql">查询字符串</param>
/// <param name="top">top值</param>
/// <returns></returns>
public string GetTopSelectSql(SelectCondition sql, int top)
{
StringBuilder sbSql = new StringBuilder(sql.GetSelect());
//sbSql.Append(sql);
sbSql.Append(" limit " + top + " offset 0");
return(sbSql.ToString());
}
/// <summary>
/// 获取top的查询字符串
/// </summary>
/// <param name="sql">查询字符串</param>
/// <param name="top">top值</param>
/// <returns></returns>
public string GetTopSelectSql(SelectCondition sql, int top)
{
PageContent objPage = new PageContent();
objPage.IsFillTotalRecords = false;
objPage.StarIndex = 0;
objPage.PageSize = top;
return(CutPageSqlCreater.GetCutPageSql(sql.GetSelect(), objPage));
}
/// <summary>
/// 获取top的查询字符串
/// </summary>
/// <param name="sql">查询字符串</param>
/// <param name="top">top值</param>
/// <returns></returns>
public string GetTopSelectSql(SelectCondition sql, int top)
{
StringBuilder sbSql = new StringBuilder(500);
sbSql.Append(sql.GetSelect());
sbSql.Append(" fetch first ");
sbSql.Append(top.ToString());
sbSql.Append(" rows only");
return(sbSql.ToString());
}
/// <summary>
/// 生成SQL语句
/// </summary>
/// <param name="list">参数列表</param>
/// <param name="oper">连接对象</param>
/// <param name="objCondition">条件对象</param>
/// <param name="objPage">分页记录类</param>
/// <returns></returns>
public static string CreatePageSql(ParamList list, DataBaseOperate oper,
SelectCondition objCondition, PageContent objPage, bool useCache)
{
if (objPage.CurrentPage < 0 || objPage.PageSize <= 0)//初始化页数
{
return("");
}
string sql = objCondition.GetSelect();
if (objPage.IsFillTotleRecords)
{
objPage.TotleRecords = GetTotleRecord(list, oper, objCondition.GetSelect(false), objPage.MaxSelectRecords,
(useCache?objCondition.CacheTables:null));//获取总记录数
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;
}
}
return(GetCutPageSql(sql, objPage));
}
/// <summary>
/// 执行sql语句，分页返回List
/// </summary>
/// <typeparam name="E">实体类型</typeparam>
/// <param name="BQL">BQL</param>
/// <param name="objPage">分页数据</param>
/// <param name="outPutTables">输出表</param>
/// <returns></returns>
public List <E> QueryPageList <E>(BQLQuery BQL, PageContent objPage,
IEnumerable <BQLEntityTableHandle> outPutTables, bool useCache)
where E : EntityBase, new()
{
AbsCondition con = ToCondition(BQL, outPutTables, false, typeof(E));
con.PageContent = objPage;
List <E> retlist = null;
IDataReader reader = null;
try
{
Dictionary <string, bool> cacheTables = null;
if (useCache)
{
cacheTables = con.CacheTables;
}
if (con.DbParamList != null)
{
con.PageContent = objPage;
con.Oper = _oper;
string sql = con.GetSql(useCache);
reader = _oper.Query(sql, con.DbParamList, cacheTables);
}
else
{
SelectCondition sCon = con as SelectCondition;
reader = con.DBinfo.CurrentDbAdapter.Query(sCon.GetSelect(), objPage, _oper);
}
retlist = LoadFromReader <E>(con.AliasManager, reader);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(retlist);
}
/// <summary>
/// 执行sql语句，分页返回DataSet
/// </summary>
/// <param name="BQL">sql语句</param>
/// <param name="objPage">分页对象</param>
public DataSet QueryDataSet(BQLQuery bql, Type tableType, PageContent objPage, bool useCache)
{
AbsCondition con = ToCondition(bql, null, true, tableType);
Dictionary <string, bool> cacheTables = null;
if (useCache)
{
cacheTables = con.CacheTables;
}
DataSet ds = null;
using (BatchAction ba = _oper.StarBatchAction())
{
if (con.DbParamList != null)
{
con.PageContent = objPage;
con.Oper = _oper;
string sql = con.GetSql(useCache);
ds = _oper.QueryDataSet(sql, con.DbParamList, cacheTables);
}
else
{
SelectCondition sCon = con as SelectCondition;
DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), objPage, _oper, null);
dt.TableName = "newTable";
ds = new DataSet();
ds.Tables.Add(dt);
}
}
return(ds);
}