/// <summary> /// 有参数赋值列表的分页 /// </summary> /// <typeparam name="TEnty">实体</typeparam> /// <param name="tablename">表名</param> /// <param name="keyfield"></param> /// <param name="pageindex">分页索引</param> /// <param name="pagesize">分页大小</param> /// <param name="strwhere"></param> /// <param name="wList">参数列表</param> /// <param name="strorder"></param> /// <param name="sqlCol"></param> /// <param name="fieldlist">跟实体相关的字段</param> /// <param name="total">总数</param> /// <returns>返回值</returns> protected IList <TEnty> GetListByPage <TEnty>(string tablename, string keyfield, int pageindex, int pagesize, string strwhere, IList <Pter> wList, string strorder, string sqlCol, string fieldlist, out int total) where TEnty : new() { int _total = 0; string sqlText = SQLPage.PageSqlText(tablename, sqlCol, keyfield, strwhere, strorder); DbCommand dbCommand = db.GetSqlStringCommand(sqlText); db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, pageindex); db.AddInParameter(dbCommand, "PageSize", DbType.Int32, pagesize); db.AddOutParameter(dbCommand, "RecordCount", DbType.Int64, 1); foreach (Pter pter in wList) { base.Pter(db, dbCommand, pter.Name, pter.Value); } IList <TEnty> infoList = new List <TEnty>(); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { Dictionary <string, string> cachUserNameMaping = new Dictionary <string, string>(); while (dataReader.Read()) { TEnty info = new TEnty(); GetEnty <TEnty>(dataReader, fieldlist.Replace("[", "").Replace("]", "").Replace("\r\n", ""), ref info, cachUserNameMaping); infoList.Add(info); } } object obj = db.GetParameterValue(dbCommand, "@RecordCount"); _total = int.Parse(obj.ToString()); total = _total; return(infoList); }
protected void Page_Load(object sender, EventArgs e) { DataView DataFarm = (DataView)SQLName.Select(DataSourceSelectArguments.Empty); foreach (DataRowView DataFarmSQL in DataFarm) { lblfarm.Text = DataFarmSQL["Farm"].ToString(); } DataView DataDate = (DataView)SQLDate.Select(DataSourceSelectArguments.Empty); foreach (DataRowView DataFarmSQL in DataDate) { lblDate.Text = DataFarmSQL["Date"].ToString(); } DataView DataAssessor = (DataView)SQLAssessor.Select(DataSourceSelectArguments.Empty); foreach (DataRowView DataFarmSQL in DataAssessor) { lblAssessor.Text = DataFarmSQL["Assessor"].ToString(); } DataView DataPage = (DataView)SQLPage.Select(DataSourceSelectArguments.Empty); foreach (DataRowView DataFarmSQL in DataPage) { lblPage.Text = DataFarmSQL["Page"].ToString(); } }
void TestOqlPage() { UserEntity ue = new UserEntity(); OQL q = OQL.From(ue) .Select(ue.ID, ue.Name, ue.Age) .Where(cmp => cmp.Comparer(ue.Age, ">", 20)) .OrderBy(ue.Age) .END; q.Limit(2, 3, true); Console.WriteLine("q:Page SQL is \r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); //当前测试总记录数5,查询后,OQL会得到总记录数 AdoHelper db = MyDB.GetDBHelperByConnectionName("conn2"); var list = EntityQuery <UserEntity> .QueryList(q, db); Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin, Age = 20 }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select(user.ID, user.NickName, user.LastLoginIP) .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .OrderBy(o => o.Desc(user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); OQL q2 = OQL.From(user) .InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID, roles.RoleName) .Where(user.NickName, roles.RoleName) .GroupBy(user.RoleID, roles.RoleName) .OrderBy(user.ID) .END; Console.WriteLine("q2:two table query use join\r\n{0}", q2); Console.WriteLine(q2.PrintParameterInfo()); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); }
public SQLPage <VUser> GetUser() { var user = new SQLPage <User>() { Count = -1, IsNext = false, Result = _user.Query() }; return(ConverToVUser(user)); }
void TestOqlPage() { Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select(user.ID, user.NickName, user.LastLoginIP) .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .OrderBy(o => o.Desc(user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); OQL q2 = OQL.From(user) .InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID, roles.RoleName) .Where(user.NickName, roles.RoleName) .GroupBy(user.RoleID, roles.RoleName) .OrderBy(user.ID) .END; Console.WriteLine("q2:two table query use join\r\n{0}", q2); Console.WriteLine(q2.PrintParameterInfo()); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); }
public SQLPage <VUser> ConverToVUser(SQLPage <User> user) { var vUser = new SQLPage <VUser>(); vUser.Count = user.Count; vUser.IsNext = user.IsNext; vUser.Pagination = user.Pagination; var dept = _departmentServices.GetDepartmentById(user.Result.Select(t => t.Department).Distinct().ToArray()); vUser.Result = user.Result.Select(t => { var value = new VUser(); if (t.Department == -1) { value.User = t; } else { SetVUser(value, t, dept); } return(value); }); return(vUser); }
void TestSqlPage() { Console.WriteLine("----SQL 词法分析 自动分页语句构造测试 开始----"); //分页,需要在 select 子句中包含排序的字段,然后排序使用字段别名 string sql = @" SELECT M.[ID], M.[NickName], T0.[ID] AS [T0_ID], T0.[Description] AS [T0_Description] FROM [LT_Users] M INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] WHERE T0.[ID] = 1 ORDER BY T0.[RoleName] ASC "; //如果上面的SQL语句升序排序,每页10行,取第二页,那么应该生成下面的分页语句 /* * SELECT Top 10 * FROM( * SELECT Top 10 * FROM ( * SELECT top 20 * M.[ID], * M.[NickName], * T0.[ID] AS [T0_ID], * T0.[Description] AS [T0_Description], * T0.[RoleName] * FROM [LT_Users] M * INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] * WHERE T0.[ID] = 1 * ORDER BY T0.[RoleName] ASC * ) P_T0 * ORDER BY [RoleName] DESC * ) P_T1 * ORDER BY [RoleName] ASC * * 注意内部的分页语句,应该附加上排序的字段 T0.[RoleName] 才可以供外部的排序条件使用, * 所以OQL处理的时候应该考虑这个问题,比如下面的查询: * SELECT Top 10 * FROM( * SELECT Top 10 * FROM ( * SELECT top 20 * M.[ID], * M.[NickName], * T0.[ID] AS [T0_ID], * T0.[Description] AS [T0_Description], * T0.[RoleName] AS [T0_RoleName] * FROM [LT_Users] M * INNER JOIN [LT_UserRoles] T0 ON M.[RoleID] = T0.[ID] * WHERE T0.[ID] = 1 * ORDER BY T0.[RoleName] ASC * ) P_T0 * ORDER BY [T0_RoleName] DESC * ) P_T1 * ORDER BY [T0_RoleName] ASC * * 跟上面的查询的区别, T0.[RoleName] AS [T0_RoleName] 这里对参与排序字段使用了别名 */ string pageSql = string.Empty; pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999); Console.WriteLine("SQL:"); Console.WriteLine(sql); Console.WriteLine("Page SQL:"); Console.WriteLine(pageSql); //其它SQL查询 sql = "select ID,[User Name],Age from User where Age>20 order by Age desc "; pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999); Console.WriteLine("SQL:"); Console.WriteLine(sql); Console.WriteLine("Page SQL:"); Console.WriteLine(pageSql); //下面的查询会把排序字段 Age 附加到select字段里面 sql = "select ID,[User Name] from User where Age>20 order by Age desc "; pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 999); Console.WriteLine("SQL:"); Console.WriteLine(sql); Console.WriteLine("Page SQL:"); Console.WriteLine(pageSql); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, sql, "", 10, 2, 0); Console.WriteLine("Count SQL:"); Console.WriteLine(pageSql); Console.WriteLine("----SQL 词法分析 自动分页语句构造测试 结束----"); }