/// <summary> /// GROUP和是否编译判断 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="query"></param> /// <returns></returns> internal CallBackDataReader GetPageReader <TModel>(LambdaQuery <TModel> query) where TModel : IModel, new() { if (query.__GroupFields.Count > 0) { return(GetGroupPageReader(query)); } if (_DBAdapter.CanCompileSP && query.__CompileSp) { return(GetSpPageReader(query)); } CheckTableCreated <TModel>(); //var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[])); var fields = query.GetQueryFieldString(); var rowOver = query.__QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { var table = TypeCache.GetTable(typeof(TModel)); rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name); } var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t."); var condition = query.GetQueryConditions(); condition = _DBAdapter.SqlFormat(condition); query.FillParames(this); var pageIndex = query.__PageIndex; var pageSize = query.__PageSize; 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)); query.ExecuteTime += dbHelper.ExecuteTime; query.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); }); query.ExecuteTime += dbHelper.ExecuteTime; ClearParame(); return(reader); }
/// <summary> /// 按编译 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="query"></param> /// <returns></returns> CallBackDataReader GetSpGroupPageReader <TModel>(LambdaQuery <TModel> query) where TModel : IModel, new() { CheckTableCreated <TModel>(); var conditions = query.GetQueryConditions(); var fields = query.GetQueryFieldString(); if (!conditions.Contains("group")) { throw new Exception("缺少group语法"); } var rowOver = query.__QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { throw new Exception("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); query.FillParames(this); var pageIndex = query.__PageIndex; var pageSize = query.__PageSize; pageIndex = pageIndex == 0 ? 1 : pageIndex; pageSize = pageSize == 0 ? 15 : pageSize; AddParam("pageIndex", pageIndex); AddParam("pageSize", pageSize); dbHelper.AddOutParam("count", -1); Dictionary <string, string> dic = new Dictionary <string, string>(); dic.Add("fields", fields); dic.Add("rowOver", rowOver); //dic.Add("sort", sort1); string sp = CompileSqlToSp(_DBAdapter.TemplateGroupPage, conditions, dic); CallBackDataReader reader; reader = new CallBackDataReader(dbHelper.RunDataReader(sp), () => { return(GetOutParam <int>("count")); }); query.ExecuteTime += dbHelper.ExecuteTime; ClearParame(); return(reader); }
/// <summary> /// 按编译 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="query"></param> /// <returns></returns> CallBackDataReader GetSpPageReader <TModel>(LambdaQuery <TModel> query) where TModel : IModel, new() { CheckTableCreated <TModel>(); //var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[])); var fields = query.GetQueryFieldString(); var rowOver = query.__QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { var table = TypeCache.GetTable(typeof(TModel)); rowOver = string.Format("t1.{0} desc", table.PrimaryKey.Name); } var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t."); var condition = query.GetQueryConditions(); condition = _DBAdapter.SqlFormat(condition); query.FillParames(this); var pageIndex = query.__PageIndex; var pageSize = query.__PageSize; 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); //string sql = string.Format("{0} with(nolock) where {1}", tableName, where); string sp = CompileSqlToSp(_DBAdapter.TemplatePage, condition, dic); CallBackDataReader reader; reader = new CallBackDataReader(dbHelper.RunDataReader(sp), () => { return(GetOutParam <int>("count")); }); ClearParame(); query.ExecuteTime += dbHelper.ExecuteTime; return(reader); }
/// <summary> /// 按是否能编译 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="query"></param> /// <returns></returns> CallBackDataReader GetGroupPageReader <TModel>(LambdaQuery <TModel> query) where TModel : IModel, new() { if (_DBAdapter.CanCompileSP && query.__CompileSp) { return(GetSpGroupPageReader(query)); } CheckTableCreated <TModel>(); var condition = query.GetQueryConditions(); var fields = query.GetQueryFieldString(); if (!condition.Contains("group")) { throw new Exception("缺少group语法"); } var rowOver = query.__QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { throw new Exception("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); query.FillParames(this); var pageIndex = query.__PageIndex; var pageSize = query.__PageSize; 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)); query.ExecuteTime += dbHelper.ExecuteTime; query.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); }); query.ExecuteTime += dbHelper.ExecuteTime; ClearParame(); return(reader); }
/// <summary> /// 对GROUP进行分页 /// </summary> /// <param name="query">包含了group的查询</param> /// <param name="count"></param> /// <returns></returns> public List <dynamic> AutoSpGroupPage <T>(LambdaQuery <T> query, out int count) where T : IModel, new() { var conditions = query.GetQueryConditions(); var fields = query.GetQueryFieldString(); if (!conditions.Contains("group")) { throw new Exception("缺少group语法"); } var rowOver = query.QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { rowOver = "t1.id desc"; } var sort1 = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", ""); conditions = _DBAdapter.SqlFormat(conditions); count = 0; //var parame = query.QueryParames; //foreach (var n in parame) //{ // AddParam(n.Key, n.Value); //} query.FillParames(this); var pageIndex = query.PageIndex; var pageSize = query.PageSize; pageIndex = pageIndex == 0 ? 1 : pageIndex; pageSize = pageSize == 0 ? 15 : pageSize; AddParam("pageIndex", pageIndex); AddParam("pageSize", pageSize); helper.AddOutParam("count", 1); Dictionary <string, string> dic = new Dictionary <string, string>(); dic.Add("fields", fields); dic.Add("rowOver", rowOver); //dic.Add("sort", sort1); string sp = CompileSqlToSp(_DBAdapter.TemplateGroupPage, conditions, dic); System.Data.Common.DbDataReader reader; try { reader = helper.RunDataReader(sp); count = GetOutParam <int>("count"); } catch (Exception ero) { if (ero.Message.Contains("找不到存储过程"))//删除后自动创建 { spCahe.Remove(sp); sp = CompileSqlToSp(_DBAdapter.TemplateGroupPage, conditions, dic); reader = helper.RunDataReader(sp); count = GetOutParam <int>("count"); } else { throw ero; } } ClearParame(); return(Dynamic.DynamicObjConvert.DataReaderToDynamic(reader)); }
/// <summary> /// 对表进行分页并编译成存储过程 /// </summary> /// <typeparam name="TItem"></typeparam> /// <param name="query"></param> /// <param name="count"></param> /// <returns></returns> public List <TItem> AutoSpPage <TItem>(LambdaQuery <TItem> query, out int count) where TItem : IModel, new() { CheckTableCreated <TItem>(); var fields = query.GetQueryFieldString(b => b.Length > 500 || b.PropertyType == typeof(byte[])); var rowOver = query.QueryOrderBy; if (string.IsNullOrEmpty(rowOver)) { rowOver = "t1.id desc"; } var orderBy = System.Text.RegularExpressions.Regex.Replace(rowOver, @"t\d\.", "t."); var condition = query.GetQueryConditions(); //var parame = query.QueryParames; condition = _DBAdapter.SqlFormat(condition); count = 0; query.FillParames(this); //foreach (var n in parame) //{ // AddParam(n.Key, n.Value); //} var pageIndex = query.PageIndex; var pageSize = query.PageSize; 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); //string sql = string.Format("{0} with(nolock) where {1}", tableName, where); string sp = CompileSqlToSp(_DBAdapter.TemplatePage, condition, dic); CallBackDataReader reader; try { reader = new CallBackDataReader(helper.RunDataReader(sp), () => { return(GetOutParam <int>("count")); }); //count = GetOutParam<int>("count"); } catch (Exception ero) { if (ero.Message.Contains("找不到存储过程"))//删除后自动创建 { spCahe.Remove(sp); sp = CompileSqlToSp(_DBAdapter.TemplatePage, condition, dic); reader = new CallBackDataReader(helper.RunDataReader(sp), () => { return(GetOutParam <int>("count")); }); } else { throw ero; } } var list = reader.GetData <TItem>(out count, query.FieldMapping); query.RowCount = count; ClearParame(); //return ObjectConvert.DataReaderToList<TItem>(reader); return(list); }