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);
}
}
}
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
