分类:
工具
调用：
using Asa.Custom.Model.DBModel.AuthenticationModel;
using Asa.DAL.DBHelper.SqlServerHelper;
using Asa.Framework.AttributeExtends.EnumAttributeExtend;
using Asa.Framework.AttributeExtends.ValidateAttributeExtend;
using Asa.System.Model;
using System;
using System.Linq.Expressions;
using static Asa.Common.Enums.BasicInfoEnum;
namespace Asa.ConsoleTest
{
/// <summary>
/// <remarks>测试框架封装函数</remarks>
/// </summary>
public class Program
{
static void Main(string[] args)
{
Expression<Func<UserModel, bool>> expression = u => u.UserName.Contains("黄") && u.Sex == 0&&u.Age>20;
UserModel userMode3 = sqlServerDbHelper.Query<UserModel>(expression);
Console.ReadKey();
}
}
}
------------------------
第一步：SQL帮助类部分
using Asa.DAL.DBHelper.ExpressionExtend;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using Asa.Framework.AttributeExtends.MappingAttributeExtend;
using Asa.Custom.Model.BasicModel;
namespace Asa.DAL.DBHelper.SqlServerHelper
{
/// <summary>
/// <remarks>SqlServer数据库工具类</remarks>
/// <author>Asa</author>
/// <createDate>2020/04/14</createDate>
/// </summary>
public class SqlServerDBHelper
{
/// <summary>
/// <remarks>根据条件查询表实体信息</remarks>
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryExpression">条件表达式</param>
/// <returns></returns>
public T Query<T>(Expression<Func<T,bool>> queryExpression) where T : BaseModel
{
// 查询结果
T result = Activator.CreateInstance<T>();
// 表实体类型
Type type = typeof(T);
// 表达式目录树访问者对象（解析表达式）
SqlVisitor sqlVisitor = new SqlVisitor();
sqlVisitor.Visit(queryExpression);
// 获取解析后的SQL条件
string sqlWhere = sqlVisitor.GetSqlWhere();
// 表列名字符串
string columnString = string.Join(",", type.GetProperties().Select(a => $"[{a.GetMappingName()}]"));
// 组装查询SQL语句
string sql = $"select {columnString} from [{type.GetMappingName()}] where {sqlWhere};";
// 委托传递SQL执行的方式及逻辑
Func<SqlCommand, T> func = sqlCommand =>
{
// 执行SQL查询
SqlDataReader reader = sqlCommand.ExecuteReader();
// 判断查询结果是否为空：不为空则为返回结果赋值
if (reader.Read())
{
// 遍历属性赋值
foreach (PropertyInfo property in type.GetProperties())
{
property.SetValue(result, reader[property.GetMappingName()]);
}
return result;
}
else
{
return default(T);
}
};
return this.ExcuteSql(sql, null, func);
}
/// <summary>
/// <remarks>执行SQL语句通用函数</remarks>
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">准备执行的SQL</param>
/// <param name="sqlParameters">SQL语句需要的参数</param>
/// <param name="func">委托：SQL执行的具体方式</param>
/// <returns></returns>
public T ExcuteSql<T>(string sql, IEnumerable<SqlParameter> sqlParameters, Func<SqlCommand, T> func)
{
// 执行SQL
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
// 打开数据库连接
sqlConnection.Open();
// 实例化执行SQL的命令对象
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
// 判断参数是否为空
if (sqlParameters != null)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
}
// 执行SQL语句
return func.Invoke(sqlCommand);
}
}
}
}
--------------------------------------------
第二步：解析表达式目录树
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
namespace Asa.DAL.DBHelper.ExpressionExtend
{
/// <summary>
/// <remarks>解读表达式目录树</remarks>
/// </summary>
public class SqlVisitor : ExpressionVisitor
{
/// <summary>
/// <remarks>SQL语句条件拼接</remarks>
/// <remarks>stack:表示同一指定类型实例的可变大小后进先出（LIFO）集合</remarks>
/// <remarks>string:指定堆栈中元素的类型</remarks>
/// </summary>
private readonly Stack<string> _sqlWhereStack = new Stack<string>();
/// <summary>
/// <remarks>返回解析后的SQL条件语句</remarks>
/// </summary>
/// <returns></returns>
public string GetSqlWhere()
{
// SQL语句条件字符串
string sqlWhereStr = string.Join(" ", _sqlWhereStack);
// 清空栈
_sqlWhereStack.Clear();
// 返回SQL条件语句解析结果字符串
return sqlWhereStr;
}
/// <summary>
/// <remarks>二元表达式：解读条件</remarks>
/// </summary>
/// <param name="binaryExpression">二元表达式</param>
/// <returns></returns>
protected override Expression VisitBinary(BinaryExpression binaryExpression)
{
// 拼接右括号：堆栈先进后出原则拼接
_sqlWhereStack.Push(")");
// 解析表达式右边
this.Visit(binaryExpression.Right);
// 解析操作类型
_sqlWhereStack.Push(SqlOperator.ToSqlOperator(binaryExpression.NodeType));
// 解析表达式左边
this.Visit(binaryExpression.Left);
// 拼接左括号
_sqlWhereStack.Push("(");
return binaryExpression;
}
/// <summary>
/// <remarks>解析属性/字段表达式</remarks>
/// </summary>
/// <param name="memberExpression">属性/字段表达式</param>
/// <returns></returns>
protected override Expression VisitMember(MemberExpression memberExpression)
{
// 接收属性/字段名称
string prop = memberExpression.Member.Name;
// 将属性/字段名称存入栈中
this._sqlWhereStack.Push(memberExpression.Member.Name);
return memberExpression;
}
/// <summary>
/// <remarks>解析常量表达式</remarks>
/// </summary>
/// <param name="constantExpression">常量表达式</param>
/// <returns></returns>
protected override Expression VisitConstant(ConstantExpression constantExpression)
{
// 将常量的值存入栈中
this._sqlWhereStack.Push(constantExpression.Value.ToString());
return constantExpression;
}
/// <summary>
/// <remarks>解析函数表达式</remarks>
/// </summary>
/// <param name="methodCallExpression">函数表达式</param>
/// <returns></returns>
protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
{
// 解析后的函数表达式
string format;
// 根据函数类型解析
switch (methodCallExpression.Method.Name)
{
case "StartWith":
format = "({0} like '{1}%')";
break;
case "Contains":
format = "({0} like '%{1}%')";
break;
case "EndWith":
format = "({0} like '%{1}')";
break;
case "Equals":
format = "({0} = '{1}')";
break;
default:
throw new NotSupportedException(methodCallExpression.NodeType + " is not supported!");
}
// 调用方法的属性：例如（name.contains("1")）,这里就是指name属性调用的contains函数
Expression instance = this.Visit(methodCallExpression.Object);
// 参数：1就代表调用contains函数传递的参数值
Expression expressionArray = this.Visit(methodCallExpression.Arguments[0]);
// 返回栈顶部的对象并删除
string right = this._sqlWhereStack.Pop();
string left = this._sqlWhereStack.Pop();
// 将解析后的结果存入栈中
this._sqlWhereStack.Push(String.Format(format, left, right));
return methodCallExpression;
}
}
}
----------------------------
using System;
using System.Linq.Expressions;
namespace Asa.DAL.DBHelper.ExpressionExtend
{
/// <summary>
/// <remarks>解析SQL语句中的操作类型</remarks>
/// </summary>
internal static class SqlOperator
{
/// <summary>
/// <remarks>解析操作类型</remarks>
/// </summary>
/// <param name="expressionType">操作类型：例如（>,=）</param>
/// <returns></returns>
internal static string ToSqlOperator(this ExpressionType expressionType)
{
// 将操作类型解析为对应的表达方式
switch (expressionType)
{
case ExpressionType.GreaterThan:
return ">";
case ExpressionType.LessThan:
return "<";
case ExpressionType.GreaterThanOrEqual:
return ">=";
case ExpressionType.LessThanOrEqual:
return "<=";
case ExpressionType.Equal:
return "=";
case ExpressionType.NotEqual:
return "<>";
case ExpressionType.Not:
return "NOT";
case ExpressionType.And:
case ExpressionType.AndAlso:
return "AND";
case ExpressionType.Or:
case ExpressionType.OrElse:
return "OR";
default:
throw new NotSupportedException(expressionType.ToString() + " is not supported!");
}
}
}
}
-----------------------
第三步：特性及特性拓展函数部分
using System;
namespace Asa.Framework.Attributes.MappingAttributeHelper
{
/// <summary>
/// <remarks>映射名称特性（抽象类）</remarks>
/// </summary>
public abstract class AbstractMappingNameAttribute : Attribute
{
/// <summary>
/// <remarks>映射后的名称</remarks>
/// </summary>
private readonly string _name;
/// <summary>
/// <remarks>构造函数注入映射名程</remarks>
/// </summary>
/// <param name="name"></param>
protected AbstractMappingNameAttribute(string name)
{
this._name = name;
}
/// <summary>
/// <remarks>获取映射字段或属性名的函数</remarks>
/// </summary>
/// <returns></returns>
public string GetMappingName()
{
return this._name;
}
}
}
-----------------
using System;
namespace Asa.Framework.Attributes.MappingAttributeHelper
{
/// <summary>
/// <remarks>类名映射特性</remarks>
/// <remarks>AttributeUsage：指定特性可以标记的对象</remarks>
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class ClassMappingNameAttribute : AbstractMappingNameAttribute
{
/// <summary>
/// <remarks>构造函数注入映射名称</remarks>
/// </summary>
/// <param name="name">映射名称</param>
public ClassMappingNameAttribute(string name) : base(name)
{
}
}
}
----------------------
using System;
namespace Asa.Framework.Attributes.MappingAttributeHelper
{
/// <summary>
/// <remarks>字段或属性名称映射特性</remarks>
/// </summary>
[AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
public class PropertyOrFieldMappingNameAttribute : AbstractMappingNameAttribute
{
/// <summary>
/// <remarks>构造函数注入字段或属性名</remarks>
/// </summary>
/// <param name="name">映射名称</param>
public PropertyOrFieldMappingNameAttribute(string name) : base(name)
{
}
}
}
---------------------
using Asa.Framework.Attributes.MappingAttributeHelper;
using System.Reflection;
namespace Asa.Framework.AttributeExtends.MappingAttributeExtend
{
/// <summary>
/// <remarks>映射名称特性拓展类</remarks>
/// </summary>
public static class MappingNameAttributeExtend
{
/// <summary>
/// <remarks>获取类映射名称</remarks>
/// </summary>
/// <typeparam name="T">标记了名称映射特性的参数</typeparam>
/// <param name="t"></param>
/// <returns></returns>
public static string GetMappingName<T>(this T t) where T : MemberInfo
{
// 判断是否标记了名称映射特性
if (t.IsDefined(typeof(AbstractMappingNameAttribute), true))
{
// 获取标记的特性的实例
AbstractMappingNameAttribute tableNameMappingAttribute = t.GetCustomAttribute<AbstractMappingNameAttribute>();
// 返回映射名称
return tableNameMappingAttribute.GetMappingName();
}
else
{
return t.Name;
}
}
}
}
备注：没有暴力校验过，可能会存在问题，谨慎观看，谨慎使用，卒！！！！！！！！！！！！！！！！！！！
50010702506256
