/// <summary>
/// 获取指定商品名称下面的商品信息
/// </summary>
/// <param name="goodsName">商品名称</param>
/// <returns></returns>
public List <GoodsBaseInfoVM> GetGoodsBaseInfoWhithGoodsName(string goodsName)
{
GoodsBaseInfo info = new GoodsBaseInfo();
info.GoodsName = goodsName;
OQL q = new OQL(info);
q.Select(info.SerialNumber, info.GoodsName, info.Manufacturer, info.CanUserMonth)
.Where(info.GoodsName)
.OrderBy(info.GoodsName, "asc");
return(GetGoodsBaseInfoList(q));
}
/// <summary>
/// 获取指定页的联系人信息
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <param name="allCount"></param>
/// <returns></returns>
public List <CustomerContactInfo> GetContactInfoList(int pageSize, int pageNumber, int allCount)
{
CustomerContactInfo info = new CustomerContactInfo();
OQL q = new OQL(info);
q.Select().OrderBy(info.CustomerName, "asc");
q.Limit(pageSize, pageNumber);
q.PageWithAllRecordCount = allCount;
//return EntityQuery<CustomerContactInfo>.QueryList(q);
return(q.ToList <CustomerContactInfo>());
}
/// <summary>
/// 获取所有的可售商品总数
/// </summary>
/// <returns></returns>
public int GetGoodsStockCount()
{
GoodsStock stock = new GoodsStock();
OQL q = new OQL(stock);
q.Select()
.Count(stock.Stocks, "库存数量")
.Where(q.Condition.AND(stock.Stocks, ">", 0));
stock = EntityQuery <GoodsStock> .QueryObject(q);
return(stock.Stocks);
}
public static List <UserModels> GetUser()
{
try
{
UserModels model = new UserModels();
OQL q = new OQL(model);
return(EntityQuery <UserModels> .QueryList(q.Select().END));
}
catch (Exception ex)
{
throw ex;
}
}
public static List <V_UserModels> GetAllList(int pageSzie, int pageNum)
{
V_UserModels model = new V_UserModels();
OQL q = new OQL(model);
q.PageEnable = true;
q.PageNumber = pageNum;
q.PageSize = pageSzie;
q.PageWithAllRecordCount = GetCount();
List <V_UserModels> list = EntityQuery <V_UserModels> .QueryList(q.Select().END);
return(list);
}
/// <summary>
/// 获取商品名称分组
/// </summary>
/// <returns></returns>
public List <string> GetAllGoodsNames()
{
GoodsBaseInfo info = new GoodsBaseInfo();
OQL q = new OQL(info);
q.Select(info.GoodsName)
.GroupBy(info.GoodsName)
.OrderBy(info.GoodsName, "asc");
List <GoodsBaseInfo> list = EntityQuery <GoodsBaseInfo> .QueryList(q);
var result = list.ConvertAll <string>(p => p.GoodsName);
return(result);
}
void TestOQL()
{
//假如GoodsBaseInfo 对象的实例info 是长生命周期的，那么应该使用下面的方式使用OQL和OQLCompare
GoodsBaseInfo info = new GoodsBaseInfo();
using (OQL q = new OQL(info))
{
OQLCompare cmp = new OQLCompare(info);
q.Select(info.GoodsName).Where(cmp.Comparer(info.SerialNumber, "=", "123456"));
string sql = q.ToString();
cmp.Dispose();
}
DisplayMemory();
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <param name="cp">where条件，没有可传人null</param>
/// <param name="orders">排序字段对象</param>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="pageIndex">当前页</param>
/// <returns></returns>
public static List <T> GetList <T>(OQLCompareFunc <T> cp, OQLOrder orders, int pageSize, int pageIndex) where T : EntityBase, ILongID, new()
{
T m = new T();
OQL q = new OQL(m);
if ((object)cp != null)
{
if ((object)orders != null)
{
q.Select().Where(cp).OrderBy(orders);
}
else
{
q.Select().Where(cp);
}
}
else
{
if ((object)orders != null)
{
q.Select().OrderBy(orders);
}
else
{
q.Select();
}
}
q.PageWithAllRecordCount = GetRecordCounts <T>(cp);
if (pageSize > 0 && pageIndex > 0 && q.PageWithAllRecordCount > 0)
{
q.Limit(pageSize, pageIndex);
}
string s = q.PrintParameterInfo();
return(EntityQuery <T> .QueryList(q));
}
//该方法已经废弃，合并在 GetOverDays 中
private int GetStockCount(int goodsID)
{
GoodsStock stock = new GoodsStock();
stock.GoodsID = goodsID;
OQL q = new OQL(stock);
q.Select(stock.Stocks)
.Where(stock.GoodsID);
stock = EntityQuery <GoodsStock> .QueryObject(q);
int stockCount = stock.Stocks;
return(stockCount);
}
//测试OQL的统计功能
private int GetStockCount(string serialNumber)
{
SuperMarketDAL.Entitys.GoodsStock stock = new SuperMarketDAL.Entitys.GoodsStock();
stock.SerialNumber = serialNumber;
OQL q = new OQL(stock);
q.Select()
.Count(stock.Stocks, "")
.Where(stock.SerialNumber);
stock = EntityQuery <SuperMarketDAL.Entitys.GoodsStock> .QueryObject(q);
int stockCount = stock.Stocks;
return(stockCount);
}
public static List <UserModels> GetUser(int page, int num)
{
try
{
UserModels model = new UserModels();
OQL q = new OQL(model);
q.PageEnable = true;
q.PageNumber = num;
q.PageSize = page;
q.PageWithAllRecordCount = UserModels.GetCount();
return(EntityQuery <UserModels> .QueryList(q.Select().END));
}
catch (Exception ex)
{
throw ex;
}
}
static void OqlInTest()
{
LT_UserRoles roles = new LT_UserRoles()
{
NickName = "Role1"
};
LT_Users users = new LT_Users();
OQL qRole = OQL.From(roles).Select(roles.ID).Where(
cmp => cmp.Comparer(roles.NickName, "like", "123%")
).END;
OQL qUser = new OQL(users);
qUser.Select().Where(qUser.Condition
.AND(users.LastLoginTime, ">=", DateTime.Now.AddDays(-10))
.NotIn(users.RoleID, qRole));
Console.WriteLine("OQL to SQL:\r\n{0},\r\n{1}", qUser, qUser.PrintParameterInfo());
}
static void TestInChild()
{
p_hege_detail detailObj = new p_hege_detail();
p_hege phegeObj = new p_hege();
OQL phegeOq = new OQL(phegeObj);
phegeOq.Select(phegeObj.id).OrderBy(phegeObj.id, "DESC");
phegeOq.TopCount = 1;
OQL detailOq = new OQL(detailObj);
detailOq.Select(detailObj.coName, detailObj.coType, detailObj.coMessage)
.Where(cmp => cmp.Comparer(detailObj.coType, "=", "Status") & cmp.Comparer(detailObj.hegeID, "in", phegeOq)) //
.OrderBy(detailObj.id, "DESC");
Console.WriteLine("SQL:\r\n{0}\r\n,{1}", detailOq.ToString(), detailOq.PrintParameterInfo());
}
/// <summary>
/// 获取用户权限菜单
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <MenuViewModel> GetMenuListByAccountId(long accountId, long menuType)
{
AdminRole role = new AdminRole();
OQL qRole = OQL.From(role)
.Select()
.OrderBy(role.ID, "asc")
.END;
StringBuilder sbMenuIds = new StringBuilder();
foreach (AdminRole item in qRole.ToList <AdminRole>())
{
sbMenuIds.Append(item.MenuIds);
}
JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>();
AdminMenu model = new AdminMenu();
OQL q = new OQL(model);
q.Select()
.Where(q.Condition.AND(model.MenuType, "=", menuType))
.OrderBy(model.ID, "asc");
List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展
return(list.ConvertAll <MenuViewModel>(o =>
{
return new MenuViewModel()
{
ID = o.ID,
ParentID = o.ParentID,
MenuKey = o.MenuKey,
MenuName = o.MenuName,
MenuUrl = o.MenuUrl,
MenuType = o.MenuType,
IDPath = o.IDPath,
Remark = o.Remark,
Sort = o.Sort,
Status = o.Status,
CreateTIme = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
};
}
));
}
/// <summary>
/// 简单查询
/// </summary>
/// <returns></returns>
public List <SODUser> getRegionList(string logName)
{
var outlist = new List <SODUser>();
try
{
SODUser model = new SODUser()
{
LogName = logName
};
OQL q = new OQL(model);
q.Select().Where(model.LogName);
outlist = new EntityQuery <SODUser>(CurrentDataBase).GetList(q);
}
catch (Exception ex)
{
throw ex;
}
return(outlist);
}
public static void 分页查询()
{
SODUser user = new SODUser();
OQL q = new OQL(user);
OQLCompareFunc resultFunc = cmp =>
{
OQLCompare resultCmp = cmp.Comparer(user.ID, ">", 0);
resultCmp = resultCmp & cmp.Comparer(user.LogName, "like", "%zhang%");
return(resultCmp);
};
q.Select().Where(resultFunc).OrderBy(user.ID);
//分页
q.PageEnable = true;
q.PageWithAllRecordCount = 10;
q.Limit(10, 1, true);
var outlist = EntityQuery <SODUser> .QueryList(q);
int pageCount = q.PageWithAllRecordCount;
}
private void btnChange_Click(object sender, EventArgs e)
{
TblTestModel model = new TblTestModel();
model.Title = "测试标题";
model.ArtContent = "测试内容";
model.CreateTime = DateTime.Now;
EntityQuery <TblTestModel> .Instance.Insert(model);
OQL q = new OQL(model);
q.Select().OrderBy(model.CreateTime, "desc");
model = EntityQuery <TblTestModel> .QueryList(q).ToList <TblTestModel>()[0];
model.Title = "已修改" + model.ID.ToString();
model.CreateTime = DateTime.Now;
EntityQuery <TblTestModel> .Instance.Update(model);
dataGV1.DataSource = EntityQuery <TblTestModel> .QueryList(q).ToList <TblTestModel>();
dataGV1.Refresh();
}
private static void TestTransaction()
{
AdoHelper db = MyDB.GetDBHelper();
EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db);
AuctionOperationLog optLog = new AuctionOperationLog();
optLog.OperaterID = 1000;
optLog.Module = "Login";
optLog.Operation = "登录成功1";
optLog.LogSource = "PC";
db.BeginTransaction();
try
{
query.Insert(optLog);
//必须设置为全部属性已经修改，否则仅会更新 Operation 字段
optLog.ResetChanges(true);
optLog.Operation = "退出登录";
query.Insert(optLog);
//optLog.Module = "Login";
//OQL q = OQL.From(optLog).Select().Where(optLog.Module).END;
OQL q = new OQL(optLog);
//q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%"));
q.Select().Count(optLog.OperaterID, "");//使用空字符串参数，这样统计的值将放到 OperaterID 属性中
//必须指定db参数，否则不再一个事务中，无法进行统计查询
optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db);
int allCount = optLog.OperaterID;
//optLog 已经使用过，在生成OQL的查询前，必须使用新的实体对象，
// 否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错
optLog = new AuctionOperationLog();
q = new OQL(optLog);
q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc");
q.Limit(10, 2);
q.PageEnable = true;
q.PageWithAllRecordCount = allCount;
//查询列表并更新到数据库
List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db);
foreach (AuctionOperationLog logItem in list)
{
logItem.AtDateTime = DateTime.Now;
}
query.Update(list);
db.Commit();
Console.WriteLine("事务操作成功。");
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message);
db.Rollback();
}
}
static void Main(string[] args)
{
Console.WriteLine("====**************** PDF.NET SOD 控制台测试程序 **************====");
Assembly coreAss = Assembly.GetAssembly(typeof(AdoHelper));//获得引用程序集
Console.WriteLine("框架核心程序集 PWMIS.Core Version:{0}", coreAss.GetName().Version.ToString());
Console.WriteLine();
Console.WriteLine(" 应用程序配置文件默认的数据库配置信息：\r\n 当前使用的数据库类型是：{0}\r\n 连接字符串为:{1}\r\n 请确保数据库服务器和数据库是否有效且已经初始化过建表脚本（项目下的2个sql脚本文件），\r\n继续请回车，退出请输入字母 Q ."
, MyDB.Instance.CurrentDBMSType.ToString(), MyDB.Instance.ConnectionString);
Console.WriteLine("=====Power by Bluedoctor,2015.2.8 http://www.pwmis.com/sqlmap ====");
string read = Console.ReadLine();
if (read.ToUpper() == "Q")
{
return;
}
//写入10000条日志，有缓存，可能不会写完
Console.WriteLine("测试日志写入10000 条信息...");
CommandLog loger = new CommandLog();
for (int t = 0; t <= 100; t++)
{
System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(WriteLog));
thread.Name = "thread" + t;
thread.Start(loger);
}
loger.Flush();
Console.WriteLine("按任意键继续");
Console.ReadLine();
EntityUser etu = new EntityUser();
ITable_User itu = etu.AsEntity();
DateTime dtt = itu.Birthday;
//测试 AdoHelper的并发能力
//for (int i = 0; i < 100; i++)
//{
// System.Threading.Thread t = new System.Threading.Thread(
// new System.Threading.ParameterizedThreadStart(TestDataSetAndOQL));
// t.Name = "thread "+i;
// t.Start();
//}
//测试生成列的脚本
EntityCommand ecmd = new EntityCommand(new Table_User(), new SqlServer());
string table_script = ecmd.CreateTableCommand;
Console.Write("1，测试 OpenSession 长连接数据库访问...");
TestDataSetAndOQL(null);
Console.WriteLine("OK");
//
Console.WriteLine("2，测试OQL 转SQL...");
RoadTeam.Model.CS.TbCsEvent CsEvent = new RoadTeam.Model.CS.TbCsEvent();
CsEvent.EventID = 1;
OQL oql = OQL.From(CsEvent)
.Select(CsEvent.EventCheck, CsEvent.EventCheckInfo, CsEvent.EventCheckor, CsEvent.EventCheckTime)
.Where(CsEvent.EventID)
.END;
Console.WriteLine(oql.ToString());
Console.WriteLine("-----------------------");
RoadTeam.Model.CS.TbCsEvent CsEvent2 = new RoadTeam.Model.CS.TbCsEvent();
CsEvent.EventID = 1;
OQL oql2 = OQL.From(CsEvent2)
.Select(true, CsEvent2.EventCheck, CsEvent2.EventCheckInfo, CsEvent2.EventCheckor, CsEvent2.EventCheckTime)
.Where(CsEvent2.EventID)
.END;
Console.WriteLine(oql2.ToString());
Console.WriteLine("-----------------------");
Console.WriteLine("OK");
//
Console.Write("3，测试实体类动态增加虚拟属性...");
UserModels um1 = new UserModels();
um1.AddPropertyName("TestName");
um1["TestName"] = 123;
int testi = (int)um1["TestName"];
um1["TestName"] = "abc";
string teststr = (string)um1["TestName"];
Console.WriteLine("OK");
//
Console.Write("4，测试缓存...");
var cache = PWMIS.Core.MemoryCache <EntityBase> .Default;
cache.Add("UserModels", um1);
var cacheData = cache.Get("UserModels");
cache.Remove("UserModels");
Console.WriteLine("OK");
//
Console.Write("5，测试自动创建实体类数据库表...");
AutoCreateEntityTable <LT_Users>();
AutoCreateEntityTable <LT_UserRoles>();
Console.WriteLine("OK");
Console.WriteLine("------------测试暂时停止，回车继续运行------");
Console.ReadLine();
//return;
Console.Write("6，测试实体类的外键查询...");
TestEntityFK();
Console.WriteLine("OK");
Console.Write("7，测试实体类批量插入...");
OqlInTest();
Console.WriteLine("OK");
Console.WriteLine("8，测试SOD POCO实体类性能...");
Console.WriteLine("SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User");
for (int i = 0; i < 10; i++)
{
Console.WriteLine("-------------Testt No.{0}----------------", i + 1);
TestPocoQuery();
}
Console.WriteLine("--------OK---------------");
Console.Write("9，测试OQL IN 子查询...");
TestInChild();
Console.WriteLine("OK");
//TestFun(1, 2, 3);
Console.WriteLine("10，测试泛型 OQL --GOQL");
TestGOQL();
Console.WriteLine("OK");
Console.WriteLine("11，测试OQL 批量更新（带条件更新）...");
UpdateTest();
Console.WriteLine("OK");
Console.WriteLine("12，测试批量数据插入性能....");
//InsertTest();
Console.WriteLine("13，OQL 自连接...");
OqlJoinTest();
//
Console.Write("14，根据接口类型，自动创建实体类测试...");
TestDynamicEntity();
Console.WriteLine("OK");
//
Console.WriteLine("15，Sql 格式化查询测试( SOD 微型ORM功能)...");
AdoHelper dbLocal = new SqlServer();
dbLocal.ConnectionString = MyDB.Instance.ConnectionString;
//DataSet ds = dbLocal.ExecuteDataSet("SELECT * FROM Table_User WHERE UID={0} AND Height>={1:5.2}", 1, 1.80M);
/*
* 下面的写法过时
* var dataList = dbLocal.GetList(reader =>
* {
* return new
* {
* UID=reader.GetInt32(0),
* Name=reader.GetString(1)
* };
* }, "SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={0:5.2}",1, 1.60);
*/
var dataList = dbLocal.ExecuteMapper("SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={1:5.2}", 1, 1.60)
.MapToList(reader => new
{
UID = reader.GetInt32(0),
Name = reader.GetString(1)
});
Console.WriteLine("OK");
//
Console.Write("16，测试属性拷贝...");
V_UserModels vum = new V_UserModels();
vum.BIGTEAM_ID = 123;//可空属性，如果目标对象不是的话，无法拷贝
vum.REGION_ID = 456;
vum.SMALLTEAM_ID = 789;
UserModels um = vum.CopyTo <UserModels>();
Console.WriteLine("OK");
//
Console.Write("17，测试【自定义查询】的实体类...");
UserPropertyView up = new UserPropertyView();
OQL q11 = new OQL(up);
OQLOrder order = new OQLOrder(q11);
q11.Select()
.Where(q11.Condition.AND(up.PropertyName, "=", "总成绩").AND(up.PropertyValue, ">", 1000))
.OrderBy(order.Asc(up.UID));
AdoHelper db11 = MyDB.GetDBHelperByConnectionName("local");
var result = EntityQuery <UserPropertyView> .QueryList(q11, db11);
//下面2行不是必须
q11.Dispose();
Console.WriteLine("OK");
//EntityContainer ec = new EntityContainer(q11);
//var ecResult = ec.MapToList(() => {
// return new { AAA = ec.GetItemValue<int>(0), BBB = ec.GetItemValue<string>(1) };
//});
/////////////////////////////////////////////////////
Console.WriteLine("18，测试实体类【自动保存】数据...");
TestAutoSave();
/////////////////测试事务////////////////////////////////////
Console.WriteLine("19，测试测试事务...");
TestTransaction();
TestTransaction2();
Console.WriteLine("事务测试完成！");
Console.WriteLine("-------PDF.NET SOD 测试全部完成-------");
Console.ReadLine();
}
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);
}
public GOQL1 <T> Select()
{
return(new GOQL1 <T>(this, currentOQL.Select()));
}
public bool HasPassword(string identity, string @namespace)
{
var entity = new UserEntity();
var oql = new OQL(entity);
var comparer = new Comparer(oql);
var dataAccess = this.EnsureService<IDataAccess>();
comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity);
comparer = comparer & comparer.IsNotNull(entity.Password);
oql.Select(entity.UserId)
.Where(comparer)
.OrderBy(p => p.Desc(entity.UserId))
.End();
return dataAccess.Exists(oql);
}
public bool Exists(string identity, string @namespace)
{
if(string.IsNullOrWhiteSpace(identity))
return false;
var entity = new UserEntity();
var oql = new OQL(entity);
var comparer = new Comparer(oql);
var dataAccess = this.EnsureService<IDataAccess>();
Utility.UserIdentityType identityType;
comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity, out identityType);
// 确保用户名是审核通过的
if(identityType == Utility.UserIdentityType.Name)
this.Censor(identity);
oql.Select(entity.UserId)
.Where(comparer)
.OrderBy(p => p.Desc(entity.UserId))
.End();
return dataAccess.Exists(oql);
}
/// <summary>
/// 获取用户权限菜单
/// </summary>
/// <param name="accountId"></param>
/// <param name="menuType"></param>
/// <returns></returns>
public List <MenuViewModel> GetMenuListByAccountId(long accountId, long menuType)
{
AdminRole role = new AdminRole();
AdminAccountRole ar = new AdminAccountRole();
ar.AccountID = accountId;
OQL joinQ = OQL.From(role)
.Join(ar).On(role.ID, ar.RoleID)
.Select(role.MenuIds)
.Where(ar.AccountID)
.OrderBy(role.Sort, "desc")
.END;
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
List <AdminRole> roleList = (List <AdminRole>)ec.MapToList <AdminRole>(() => new AdminRole()
{
MenuIds = role.MenuIds,
});
StringBuilder sbMenuIds = new StringBuilder();
foreach (AdminRole item in roleList)
{
sbMenuIds.Append(item.MenuIds);
}
//long[] roleArr = Array.ConvertAll(sbMenuIds.ToString().Split(','), long.Parse);
JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>();
AdminMenu model = new AdminMenu();
OQL q = new OQL(model);
if (menuType > 0)
{
q.Select()
.Where(q.Condition.AND(model.MenuType, "=", menuType).AND(model.Status, ">", -1))
.OrderBy(model.Sort, "desc");
}
else
{
q.Select()
.Where(q.Condition.AND(model.Status, ">", -1))
.OrderBy(model.Sort, "desc");
}
List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展
return(list.ConvertAll <MenuViewModel>(o =>
{
return new MenuViewModel()
{
ID = o.ID,
ParentID = o.ParentID,
MenuKey = o.MenuKey,
MenuName = o.MenuName,
MenuUrl = o.MenuUrl,
MenuType = o.MenuType,
Sort = o.Sort,
Status = o.Status,
CreateTime = o.CreateTime,
};
}
));
}
public bool HasPassword(int userId)
{
var entity = new UserEntity();
var oql = new OQL(entity);
var dataAccess = this.EnsureService<IDataAccess>();
oql.Select(entity.UserId)
.Where(oql.Condition.And(entity.Password, "IS", "NOT NULL"))
.OrderBy(p => p.Desc(entity.UserId))
.End();
return dataAccess.Exists(oql);
}
public User GetUser(string identity, string @namespace)
{
var entity = new UserEntity();
var oql = new OQL(entity);
var comparer = new Comparer(oql);
var dataAccess = this.EnsureService<IDataAccess>();
comparer = Utility.GetUserIdentityComparer(identity, @namespace, comparer, entity);
entity = dataAccess.Find<UserEntity>(oql.Select().Where(comparer).End());
return Mapper.Map<UserEntity, User>(entity);
}
public static bool GetUserId(IDataAccess dataAccess, string identity, string @namespace, out int userId)
{
if(dataAccess == null)
throw new ArgumentNullException("dataAccess");
if(string.IsNullOrWhiteSpace(identity))
throw new ArgumentNullException("identity");
var entity = new UserEntity();
var oql = new OQL(entity);
var comparer = new Comparer(oql);
comparer = GetUserIdentityComparer(identity, @namespace, comparer, entity);
oql = oql.Select(entity.UserId)
.Where(comparer)
.End();
entity = dataAccess.Find<UserEntity>(oql);
var result = entity != null && entity.UserId > 0;
userId = 0;
if(result)
userId = entity.UserId;
return result;
}