LiteORM学习三：查询设计

LiteORM学习三：查询设计

查询
更有趣的部分是查询接口。这是非常简陋，但很有效。有一个工厂方法，查询（）返回一个IQuery对象。

这个对象可以帮助我们定义了SELECT语句的WHERE子句。注意，当我们到一个限制值列，我们不使用列的名称。

记住，ColumnAttribute别名属性？这是它派上用场。我们指定列的别名，这将是解决实际的列名。

这样做的好处是我们可以改变，而不需要修改任何查询的数据库列的名称。一个例子应该能够更容易理解。语法是非常简陋，但也很相似，实际的SQL语句，所以它应该是相当简单的把握。

using lite;

static void Main(string [] args)
{
IDb db = DbFactory.Instance.GetDb();

// select * from dbo.purchase where id = 1
IQuery q = db.Query();
// note that we are not using the "purchase_id" to reference the column
// we are using "id" which is the alias for [ purchase_id ] column (see above)
q.Constrain("id").Equal( 1 );
IList list = db. Select (typeof(Purchase), q);
if (list. Count > 0 )
{
Purchase p = (Purchase) list [ 0 ] ;
...
}

// select * from dbo.purchase where customer = 1
IQuery q1 = db.Query();
q1.Constrain("customer").Equal( 1 );
list = db. Select (typeof(Purchase), q1);

// select * from dbo.purchase where customer = 1 and product = 2
IQuery q2 = db.Query();
q2.Constrain("customer").Equal( 1 ). And ()
.Constrain("product").Equal( 2 );
list = db. Select (typeof(Purchase), q2);

// select * from dbo.purchase where
// quantity <= 10 and (customer = 1 or product = 2 )
IQuery q3 = db.Query().Constrain("customer").Equal( 1 ). Or ()
.Constrain("product").Equal( 2 );
IQuery q4 = db.Query().Constrain("quantity").LessEqual( 10 ). And ()
.Constrain(q3);
list = db. Select (typeof(Purchase), q4);

// select * from dbo.purchase where (customer = 1 and product = 2 )
// or (quantity > 5 and purch_date >= dateadd ( day , - 10 , getdate ()))
IQuery q5 = db.Query().Constrain("customer").Equal( 1 ). And ()
.Constrain("product").Equal( 2 );
IQuery q6 = db.Query().Constrain("quantity").Greater( 5 ). And ()
.Constrain("date").GreaterEqual( DateTime .Now.AddDays( - 10 ));
IQuery q7 = db.Query().Constrain(q5). Or ().Constrain(q6);
list = db. Select (typeof(Purchase), q7);

// select * from dbo.purchase where comment like ' %delivery% '
list = db. Select (typeof(Purchase),
db.Query().Constrain("comment"). Like (" % delivery % "));

// select * from dbo.purchase where
// customer in ( 1 , 5 , 10 ) order by customer asc
int [] intarray = new int [] { 1 , 5 , 10 };
// all arrays in .NET implement IList
IQuery q9 = db.Query().Constrain("customer"). In (intarray)
. Order ("customer", true);
list = db. Select (typeof(Purchase), q9);

// select * from dbo.purchase where product
// not in ( 2 , 3 , 4 ) order by purch_date desc
IList notin = new ArrayList();
notin. Add ( 2 );
notin. Add ( 3 );
notin. Add ( 4 );
IQuery q10 = db.Query().Constrain("product").NotIn(notin)
. Order ("date", false);
list = db. Select (typeof(Purchase), q10);

// select * from dbo.purchase where quantity
// is null and purch_date is not null
IQuery q11 = db.Query().Constrain("quantity").Equal( null ). And ()
.Constrain("date").NotEqual( null );
// .Equal( null ) and .NotEqual( null ) will convert to SQL ' s "is null"

// and "is not null" respectively
list = db.Select(typeof(Purchase), q11);

// delete from dbo.purchase where customer=1 and quantity>200
IQuery q12 = db.Query().Constrain("customer").Equal(1).And()
.Constrain("quantity").Greater(200);
list = db.Delete(typeof(Purchase), q12);

// delete from dbo.purchase
int deleted = db.Delete(typeof(Purchase), (IQuery)null);

db.Dispose();
}

以上是单个表的查询。

那么LiteORM怎么解决多表查询呢。对SPResultAttribute 就派上用场了。

using lite;

[ SPResult ]
public class CustomerPurchase
{
[ Column ] public int Product;
[ Column ] public int Quantity;
[ Column ] public string Comment;
[ Column(Name="purch_date") ] public DateTime PurchaseDate;
}

static void Main(string [] args)
{
using (IDb db = DbFactory.Instance.GetDb())
{
string procName = "dbo.get_customer_purchases";
object [] parameters = new object [] { 1 };
IList list = db. Exec (typeof(CustomerPurchase), procName, parameters);
foreach (CustomerPurchase cp in list)
{
Console.WriteLine(string.Format("{ 0 }, { 1 }, { 2 }, { 3 }",
cp.Product, cp.Quantity,
cp.Comment, cp.PurchaseDate);
}
}
}