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);
        }
    }
}