private void btnTest_Click(object sender, EventArgs e)
{
GOQL <TblTestModel> q = OQL.From <TblTestModel>()
.Select(s => new object[] { s.ID, s.Title, s.ReadCount, s.CreateTime }).END;
dataGV1.DataSource = q.ToList();
dataGV1.Refresh();
}
void Test1()
{
Users user = new Users()
{
NickName = "pdf.net", RoleID = RoleNames.Admin
};
UserRoles roles = new UserRoles()
{
RoleName = "role1"
};
//测试字段直接比较
OQL q00 = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime))
.END;
Console.WriteLine("q00:one table and select all fields \r\n{0}", q00);
Console.WriteLine(q00.PrintParameterInfo());
OQL q0 = OQL.From(user)
.Select()
.Where(user.NickName, user.RoleID)
.OrderBy(user.ID)
.END;
q0.SelectStar = true;
Console.WriteLine("q0:one table and select all fields \r\n{0}", q0);
Console.WriteLine(q0.PrintParameterInfo());
OQL q01 = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.ID, OQLCompare.CompareType.Equal, 0))
.OrderBy(user.ID)
.END;
q01.SelectStar = true;
Console.WriteLine("q01:one table and select all fields \r\n{0}", q01);
Console.WriteLine(q01.PrintParameterInfo());
//var userList = EntityQuery<Users>.QueryList(q0);
//if (userList.Count > 0)
//{
// Users u = userList[0];
// Console.WriteLine("User Type is:" + u.RoleID.ToString());
// u.RoleID = RoleNames.User;
// EntityQuery<Users>.Instance.Update(u);
//}
OQL q = OQL.From(user)
.Select(user.ID, user.UserName, user.RoleID)
.END;
q.Select(user.LastLoginIP).Where(user.NickName);
Console.WriteLine("q1:one table and select some fields\r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
//动态指定查询的字段和比较关心、值
q = OQL.From(user).Select().Where(new QueryParameter[]
{
new QueryParameter("ID", "=", 1)
}
).END;
Console.WriteLine("q1:QueryParameter Test\r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
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());
OQL q3 = OQL.From(user, roles)
.Select(user.ID, user.UserName, roles.ID, roles.RoleName)
.Where(cmp => cmp.Comparer(user.RoleID, "=", roles.ID)
& cmp.EqualValue(roles.RoleName))
.OrderBy(user.ID)
.END;
Console.WriteLine("q3:two table query not use join\r\n{0}", q3);
Console.WriteLine(q3.PrintParameterInfo());
OQL q4 = OQL.From(user).InnerJoin(roles).On(user.RoleID, roles.ID)
.Select(user.RoleID).Count(user.RoleID, "roldid_count") //
.Where(user.NickName)
.GroupBy(user.RoleID)
.END;
Console.WriteLine("q4:count from two table query \r\n{0}", q4);
Console.WriteLine(q4.PrintParameterInfo());
OQL q5 = OQL.From(user)
.Select(user.RoleID).Count(user.RoleID, "count_rolid")
.GroupBy(user.RoleID)
.Having(p => p.Count(user.RoleID, OQLCompare.CompareType.GreaterThanOrEqual, 2))
.END;
Console.WriteLine("q5:having Test: \r\n{0}", q5);
Console.WriteLine(q5.PrintParameterInfo());
//q5 = OQL.From(user)
// .Select(user.Age).Sum(user.Age,"sum_age")
// .GroupBy(user.Age)
// .OrderBy(user.Age)
// .END;
//Console.WriteLine("q5:having Test: \r\n{0}", q5);
//Console.WriteLine(q5.PrintParameterInfo());
OQL q6 = OQL.From(user).Select()
.Where(cmp =>
cmp.Comparer(user.RoleID, "is not", null) &
cmp.Comparer(user.AddTime, ">=", DateTime.Now.AddDays(-1)) &
cmp.Comparer(user.AddTime, "<", DateTime.Now)
)
.END;
q6.SelectStar = true;
Console.WriteLine("q6:SQL 'IS' Test: \r\n{0}", q6);
Console.WriteLine(q6.PrintParameterInfo());
OQL q7 = OQL.From(user).Select()
.Where(cmp => cmp.Between(user.ID, 5, 10))
.END;
q7.SelectStar = true;
Console.WriteLine("q7:SQL Between Test: \r\n{0}", q7);
Console.WriteLine(q7.PrintParameterInfo());
//Compare 对象使用 ComparerSqlFunction 方法，解决SQL函数操作结果的类型跟字段类型不一致的问题
//感谢网友 【有事M我】发现此问题 2014.3.11
GOQL <Users> q8 = OQL.FromObject <Users>()
.Select()
.Where((cmp, u) => cmp.ComparerSqlFunction(u.NickName, ">", 0, "CHARINDEX( 'xiao',{0} )"))
.END;
string sql;
q8.Print(out sql);
Console.WriteLine("q8:SQL Function Test: \r\n{0}", sql);
}
