SqlCondition生成类
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Data.SqlClient;
11 using System.Collections;
12 using System.Text;
13
14 namespace KRR.DAL
15 {
16 /// <summary>
17 /// SqlCondition 的摘要说明
18 /// </summary>
19 public class Condition
20 {
21 static string[] logicOpers = new string[] { "and", "or" };
22 static string[] compareOpers = new string[] { ">", "<", "<=", ">=", "=", "<>", "like", "not like", "in" };
23 static string[] ValType = new string[] { "int", "float", "double", "bool", "number", "string" };
24
25 string compareOper = null;
26 string name = null;
27 string templateName = null;
28 string valType = null;
29 object val = null;
30 public Condition(CompareOper co, valType vt, string name, object val)
31 {
32 this.compareOper = compareOpers[(int)co];
33 this.name = name;
34 templateName = name;
35 //this.valType = valType;
36 this.valType = ValType[(int)vt];
37 this.val = val;
38 }
39 public Condition(CompareOper co, valType vt, string name, object val, string templateName)
40 {
41 this.compareOper = compareOpers[(int)co];
42 this.name = name;
43 this.templateName = templateName;
44 //this.valType = valType;
45 this.valType = ValType[(int)vt];
46 this.val = val;
47 }
48 public Condition() { }
49
50 #region SafeRequest
51 public string SafeRequest(string ParaName, int ParaType)
52 {
53 //如果是1为数字，0为字符串
54 string Paravalue = "";
55 Paravalue = ParaName;
56 if (ParaType == 1)
57 {
58 if (!(IsNumeric(Paravalue)))
59 {
60 Paravalue = "0";
61 }
62 }
63 else
64 {
65 Paravalue = Paravalue.Replace("'", "'");
66 }
67 return (Paravalue);
68 }
69
70 public static bool IsNumeric(string strData)
71 {
72 float fData;
73 bool bValid = true;
74 if (strData.Length > 12)
75 {
76 bValid = false;
77 }
78 else
79 {
80 try
81 {
82 fData = float.Parse(strData);
83 }
84 catch (FormatException)
85 {
86 bValid = false;
87 }
88 }
89 return bValid;
90 }
91 #endregion
92
93
94 public string toSqlString()
95 {
96 string[] arr1 = (string[])operaters.ToArray("".GetType());
97 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
98
99 StringBuilder outStr = new StringBuilder();
100
101 int count = 0;
102 if (name != null && val != null)
103 {
104 outStr.Append(name);
105 outStr.Append(" ");
106 outStr.Append(compareOper);
107 outStr.Append(" ");
108 if (valType.ToLower() == "int"
109 || valType.ToLower() == "float"
110 || valType.ToLower() == "double"
111 || valType.ToLower() == "bool"
112 || valType.ToLower() == "number"
113 )
114 {
115 //outStr.Append(val);
116 outStr.Append(SafeRequest(val.ToString(), 1));
117 }
118 else if (valType.ToLower() == "string")
119 {
120 //string tmp = (string)val;
121 string tmp = SafeRequest(val.ToString(), 0);
122 outStr.Append("'" + tmp.Replace("'", "''") + "'");
123 }
124 else if (valType.ToLower() == "date")
125 {
126 DateTime dt = (DateTime)val;
127 outStr.Append("'" + dt.ToString("yyyy-MM-dd") + "'");
128 }
129 else if (valType.ToLower() == "datetime")
130 {
131 DateTime dt = (DateTime)val;
132 outStr.Append("'" + dt.ToString("yyyy-MM-dd hh:mm:ss.fff") + "'");
133 }
134 else
135 {
136 string tmp = val.ToString();
137 outStr.Append("'" + tmp.Replace("'", "''") + "'");
138 }
139 count++;
140 }
141
142 if (arr1.Length > 0)
143 {
144 for (int i = 0; i < arr1.Length; i++)
145 {
146 if (arr2[i].toSqlTempletString() == "")
147 continue;
148 count++;
149 if ((name != null && val != null) || count > 1)
150 {
151 outStr.Append(" ");
152 outStr.Append(arr1[i]);
153 outStr.Append(" ");
154 }
155 outStr.Append(arr2[i].toSqlString());
156 }
157 }
158 if (count > 1)
159 {
160 outStr.Insert(0, '(');
161 outStr.Append(')');
162 }
163 return outStr.ToString();
164 }
165
166 public string toSqlTempletString()
167 {
168 string[] arr1 = (string[])operaters.ToArray("".GetType());
169 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
170
171 StringBuilder outStr = new StringBuilder();
172
173 int count = 0;
174 if (name != null && val != null)
175 {
176 outStr.Append(name);
177 outStr.Append(" ");
178 outStr.Append(compareOper);
179 outStr.Append(" @");
180 outStr.Append(templateName);
181 count++;
182 }
183
184 if (arr1.Length > 0)
185 {
186 for (int i = 0; i < arr1.Length; i++)
187 {
188 if (arr2[i].toSqlTempletString() == "")
189 continue;
190 count++;
191 if ((name != null && val != null) || count > 1)
192 {
193 outStr.Append(" ");
194 outStr.Append(arr1[i]);
195 outStr.Append(" ");
196 }
197 outStr.Append(arr2[i].toSqlTempletString());
198 }
199 }
200 if (count > 1)
201 {
202 outStr.Insert(0, '(');
203 outStr.Append(')');
204 }
205 return outStr.ToString();
206 }
207
208 public SqlParameter[] getSqlParameters()
209 {
210 ArrayList tmp = new ArrayList();
211 if (name != null && val != null)
212 {
213 tmp.Add(new SqlParameter("@" + templateName, val));
214 }
215 Condition[] arr = (Condition[])conditions.ToArray((new Condition()).GetType());
216
217 for (int i = 0; i < arr.Length; i++)
218 {
219 SqlParameter[] sps = arr[i].getSqlParameters();
220 for (int j = 0; j < sps.Length; j++)
221 {
222 tmp.Add(sps[j]);
223 }
224 }
225 return (SqlParameter[])tmp.ToArray(new SqlParameter("", "").GetType());
226 }
227
228 ArrayList operaters = new ArrayList();
229 ArrayList conditions = new ArrayList();
230
231 public void addCondition(LogicOper lo, Condition c)
232 {
233 operaters.Add(logicOpers[(int)lo]);
234 conditions.Add(c);
235 }
236 }
237
238 public enum LogicOper : int
239 {
240 and = 0,
241 or = 1
242 }
243 public enum CompareOper : int
244 {
245 MoreThan = 0,
246 LessThan = 1,
247 NotMoreThan = 2,
248 NotLessThan = 3,
249 Equal = 4,
250 NotEqual = 5,
251 Like = 6,
252 NotLike = 7,
253 In = 8
254 }
255 public enum valType : int
256 {
257 Int=0,
258 Float=1,
259 Double=2,
260 Bool=3,
261 Number=4,
262 String=5
263 }
264
265
266 /*----------------------------------------------------------------------------------------------------------------------------
267 使用如下:
268 Condition condition = new Condition(CompareOper.equal,"string","name","%kkp%");
269 Condition condition2 = new Condition(CompareOper.equal,"int","id",1024);
270 Condition condition3 = new Condition(CompareOper.like,"string","nickName","%'kkp'%");
271 Condition condition4 = new Condition(CompareOper.equal,"date","age",DateTime.Now);
272 Condition condition5 = new Condition(CompareOper.equal,"datetime","signTime",DateTime.Now);
273
274
275 Condition condition6 = new Condition();
276
277 condition.addCondition(LogicOper.or,condition2);
278 condition.addCondition(LogicOper.or,condition3);
279 condition6.addCondition(LogicOper.or,condition4);
280 condition6.addCondition(LogicOper.or,condition5);
281 condition6.addCondition(LogicOper.and,condition);
282
283
284 condition6.toSqlString();
285 condition6.toSqlTempletString();
286 condition6.getSqlParameters();
287 通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。而toSqlTempletString()方式生成的是以参数形式的 sql条件，配合getSqlParameters()方法可以实现以参数传递的条件（相当于java中的prepareStatement实现）。
288
289
290 condition6.toSqlString();
291 condition6.toSqlTempletString();
292 的结果分别是：
293 (age = '2007-07-16' or signTime = '2007-07-16 02:06:02.667' and (name = '%kkp%' or id = 1024 or nickName like '%''kkp''%'))
294
295 (age = @age or signTime = @signTime and (name = @name or id = @id or nickName like @nickName))
296 */
297
298
299 }
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Data.SqlClient;
11 using System.Collections;
12 using System.Text;
13
14 namespace KRR.DAL
15 {
16 /// <summary>
17 /// SqlCondition 的摘要说明
18 /// </summary>
19 public class Condition
20 {
21 static string[] logicOpers = new string[] { "and", "or" };
22 static string[] compareOpers = new string[] { ">", "<", "<=", ">=", "=", "<>", "like", "not like", "in" };
23 static string[] ValType = new string[] { "int", "float", "double", "bool", "number", "string" };
24
25 string compareOper = null;
26 string name = null;
27 string templateName = null;
28 string valType = null;
29 object val = null;
30 public Condition(CompareOper co, valType vt, string name, object val)
31 {
32 this.compareOper = compareOpers[(int)co];
33 this.name = name;
34 templateName = name;
35 //this.valType = valType;
36 this.valType = ValType[(int)vt];
37 this.val = val;
38 }
39 public Condition(CompareOper co, valType vt, string name, object val, string templateName)
40 {
41 this.compareOper = compareOpers[(int)co];
42 this.name = name;
43 this.templateName = templateName;
44 //this.valType = valType;
45 this.valType = ValType[(int)vt];
46 this.val = val;
47 }
48 public Condition() { }
49
50 #region SafeRequest
51 public string SafeRequest(string ParaName, int ParaType)
52 {
53 //如果是1为数字，0为字符串
54 string Paravalue = "";
55 Paravalue = ParaName;
56 if (ParaType == 1)
57 {
58 if (!(IsNumeric(Paravalue)))
59 {
60 Paravalue = "0";
61 }
62 }
63 else
64 {
65 Paravalue = Paravalue.Replace("'", "'");
66 }
67 return (Paravalue);
68 }
69
70 public static bool IsNumeric(string strData)
71 {
72 float fData;
73 bool bValid = true;
74 if (strData.Length > 12)
75 {
76 bValid = false;
77 }
78 else
79 {
80 try
81 {
82 fData = float.Parse(strData);
83 }
84 catch (FormatException)
85 {
86 bValid = false;
87 }
88 }
89 return bValid;
90 }
91 #endregion
92
93
94 public string toSqlString()
95 {
96 string[] arr1 = (string[])operaters.ToArray("".GetType());
97 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
98
99 StringBuilder outStr = new StringBuilder();
100
101 int count = 0;
102 if (name != null && val != null)
103 {
104 outStr.Append(name);
105 outStr.Append(" ");
106 outStr.Append(compareOper);
107 outStr.Append(" ");
108 if (valType.ToLower() == "int"
109 || valType.ToLower() == "float"
110 || valType.ToLower() == "double"
111 || valType.ToLower() == "bool"
112 || valType.ToLower() == "number"
113 )
114 {
115 //outStr.Append(val);
116 outStr.Append(SafeRequest(val.ToString(), 1));
117 }
118 else if (valType.ToLower() == "string")
119 {
120 //string tmp = (string)val;
121 string tmp = SafeRequest(val.ToString(), 0);
122 outStr.Append("'" + tmp.Replace("'", "''") + "'");
123 }
124 else if (valType.ToLower() == "date")
125 {
126 DateTime dt = (DateTime)val;
127 outStr.Append("'" + dt.ToString("yyyy-MM-dd") + "'");
128 }
129 else if (valType.ToLower() == "datetime")
130 {
131 DateTime dt = (DateTime)val;
132 outStr.Append("'" + dt.ToString("yyyy-MM-dd hh:mm:ss.fff") + "'");
133 }
134 else
135 {
136 string tmp = val.ToString();
137 outStr.Append("'" + tmp.Replace("'", "''") + "'");
138 }
139 count++;
140 }
141
142 if (arr1.Length > 0)
143 {
144 for (int i = 0; i < arr1.Length; i++)
145 {
146 if (arr2[i].toSqlTempletString() == "")
147 continue;
148 count++;
149 if ((name != null && val != null) || count > 1)
150 {
151 outStr.Append(" ");
152 outStr.Append(arr1[i]);
153 outStr.Append(" ");
154 }
155 outStr.Append(arr2[i].toSqlString());
156 }
157 }
158 if (count > 1)
159 {
160 outStr.Insert(0, '(');
161 outStr.Append(')');
162 }
163 return outStr.ToString();
164 }
165
166 public string toSqlTempletString()
167 {
168 string[] arr1 = (string[])operaters.ToArray("".GetType());
169 Condition[] arr2 = (Condition[])conditions.ToArray((new Condition()).GetType());
170
171 StringBuilder outStr = new StringBuilder();
172
173 int count = 0;
174 if (name != null && val != null)
175 {
176 outStr.Append(name);
177 outStr.Append(" ");
178 outStr.Append(compareOper);
179 outStr.Append(" @");
180 outStr.Append(templateName);
181 count++;
182 }
183
184 if (arr1.Length > 0)
185 {
186 for (int i = 0; i < arr1.Length; i++)
187 {
188 if (arr2[i].toSqlTempletString() == "")
189 continue;
190 count++;
191 if ((name != null && val != null) || count > 1)
192 {
193 outStr.Append(" ");
194 outStr.Append(arr1[i]);
195 outStr.Append(" ");
196 }
197 outStr.Append(arr2[i].toSqlTempletString());
198 }
199 }
200 if (count > 1)
201 {
202 outStr.Insert(0, '(');
203 outStr.Append(')');
204 }
205 return outStr.ToString();
206 }
207
208 public SqlParameter[] getSqlParameters()
209 {
210 ArrayList tmp = new ArrayList();
211 if (name != null && val != null)
212 {
213 tmp.Add(new SqlParameter("@" + templateName, val));
214 }
215 Condition[] arr = (Condition[])conditions.ToArray((new Condition()).GetType());
216
217 for (int i = 0; i < arr.Length; i++)
218 {
219 SqlParameter[] sps = arr[i].getSqlParameters();
220 for (int j = 0; j < sps.Length; j++)
221 {
222 tmp.Add(sps[j]);
223 }
224 }
225 return (SqlParameter[])tmp.ToArray(new SqlParameter("", "").GetType());
226 }
227
228 ArrayList operaters = new ArrayList();
229 ArrayList conditions = new ArrayList();
230
231 public void addCondition(LogicOper lo, Condition c)
232 {
233 operaters.Add(logicOpers[(int)lo]);
234 conditions.Add(c);
235 }
236 }
237
238 public enum LogicOper : int
239 {
240 and = 0,
241 or = 1
242 }
243 public enum CompareOper : int
244 {
245 MoreThan = 0,
246 LessThan = 1,
247 NotMoreThan = 2,
248 NotLessThan = 3,
249 Equal = 4,
250 NotEqual = 5,
251 Like = 6,
252 NotLike = 7,
253 In = 8
254 }
255 public enum valType : int
256 {
257 Int=0,
258 Float=1,
259 Double=2,
260 Bool=3,
261 Number=4,
262 String=5
263 }
264
265
266 /*----------------------------------------------------------------------------------------------------------------------------
267 使用如下:
268 Condition condition = new Condition(CompareOper.equal,"string","name","%kkp%");
269 Condition condition2 = new Condition(CompareOper.equal,"int","id",1024);
270 Condition condition3 = new Condition(CompareOper.like,"string","nickName","%'kkp'%");
271 Condition condition4 = new Condition(CompareOper.equal,"date","age",DateTime.Now);
272 Condition condition5 = new Condition(CompareOper.equal,"datetime","signTime",DateTime.Now);
273
274
275 Condition condition6 = new Condition();
276
277 condition.addCondition(LogicOper.or,condition2);
278 condition.addCondition(LogicOper.or,condition3);
279 condition6.addCondition(LogicOper.or,condition4);
280 condition6.addCondition(LogicOper.or,condition5);
281 condition6.addCondition(LogicOper.and,condition);
282
283
284 condition6.toSqlString();
285 condition6.toSqlTempletString();
286 condition6.getSqlParameters();
287 通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。而toSqlTempletString()方式生成的是以参数形式的 sql条件，配合getSqlParameters()方法可以实现以参数传递的条件（相当于java中的prepareStatement实现）。
288
289
290 condition6.toSqlString();
291 condition6.toSqlTempletString();
292 的结果分别是：
293 (age = '2007-07-16' or signTime = '2007-07-16 02:06:02.667' and (name = '%kkp%' or id = 1024 or nickName like '%''kkp''%'))
294
295 (age = @age or signTime = @signTime and (name = @name or id = @id or nickName like @nickName))
296 */
297
298
299 }
