PDF.NET数据开发框架(Pwmis Data develop Framework,http://www.pwmis.com/sqlmap) 是一套借鉴iBatis、Hibernate、Linq等数据访问框架而来的轻量级数据开发框架,主要特点是具有iBatis特点的SQL-MAP功能和框架独特的实体对象查询语言--OQL,下面我们使用OQL来构造一个复杂的实体查询。
首先定义两个实体类:用户类和订单类,可以使用框架提供的实体类生成器生成,下面是详细代码:
本类由PWMIS 实体类生成工具(Ver 4.1)自动生成
http://www.pwmis.com/sqlmap
使用前请先在项目工程中引用 PWMIS.Core.dll
2010/12/8 15:01:22
*/
using System;
using PWMIS.Common;
using PWMIS.DataMap.Entity;
namespace LocalDB
{
/// <summary>
/// 用户表
/// </summary>
[Serializable()]
public partial class Table_User : EntityBase
{
public Table_User()
{
TableName = " Table_User " ;
EntityMap = EntityMapType.SqlMap;
// IdentityName = "标识字段名";
IdentityName = " UID " ;
// PrimaryKeys.Add("主键字段名");
PrimaryKeys.Add( " UID " );
PropertyNames = new string [] { " UID " , " Name " , " Sex " , " Height " , " Birthday " };
PropertyValues = new object [PropertyNames.Length];
}
/// <summary>
/// 用户标识
/// </summary>
public System.Int32 UID
{
get { return getProperty < System.Int32 > ( " UID " );}
set {setProperty( " UID " ,value );}
}
/// <summary>
/// 姓名
/// </summary>
public System.String Name
{
get { return getProperty < System.String > ( " Name " );}
set {setProperty( " Name " ,value , 50 );}
}
/// <summary>
/// 性别
/// </summary>
public System.Boolean Sex
{
get { return getProperty < System.Boolean > ( " Sex " );}
set {setProperty( " Sex " ,value );}
}
/// <summary>
/// 身高
/// </summary>
public System.Double Height
{
get { return getProperty < System.Double > ( " Height " );}
set {setProperty( " Height " ,value );}
}
/// <summary>
/// 出生日期
/// </summary>
public System.DateTime Birthday
{
get { return getProperty < System.DateTime > ( " Birthday " );}
set {setProperty( " Birthday " ,value );}
}
}
/// <summary>
/// 订单表
/// </summary>
[Serializable()]
public partial class Table_Order : EntityBase
{
public Table_Order()
{
TableName = " Table_Order " ;
EntityMap = EntityMapType.SqlMap;
// IdentityName = "标识字段名";
IdentityName = " OID " ;
// PrimaryKeys.Add("主键字段名");
PrimaryKeys.Add( " OID " );
PropertyNames = new string [] { " OID " , " UID " , " ProductName " , " BuyCount " , " OrderDate " };
PropertyValues = new object [PropertyNames.Length];
}
/// <summary>
/// 订单编号
/// </summary>
public System.Int32 OID
{
get { return getProperty < System.Int32 > ( " OID " ); }
set { setProperty( " OID " , value); }
}
/// <summary>
/// 用户号
/// </summary>
public System.Int32 UID
{
get { return getProperty < System.Int32 > ( " UID " ); }
set { setProperty( " UID " , value); }
}
/// <summary>
/// 产品名字
/// </summary>
public System.String Name
{
get { return getProperty < System.String > ( " ProductName " ); }
set { setProperty( " ProductName " , value, 50 ); }
}
/// <summary>
/// 购买数量
/// </summary>
public System.Int32 Count
{
get { return getProperty < System.Int32 > ( " BuyCount " ); }
set { setProperty( " BuyCount " , value); }
}
/// <summary>
/// 购买日期
/// </summary>
public System.DateTime OrderDate
{
get { return getProperty < System.DateTime > ( " OrderDate " ); }
set { setProperty( " OrderDate " , value); }
}
}
}
然后,我们来构造两个复杂的查询条件,直接上代码:
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PWMIS.DataMap.Entity;
namespace TestSqlMapEntity
{
class Program
{
static void Main( string [] args)
{
LocalDB.Table_User user = new LocalDB.Table_User();
List < OQLCompare > OrCmp1 = new List < OQLCompare > ();
OQLCompare cmp = new OQLCompare(user);
OrCmp1.Add(cmp.Comparer(user.UID, " = " , 1 ));
OrCmp1.Add(cmp.Comparer(user.UID, " = " , 2 ));
OrCmp1.Add(cmp.Comparer(user.UID, " = " , 3 ));
List < OQLCompare > OrCmp2 = new List < OQLCompare > ();
OrCmp2.Add(cmp.Comparer(user.Name, " = " , " aaa " ));
OrCmp2.Add(cmp.Comparer(user.Name, " = " , " bbb " ));
OQLCompare result = new OQLCompare(
cmp.Comparer(OrCmp1, OQLCompare.CompareLogic.OR),
OQLCompare.CompareLogic.AND,
cmp.Comparer(OrCmp2, OQLCompare.CompareLogic.OR));
OQL oql = OQL.From(user).Select().Where(result).END;
Console.WriteLine( " OQL To SQL is:\r\n " + oql.ToString());
/*
*
* OQL To SQL is:
SELECT UID,Name,Sex,Height,Birthday
FROM Table_User
Where ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 ) AND ( Name = @CP4 Or Na
me = @CP5 ) )
*/
oql.ReSet();
LocalDB.Table_Order order = new LocalDB.Table_Order();
OQL q_order = OQL.From(order).Select(order.UID ).Where(
new OQLCompare(order).Comparer(order.OrderDate, " >= " , DateTime.Now.AddDays( - 10 ))
).END ;
oql.Select().Where(oql.Condition.AND(user.Sex , " = " , true ).IN(user.UID, q_order));
Console.WriteLine( " OQL To SQL is:\r\n " + oql.ToString());
/*
*
OQL To SQL is:
SELECT UID,Name,Sex,Height,Birthday
FROM Table_User
Where Sex = @Sex0 AND UID IN (
SELECT UID
FROM Table_Order
Where OrderDate >= @CP1
)
*/
Console.Read();
}
}
}
程序中有两段代码,第一段代码是为了构造一个复杂的Where条件:
Where ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 ) AND ( Name = @CP4 Or Name = @CP5 ) )
意思是查询符合条件的多个UID并且查询符合条件的多个用户姓名,输出的完整SQL语句如下:
FROM Table_User
Where ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 ) AND ( Name = @CP4 Or Name = @CP5 ) )
第二段代码是为了以UID为外键,从订单表中查询哪些用户在10日内提交了订单的用户信息,程序声明了两个OQL对象:
OQL oql;//用户相关的OQL对象;
OQL q_order //订单相关的OQL对象;
关键点在于q_order 对象作为oql对象的 IN 查询的参数,实现了SQL的IN子查询:
oql.Select().Where(oql.Condition.AND(user.Sex ,"=",true).IN(user.UID, q_order));
该条查询输出的完整SQL语句如下:
FROM Table_User
Where Sex = @Sex0 AND UID IN (
SELECT UID
FROM Table_Order
Where OrderDate >= @CP1
)
OK,到此为止,我们可以使用我们的OQL查询出真正的实体集合了:
一行代码搞定,是不是很简单?
PDF.NET的宗旨就是为了最大化精简你的数据开发,有兴趣请看我的博客其它文章或者到官网:http://www.pwmis.com/sqlmap
本文转自深蓝医生博客园博客,原文链接:http://www.cnblogs.com/bluedoctor/archive/2011/02/24/1963606.html,如需转载请自行联系原作者