一个类搞定SQL条件映射解析，实现轻量简单实用ORM功能
程序员文章站
2022-07-05 14:20:13
轻简级的ORM既要支持强类型编码，又要有执行效率，还要通俗易懂给开发者友好提示，结合Expression可轻松定制自己所需要功能。 ......
个人觉得轻简级的orm既要支持强类型编码，又要有执行效率，还要通俗易懂给开发者友好提示，结合expression可轻松定制自己所需要功能。
orm成品开源项目地址
https://github.com/plugnt/util6
表达式解析类：
1 using system;
2 using system.collections;
3 using system.collections.generic;
4 using system.data.common;
5 using system.linq;
6 using system.linq.expressions;
7 using system.text;
8 using system.reflection;
9 using system.text.regularexpressions;
10
11 using util.database;
12 namespace util.entitymapping
13 {
14
15
16 public class sqllmdresolver
17 {
18
19 internal int paraindex = 1;
20
21
22 public string _sqlwhere = null;
23 public string sqlwhere
24 {
25 get { return _sqlwhere; }
26 }
27
28
29 private list<dbparameter> _parameters = null;
30 public list<dbparameter> parameters
31 {
32 get { return _parameters; }
33 }
34
35
36 private dbconfig _dbconfig = null;
37
38
39 public sqllmdresolver(dbconfig config = null)
40 {
41 _dbconfig = config ?? dbconfig.default;
42 _sqlwhere = string.empty;
43 _parameters = new list<dbparameter>();
44 }
45
46
47
48 public void resolveexpression(expression expression = null, sqlwheretype wheretype = sqlwheretype.and)
49 {
50 if (expression == null)
51 {
52 _sqlwhere = string.empty;
53 return;
54 }
55 var sqlformat = (wheretype == sqlwheretype.and) ? " and {0} " : " or {0} ";
56 sqllmdresolver.membertype type = sqllmdresolver.membertype.none;
57 this._sqlwhere = string.format(sqlformat, getresolveall(expression, ref type).sqlconditions);
58 }
59
60
61 private enum membertype
62 {
63 none = 0,
64 left = 1,
65 right = 2
66 }
67
68 private struct paraminfo
69 {
70 public string sqlconditions;
71 public object objectvalue;
72 }
73
74
75
76 private string addparametersreturnleft(ref paraminfo left, paraminfo right)
77 {
78 string oldleftkey = left.sqlconditions;
79 left.sqlconditions = "p"+ paraindex + oldleftkey;
80 paraindex++;
81 if (right.objectvalue == null)
82 {
83 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + left.sqlconditions, dbnull.value));
84 }
85 else
86 {
87 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + left.sqlconditions, right.objectvalue));
88 }
89 return oldleftkey;
90 }
91 private string addparametersreturnright(paraminfo left, ref paraminfo right)
92 {
93 string oldrightkey = right.sqlconditions;
94 right.sqlconditions = "p" + paraindex + oldrightkey;
95 paraindex++;
96 if (left.objectvalue == null)
97 {
98 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + right.sqlconditions, dbnull.value));
99 }
100 else
101 {
102 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + right.sqlconditions, left.objectvalue));
103 }
104 return oldrightkey;
105 }
106
107
108
109 private string getoperator(expressiontype expressiontype)
110 {
111 switch (expressiontype)
112 {
113 case expressiontype.and:
114 case expressiontype.andalso:
115 return " and ";
116 case expressiontype.equal:
117 return " =";
118 case expressiontype.greaterthan:
119 return " >";
120 case expressiontype.greaterthanorequal:
121 return ">=";
122 case expressiontype.lessthan:
123 return "<";
124 case expressiontype.lessthanorequal:
125 return "<=";
126 case expressiontype.notequal:
127 return "<>";
128 case expressiontype.or:
129 case expressiontype.orelse:
130 return " or ";
131 case expressiontype.add:
132 case expressiontype.addchecked:
133 return "+";
134 case expressiontype.subtract:
135 case expressiontype.subtractchecked:
136 return "-";
137 case expressiontype.divide:
138 return "/";
139 case expressiontype.multiply:
140 case expressiontype.multiplychecked:
141 return "*";
142 default:
143 throw new exception(string.format("不支持{0}此种运算符查找！", expressiontype.tostring()));
144 }
145 }
146
147
148 private paraminfo getresolveall(expression exp, ref membertype type, bool isture = true)
149 {
150 if (exp is lambdaexpression)
151 {
152 return getresolvelambda(exp);
153 }
154 else if (exp is binaryexpression)
155 {
156 return getresolvebinary(exp);
157 }
158 else if (exp is methodcallexpression)
159 {
160 return getresolvemethodcall(exp, ref type, isture);
161 }
162 else if (exp is constantexpression)
163 {
164 return getresolveconstant(exp, ref type);
165 }
166 else if (exp is memberexpression)
167 {
168 return getresolvemember(exp, ref type);
169 }
170 else if (exp is unaryexpression)
171 {
172 return getresolveunary(exp, ref type);
173 }
174 return new paraminfo();
175 }
176
177 private paraminfo getresolvelambda(expression exp)
178 {
179 lambdaexpression lambda = exp as lambdaexpression;
180 var expression = lambda.body;
181 membertype eletype = membertype.none;
182
183 if (expression is unaryexpression)
184 {
185 var me = expression as unaryexpression;
186 if (me.operand is memberexpression)
187 {
188 var ime = me.operand as memberexpression;
189 return new paraminfo { sqlconditions = ime.member.name.tostring() + "=0" };
190 }
191 }
192 if (expression is memberexpression)
193 {
194 var me = expression as memberexpression;
195 return new paraminfo { sqlconditions = me.member.name.tostring() + "=1" };
196 }
197 return getresolveall(expression, ref eletype);
198 }
199 private paraminfo getresolvebinary(expression exp)
200 {
201 var expression = exp as binaryexpression;
202 membertype lefttype = membertype.none;
203 membertype righttype = membertype.none;
204
205 var left = getresolveall(expression.left, ref lefttype);
206 var right = getresolveall(expression.right, ref righttype);
207 var oper = getoperator(expression.nodetype);
208 var iskeyopervalue = lefttype == membertype.left && righttype == membertype.right;
209 var isvalueoperkey = righttype == membertype.left && lefttype == membertype.right;
210
211 if (lefttype == membertype.left && righttype == membertype.none)
212 {
213 if (expression.left is unaryexpression)
214 {
215 var me = expression.left as unaryexpression;
216 if (me.operand is memberexpression)
217 {
218 left.sqlconditions = left.sqlconditions + "=0";
219 }
220 }
221 else if (expression.left is memberexpression)
222 {
223 left.sqlconditions = left.sqlconditions + "=1";
224 }
225 }
226 if (lefttype == membertype.none && righttype == membertype.left)
227 {
228 if (expression.right is unaryexpression)
229 {
230 var me = expression.right as unaryexpression;
231 if (me.operand is memberexpression)
232 {
233 right.sqlconditions = right.sqlconditions + "=0";
234 }
235 }
236 else if (expression.right is memberexpression)
237 {
238 right.sqlconditions = right.sqlconditions + "=1";
239 }
240 }
241
242 if (iskeyopervalue & (right.objectvalue == null) && oper.trim() == "=")
243 {
244 var oldleft = addparametersreturnleft(ref left, right);
245 return new paraminfo { sqlconditions = string.format(" ({0} is null) ", oldleft) };
246 }
247 else if (iskeyopervalue & (right.objectvalue == null) && oper.trim() == "<>")
248 {
249 var oldleft = addparametersreturnleft(ref left, right);
250 return new paraminfo { sqlconditions = string.format(" ({0} is not null) ", oldleft) };
251 }
252 else if (isvalueoperkey & (left.objectvalue == null) && oper.trim() == "=")
253 {
254 return new paraminfo { sqlconditions = string.format(" ({0} is null) ", right.sqlconditions) };
255 }
256 else if (isvalueoperkey & (left.objectvalue == null) && oper.trim() == "<>")
257 {
258 return new paraminfo { sqlconditions = string.format(" ({0} is not null) ", right.sqlconditions) };
259 }
260
261 else if (iskeyopervalue)
262 {
263 var oldleft = addparametersreturnleft(ref left, right);
264 return new paraminfo { sqlconditions = string.format(" ({0} {1} @{2}) ", oldleft, oper, left.sqlconditions) };
265 }
266 else if (isvalueoperkey)
267 {
268 var oldright = addparametersreturnright(left, ref right);
269 return new paraminfo { sqlconditions = string.format(" (@{0} {1} {2}) ", right.sqlconditions, oper, oldright) };
270 }
271 else if (lefttype == membertype.right && righttype == membertype.right)
272 {
273 return new paraminfo { sqlconditions = string.format(" ('{0}' {1} '{2}') ", left.sqlconditions, oper, right.sqlconditions) };
274 }
275 else
276 {
277 return new paraminfo { sqlconditions = string.format(" ({0} {1} {2}) ", left.sqlconditions, oper, right.sqlconditions) };
278 }
279 }
280 private paraminfo getresolvemethodcall(expression exp, ref membertype type, bool isture)
281 {
282 methodcallexpression mce = (methodcallexpression)exp;
283 string methodname = mce.method.name;
284 if (methodname == "contains")
285 {
286 membertype lefttype = membertype.none;
287 membertype righttype = membertype.none;
288 if (mce.method.declaringtype != typeof(string) && mce.method.declaringtype.getinterface("ienumerable") != null)
289 {
290 var left = getresolveall(mce.arguments[0], ref righttype);
291 var right = getresolveall(mce.object, ref lefttype);
292 string oldleftkey = left.sqlconditions;
293
294 string leftkey = "p" + paraindex + left.sqlconditions;
295 paraindex++;
296 var sqlparameternames = "";
297 var membertype = membertype.right;
298 var list = getresolvemember(mce.object as memberexpression, ref membertype).objectvalue as ienumerable;
299 var count = 1;
300 foreach (var item in list)
301 {
302 var parametername = leftkey + count;
303 sqlparameternames += ",@" + parametername;
304 if (item == null)
305 {
306 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + parametername, dbnull.value));
307 }
308 else
309 {
310 this._parameters.add(dbprovider.makeparam(_dbconfig, "@" + parametername, item));
311 }
312 count++;
313 }
314 sqlparameternames = sqlparameternames.trimstart(',');
315 return new paraminfo { sqlconditions = string.format("({0} {1} in ({2}))", oldleftkey, isture == false ? " not " : "", sqlparameternames) };
316 }
317 else
318 {
319 var left = getresolveall(mce.object, ref lefttype);
320 var right = getresolveall(mce.arguments[0], ref righttype);
321 var oldleft = addparametersreturnleft(ref left, right);
322 return new paraminfo { sqlconditions = string.format("({0} {1} like '%'+@{2}+'%')", oldleft, isture == false ? " not " : "", left.sqlconditions) };
323 }
324 }
325 else if (methodname == "startswith")
326 {
327 membertype lefttype = membertype.none;
328 membertype righttype = membertype.none;
329 var left = getresolveall(mce.object, ref lefttype);
330 var right = getresolveall(mce.arguments[0], ref righttype);
331 var oldleft = addparametersreturnleft(ref left, right);
332 return new paraminfo { sqlconditions = string.format("({0} {1} like @{2}+'%')", oldleft, isture == false ? " not " : "", left.sqlconditions) };
333 }
334 else if (methodname == "endwith")
335 {
336 membertype lefttype = membertype.none;
337 membertype righttype = membertype.none;
338 var left = getresolveall(mce.object, ref lefttype);
339 var right = getresolveall(mce.arguments[0], ref righttype);
340 var oldleft = addparametersreturnleft(ref left, right);
341 return new paraminfo { sqlconditions = string.format("({0} {1} like '%'+@{2})", oldleft, isture == false ? " not " : "", left.sqlconditions) };
342 }
343 else if (methodname == "tostring")
344 {
345 type = membertype.right;
346 return getresolveall(mce.object, ref type);
347 }
348 else if (methodname.startswith("to"))
349 {
350 type = membertype.right;
351 return getresolveall(mce.arguments[0], ref type);
352 }
353 return new paraminfo();
354 }
355
356 private paraminfo getresolveconstant(expression exp, ref membertype type)
357 {
358 type = membertype.right;
359 constantexpression ce = ((constantexpression)exp);
360 if (ce.value == null)
361 {
362 return new paraminfo();
363 }
364 else
365 {
366 return new paraminfo { objectvalue = ce.value };
367 }
368 }
369 private paraminfo getresolveunary(expression exp, ref membertype type)
370 {
371 unaryexpression ue = ((unaryexpression)exp);
372 var mex = ue.operand;
373 return getresolveall(mex, ref type, false);
374 }
375
376 private paraminfo getresolvemembermethod(memberexpression exp)
377 {
378 var proinfo = exp.member as system.reflection.propertyinfo;
379 if (proinfo != null)
380 {
381 object dyninv = proinfo.getvalue(null, null);
382 return new paraminfo { objectvalue = dyninv };
383 }
384 else
385 {
386 var fieinfo = exp.member as system.reflection.fieldinfo;
387 if (fieinfo != null)
388 {
389 object dyninv = fieinfo.getvalue(null);
390 return new paraminfo { objectvalue = dyninv };
391 }
392 }
393 return new paraminfo();
394 }
395 private paraminfo getresolvememberconstant(memberexpression exp, object obj)
396 {
397 var proinfo = exp.member as system.reflection.propertyinfo;
398 if (proinfo != null)
399 {
400 var dyninv = proinfo.getvalue(obj, null);
401 return new paraminfo { objectvalue = dyninv };
402 }
403 else
404 {
405 var fieinfo = exp.member as system.reflection.fieldinfo;
406 if (fieinfo != null)
407 {
408 var dyninv = fieinfo.getvalue(obj);
409 return new paraminfo { objectvalue = dyninv };
410 }
411 }
412 return new paraminfo();
413 }
414 private paraminfo getresolvemember(expression exp, ref membertype type)
415 {
416 memberexpression me = ((memberexpression)exp);
417 if (me.expression == null)
418 {
419 type = membertype.right;
420 return getresolvemembermethod(me);
421 }
422
423 if (me.expression.nodetype != expressiontype.parameter)
424 {
425 type = membertype.right;
426 object dyninv = null;
427 try
428 {
429 var conexp = me.expression as constantexpression;
430 if (conexp != null)
431 {
432 return getresolvememberconstant(me, conexp.value);
433 }
434 else
435 {
436 var memberinfos = new stack<memberinfo>();
437 while (exp is memberexpression)
438 {
439 var memberexpr = exp as memberexpression;
440 memberinfos.push(memberexpr.member);
441 exp = memberexpr.expression;
442 }
443
444 var constexpr = exp as constantexpression;
445 if (constexpr == null)
446 {
447 var member = exp as memberexpression;
448 if (member == null)
449 {
450 throw new exception("不支持的子表达式" + me.member.name);
451 }
452 return getresolvemembermethod(member);
453 }
454 var objreference = constexpr.value;
455
456 while (memberinfos.count > 0)
457 {
458 var mi = memberinfos.pop();
459 if (mi.membertype == membertypes.property)
460 {
461 objreference = objreference.gettype().getproperty(mi.name).getvalue(objreference, null);
462 }
463 else if (mi.membertype == membertypes.field)
464 {
465 objreference = objreference.gettype().getfield(mi.name).getvalue(objreference);
466 }
467 }
468 dyninv = objreference;
469 }
470 }
471 catch (exception ex)
472 {
473 throw new exception("表达式解析出错(" + me.nodetype.tostring() + "):" + ex.message);
474 }
475
476 if (dyninv == null)
477 {
478 return new paraminfo();
479 }
480 else
481 {
482 return new paraminfo { objectvalue = dyninv };
483 }
484 }
485 else
486 {
487 string name = me.member.name;
488 type = membertype.left;
489 return new paraminfo { sqlconditions = name };
490 }
491 }
492
493 }
494
495 }
测试代码如下：
1 [testmethod]
2 public void testsqllmdresolve()
3 {
4 //orm数据映射
5 dbconfig.usedefaultconfig(new tmodeldbconfig(getdbpath()));
6
7
8 var where = getsqlwhere<cms_category>(m => m.enabled && m.name == "test");
9 console.writeline("lmdsql1:" + where);
10 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && m.enabled);
11 console.writeline("lmdsql2:" + where);
12
13 //条件优先级
14 where = getsqlwhere<cms_category>(m => (!m.enabled && m.name.contains("test") && m.enabled) || m.name.startswith("test"));
15 console.writeline("lmdsql3:" + where);
16 where = getsqlwhere<cms_category>(m => (m.enabled && m.name.contains("test") && m.enabled) || (m.name.startswith("test") && !m.isused && m.isused));
17
18 //其他判断
19 console.writeline("lmdsql4:" + where);
20 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && !m.enabled);
21 console.writeline("lmdsql5:" + where);
22 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && m.enabled == true);
23 console.writeline("lmdsql6:" + where);
24 where = getsqlwhere<cms_category>(m => m.name.contains("test") && m.enabled || m.name.startswith("test"));
25 console.writeline("lmdsql7:" + where);
26 where = getsqlwhere<cms_category>(m => m.enabled);
27 console.writeline("lmdsql8:" + where);
28 where = getsqlwhere<cms_category>(m => !m.enabled);
29 console.writeline("lmdsql9:" + where);
30 where = getsqlwhere<cms_category>(m => m.name.startswith("test"));
31 console.writeline("lmdsql10:" + where);
32 where = getsqlwhere<cms_category>(m => !m.name.startswith("test"));
33 console.writeline("lmdsql11:" + where);
34 where = getsqlwhere<cms_category>(m => m.name.startswith("test") || m.name.contains("test"));
35 console.writeline("lmdsql12:" + where);
36
37 //条件判断是否前包含，判断常量相等，多层判断
38 var extend = new cms_category_extend();
39 extend.mytest2 = new cms_category_extend();
40 extend.mytest2.mytest1 = new cms_category { name = "hehhe" };
41 where = getsqlwhere<cms_category>(m => m.name.startswith("test") || m.name == cms_category.testconst ||
42 m.name == extend.mytest2.mytest1.name);
43 console.writeline("lmdsql13:" + where);
44
45 //判断列表包含
46 var list = new list<string> { "a", "b", "c" };
47 where = getsqlwhere<cms_category>(m => list.contains(m.name));
48 console.writeline("lmdsql14:" + where);
49
50 object testname = "test";
51 where = getsqlwhere<cms_category>(m => m.enabled && m.name == (string)testname);
52 console.writeline("lmdsql15:" + where);
53 object testparent_id = 1;
54 //枚举判断
55 where = getsqlwhere<cms_category>(m => (m.id == (int)testparent_id) || (m.enabled && m.parent_id == status.success));
56 console.writeline("lmdsql16:" + where);
57
58 //静态字段判断
59 where = getsqlwhere<cms_category>(m => m.name == cms_category.teststatic);
60 console.writeline("lmdsql17:" + where);
61 }
62
63 private string getsqlwhere<t>(expression<func<t, bool>> expression)
64 {
65 sqllmdresolver exp = new sqllmdresolver();
66 exp.resolveexpression(expression);
67 return exp.sqlwhere + "\r\n" + string.join(",", exp.parameters.select(m => m.parametername + ":" + m.value.tostring()));
68 }
成品测试如下：
1 using system;
2 using system.collections.generic;
3 using system.collections.concurrent;
4 using system.linq;
5 using system.text;
6 using system.data;
7 using system.data.common;
8 using microsoft.visualstudio.testtools.unittesting;
9
10 using util.database;
11 using util.entitymapping;
12 using system.linq.expressions;
13 namespace util.unittest
14 {
15 [testclass]
16 public class testdatabase_unit
17 {
18
19 #region lmd生成sql条件测试
20
21
22 [testmethod]
23 public void testsqllmdresolve()
24 {
25 //orm数据映射
26 dbconfig.usedefaultconfig(new tmodeldbconfig(getdbpath()));
27
28
29 var where = getsqlwhere<cms_category>(m => m.enabled && m.name == "test");
30 console.writeline("lmdsql1:" + where);
31 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && m.enabled);
32 console.writeline("lmdsql2:" + where);
33
34 //条件优先级
35 where = getsqlwhere<cms_category>(m => (!m.enabled && m.name.contains("test") && m.enabled) || m.name.startswith("test"));
36 console.writeline("lmdsql3:" + where);
37 where = getsqlwhere<cms_category>(m => (m.enabled && m.name.contains("test") && m.enabled) || (m.name.startswith("test") && !m.isused && m.isused));
38
39 //其他判断
40 console.writeline("lmdsql4:" + where);
41 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && !m.enabled);
42 console.writeline("lmdsql5:" + where);
43 where = getsqlwhere<cms_category>(m => !m.enabled && m.name.contains("test") && m.enabled == true);
44 console.writeline("lmdsql6:" + where);
45 where = getsqlwhere<cms_category>(m => m.name.contains("test") && m.enabled || m.name.startswith("test"));
46 console.writeline("lmdsql7:" + where);
47 where = getsqlwhere<cms_category>(m => m.enabled);
48 console.writeline("lmdsql8:" + where);
49 where = getsqlwhere<cms_category>(m => !m.enabled);
50 console.writeline("lmdsql9:" + where);
51 where = getsqlwhere<cms_category>(m => m.name.startswith("test"));
52 console.writeline("lmdsql10:" + where);
53 where = getsqlwhere<cms_category>(m => !m.name.startswith("test"));
54 console.writeline("lmdsql11:" + where);
55 where = getsqlwhere<cms_category>(m => m.name.startswith("test") || m.name.contains("test"));
56 console.writeline("lmdsql12:" + where);
57
58 //条件判断是否前包含，判断常量相等，多层判断
59 var extend = new cms_category_extend();
60 extend.mytest2 = new cms_category_extend();
61 extend.mytest2.mytest1 = new cms_category { name = "hehhe" };
62 where = getsqlwhere<cms_category>(m => m.name.startswith("test") || m.name == cms_category.testconst ||
63 m.name == extend.mytest2.mytest1.name);
64 console.writeline("lmdsql13:" + where);
65
66 //判断列表包含
67 var list = new list<string> { "a", "b", "c" };
68 where = getsqlwhere<cms_category>(m => list.contains(m.name));
69 console.writeline("lmdsql14:" + where);
70
71 object testname = "test";
72 where = getsqlwhere<cms_category>(m => m.enabled && m.name == (string)testname);
73 console.writeline("lmdsql15:" + where);
74 object testparent_id = 1;
75 //枚举判断
76 where = getsqlwhere<cms_category>(m => (m.id == (int)testparent_id) || (m.enabled && m.parent_id == status.success));
77 console.writeline("lmdsql16:" + where);
78
79 //静态字段判断
80 where = getsqlwhere<cms_category>(m => m.name == cms_category.teststatic);
81 console.writeline("lmdsql17:" + where);
82 }
83
84 private string getsqlwhere<t>(expression<func<t, bool>> expression)
85 {
86 sqllmdresolver exp = new sqllmdresolver();
87 exp.resolveexpression(expression);
88 return exp.sqlwhere + "\r\n" + string.join(",", exp.parameters.select(m => m.parametername + ":" + m.value.tostring()));
89 }
90
91
92 #endregion
93
94 #region access orm测试
95
96 private string getdbpath()
97 {
98 var path = appdomain.currentdomain.basedirectory;
99 if (path.endswith("debug", stringcomparison.ordinalignorecase))
100 {
101 path = path.substring(0, path.lastindexof('\\'));
102 path = path.substring(0, path.lastindexof('\\'));
103 path = path.substring(0, path.lastindexof('\\'));
104 }
105 path = path.trimend('\\') + @"\database";
106 return path;
107 }
108 [testmethod]
109 public void testdbconfig()
110 {
111 //初始化配置
112 dbconfig.usedefaultconfig(new tmodeldbconfig(getdbpath()));
113
114 //t4模版获取数据库信息
115 list<tableinfo> list = dbfactory.getshematables();
116 console.writeline(list.count.tostring());
117 }
118
119
120 [testmethod]
121 public void testaccessorm()
122 {
123
124 //orm数据映射
125 dbconfig.usedefaultconfig(new tmodeldbconfig(getdbpath()));
126 console.writeline("start loadding...");
127 console.writeline(new cms_category().query(m => m.name == "城市").tocount());
128 var cat = new cms_category().query(m => m.name == "城市").sortasc(m => m.name).tomodel();
129 console.writeline(cat.name);
130
131 //设置只更新部分
132 //cat.setparthandled();
133 //cat.description = "test";
134 //cat.update(m=>m.id == 1);
135
136 console.writeline(cat.tovalue(m => m.name));
137 console.writeline(new cms_category().query(m => m.name == "城市").tolist()[0].name);
138 console.writeline(new cms_category().query(m => m.name == "城市" && m.id > 0 && m.name == "" || (m.id == 0 || m.name == "")).tocount());
139 //指定条件规则查询
140 console.writeline(new cms_category().query(m => (m.name == "城市" && (m.id > 0 || m.name == "")) || (m.id == 0 || m.name == "")).tocount());
141
142 var citylist = new list<string> { "城市", "b", "c" };
143 var layer = new layermodel { list = citylist };
144 console.writeline(new cms_category().query(m => m.name == "城市" || citylist.contains(m.name) || m.parent_id == status.success).tocount());
145 console.writeline(new cms_category().query(m => m.name == "城市" || layer.list.contains(m.name)).tocount());
146
147
148 //获取全部
149 var datslist = new cms_category().query().tolist();
150 console.writeline(datslist.count);
151 //获取n条
152 datslist = new cms_category().query().tolist(6);
153 console.writeline(datslist.count);
154 //获取部分
155 var partlist = new cms_category().query().topartlist(6, "id", "name").select(m => new cms_category
156 {
157 id = int.parse(m[0]),
158 name = m[1]
159 }).tolist();
160 console.writeline(partlist.count);
161 //分页查询
162 var mapper = new cms_category().query();
163 var datacount = mapper.tocount();
164 datslist = mapper.tolist(20, 1, datacount);
165 console.writeline(datslist.count);
166 //条件拼接查询
167 mapper.and(m => m.name == "test")
168 .and(m => m.id > 0)
169 .or(m => m.parent_id > 0);
170 mapper.or(m => m.parent_id > 0);
171
172
173
174 var channels = new cms_channel().query().tolist();
175 console.writeline(channels.count);
176 var grade = new ucl_grade { id = 5 };
177 grade.grade_name = "新手1";
178 var dal = new uclgradedataaccess(grade);
179 //保持数据库连接
180 using (var db = new dbbuilder(new tmodeldbconfig(getdbpath())).keepconnect())
181 {
182 //使用数据库db操作并跟踪实体修改状态
183 dal.usedatabase(db).setparthandled();
184 grade.grade = 8;
185 grade.grade_name = "新手";
186 dal.update();
187 }
188 //db销毁后重连数据库
189 console.writeline(dal.tovalue(m => m.grade_name));
190
191
192 //使用事务(在事务中处理)
193 using (var db = new dbbuilder(new tmodeldbconfig(getdbpath())).keepconnect())
194 {
195 try
196 {
197 db.begintransaction();
198 //todo:something
199 //使用数据库db操作并跟踪实体修改状态
200 dal.usedatabase(db).setparthandled();
201 grade.grade = 8;
202 grade.grade_name = "新手";
203 dal.update();
204 db.committransaction();
205 }
206 catch (exception ex)
207 {
208 db.rollbacktransaction();
209 }
210 }
211
212 //使用事务(批处理事务)
213 var parlist = new list<dbparaminfo>();
214 //添加到批处理事务中，如果执行失败则回滚事务
215 parlist.add(dal.getupdatedbparaminfo().useverifyexecresult());
216 //todo:添加其他操作到parlist
217 var execcount = new dbbuilder(new tmodeldbconfig(getdbpath())).executesqltran(parlist);
218 console.writeline(execcount);
219 }
220
221
222 [testmethod]
223 public void testmappingfield()
224 {
225 var cat = new cms_category();
226 var watch = system.diagnostics.stopwatch.startnew();
227 var eachcount = 100000;
228 for (var i = 0; i < eachcount; i++)
229 {
230 var field = new cms_category().expfield(f => f.name);
231 }
232 watch.stop();
233 console.writeline("linq反射取" + eachcount + "次字段毫秒数:" + watch.elapsedmilliseconds);
234 }
235
236
237
238 //===============================================================================================
239 //access 测试配置类
240 //===============================================================================================
241 public class tmodeldbconfig : dbconfig
242 {
243 public static void dbwriteloginfo(string info, string title, string logpath, string encoding)
244 {
245 console.writeline("dblog:" + info);
246 }
247 public tmodeldbconfig(string solutiondir) : base("system.data.oledb",
248 @"provider=microsoft.jet.oledb.4.0;data source=" + solutiondir + @"\plugnt_cms.mdb;user id=;password=;",
249 dbwriteloginfo)
250 { }
251
252 }
253
254
255 [table("cms_channel")]
256 public partial class cms_channel : basemapper<cms_channel>
257 {
258 public int id { get; set; }
259 public string no { get; set; }
260 public string title { get; set; }
261 }
262 public class layermodel
263 {
264 public list<string> list { get; set; }
265 }
266 public partial class cms_category : basemapper<cms_category>
267 {
268
269 public static string teststatic = "teststatic";
270 public const string testconst = "testconst";
271
272 public int id { get; set; }
273 public string name { get; set; }
274 //public int parent_id { get; set; }
275 public status parent_id { get; set; }
276
277 [obsolete("test")]
278 public bool enabled { get; set; }
279 [obsolete("test")]
280 public bool isused { get; set; }
281
282
283 public override string tablename
284 {
285 get { return "cms_category"; }
286 }
287 protected override cms_category convertentity(idatareader reader)
288 {
289 return new cms_category
290 {
291 id = int.parse(reader["id"].tostring()),
292 name = reader["name"].tostring(),
293 parent_id = (status)int.parse(reader["parent_id"].tostring()),
294 };
295 }
296 protected override list<dbfieldinfo> convertfields(cms_category model)
297 {
298 return new list<dbfieldinfo>
299 {
300 new dbfieldinfo { name = "id", value = model.id , isidentity =true },
301 new dbfieldinfo { name = "name", value = model.name },
302 new dbfieldinfo { name = "parent_id", value = model.parent_id },
303 };
304 }
305 }
306
307 public class cms_category_extend : cms_category
308 {
309 public cms_category mytest1 { get; set; }
310 public cms_category_extend mytest2 { get; set; }
311 public string myname { get; set; }
312 }
313 public class ucl_grade
314 {
315 public int id { get; set; }
316 public int grade { get; set; }
317 public string grade_name { get; set; }
318 }
319
320 public class uclgradedataaccess : basemapper<ucl_grade>
321 {
322 public uclgradedataaccess(ucl_grade model = null)
323 {
324 contextentity = model;
325 }
326 public override string tablename
327 {
328 get { return "ucl_grade"; }
329 }
330 protected override ucl_grade convertentity(idatareader reader)
331 {
332 return new ucl_grade
333 {
334 id = int.parse(reader["id"].tostring()),
335 grade = int.parse(reader["grade"].tostring()),
336 grade_name = reader["grade_name"].tostring(),
337 };
338 }
339 protected override list<dbfieldinfo> convertfields(ucl_grade model)
340 {
341 return new list<dbfieldinfo>
342 {
343 new dbfieldinfo { name = "id", value = model.id , isprimarykey =true , isidentity =true },
344 new dbfieldinfo { name = "grade", value = model.grade },
345 new dbfieldinfo { name = "grade_name", value = model.grade_name },
346 };
347 }
348 }
349 public enum status
350 {
351 success
352 }
353
354 #endregion
355
356 }
357 }
部分测试截图：
orm成品开源项目地址
https://github.com/plugnt/util6