使用表达式树+反射实现简易ORM对象映射操作
为了简单起见我就创建一个文件 BaseDAL.cs , 使用方法也非常简单,看代码吧
先创建一个Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BaseDAL.Model
{
[Table("UserInfo")]
public class MdUserInfo
{
/// <summary>
/// 用户编号
/// </summary>
///
[PrimaryKey]
public string UserNo { set; get; }
/// <summary>
/// 门店编号
/// </summary>
public string ShopNo { set; get; }
/// <summary>
/// 用户姓名
/// </summary>
public string UserNamc { set; get; }
}
}
然后在创建一个UserInfoDAL类继承自BaseDAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BaseDAL.Model;
namespace BaseDAL.DAL
{
public class UserInfoDAL:BaseDAL<MdUserInfo>
{
}
}
好 接下来就是使用示例了
using BaseDAL.DAL;
using BaseDAL.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BaseDAL
{
class Program
{
static void Main(string[] args)
{
UserInfoDAL userInfoDAL = new UserInfoDAL();
//查
List<MdUserInfo> listUser = userInfoDAL.Query(p => p.UserNamc.Contains("马") && p.UserNo == "00008");
//改
for (int i = 0; i < listUser.Count; i++)
{
listUser[i].UserNamc = listUser[i].UserNamc + i;
userInfoDAL.Update(listUser[i]);
}
//增
MdUserInfo userInfo = new MdUserInfo();
userInfo.UserNo = "00001";
userInfo.UserNamc = "测试新增";
userInfo.ShopNo = "10";
userInfoDAL.Insert(userInfo);
//删
userInfoDAL.Delete(p => p.UserNo == "00001");
}
}
}
主要实现思路就是利用Expression表达式树+反射,还有就是使用了Attribute来对类进行标注,简简单单400行代码 ٩(๑❛ᴗ❛๑)۶
BaseDAL.cs代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
namespace BaseDAL
{
/// <summary>
/// 数据基础访问类
/// </summary>
/// <typeparam name="TEntity"></typeparam>
public class BaseDAL<TEntity> where TEntity : class
{
/// <summary>
/// 数据库连接字符串
/// </summary>
public static string SqlConnection = "server=192.168.1.12;database=EatPos2;uid=sa;pwd=1234;";
/// <summary>
/// 当前操作表名称
/// </summary>
public string TableName { get; private set; }
/// <summary>
/// 表列
/// </summary>
private DataColumnCollection TableColumns { set;get; }
/// <summary>
/// Model属性列表
/// </summary>
private List<PropertyInfo> listProperty = new List<PropertyInfo>();
/// <summary>
/// 当前表主键
/// </summary>
private List<PropertyInfo> listPrimaryKey = new List<PropertyInfo>();
/// <summary>
/// 标注自增列属性
/// </summary>
private PropertyInfo AutoIncrease = null;
public BaseDAL()
{
Type type = typeof(TEntity);
#region 初始化表名称
object[] objs = type.GetCustomAttributes(typeof(TableAttribute), false);
if (objs.Length > 0)
{
TableAttribute table = objs[0] as TableAttribute;
TableName = table.TableName;
}
else
{
TableName = type.Name;
}
#endregion
#region 初始化属性列表
PropertyInfo[] propertys = type.GetProperties();
listProperty = new List<PropertyInfo>(propertys);
foreach (PropertyInfo property in listProperty)
{
Attribute primary = property.GetCustomAttribute(typeof(PrimaryKeyAttribute));
if(primary!=null )
{
listPrimaryKey.Add(property);
}
Attribute autoIncrease = property.GetCustomAttribute(typeof(AutoIncreaseAttribute));
if (autoIncrease != null)
{
AutoIncrease = property;
}
}
#endregion
}
/// <summary>
/// 查询
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public List<TEntity> Query(Expression<Func<TEntity, bool>> expression = null)
{
StringBuilder buff = new StringBuilder();
if (expression != null)
{
ExpressionHelper.ParseExpression(expression.Body, buff);
}
string strSql = string.Format("Select * From {0} ",TableName);
if (buff.Length > 0)
{
strSql += " Where "+buff.ToString();
}
List<TEntity> listEntity = new List<TEntity>();
DataTable table = SqlHelper.ExecuteTable(strSql);
TableColumns = table.Columns;
foreach (DataRow dataRow in table.Rows)
{
TEntity model = DataRowToEntity(dataRow);
listEntity.Add(model);
}
return listEntity;
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(TEntity model)
{
#region 构建set字段
StringBuilder setbuff = new StringBuilder();
foreach (PropertyInfo property in listProperty)
{
if (listPrimaryKey.Contains(property))
continue;
if (setbuff.Length > 0)
{
setbuff.Append(",");
}
object value = property.GetValue(model);
setbuff.AppendFormat("{0}={1}", property.Name, ExpressionHelper.GetSqlValue(value));
}
#endregion
#region 构建where字段
string strWhere="";
if (listPrimaryKey.Count > 0)
{
foreach (PropertyInfo property in listPrimaryKey)
{
if (strWhere != "")
{
strWhere += " And ";
}
object value = property.GetValue(model);
strWhere += string.Format("{0}={1}", property.Name, ExpressionHelper.GetSqlValue(value));
}
strWhere = " Where " + strWhere;
}
#endregion
string strSql = string.Format("Update {0} Set {1} {2}",TableName,setbuff.ToString(),strWhere);
return SqlHelper.ExecuteSql(strSql);
}
/// <summary>
/// 增加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(TEntity model)
{
StringBuilder fieldName = new StringBuilder();
StringBuilder fieldValue = new StringBuilder();
foreach (PropertyInfo property in listProperty)
{
if (AutoIncrease != null && property.Equals(AutoIncrease))
continue;
if (fieldName.Length > 0)
{
fieldName.Append(",");
fieldValue.Append(",");
}
fieldName.Append(property.Name);
fieldValue.Append(ExpressionHelper.GetSqlValue(property.GetValue(model)));
}
string strSql = string.Format("Insert Into {0}({1}) Values({2})",TableName,fieldName.ToString(),fieldValue.ToString());
if (AutoIncrease != null)
{
strSql += "\r\n;select @@IDENTITY";
object obj = SqlHelper.ExecuteScalar(strSql);
AutoIncrease.SetValue(model, obj);
return Convert.ToInt32(obj);
}
return SqlHelper.ExecuteSql(strSql);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public int Delete(Expression<Func<TEntity, bool>> expression)
{
StringBuilder buff = new StringBuilder();
if (expression != null)
{
ExpressionHelper.ParseExpression(expression.Body, buff);
}
string strSql = string.Format("Delete From {0} ", TableName);
if (buff.Length > 0)
{
strSql += " Where " + buff.ToString();
}
return SqlHelper.ExecuteSql(strSql);
}
#region 私有方法
/// <summary>
/// 转换成Model
/// </summary>
/// <param name="dataRow"></param>
/// <returns></returns>
private TEntity DataRowToEntity(DataRow dataRow)
{
ConstructorInfo construct = typeof(TEntity).GetConstructor(new Type[]{});
object model = construct.Invoke(null);
for (int i = 0; i < listProperty.Count; i++)
{
PropertyInfo property = listProperty[i];
if (TableColumns.Contains(property.Name))
{
property.SetValue(model, dataRow[property.Name], null);
}
}
return model as TEntity;
}
#endregion
}
#region ExpressionHelper + 表达式帮助类
/// <summary>
/// 表达式帮助类
/// </summary>
class ExpressionHelper
{
/// <summary>
/// 解析Where条件
/// </summary>
/// <param name="expression"></param>
/// <param name="buff"></param>
public static void ParseExpression(Expression expression, StringBuilder buff)
{
BinaryExpression binary = null;
MemberExpression left = null;
ConstantExpression rigth = null;
if (expression.NodeType == ExpressionType.AndAlso)
{
ParseExpression((expression as System.Linq.Expressions.BinaryExpression).Left, buff);
buff.Append(" And ");
ParseExpression((expression as System.Linq.Expressions.BinaryExpression).Right, buff);
}
else if (expression.NodeType == ExpressionType.OrElse)
{
ParseExpression((expression as System.Linq.Expressions.BinaryExpression).Left, buff);
buff.Append(" Or ");
ParseExpression((expression as System.Linq.Expressions.BinaryExpression).Right, buff);
}
else if (expression.NodeType == ExpressionType.Call)
{
MethodCallExpression call = expression as MethodCallExpression;
string methodName = call.Method.Name;
if (methodName == "Contains")
{
left = call.Object as MemberExpression;
rigth = call.Arguments[0] as ConstantExpression;
buff.AppendFormat("{0} like '%{1}%'", left.Member.Name, rigth.Value);
}
}
else
{
binary = expression as System.Linq.Expressions.BinaryExpression;
left = binary.Left as MemberExpression;
rigth = binary.Right as ConstantExpression;
switch (expression.NodeType)
{
case ExpressionType.Equal:
buff.AppendFormat("{0} = {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
case ExpressionType.NotEqual:
buff.AppendFormat("{0} <> {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
case ExpressionType.GreaterThan:// 大于
buff.AppendFormat("{0} > {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
case ExpressionType.GreaterThanOrEqual:// 大于等于
buff.AppendFormat("{0} >= {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
case ExpressionType.LessThan:// 小于
buff.AppendFormat("{0} < {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
case ExpressionType.LessThanOrEqual:// 小于等于
buff.AppendFormat("{0} <= {1}", left.Member.Name, GetSqlValue(rigth.Value));
break;
}
}
return;
}
/// <summary>
/// 取Sql参数值
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static string GetSqlValue(object obj)
{
if (obj == null)
return "";
if (obj.GetType().Equals(typeof(string)))
{
return string.Format("'{0}'", obj.ToString());
}
else if (obj.GetType().Equals(typeof(DateTime)))
{
return string.Format("'{0}'", Convert.ToDateTime(obj).ToString("yyyy-MM-dd HH:mm:ss"));
}
else if (obj.GetType().Equals(typeof(int)) || obj.GetType().Equals(typeof(double)) || obj.GetType().Equals(typeof(float)))
{
return obj.ToString();
}
else if (obj.GetType().Equals(typeof(bool)))
{
bool value = Convert.ToBoolean(obj);
return value ? "1" : "0";
}
return "";
}
}
#endregion
#region 特性标注
#region TableAttribute +映射表名称标注
/// <summary>
/// 映射表名称标注
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : Attribute
{
/// <summary>
/// 表名称
/// </summary>
public string TableName { set; get; }
public TableAttribute(string _tableName)
{
this.TableName = _tableName;
}
}
#endregion
#region PrimaryKeyAttribute +映射主键标注
[AttributeUsage(AttributeTargets.Property)]
public class PrimaryKeyAttribute : Attribute
{
}
#endregion
#region AutoIncreaseAttribute +映射自增列标注
[AttributeUsage(AttributeTargets.Property)]
public class AutoIncreaseAttribute : Attribute
{
}
#endregion
#endregion
#region SqlHelper
class SqlHelper {
public static DataTable ExecuteTable(string strSql)
{
using (SqlConnection conn = new SqlConnection(BaseDAL<object>.SqlConnection))
{
SqlDataAdapter adapter = new SqlDataAdapter(strSql, conn);
DataTable table=new DataTable();
adapter.FillSchema(table,SchemaType.Mapped);
adapter.Fill(table);
return table;
}
}
public static int ExecuteSql(string strSql)
{
using (SqlConnection conn = new SqlConnection(BaseDAL<object>.SqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
return cmd.ExecuteNonQuery();
}
}
public static object ExecuteScalar(string strSql)
{
using (SqlConnection conn = new SqlConnection(BaseDAL<object>.SqlConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
return cmd.ExecuteScalar();
}
}
}
#endregion
}
下载链接:http://pan.baidu.com/s/1midzwes 密码:mbvx