/// <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 词法分析 自动分页语句构造测试 结束----");
}
