一个类搞定SQL条件映射解析，实现轻量简单实用ORM功能
2024-09-03 19:46:29
个人觉得轻简级的ORM既要支持强类型编码，又要有执行效率，还要通俗易懂给开发者友好提示，结合Expression可轻松定制自己所需要功能。
Orm成品开源项目地址
https://github.com/PlugNT/util6
表达式解析类：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Reflection;
using System.Text.RegularExpressions; using Util.Database;
namespace Util.EntityMapping
{ public class SqlLmdResolver
{ internal int ParaIndex = ; public string _SqlWhere = null;
public string SqlWhere
{
get { return _SqlWhere; }
} private List<DbParameter> _Parameters = null;
public List<DbParameter> Parameters
{
get { return _Parameters; }
} private DbConfig _DbConfig = null; public SqlLmdResolver(DbConfig config = null)
{
_DbConfig = config ?? DbConfig.Default;
_SqlWhere = string.Empty;
_Parameters = new List<DbParameter>();
} public void ResolveExpression(Expression expression = null, SqlWhereType whereType = SqlWhereType.And)
{
if (expression == null)
{
_SqlWhere = string.Empty;
return;
}
var sqlFormat = (whereType == SqlWhereType.And) ? " AND {0} " : " OR {0} ";
SqlLmdResolver.MemberType type = SqlLmdResolver.MemberType.None;
this._SqlWhere = string.Format(sqlFormat, GetResolveAll(expression, ref type).SqlConditions);
} private enum MemberType
{
None = ,
Left = ,
Right =
} private struct ParamInfo
{
public string SqlConditions;
public object ObjectValue;
} private string AddParametersReturnLeft(ref ParamInfo left, ParamInfo right)
{
string oldLeftKey = left.SqlConditions;
left.SqlConditions = "P"+ ParaIndex + oldLeftKey;
ParaIndex++;
if (right.ObjectValue == null)
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + left.SqlConditions, DBNull.Value));
}
else
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + left.SqlConditions, right.ObjectValue));
}
return oldLeftKey;
}
private string AddParametersReturnRight(ParamInfo left, ref ParamInfo right)
{
string oldRightKey = right.SqlConditions;
right.SqlConditions = "P" + ParaIndex + oldRightKey;
ParaIndex++;
if (left.ObjectValue == null)
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + right.SqlConditions, DBNull.Value));
}
else
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + right.SqlConditions, left.ObjectValue));
}
return oldRightKey;
} private string GetOperator(ExpressionType expressiontype)
{
switch (expressiontype)
{
case ExpressionType.And:
case ExpressionType.AndAlso:
return " AND ";
case ExpressionType.Equal:
return " =";
case ExpressionType.GreaterThan:
return " >";
case ExpressionType.GreaterThanOrEqual:
return ">=";
case ExpressionType.LessThan:
return "<";
case ExpressionType.LessThanOrEqual:
return "<=";
case ExpressionType.NotEqual:
return "<>";
case ExpressionType.Or:
case ExpressionType.OrElse:
return " OR ";
case ExpressionType.Add:
case ExpressionType.AddChecked:
return "+";
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
return "-";
case ExpressionType.Divide:
return "/";
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
return "*";
default:
throw new Exception(string.Format("不支持{0}此种运算符查找！", expressiontype.ToString()));
}
} private ParamInfo GetResolveAll(Expression exp, ref MemberType type, bool isTure = true)
{
if (exp is LambdaExpression)
{
return GetResolveLambda(exp);
}
else if (exp is BinaryExpression)
{
return GetResolveBinary(exp);
}
else if (exp is MethodCallExpression)
{
return GetResolveMethodCall(exp, ref type, isTure);
}
else if (exp is ConstantExpression)
{
return GetResolveConstant(exp, ref type);
}
else if (exp is MemberExpression)
{
return GetResolveMember(exp, ref type);
}
else if (exp is UnaryExpression)
{
return GetResolveUnary(exp, ref type);
}
return new ParamInfo();
} private ParamInfo GetResolveLambda(Expression exp)
{
LambdaExpression lambda = exp as LambdaExpression;
var expression = lambda.Body;
MemberType EleType = MemberType.None; if (expression is UnaryExpression)
{
var me = expression as UnaryExpression;
if (me.Operand is MemberExpression)
{
var ime = me.Operand as MemberExpression;
return new ParamInfo { SqlConditions = ime.Member.Name.ToString() + "=0" };
}
}
if (expression is MemberExpression)
{
var me = expression as MemberExpression;
return new ParamInfo { SqlConditions = me.Member.Name.ToString() + "=1" };
}
return GetResolveAll(expression, ref EleType);
}
private ParamInfo GetResolveBinary(Expression exp)
{
var expression = exp as BinaryExpression;
MemberType leftType = MemberType.None;
MemberType rightType = MemberType.None; var left = GetResolveAll(expression.Left, ref leftType);
var right = GetResolveAll(expression.Right, ref rightType);
var oper = GetOperator(expression.NodeType);
var isKeyOperValue = leftType == MemberType.Left && rightType == MemberType.Right;
var isValueOperKey = rightType == MemberType.Left && leftType == MemberType.Right; if (leftType == MemberType.Left && rightType == MemberType.None)
{
if (expression.Left is UnaryExpression)
{
var me = expression.Left as UnaryExpression;
if (me.Operand is MemberExpression)
{
left.SqlConditions = left.SqlConditions + "=0";
}
}
else if (expression.Left is MemberExpression)
{
left.SqlConditions = left.SqlConditions + "=1";
}
}
if (leftType == MemberType.None && rightType == MemberType.Left)
{
if (expression.Right is UnaryExpression)
{
var me = expression.Right as UnaryExpression;
if (me.Operand is MemberExpression)
{
right.SqlConditions = right.SqlConditions + "=0";
}
}
else if (expression.Right is MemberExpression)
{
right.SqlConditions = right.SqlConditions + "=1";
}
} if (isKeyOperValue & (right.ObjectValue == null) && oper.Trim() == "=")
{
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format(" ({0} is null) ", oldLeft) };
}
else if (isKeyOperValue & (right.ObjectValue == null) && oper.Trim() == "<>")
{
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format(" ({0} is not null) ", oldLeft) };
}
else if (isValueOperKey & (left.ObjectValue == null) && oper.Trim() == "=")
{
return new ParamInfo { SqlConditions = string.Format(" ({0} is null) ", right.SqlConditions) };
}
else if (isValueOperKey & (left.ObjectValue == null) && oper.Trim() == "<>")
{
return new ParamInfo { SqlConditions = string.Format(" ({0} is not null) ", right.SqlConditions) };
} else if (isKeyOperValue)
{
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format(" ({0} {1} @{2}) ", oldLeft, oper, left.SqlConditions) };
}
else if (isValueOperKey)
{
var oldRight = AddParametersReturnRight(left, ref right);
return new ParamInfo { SqlConditions = string.Format(" (@{0} {1} {2}) ", right.SqlConditions, oper, oldRight) };
}
else if (leftType == MemberType.Right && rightType == MemberType.Right)
{
return new ParamInfo { SqlConditions = string.Format(" ('{0}' {1} '{2}') ", left.SqlConditions, oper, right.SqlConditions) };
}
else
{
return new ParamInfo { SqlConditions = string.Format(" ({0} {1} {2}) ", left.SqlConditions, oper, right.SqlConditions) };
}
}
private ParamInfo GetResolveMethodCall(Expression exp, ref MemberType type, bool isTure)
{
MethodCallExpression mce = (MethodCallExpression)exp;
string methodName = mce.Method.Name;
if (methodName == "Contains")
{
MemberType leftType = MemberType.None;
MemberType rightType = MemberType.None;
if (mce.Method.DeclaringType != typeof(string) && mce.Method.DeclaringType.GetInterface("IEnumerable") != null)
{
var left = GetResolveAll(mce.Arguments[], ref rightType);
var right = GetResolveAll(mce.Object, ref leftType);
string oldLeftKey = left.SqlConditions; string leftKey = "P" + ParaIndex + left.SqlConditions;
ParaIndex++;
var sqlParameterNames = "";
var memberType = MemberType.Right;
var list = GetResolveMember(mce.Object as MemberExpression, ref memberType).ObjectValue as IEnumerable;
var count = ;
foreach (var item in list)
{
var parameterName = leftKey + count;
sqlParameterNames += ",@" + parameterName;
if (item == null)
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + parameterName, DBNull.Value));
}
else
{
this._Parameters.Add(DbProvider.MakeParam(_DbConfig, "@" + parameterName, item));
}
count++;
}
sqlParameterNames = sqlParameterNames.TrimStart(',');
return new ParamInfo { SqlConditions = string.Format("({0} {1} IN ({2}))", oldLeftKey, isTure == false ? " NOT " : "", sqlParameterNames) };
}
else
{
var left = GetResolveAll(mce.Object, ref leftType);
var right = GetResolveAll(mce.Arguments[], ref rightType);
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format("({0} {1} LIKE '%'+@{2}+'%')", oldLeft, isTure == false ? " NOT " : "", left.SqlConditions) };
}
}
else if (methodName == "StartsWith")
{
MemberType leftType = MemberType.None;
MemberType rightType = MemberType.None;
var left = GetResolveAll(mce.Object, ref leftType);
var right = GetResolveAll(mce.Arguments[], ref rightType);
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format("({0} {1} LIKE @{2}+'%')", oldLeft, isTure == false ? " NOT " : "", left.SqlConditions) };
}
else if (methodName == "EndWith")
{
MemberType leftType = MemberType.None;
MemberType rightType = MemberType.None;
var left = GetResolveAll(mce.Object, ref leftType);
var right = GetResolveAll(mce.Arguments[], ref rightType);
var oldLeft = AddParametersReturnLeft(ref left, right);
return new ParamInfo { SqlConditions = string.Format("({0} {1} LIKE '%'+@{2})", oldLeft, isTure == false ? " NOT " : "", left.SqlConditions) };
}
else if (methodName == "ToString")
{
type = MemberType.Right;
return GetResolveAll(mce.Object, ref type);
}
else if (methodName.StartsWith("To"))
{
type = MemberType.Right;
return GetResolveAll(mce.Arguments[], ref type);
}
return new ParamInfo();
} private ParamInfo GetResolveConstant(Expression exp, ref MemberType type)
{
type = MemberType.Right;
ConstantExpression ce = ((ConstantExpression)exp);
if (ce.Value == null)
{
return new ParamInfo();
}
else
{
return new ParamInfo { ObjectValue = ce.Value };
}
}
private ParamInfo GetResolveUnary(Expression exp, ref MemberType type)
{
UnaryExpression ue = ((UnaryExpression)exp);
var mex = ue.Operand;
return GetResolveAll(mex, ref type, false);
} private ParamInfo GetResolveMemberMethod(MemberExpression exp)
{
var proInfo = exp.Member as System.Reflection.PropertyInfo;
if (proInfo != null)
{
object dynInv = proInfo.GetValue(null, null);
return new ParamInfo { ObjectValue = dynInv };
}
else
{
var fieInfo = exp.Member as System.Reflection.FieldInfo;
if (fieInfo != null)
{
object dynInv = fieInfo.GetValue(null);
return new ParamInfo { ObjectValue = dynInv };
}
}
return new ParamInfo();
}
private ParamInfo GetResolveMemberConstant(MemberExpression exp, object obj)
{
var proInfo = exp.Member as System.Reflection.PropertyInfo;
if (proInfo != null)
{
var dynInv = proInfo.GetValue(obj, null);
return new ParamInfo { ObjectValue = dynInv };
}
else
{
var fieInfo = exp.Member as System.Reflection.FieldInfo;
if (fieInfo != null)
{
var dynInv = fieInfo.GetValue(obj);
return new ParamInfo { ObjectValue = dynInv };
}
}
return new ParamInfo();
}
private ParamInfo GetResolveMember(Expression exp, ref MemberType type)
{
MemberExpression me = ((MemberExpression)exp);
if (me.Expression == null)
{
type = MemberType.Right;
return GetResolveMemberMethod(me);
} if (me.Expression.NodeType != ExpressionType.Parameter)
{
type = MemberType.Right;
object dynInv = null;
try
{
var conExp = me.Expression as ConstantExpression;
if (conExp != null)
{
return GetResolveMemberConstant(me, conExp.Value);
}
else
{
var memberInfos = new Stack<MemberInfo>();
while (exp is MemberExpression)
{
var memberExpr = exp as MemberExpression;
memberInfos.Push(memberExpr.Member);
exp = memberExpr.Expression;
} var constExpr = exp as ConstantExpression;
if (constExpr == null)
{
var member = exp as MemberExpression;
if (member == null)
{
throw new Exception("不支持的子表达式" + me.Member.Name);
}
return GetResolveMemberMethod(member);
}
var objReference = constExpr.Value; while (memberInfos.Count > )
{
var mi = memberInfos.Pop();
if (mi.MemberType == MemberTypes.Property)
{
objReference = objReference.GetType().GetProperty(mi.Name).GetValue(objReference, null);
}
else if (mi.MemberType == MemberTypes.Field)
{
objReference = objReference.GetType().GetField(mi.Name).GetValue(objReference);
}
}
dynInv = objReference;
}
}
catch (Exception ex)
{
throw new Exception("表达式解析出错(" + me.NodeType.ToString() + "):" + ex.Message);
} if (dynInv == null)
{
return new ParamInfo();
}
else
{
return new ParamInfo { ObjectValue = dynInv };
}
}
else
{
string name = me.Member.Name;
type = MemberType.Left;
return new ParamInfo { SqlConditions = name };
}
} } }
测试代码如下：
[TestMethod]
public void TestSqlLmdResolve()
{
//ORM数据映射
DbConfig.UseDefaultConfig(new TModelDbConfig(GetDbPath())); var where = GetSqlWhere<cms_category>(m => m.enabled && m.name == "test");
Console.WriteLine("LmdSql1:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && m.enabled);
Console.WriteLine("LmdSql2:" + where); //条件优先级
where = GetSqlWhere<cms_category>(m => (!m.enabled && m.name.Contains("test") && m.enabled) || m.name.StartsWith("test"));
Console.WriteLine("LmdSql3:" + where);
where = GetSqlWhere<cms_category>(m => (m.enabled && m.name.Contains("test") && m.enabled) || (m.name.StartsWith("test") && !m.isused && m.isused)); //其他判断
Console.WriteLine("LmdSql4:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && !m.enabled);
Console.WriteLine("LmdSql5:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && m.enabled == true);
Console.WriteLine("LmdSql6:" + where);
where = GetSqlWhere<cms_category>(m => m.name.Contains("test") && m.enabled || m.name.StartsWith("test"));
Console.WriteLine("LmdSql7:" + where);
where = GetSqlWhere<cms_category>(m => m.enabled);
Console.WriteLine("LmdSql8:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled);
Console.WriteLine("LmdSql9:" + where);
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test"));
Console.WriteLine("LmdSql10:" + where);
where = GetSqlWhere<cms_category>(m => !m.name.StartsWith("test"));
Console.WriteLine("LmdSql11:" + where);
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test") || m.name.Contains("test"));
Console.WriteLine("LmdSql12:" + where); //条件判断是否前包含，判断常量相等，多层判断
var extend = new cms_category_extend();
extend.mytest2 = new cms_category_extend();
extend.mytest2.mytest1 = new cms_category { name = "hehhe" };
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test") || m.name == cms_category.TestConst ||
m.name == extend.mytest2.mytest1.name);
Console.WriteLine("LmdSql13:" + where); //判断列表包含
var list = new List<string> { "a", "b", "c" };
where = GetSqlWhere<cms_category>(m => list.Contains(m.name));
Console.WriteLine("LmdSql14:" + where); object testName = "test";
where = GetSqlWhere<cms_category>(m => m.enabled && m.name == (string)testName);
Console.WriteLine("LmdSql15:" + where);
object testParent_id = ;
//枚举判断
where = GetSqlWhere<cms_category>(m => (m.id == (int)testParent_id) || (m.enabled && m.parent_id == Status.Success));
Console.WriteLine("LmdSql16:" + where); //静态字段判断
where = GetSqlWhere<cms_category>(m => m.name == cms_category.TestStatic);
Console.WriteLine("LmdSql17:" + where);
} private string GetSqlWhere<T>(Expression<Func<T, bool>> expression)
{
SqlLmdResolver exp = new SqlLmdResolver();
exp.ResolveExpression(expression);
return exp.SqlWhere + "\r\n" + string.Join(",", exp.Parameters.Select(m => m.ParameterName + ":" + m.Value.ToString()));
}
成品测试如下：
using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting; using Util.Database;
using Util.EntityMapping;
using System.Linq.Expressions;
namespace Util.UnitTest
{
[TestClass]
public class TestDatabase_Unit
{ #region lmd生成sql条件测试 [TestMethod]
public void TestSqlLmdResolve()
{
//ORM数据映射
DbConfig.UseDefaultConfig(new TModelDbConfig(GetDbPath())); var where = GetSqlWhere<cms_category>(m => m.enabled && m.name == "test");
Console.WriteLine("LmdSql1:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && m.enabled);
Console.WriteLine("LmdSql2:" + where); //条件优先级
where = GetSqlWhere<cms_category>(m => (!m.enabled && m.name.Contains("test") && m.enabled) || m.name.StartsWith("test"));
Console.WriteLine("LmdSql3:" + where);
where = GetSqlWhere<cms_category>(m => (m.enabled && m.name.Contains("test") && m.enabled) || (m.name.StartsWith("test") && !m.isused && m.isused)); //其他判断
Console.WriteLine("LmdSql4:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && !m.enabled);
Console.WriteLine("LmdSql5:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled && m.name.Contains("test") && m.enabled == true);
Console.WriteLine("LmdSql6:" + where);
where = GetSqlWhere<cms_category>(m => m.name.Contains("test") && m.enabled || m.name.StartsWith("test"));
Console.WriteLine("LmdSql7:" + where);
where = GetSqlWhere<cms_category>(m => m.enabled);
Console.WriteLine("LmdSql8:" + where);
where = GetSqlWhere<cms_category>(m => !m.enabled);
Console.WriteLine("LmdSql9:" + where);
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test"));
Console.WriteLine("LmdSql10:" + where);
where = GetSqlWhere<cms_category>(m => !m.name.StartsWith("test"));
Console.WriteLine("LmdSql11:" + where);
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test") || m.name.Contains("test"));
Console.WriteLine("LmdSql12:" + where); //条件判断是否前包含，判断常量相等，多层判断
var extend = new cms_category_extend();
extend.mytest2 = new cms_category_extend();
extend.mytest2.mytest1 = new cms_category { name = "hehhe" };
where = GetSqlWhere<cms_category>(m => m.name.StartsWith("test") || m.name == cms_category.TestConst ||
m.name == extend.mytest2.mytest1.name);
Console.WriteLine("LmdSql13:" + where); //判断列表包含
var list = new List<string> { "a", "b", "c" };
where = GetSqlWhere<cms_category>(m => list.Contains(m.name));
Console.WriteLine("LmdSql14:" + where); object testName = "test";
where = GetSqlWhere<cms_category>(m => m.enabled && m.name == (string)testName);
Console.WriteLine("LmdSql15:" + where);
object testParent_id = ;
//枚举判断
where = GetSqlWhere<cms_category>(m => (m.id == (int)testParent_id) || (m.enabled && m.parent_id == Status.Success));
Console.WriteLine("LmdSql16:" + where); //静态字段判断
where = GetSqlWhere<cms_category>(m => m.name == cms_category.TestStatic);
Console.WriteLine("LmdSql17:" + where);
} private string GetSqlWhere<T>(Expression<Func<T, bool>> expression)
{
SqlLmdResolver exp = new SqlLmdResolver();
exp.ResolveExpression(expression);
return exp.SqlWhere + "\r\n" + string.Join(",", exp.Parameters.Select(m => m.ParameterName + ":" + m.Value.ToString()));
} #endregion #region access orm测试 private string GetDbPath()
{
var path = AppDomain.CurrentDomain.BaseDirectory;
if (path.EndsWith("debug", StringComparison.OrdinalIgnoreCase))
{
path = path.Substring(, path.LastIndexOf('\\'));
path = path.Substring(, path.LastIndexOf('\\'));
path = path.Substring(, path.LastIndexOf('\\'));
}
path = path.TrimEnd('\\') + @"\DataBase";
return path;
}
[TestMethod]
public void TestDbConfig()
{
//初始化配置
DbConfig.UseDefaultConfig(new TModelDbConfig(GetDbPath())); //T4模版获取数据库信息
List<TableInfo> list = DbFactory.GetShemaTables();
Console.WriteLine(list.Count.ToString());
} [TestMethod]
public void TestAccessOrm()
{ //ORM数据映射
DbConfig.UseDefaultConfig(new TModelDbConfig(GetDbPath()));
Console.WriteLine("Start loadding...");
Console.WriteLine(new cms_category().Query(m => m.name == "城市").ToCount());
var cat = new cms_category().Query(m => m.name == "城市").SortAsc(m => m.name).ToModel();
Console.WriteLine(cat.name); //设置只更新部分
//cat.SetPartHandled();
//cat.description = "test";
//cat.Update(m=>m.id == 1); Console.WriteLine(cat.ToValue(m => m.name));
Console.WriteLine(new cms_category().Query(m => m.name == "城市").ToList()[].name);
Console.WriteLine(new cms_category().Query(m => m.name == "城市" && m.id > && m.name == "" || (m.id == || m.name == "")).ToCount());
//指定条件规则查询
Console.WriteLine(new cms_category().Query(m => (m.name == "城市" && (m.id > || m.name == "")) || (m.id == || m.name == "")).ToCount()); var cityList = new List<string> { "城市", "b", "c" };
var layer = new LayerModel { List = cityList };
Console.WriteLine(new cms_category().Query(m => m.name == "城市" || cityList.Contains(m.name) || m.parent_id == Status.Success).ToCount());
Console.WriteLine(new cms_category().Query(m => m.name == "城市" || layer.List.Contains(m.name)).ToCount()); //获取全部
var datsList = new cms_category().Query().ToList();
Console.WriteLine(datsList.Count);
//获取N条
datsList = new cms_category().Query().ToList();
Console.WriteLine(datsList.Count);
//获取部分
var partList = new cms_category().Query().ToPartList(, "id", "name").Select(m => new cms_category
{
id = int.Parse(m[]),
name = m[]
}).ToList();
Console.WriteLine(partList.Count);
//分页查询
var mapper = new cms_category().Query();
var dataCount = mapper.ToCount();
datsList = mapper.ToList(, , dataCount);
Console.WriteLine(datsList.Count);
//条件拼接查询
mapper.And(m => m.name == "test")
.And(m => m.id > )
.Or(m => m.parent_id > );
mapper.Or(m => m.parent_id > ); var channels = new cms_channel().Query().ToList();
Console.WriteLine(channels.Count);
var grade = new ucl_grade { id = };
grade.grade_name = "新手1";
var dal = new UclGradeDataAccess(grade);
//保持数据库连接
using (var db = new DbBuilder(new TModelDbConfig(GetDbPath())).KeepConnect())
{
//使用数据库db操作并跟踪实体修改状态
dal.UseDatabase(db).SetPartHandled();
grade.grade = ;
grade.grade_name = "新手";
dal.Update();
}
//db销毁后重连数据库
Console.WriteLine(dal.ToValue(m => m.grade_name)); //使用事务(在事务中处理)
using (var db = new DbBuilder(new TModelDbConfig(GetDbPath())).KeepConnect())
{
try
{
db.BeginTransaction();
//TODO:something
//使用数据库db操作并跟踪实体修改状态
dal.UseDatabase(db).SetPartHandled();
grade.grade = ;
grade.grade_name = "新手";
dal.Update();
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
} //使用事务(批处理事务)
var parList = new List<DbParamInfo>();
//添加到批处理事务中，如果执行失败则回滚事务
parList.Add(dal.GetUpdateDbParamInfo().UseVerifyExecResult());
//TODO:添加其他操作到parList
var execCount = new DbBuilder(new TModelDbConfig(GetDbPath())).ExecuteSqlTran(parList);
Console.WriteLine(execCount);
} [TestMethod]
public void TestMappingField()
{
var cat = new cms_category();
var watch = System.Diagnostics.Stopwatch.StartNew();
var eachCount = ;
for (var i = ; i < eachCount; i++)
{
var field = new cms_category().ExpField(f => f.name);
}
watch.Stop();
Console.WriteLine("Linq反射取" + eachCount + "次字段毫秒数:" + watch.ElapsedMilliseconds);
} //===============================================================================================
//access 测试配置类
//===============================================================================================
public class TModelDbConfig : DbConfig
{
public static void DBWriteLogInfo(string info, string title, string logpath, string encoding)
{
Console.WriteLine("dblog:" + info);
}
public TModelDbConfig(string solutionDir) : base("System.Data.OleDb",
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + solutionDir + @"\PlugNT_CMS.mdb;User ID=;Password=;",
DBWriteLogInfo)
{ } } [Table("cms_channel")]
public partial class cms_channel : BaseMapper<cms_channel>
{
public int id { get; set; }
public string no { get; set; }
public string title { get; set; }
}
public class LayerModel
{
public List<string> List { get; set; }
}
public partial class cms_category : BaseMapper<cms_category>
{ public static string TestStatic = "TestStatic";
public const string TestConst = "TestConst"; public int id { get; set; }
public string name { get; set; }
//public int parent_id { get; set; }
public Status parent_id { get; set; } [Obsolete("test")]
public bool enabled { get; set; }
[Obsolete("test")]
public bool isused { get; set; } public override string TableName
{
get { return "cms_category"; }
}
protected override cms_category ConvertEntity(IDataReader reader)
{
return new cms_category
{
id = int.Parse(reader["id"].ToString()),
name = reader["name"].ToString(),
parent_id = (Status)int.Parse(reader["parent_id"].ToString()),
};
}
protected override List<DbFieldInfo> ConvertFields(cms_category model)
{
return new List<DbFieldInfo>
{
new DbFieldInfo { Name = "id", Value = model.id , IsIdentity =true },
new DbFieldInfo { Name = "name", Value = model.name },
new DbFieldInfo { Name = "parent_id", Value = model.parent_id },
};
}
} public class cms_category_extend : cms_category
{
public cms_category mytest1 { get; set; }
public cms_category_extend mytest2 { get; set; }
public string myname { get; set; }
}
public class ucl_grade
{
public int id { get; set; }
public int grade { get; set; }
public string grade_name { get; set; }
} public class UclGradeDataAccess : BaseMapper<ucl_grade>
{
public UclGradeDataAccess(ucl_grade model = null)
{
ContextEntity = model;
}
public override string TableName
{
get { return "ucl_grade"; }
}
protected override ucl_grade ConvertEntity(IDataReader reader)
{
return new ucl_grade
{
id = int.Parse(reader["id"].ToString()),
grade = int.Parse(reader["grade"].ToString()),
grade_name = reader["grade_name"].ToString(),
};
}
protected override List<DbFieldInfo> ConvertFields(ucl_grade model)
{
return new List<DbFieldInfo>
{
new DbFieldInfo { Name = "id", Value = model.id , IsPrimaryKey =true , IsIdentity =true },
new DbFieldInfo { Name = "grade", Value = model.grade },
new DbFieldInfo { Name = "grade_name", Value = model.grade_name },
};
}
}
public enum Status
{
Success
} #endregion }
}
部分测试截图：
Orm成品开源项目地址
https://github.com/PlugNT/util6
最新文章
- is not in the sudoers file的解决方法
- Java创建对象的几种方法
- SQL CREATE TABLE 语句\SQL 约束 (Constraints)\SQL NOT NULL 约束\SQL UNIQUE 约束
- Linux RHCS 基础维护命令
- 4.kvm克隆虚拟机
- C++—函数
- static inline
- NOI 1998 免费馅饼
- xmlns:tools="http://schemas.android.com/tools"以及tools:context=".ConfActivity"是什么意思
- HDU3591找零，背包
- Lambda 笔记
- WITH common_table_expression
- json生成方式
- Django 入门案例开发（下）——创建项目应用及模型类
- Trace 2018徐州icpc网络赛 （二分）（树状数组）
- 【转】导致SQL执行慢的原因
- Java:JDBC的基本使用
- 《转》理解restful
- 第十七次ScrumMeeting博客
- 2018.06.29 NOIP模拟 Minimum（最小生成树）
热门文章
- CI/CD DevOps
- SQL的概念与发展 - 极客时间学习笔记
- mysql dump备份 、 mysql还原操作练习
- [小程序]微信小程序获取input并发送网络请求
- Centos7安装dubbo与zookeeper服务配置
- lf 前后端分离 (4) 价格策略
- <console>:14: error: not found: value spark import spark.implicits.
- win10经常无法复制粘贴
- 题解 P3620 【[APIO/CTSC 2007]数据备份】
- [LeetCode] 103. Binary Tree Zigzag Level Order Traversal 二叉树的之字形层序遍历