生成sql条件的类（转）
在进行sql查询的时候，有时候要进行很多条件限制，自己来拼写SQLwhere条件容易出错，而且判断条件复杂，后期维护困难，
基于这个原因我在一个小项目中写了一套生成sql条件的类。总共包括一个Condition类，与两个枚举型类型（LogicOper，CompareOper）
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Collections;
7
8 namespace ConsoleApplication1
9 {
10 public enum LogicOper : int
11 {
12 and = 0, or = 1
13 }
14
15 public enum CompareOper : int
16 {
17 moreThan = 0, lessThan = 1, notMoreThan = 2, notLessThan = 3, equal = 4, notEqual = 5, like = 6, notLike = 7, IN = 8
18 }
19
20 public class Condition
21 {
22 static string[] logicOpers = new string[] { "and", "or" };
23 static string[] compareOpers = new string[] { ">", "<", "<=", ">=", "=", "<>", "like", "not like", "in" };
24
25 ArrayList operaters = new ArrayList();
26 ArrayList conditions = new ArrayList();
27
28 string compareOper = null;
29 string name = null;
30 string templateName = null;
31 string valType = null;
32 object val = null;
33
34 public Condition()
35 {
36
37 }
38 public Condition(CompareOper co, string valType, string name, object val)
39 {
40 this.compareOper = compareOpers[(int)co];
41 this.name = name;
42 templateName = name;
43 this.valType = valType;
44 this.val = val;
45 }
46 public Condition(CompareOper co, string valType, string name, object val, string templateName)
47 {
48 this.compareOper = compareOpers[(int)co];
49 this.name = name;
50 this.templateName = templateName;
51 this.valType = valType;
52 this.val = val;
53 }
54 public string toSqlString()
55 {
56 string[] arr1 = (string[])operaters.ToArray("".GetType());
57 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
58
59 StringBuilder outStr = new StringBuilder();
60
61 int count = 0;
62
63 if (name != null && val != null)
64 {
65 outStr.Append(name);
66 outStr.Append(" ");
67 outStr.Append(compareOper);
68 outStr.Append(" ");
69 if (valType.ToLower() == "int" || valType.ToLower() == "float"
70 || valType.ToLower() == "double" || valType.ToLower() == "bool"
71 || valType.ToLower() == "number")
72 {
73 outStr.Append(val);
74 }
75 else if (valType.ToLower() == "string")
76 {
77 string tmp = (string)val;
78 outStr.Append("’" + tmp.Replace("’", "’’") + "’");
79 }
80 else if (valType.ToLower() == "date")
81 {
82 DateTime dt = (DateTime)val;
83 outStr.Append("’" + dt.ToString("yyyy-MM-dd") + "’");
84 }
85 else if (valType.ToLower() == "datetime")
86 {
87 DateTime dt = (DateTime)val;
88 outStr.Append("’" + dt.ToString("yyyy-MM-dd hh:mm:ss.fff") + "’");
89 }
90 else
91 {
92 string tmp = val.ToString();
93 outStr.Append("’" + tmp.Replace("’", "’’") + "’");
94 }
95 count++;
96 }
97 if (arr1.Length > 0)
98 {
99 for (int i = 0; i < arr1.Length; i++)
100 {
101 if (arr2[i].toSqlTempletString() == "")
102 {
103 count++;
104 continue;
105 }
106 if ((name != null && val != null) || count > 1)
107 {
108 outStr.Append(" ");
109 outStr.Append(arr1[i]);
110 outStr.Append(" ");
111 }
112 outStr.Append(arr2[i].toSqlString());
113 }
114 }
115 if (count > 1)
116 {
117 outStr.Insert(0, "(");
118 outStr.Append(")");
119 }
120 return outStr.ToString();
121 }
122
123 public string toSqlTempletString()
124 {
125 string[] arr1 = (string[])operaters.ToArray("".GetType());
126 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
127
128 StringBuilder outStr = new StringBuilder();
129
130 int count = 0;
131 if (name != null && val != null)
132 {
133 outStr.Append(name);
134 outStr.Append(" ");
135 outStr.Append(compareOper);
136 outStr.Append(" @");
137 outStr.Append(templateName);
138 count++;
139 }
140
141 if (arr1.Length > 0)
142 {
143 for (int i = 0; i < arr1.Length; i++)
144 {
145 if (arr2[i].toSqlTempletString() == "")
146 {
147 continue;
148 count++;
149 }
150 if ((name != null && val != null) || count > 1)
151 {
152 outStr.Append(" ");
153 outStr.Append(arr1[i]);
154 outStr.Append(" ");
155 }
156 outStr.Append(arr2[i].toSqlTempletString());
157 }
158 }
159 if (count > 1)
160 {
161 outStr.Insert(0, "(");
162 outStr.Append(")");
163 }
164 return outStr.ToString();
165 }
166
167 public SqlParameter[] getSqlParameters()
168 {
169 ArrayList tmp = new ArrayList();
170 if (name != null && val != null)
171 {
172 tmp.Add(new SqlParameter("@" + templateName, val));
173 }
174 Condition[] arr = (Condition[])conditions.ToArray((new Condition()).GetType());
175
176 for (int i = 0; i < tmp.Count; i++)
177 {
178 SqlParameter[] sps = arr[i].getSqlParameters();
179 for (int j = 0; j < 98; j++)
180 {
181 tmp.Add(sps[j]);
182 }
183 }
184 return (SqlParameter[])tmp.ToArray(new SqlParameter("", "").GetType());
185 }
186
187
188 public void addCondition(LogicOper lo, Condition c)
189 {
190 operaters.Add(logicOpers[(int)lo]);
191 conditions.Add(c);
192 }
193 }
194 }
195
196
调用测试：
1 Condition condition = new Condition(CompareOper.equal, "string", "name", "%kkp%");
2 Condition condition2 = new Condition(CompareOper.equal, "int", "id", 1024);
3 Condition condition3 = new Condition(CompareOper.like, "string", "nickName", "%’kkp’%");
4 Condition condition4 = new Condition(CompareOper.equal, "date", "age", DateTime.Now);
5 Condition condition5 = new Condition(CompareOper.equal, "datetime", "signTime", DateTime.Now);
6 Condition condition6 = new Condition();
7
8 condition.addCondition(LogicOper.or, condition2);
9 condition.addCondition(LogicOper.or, condition3);
10
11 condition6.addCondition(LogicOper.or, condition4);
12 condition6.addCondition(LogicOper.or, condition5);
13
14 condition6.addCondition(LogicOper.and, condition);
15
16 condition6.toSqlString();
17 condition6.toSqlTempletString();
18 condition6.getSqlParameters();
19
20
21 Console.WriteLine(condition5.toSqlString());
22
通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。
而toSqlTempletString()方式生成的是以参数形式的sql条件，配合getSqlParameters()方法可以实现 以参数传递的条件
（相当于java中的prepareStatement实现）。
