/// <summary>
/// GROUP和是否编译判断
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
internal CallBackDataReader GetPageReader(LambdaQueryBase query1)
{
if (query1.__GroupFields.Count > 0)
{
return(GetGroupPageReader(query1));
}
if (_DBAdapter.CanCompileSP && query1.__CompileSp)
{
return(GetSpPageReader(query1));
}
CheckTableCreated(query1.__MainType);
//var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[]));
var fields = query1.GetQueryFieldString();
var rowOver = query1.__QueryOrderBy;
if (string.IsNullOrEmpty(rowOver))
{
var table = TypeCache.GetTable(query1.__MainType);
rowOver = string.Format("t1.{0} desc", table.PrimaryKey.MapingName);
}
var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t.");
var condition = query1.GetQueryConditions();
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
string countSql = string.Format("select count(*) from {0}", condition);
int count = Convert.ToInt32(__DbHelper.ExecScalar(countSql));
query1.ExecuteTime += __DbHelper.ExecuteTime;
query1.RowCount = count;
//if (count == 0)
//{
// return null;
//}
int pageCount = (count + pageSize - 1) / pageSize;
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
var start = pageSize * (pageIndex - 1) + 1;
var end = start + pageSize - 1;
string sql = _DBAdapter.PageSqlFormat(fields, rowOver, condition, start, end, orderBy);
var reader = new CallBackDataReader(__DbHelper.ExecDataReader(sql), () =>
{
return(count);
});
query1.ExecuteTime += __DbHelper.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// 按编译
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
CallBackDataReader GetSpGroupPageReader(LambdaQueryBase query1)
{
//var query1 = query as RelationLambdaQuery<TModel>;
CheckTableCreated(query1.__MainType);
var sb = new StringBuilder();
query1.GetQueryConditions(sb);
var conditions = sb.ToString();
var fields = query1.GetQueryFieldString();
if (!conditions.Contains("group"))
{
throw new CRLException("缺少group语法");
}
var rowOver = query1.GetOrder();
if (string.IsNullOrEmpty(rowOver))
{
throw new CRLException("Group分页需指定Group排序字段");
//var table = TypeCache.GetTable(typeof(T));
//rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name);
}
var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
conditions = _DBAdapter.SqlFormat(conditions);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
AddParam("pageIndex", pageIndex);
AddParam("pageSize", pageSize);
var db = GetDBHelper(AccessType.Read);
db.AddOutParam("count", -1);
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("fields", fields);
dic.Add("rowOver", rowOver);
//dic.Add("pageSize", pageSize.ToString());
//dic.Add("sort", sort1);
string sp = CompileSqlToSp(_DBAdapter.TemplateGroupPage, conditions, dic);
CallBackDataReader reader;
reader = new CallBackDataReader(db.RunDataReader(sp), () =>
{
return(GetOutParam <int>("count"));
}, sp);
query1.ExecuteTime += db.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// 按编译
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
CallBackDataReader GetSpPageReader(LambdaQueryBase query1)
{
//var query1 = query as RelationLambdaQuery<TModel>;
CheckTableCreated(query1.__MainType);
//var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[]));
var fields = query1.GetQueryFieldString();
var rowOver = query1.GetOrder();
if (string.IsNullOrEmpty(rowOver))
{
var table = TypeCache.GetTable(query1.__MainType);
if (table.PrimaryKey == null)
{
throw new Exception("分页缺少默认排序字段");
}
rowOver = string.Format("t1.{0} desc", table.PrimaryKey.MapingName);
}
var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t.");
var sb = new StringBuilder();
query1.GetQueryConditions(sb);
var condition = sb.ToString();
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
AddParam("pageIndex", pageIndex);
AddParam("pageSize", pageSize);
AddOutParam("count", -1);
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("fields", fields);
dic.Add("sort", orderBy);
dic.Add("rowOver", rowOver);
//dic.Add("pageSize", pageSize.ToString());
//string sql = string.Format("{0} with(nolock) where {1}", tableName, where);
string sp = CompileSqlToSp(_DBAdapter.TemplatePage, condition, dic);
CallBackDataReader reader;
var db = GetDBHelper(DataAccessType.Read);
reader = new CallBackDataReader(db.RunDataReader(sp), () =>
{
return(GetOutParam <int>("count"));
}, sp);
ClearParame();
query1.ExecuteTime += db.ExecuteTime;
return(reader);
}
internal override CallBackDataReader GetPageData(string query, string fields, string sort, int pageSize, int pageIndex)
{
helper.AddParam("query_", query);
helper.AddParam("fields_", fields);
helper.AddParam("sort_", sort);
helper.AddParam("pageSize_", pageSize);
helper.AddParam("pageIndex_", pageIndex);
helper.AddOutParam("count_");
helper.AddOutParam("v_Cursor");
var reader = new CallBackDataReader(helper.RunDataReader("sp_page"), () =>
{
return(Convert.ToInt32(helper.GetOutParam("count_")));
});
return(reader);
}
/// <summary>
/// 按是否能编译
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
CallBackDataReader GetGroupPageReader(LambdaQueryBase query1)
{
//var query1 = query as RelationLambdaQuery<TModel>;
if (_DBAdapter.CanCompileSP && query1.__CompileSp)
{
return(GetSpGroupPageReader(query1));
}
CheckTableCreated(query1.__MainType);
var condition = query1.GetQueryConditions();
var fields = query1.GetQueryFieldString();
if (!condition.Contains("group"))
{
throw new CRLException("缺少group语法");
}
var rowOver = query1.__QueryOrderBy;
if (string.IsNullOrEmpty(rowOver))
{
throw new CRLException("Group分页需指定Group排序字段");
//var table = TypeCache.GetTable(typeof(T));
//rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name);
}
var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex == 0 ? 1 : pageIndex;
pageSize = pageSize == 0 ? 15 : pageSize;
string countSql = string.Format("select count(*) from (select count(*) as a from {0}) t", condition);
int count = Convert.ToInt32(__DbHelper.ExecScalar(countSql));
query1.ExecuteTime += __DbHelper.ExecuteTime;
query1.RowCount = count;
//if (count == 0)
//{
// return null;
//}
int pageCount = (count + pageSize - 1) / pageSize;
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
var start = pageSize * (pageIndex - 1) + 1;
var end = start + pageSize - 1;
string sql = _DBAdapter.PageSqlFormat(fields, rowOver, condition, start, end, "");
//System.Data.Common.DbDataReader reader;
//reader = dbHelper.ExecDataReader(sql);
var reader = new CallBackDataReader(__DbHelper.ExecDataReader(sql), () =>
{
return(count);
});
query1.ExecuteTime += __DbHelper.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// GROUP和是否编译判断
/// </summary>
/// <param name="query1"></param>
/// <returns></returns>
internal CallBackDataReader GetPageReader(LambdaQueryBase query1)
{
if (query1.__GroupFields != null)
{
return(GetGroupPageReader(query1));
}
if (_DBAdapter.CanCompileSP && query1.__CompileSp)
{
return(GetSpPageReader(query1));
}
CheckTableCreated(query1.__MainType);
//var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[]));
var fields = query1.GetQueryFieldString();
var rowOver = query1.GetOrder();
if (string.IsNullOrEmpty(rowOver))
{
var table = TypeCache.GetTable(query1.__MainType);
if (table.PrimaryKey == null)
{
throw new Exception("分页缺少默认排序字段");
}
rowOver = string.Format("t1.{0} desc", table.PrimaryKey.MapingName);
}
//var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "");
rowOver = _DBAdapter.SqlFormat(rowOver);
var orderBy = rowOver;
var sb = new StringBuilder();
query1.GetQueryConditions(sb);
var condition = sb.ToString();
condition = _DBAdapter.SqlFormat(condition);
query1.FillParames(this);
var pageIndex = query1.SkipPage;
var pageSize = query1.TakeNum;
pageIndex = pageIndex <= 0 ? 1 : pageIndex;
pageSize = pageSize <= 0 ? 15 : pageSize;
string countSql = string.Format("select count(*) {0}", condition);
var db = GetDBHelper(DataAccessType.Read);
int count = Convert.ToInt32(SqlStopWatch.ExecScalar(db, countSql));
query1.ExecuteTime += db.ExecuteTime;
query1.__RowCount = count;
if (count == 0)
{
return(null);
}
int pageCount = (count + pageSize - 1) / pageSize;
if (pageIndex > pageCount)
{
pageIndex = pageCount;
}
var start = pageSize * (pageIndex - 1) + 1;
var end = start + pageSize - 1;
string sql = _DBAdapter.PageSqlFormat(db, fields, rowOver, condition, start, end, orderBy);
var reader = new CallBackDataReader(db.ExecDataReader(sql), () =>
{
return(count);
}, sql);
query1.ExecuteTime += db.ExecuteTime;
ClearParame();
return(reader);
}
/// <summary>
/// page
/// </summary>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <param name="sort"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
internal virtual CallBackDataReader GetPageData(string query, string fields, string sort, int pageSize, int pageIndex)
{
helper.AddParam("query_", query);
helper.AddParam("fields_", fields);
helper.AddParam("sort_", sort);
helper.AddParam("pageSize_", pageSize);
helper.AddParam("pageIndex_", pageIndex);
helper.AddOutParam("count_",1);
//var reader = helper.RunDataReader("sp_page");
var reader = new CallBackDataReader(helper.RunDataReader("sp_page"), () =>
{
return Convert.ToInt32(helper.GetOutParam("count_"));
});
return reader;
}
