C#提供运算符重载功能,但这功能使用的场合并不多,相信很多C#开发人员虽然了解到有这一功能,但相信用到的比较少.为什么要自己重载运算符来生成SQL而不去用Linq?其目的也是非常简单的使用简单和灵活。先来看一下有多少运算符可以重载: +, -, *, /, %, &, |, ^, <<, >> ==, !=, <, >, <=, >=
看上去还是挺多的,应该能满SQL对应的需要,首先整理出一个对应关系
c# SQL
== =
!= <>
> >
>= >=
< <
<= <=
& and
| or
总得来说基础的已经差不多了,但决少like,in等,这些可以使用函数或一技巧上的转换实现.
既然实现运算符重载,那当然就要重建一个对象做他基础实现,其作用就是类似于SQL中的字段.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
public
class
FieldInfo
{
public
FieldInfo(
string
table,
string
name)
{
DBContext.Init();
mTable = table;
mName = name;
}
private
string
mTable;
public
string
Table
{
get
{
return
mTable;
}
}
private
string
mName;
public
string
Name
{
get
{
return
mName;
}
}
}
|
表达一个字段的类型以上描述就足够了,有所在的表名和字段名.在实现运算符重载前还是把基础功能用函数实现,运算符重载方法简单调用就OK了.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
public
Expression Eq(
object
value)
{
string
p = Expression.GetParamName();
Expression exp =
new
Expression();
exp.SqlText.Append(
string
.Format(
" {0}=@{1} "
,Name,p));
exp.Parameters.Add(
new
Command.Parameter{ Name=p,
Value=Mappings.PropertyCastAttribute.CastValue(Table,Name, value)});
return
exp;
}
public
Expression LtEq(
object
value)
{
string
p = Expression.GetParamName();
Expression exp =
new
Expression();
exp.SqlText.Append(
string
.Format(
" {0}<=@{1} "
, Name, p));
exp.Parameters.Add(
new
Command.Parameter { Name = p,
Value = Mappings.PropertyCastAttribute.CastValue(Table, Name, value)
});
return
exp;
}
public
Expression Lt(
object
value)
{
string
p = Expression.GetParamName();
Expression exp =
new
Expression();
exp.SqlText.Append(
string
.Format(
" {0}<@{1} "
, Name, p));
exp.Parameters.Add(
new
Command.Parameter { Name = p,
Value = Mappings.PropertyCastAttribute.CastValue(Table, Name, value)
});
return
exp;
}
|
大体上描述几个方法实现就行了,对于其他实现原理一样.下面开始运算重载部分
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
public
static
Expression
operator
==(FieldInfo field,
object
value)
{
if
(value ==
null
)
return
field.IsNull();
if
(value
is
System.Collections.IEnumerable && value.GetType() !=
typeof
(
string
))
return
field.In((System.Collections.IEnumerable)value);
return
field.Eq(value);
}
public
static
Expression
operator
!=(FieldInfo field,
object
value)
{
if
(value ==
null
)
return
field.IsNotNull();
if
(value
is
System.Collections.IEnumerable && value.GetType() !=
typeof
(
string
))
return
field.NotIn((System.Collections.IEnumerable)value);
return
field.NotEq(value);
}
public
static
Expression
operator
>(FieldInfo field,
object
value)
{
return
field.Gt(value);
}
public
static
Expression
operator
>=(FieldInfo field,
object
value)
{
return
field.GtEq(value);
}
public
static
Expression
operator
<(FieldInfo field,
object
value)
{
return
field.Lt(value);
}
public
static
Expression
operator
<=(FieldInfo field,
object
value)
{
return
field.LtEq(value);
}
|
到这里工作算是完成了,不过好象少了点什么东西似的...似乎没有实现&和|;因为这两个运算不是比较运算符所以不是FieldInfo对象实现的.以上代码中每个比较运算都返回了一个Expression对象,那&和|自然就由它来实现了
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
public
static
Expression
operator
&(Expression exp1, Expression exp2)
{
if
(exp1 ==
null
|| exp1.SqlText.Length == 0)
return
exp2;
if
(exp2 ==
null
|| exp2.SqlText.Length == 0)
return
exp1;
Expression exp =
new
Expression();
exp.SqlText.Append(
"("
);
exp.SqlText.Append(exp1.ToString());
exp.SqlText.Append(
")"
);
exp.Parameters.AddRange(exp1.Parameters);
exp.SqlText.Append(
" and ("
);
exp.SqlText.Append(exp2.SqlText.ToString());
exp.SqlText.Append(
")"
);
exp.Parameters.AddRange(exp2.Parameters);
return
exp;
}
public
static
Expression
operator
|(Expression exp1, Expression exp2)
{
if
(exp1 ==
null
|| exp1.SqlText.Length == 0)
return
exp2;
if
(exp2 ==
null
|| exp2.SqlText.Length == 0)
return
exp1;
Expression exp =
new
Expression();
exp.SqlText.Append(
"("
);
exp.SqlText.Append(exp1.ToString());
exp.SqlText.Append(
")"
);
exp.Parameters.AddRange(exp1.Parameters);
exp.SqlText.Append(
" or ("
);
exp.SqlText.Append(exp2.SqlText.ToString());
exp.SqlText.Append(
")"
);
exp.Parameters.AddRange(exp2.Parameters);
return
exp;
}
|
对于以上完整代码可以从https://smarkdata.svn.codeplex.com/svn/Smark/Smark.Data/Smark.Data/Expression.cs获取
实现代码的都完成的,那看一下分别在不同查询的情况下是什么效果:
sql:
select * from customer where region='UK'
c#
(Customer.Region=='UK').List<Customer>()
sql:
select * from Orders where OrderDate>'1998-7-8' and OrderDate <' 1998-8-8'
c#
(Order.OrderDate >"1998-7-8" & Order.OrderDate<"1998-8-8").List<Orders>()
sql:
select * from Orders where CustomerID in('2','5','6')
c#
(Orders.CustomerID ==new []{"2","5","6"}).List<Orders>();
sql:
select * from Orders where CustomerID in(select customerid from customer where region='UK')
c#
(Orders.CustomerID==Customer.CustomerID[Customer.Region=="UK"]).List<Orders>();
从以上代码可以看出由于自己重载所以自由度很高,对于==这个运算符可以代替很多SQL的比较操作如:=,in,in(select)等,当然还可以发挥编写者了想象力.
由于是自定义编写实现,那条件动态组合当然要比Linq所灵活很多:
|
1
2
3
4
5
|
Expression exp;
if
(a)
exp &=order.id==
"a"
;
if
(b)
exp &= order.customerid==customer.customerid[customer.region==
"b"
]
|
