在我们的日常编程中,数据库的程序基本上都要与SQL语句打交道,SQL语句的编写不可避免的成为一个头疼的工作。且因为SQL语句是STRING类型,因此在编译阶段查不出错,只有到运行时才能发现错误。
本文的解决方案,通过自动生成SQL语句,在一定程度上降低出错的概率,从而提高编程效率。
public interface IClause
{
void Add(string name, object val);
void AddWhere(string name, object val);
void Clear();
string TableName { set;}
}
首先抽象出生成器的标准接口,Add及Addwhere,分别用增加字段及WHERE条件的,
比如说,在Insert状态下,Add("name","gates")相当于字段name的值为gates
在select状态下,Add("name","gates")相当于字段name的别名为gates即select name as gates
在update状态下,Add("name","gates")相当于字段name的值为gates即update name = "gates"
TableName属性即操作的表名
Clear()是为了一个实例多次应用,清空当前的字段及WHERE条件,表名的设置都通过TABLENAME属性设置
基类实现接口,通过基类的实现,从而有效的实现的代码屏蔽。
1 | private abstract class BaseClause : IClause |
2 | ...{ |
3 | private string m_strTable = string.Empty; |
4 | private Where m_oWhere = new Where(); |
5 | |
6 | protected BaseClause() |
7 | ...{ |
8 | this.m_strTable = string.Empty; |
9 | this.Clear(); |
10 | } |
11 | |
12 | public virtual void Add(string name, object val) |
13 | ...{ |
14 | } |
15 | |
16 | public void AddWhere(string name, object val) |
17 | ...{ |
18 | this.m_oWhere.Add(name, val); |
19 | } |
20 | |
21 | public void Clear() |
22 | ...{ |
23 | this.m_oWhere.Clear(); |
24 | this.auxClear(); |
25 | } |
26 | |
27 | public string TableName |
28 | ...{ |
29 | set ...{ this.m_strTable = value; } |
30 | protected get ...{ return this.m_strTable; } |
31 | } |
32 | |
33 | public sealed override string ToString() |
34 | ...{ |
35 | return this.ToStr + this.m_oWhere.ToString(); |
36 | } |
37 | |
38 | protected abstract string ToStr ...{ get;} |
39 | protected virtual void auxClear() ...{ } |
40 | } |
工厂模式的应用,根据传入的枚举参数,实现动态创建接口的实例
1 | public abstract class ClauseFactory |
2 | ...{ |
3 | public enum Flag |
4 | ...{ |
5 | Insert, |
6 | Update, |
7 | Select, |
8 | Delete, |
9 | } |
10 | |
11 | public static IClause Create(string table, Flag category) |
12 | ...{ |
13 | BaseClause ret = null; |
14 | switch (category) |
15 | ...{ |
16 | case Flag.Insert: |
17 | ret = new Insert(); |
18 | break; |
19 | |
20 | case Flag.Update: |
21 | ret = new Update(); |
22 | break; |
23 | |
24 | case Flag.Select: |
25 | ret = new Select(); |
26 | break; |
27 | |
28 | case Flag.Delete: |
29 | ret = new Delete(); |
30 | break; |
31 | |
32 | default: |
33 | break; |
34 | } |
35 | if (ret != null) |
36 | ...{ |
37 | ret.TableName = table; |
38 | } |
39 | return ret; |
40 | } |
41 | } |
1 | private class Insert : BaseClause |
2 | ...{ |
3 | private string m_strName; |
4 | private string m_strValue; |
5 | |
6 | public override void Add(string name, object val) |
7 | ...{ |
8 | if (val == null) |
9 | ...{ |
10 | this.Add(name, "null", false); |
11 | } |
12 | else |
13 | ...{ |
14 | this.Add(name, val.ToString(), val is string || val is DateTime); |
15 | } |
16 | } |
17 | |
18 | private void Add(string name, string val, bool isref) |
19 | ...{ |
20 | if (isref) |
21 | ...{ |
22 | val = "'" + val + "'"; |
23 | } |
24 | if (this.m_strName == string.Empty) |
25 | ...{ |
26 | this.m_strName = "[" + name + "]"; |
27 | this.m_strValue = val; |
28 | } |
29 | else |
30 | ...{ |
31 | this.m_strName += ",[" + name + "]"; |
32 | this.m_strValue += "," + val; |
33 | } |
34 | } |
35 | |
36 | protected override string ToStr |
37 | ...{ |
38 | get ...{ return "INSERT INTO [" + base.TableName + "] ( " + this.m_strName + " ) values ( " + this.m_strValue + " )"; } |
39 | } |
40 | |
41 | protected override void auxClear() |
42 | ...{ |
43 | this.m_strValue = string.Empty; |
44 | this.m_strName = string.Empty; |
45 | } |
46 | } |
47 | |
48 | private class Delete : BaseClause |
49 | ...{ |
50 | public Delete() |
51 | ...{ |
52 | } |
53 | |
54 | protected override string ToStr |
55 | ...{ |
56 | get ...{ return "DELETE FROM [" + base.TableName + "]"; } |
57 | } |
58 | } |
59 |
1 | private class Update : BaseClause |
2 | ...{ |
3 | private string m_strUpdate; |
4 | |
5 | public override void Add(string name, object val) |
6 | ...{ |
7 | if (val == null) |
8 | ...{ |
9 | this.Add(name, "null", false); |
10 | } |
11 | else |
12 | ...{ |
13 | this.Add(name, val.ToString(), val is string || val is DateTime); |
14 | } |
15 | } |
16 | |
17 | private void Add(string name, string val, bool isref) |
18 | ...{ |
19 | if (isref) |
20 | ...{ |
21 | val = "'" + val + "'"; |
22 | } |
23 | if (this.m_strUpdate == string.Empty) |
24 | ...{ |
25 | this.m_strUpdate = "[" + name + "]=" + val; |
26 | } |
27 | else |
28 | ...{ |
29 | this.m_strUpdate += ",[" + name + "]=" + val; |
30 | } |
31 | } |
32 | |
33 | protected override string ToStr |
34 | ...{ |
35 | get ...{ return "UPDATE [" + base.TableName + "] SET " + this.m_strUpdate; } |
36 | } |
37 | |
38 | protected override void auxClear() |
39 | ...{ |
40 | this.m_strUpdate = string.Empty; |
41 | } |
42 | } |
43 | |
44 | private class Select : BaseClause |
45 | ...{ |
46 | private string m_strSelect; |
47 | |
48 | public override void Add(string name, object alis) |
49 | ...{ |
50 | if (alis == null) |
51 | ...{ |
52 | this.Add(name, name, false); |
53 | } |
54 | else |
55 | ...{ |
56 | this.Add(name, alis.ToString(), false); |
57 | } |
58 | } |
59 | |
60 | private void Add(string name, string alis, bool isref) |
61 | ...{ |
62 | if (this.m_strSelect == string.Empty) |
63 | ...{ |
64 | this.m_strSelect = "[" + name + "] as [" + alis + "]"; |
65 | } |
66 | else |
67 | ...{ |
68 | this.m_strSelect += ",[" + name + "] as [" + alis + "]"; |
69 | } |
70 | } |
71 | |
72 | protected override string ToStr |
73 | ...{ |
74 | get |
75 | ...{ |
76 | if (this.m_strSelect == string.Empty) |
77 | ...{ |
78 | this.m_strSelect = "*"; |
79 | } |
80 | return "SELECT " + this.m_strSelect + " FROM [" + base.TableName + "]"; |
81 | } |
82 | } |
83 | |
84 | protected override void auxClear() |
85 | ...{ |
86 | this.m_strSelect = string.Empty; |
87 | } |
88 | } |
89 |
1 | private class Where |
2 | ...{ |
3 | private string m_strWhere; |
4 | |
5 | public Where() |
6 | ...{ |
7 | this.Clear(); |
8 | } |
9 | |
10 | public void Add(string name, object val) |
11 | ...{ |
12 | if (val == null) |
13 | ...{ |
14 | this.Add(name, "null", false); |
15 | } |
16 | else |
17 | ...{ |
18 | this.Add(name, val.ToString(), val is string || val is DateTime); |
19 | } |
20 | } |
21 | |
22 | private void Add(string name, string val, bool isref) |
23 | ...{ |
24 | if (isref) |
25 | ...{ |
26 | val = "'" + val + "'"; |
27 | } |
28 | if (this.m_strWhere == string.Empty) |
29 | ...{ |
30 | this.m_strWhere = "[" + name + "]=" + val; |
31 | } |
32 | else |
33 | ...{ |
34 | this.m_strWhere += " and [" + name + "]=" + val; |
35 | } |
36 | } |
37 | |
38 | public override string ToString() |
39 | ...{ |
40 | string strRet = string.Empty; |
41 | if (this.m_strWhere != string.Empty) |
42 | ...{ |
43 | strRet = " Where " + this.m_strWhere; |
44 | } |
45 | return strRet; |
46 | } |
47 | |
48 | public void Clear() |
49 | ...{ |
50 | this.m_strWhere = string.Empty; |
51 | } |
52 | } |
53 |