图片 2

SqlHelper简单实现,Lambda表达式解析类

事先那么些说要改成代码中充满着各类Select的Sql语句字符串的状态,让自家尝试着做二个轻巧的SqlHelper,要持有以下职能:

本条ExpressionHelper类,是成套SqlHelper中,最基本的3个类,首要效率正是将拉姆da表明式转换为Sql语句。同时那么些调换进程比较复杂,所以上边详细疏解一下思路和这几个类的机能。

1.决不在专门的学业代码中揭发DataTable只怕DataSet类型;

 

二.足以变成Select操作,必要辅助条件查询,分页,单条记录查询,排序,连表,单个值;

0x01基本

3.足以做到增加单条记下,添增多条记下,修改记录和删除记录;

在Sql语句中,首要由表名,字段,条件语句等成分构成,而这一个因素中,表名,字段能够由实体表示出来,条件语句能够由Lambda表达式表现出来。

4.选拔进程中要同盟老代码,既允许间接传入Sql;

在Sql语句中,条件语句能够说是Select中的主题,当中大多效应和拉姆da中提供的艺术很相似,举个例子:

伍.要同时包容MySql和Sql Server。

实体:

自己前边写过不少Linq的代码,对那种函数式编制程序至极欣赏,所以思虑能不能够依照Lambda表明式来生成Sql。对于那某些的剧情有不少,所以自身单开3个多元来享受一下小编的化解方案:

图片 1

化解方案的组织如下:

 1     [TableName("RA_MyBlog_Article")]
 2     public class ArticleEntity
 3     {
 4         [Primary]
 5         [Identity]
 6         //文章ID
 7         public int articleID { get; set; }
 8         //分类ID
 9         public int categoryID { get; set; }
10         //文章标题
11         public string articleTitle { get; set; }
12         //文章版权
13         public string articleCopyright { get; set; }
14         //文章创建时间
15         public DateTime articleDate { get; set; }
16         //文章摘要
17         public string articleAbstract { get; set; }
18         //文章内容
19         public string articleContain { get; set; }
20         //文章所属User
21         public int userID { get; set; }
22     }

图片 2

Lambda表达式 Sql表达式
GetList<ArticleEntity>(a=>a.articleID > 1 && a.userID = 1) Select * From RA_MyBlog_Article Where articleID > 1 And userID = 1
GetList<ArticleEntity>(a=>list.Contains(a.articleID)) Select * From RA_MyBlog_Article Where articleID In [list中的元素]
Join<UserEntity,ArticleEntity>((a,b)=>a.UserID == b.UserID) Select * From RA_MyBlog_Article,RA_MyBlog_User Join RA_MyBlog_User On (RA_MyBlog_Article.UserID = RA_MyBlog_User.UserID )

 传送门:

 

SqlHelper轻松实现(通过Expression和反光)壹.引言

 

SqlHelper轻便完结(通过Expression和反光)2.天性和实业设计

 

SqlHelper轻易完结(通过Expression和反光)三.实体,数据传输对象(DTO)Helper类设计

 

SqlHelper轻巧达成(通过Expression和反光)四.指标反射Helper类

 

SqlHelper轻便落成(通过Expression和反光)伍.拉姆da表达式解析类

从上表能够见到,由Lambda表明式转变为Sql表达式是全然大概的。

SqlHelper轻便完结(通过Expression和反光)陆.Providor情势(工厂+战略)可布署数据库选择

0x02 Lambda表达式

SqlHelper轻易达成(通过Expression和反光)7.MySql数据管理类

Lambda设计到的开始和结果相比较多,我们有乐趣能够去百度找一下那方面包车型客车介绍,这里只介绍一些关乎到的有的:

SqlHelper轻松实现(通过Expression和反光)八.Sql
Server数目管理类

以地点的表明式GetList<ArticleEntity>(a=>a.articleID > 一 &&
a.userID = 1)为例,个中a=>a.articleID > 一 && a.userID =
一那壹部分是我们须求的部分。在C#中,由Expression对象承担对Lambda表达式的解析和拍卖。Expression的子类有不少,布满在System.Linq.Expressions命名空间下,这里运用的有

SqlHelper简单完毕(通过Expression和反光)九.Sql发挥对象SqlSession

UnaryExpression:壹元表达式,比方取反’!’

SqlHelper轻易达成(通过Expression和反光)十.选取方法

ConstantExpression:常量表达式,举例壹

MemberExpression:成员表明式,一般为变量,例如a.articleID

MethodCallExpression:函数表明式,比方Contains()

BinaryExpression:2元表明式,比方a.articleID > 一

对此这些事例:a.articleID > 一 && a.userID =
一,全体是三个与项指标二元表明式,左成分为a.articleID >

一,是贰个Larger类型的二元表明式,当中左成分是成员表明式,右成分是常量表达式。右成分为a.userID

一,是一个十分类型的二元表明式,在那之中左成分是成员表达式,右成分是常量表明式。

2.用到的枚举,没啥可说的,包蕴了拉姆da表明式中普及的要素类型。

 1 namespace RA.DataAccess.Enumerations
 2 {
 3     public enum EnumNodeType
 4     {
 5         [Description("二元运算符")]
 6         BinaryOperator = 1,
 7         [Description("一元运算符")]
 8         UndryOperator = 2,
 9         [Description("常量表达式")]
10         Constant = 3,
11         [Description("成员(变量)")]
12         MemberAccess = 4,
13         [Description("函数")]
14         Call = 5,
15         [Description("未知")]
16         Unknown = -99,
17         [Description("不支持")]
18         NotSupported = -98
19     }
20 }

0x0三 功效部分

1.料定表明式类型

 1         /// <summary>
 2         /// 判断表达式类型
 3         /// </summary>
 4         /// <param name="func">lambda表达式</param>
 5         /// <returns></returns>
 6         private static EnumNodeType CheckExpressionType(Expression func)
 7         {
 8             switch (func.NodeType)
 9             {
10                 case ExpressionType.AndAlso:
11                 case ExpressionType.OrElse:
12                 case ExpressionType.Equal:
13                 case ExpressionType.GreaterThanOrEqual:
14                 case ExpressionType.LessThanOrEqual:
15                 case ExpressionType.GreaterThan:
16                 case ExpressionType.LessThan:
17                 case ExpressionType.NotEqual:
18                     return EnumNodeType.BinaryOperator;
19                 case ExpressionType.Constant:
20                     return EnumNodeType.Constant;
21                 case ExpressionType.MemberAccess:
22                     return EnumNodeType.MemberAccess;
23                 case ExpressionType.Call:
24                     return EnumNodeType.Call;
25                 case ExpressionType.Not:
26                 case ExpressionType.Convert:
27                     return EnumNodeType.UndryOperator;
28                 default:
29                     return EnumNodeType.Unknown;
30             }
31         }

二.确定1元表明式:

 1         /// <summary>
 2         /// 判断一元表达式
 3         /// </summary>
 4         /// <param name="func"></param>
 5         /// <returns></returns>
 6         private static string VisitUnaryExpression(UnaryExpression func)
 7         {
 8             var result = ExpressionTypeToString(func.NodeType);
 9             var funcType = CheckExpressionType(func.Operand);
10             switch (funcType)
11             {
12                 case EnumNodeType.BinaryOperator:
13                     return result + VisitBinaryExpression(func.Operand as BinaryExpression);
14                 case EnumNodeType.Constant:
15                     return result + VisitConstantExpression(func.Operand as ConstantExpression);
16                 case EnumNodeType.Call:
17                     return result + VisitMethodCallExpression(func.Operand as MethodCallExpression);
18                 case EnumNodeType.UndryOperator:
19                     return result + VisitUnaryExpression(func.Operand as UnaryExpression);
20                 case EnumNodeType.MemberAccess:
21                     return result + VisitMemberAccessExpression(func.Operand as MemberExpression);
22                 default:
23                     throw new NotSupportedException("不支持的操作在一元操作处理中:" + funcType.GetDescription());
24             }
25         }

叁.推断常量表明式:

 1         /// <summary>
 2         /// 判断常量表达式
 3         /// </summary>
 4         /// <param name="func"></param>
 5         /// <returns></returns>
 6         private static string VisitConstantExpression(ConstantExpression func)
 7         {
 8             if (func.Value.ToString() == "")
 9             {
10                 return "\'\' ";
11             }
12             else if (func.Value.ToString() == "True")
13             {
14                 return "1 = 1 ";
15             }
16             else if (func.Value.ToString() == "False")
17             {
18                 return "0 = 1 ";
19             }
20             else
21             {
22                 return "'" + func.Value.ToString() + "' ";
23                 
24             }
25         }

4.剖断变量表明式

 1         /// <summary>
 2         /// 判断包含变量的表达式
 3         /// </summary>
 4         /// <param name="func"></param>
 5         /// <returns></returns>
 6         private static string VisitMemberAccessExpression(MemberExpression func)
 7         {
 8             try
 9             {
10                 var tablename = EntityHelper.GetTableName(func.Expression.Type);
11                 return tablename + "." + func.Member.Name + " ";
12             }catch
13             {
14                 object value;
15                 switch (func.Type.Name)
16                 {
17                     case "Int32":
18                     {
19                         var getter = Expression.Lambda<Func<int>>(func).Compile();
20                         value = getter();
21                     }
22                         break;
23                     case "String":
24                     {
25                         var getter = Expression.Lambda<Func<string>>(func).Compile();
26                         value = "'" + getter() + "'";
27                     }
28                         break;
29                     case "DateTime":
30                     {
31                         var getter = Expression.Lambda<Func<DateTime>>(func).Compile();
32                         value = "'" + getter() + "'";
33                     }
34                         break;
35                     default:
36                     {
37                         var getter = Expression.Lambda<Func<object>>(func).Compile();
38                         value = getter();
39                     }
40                         break;
41                 }
42                 return value.ToString();
43             }
44         }

伍.推断函数表明式:为了演示,此处这么些表达式只支持Contains()函数,别的的函数能够按需加上。

 1         /// <summary>
 2         /// 判断包含函数的表达式
 3         /// </summary>
 4         /// <param name="func"></param>
 5         /// <returns></returns>
 6         private static String VisitMethodCallExpression(MethodCallExpression func)
 7         {
 8             if (func.Method.Name.Contains("Contains"))
 9             {
10                 //获得调用者的内容元素
11                 var getter = Expression.Lambda<Func<object>>(func.Object).Compile();
12                 var data = getter() as IEnumerable;
13                 //获得字段
14                 var caller = func.Arguments[0];
15                 while (caller.NodeType == ExpressionType.Call)
16                 {
17                     caller = (caller as MethodCallExpression).Object;
18                 }
19                 var field = VisitMemberAccessExpression(caller as MemberExpression);
20                 var list = (from object i in data select "'" + i + "'").ToList();
21                 return field + " IN (" + string.Join(",", list.Cast<string>().ToArray()) + ") ";
22             }
23             else
24             {
25                 throw new NotSupportedException("不支持的函数操作:" + func.Method.Name);
26             }
27         }

陆.肯定二元表达式:二元表达式一般由别的表明式组成,有时还会有嵌套的事态,所以那边使用递归来分析。

 1         /// <summary> 
 2         /// 判断包含二元运算符的表达式
 3         /// </summary>
 4         /// <remarks>注意,这个函数使用了递归,修改时注意不要修改了代码顺序和逻辑</remarks>
 5         /// <param name="func"></param>
 6         private static string VisitBinaryExpression(BinaryExpression func)
 7         {
 8             var result = "(";
 9             var leftType = CheckExpressionType(func.Left);
10             switch (leftType)
11             {
12                 case EnumNodeType.BinaryOperator:
13                     result += VisitBinaryExpression(func.Left as BinaryExpression);break;
14                 case EnumNodeType.Constant:
15                     result += VisitConstantExpression(func.Left as ConstantExpression);break;
16                 case EnumNodeType.MemberAccess:
17                     result += VisitMemberAccessExpression(func.Left as MemberExpression);break;
18                 case EnumNodeType.UndryOperator:
19                     result += VisitUnaryExpression(func.Left as UnaryExpression);break;
20                 case EnumNodeType.Call:
21                     result += VisitMethodCallExpression(func.Left as MethodCallExpression);break;
22                 default:
23                     throw new NotSupportedException("不支持的操作在二元操作处理中:" + leftType.GetDescription());
24             }
25 
26             result += ExpressionTypeToString(func.NodeType) + " ";
27 
28             var rightType = CheckExpressionType(func.Right);
29             switch (rightType)
30             {
31                 case EnumNodeType.BinaryOperator:
32                     result += VisitBinaryExpression(func.Right as BinaryExpression); break;
33                 case EnumNodeType.Constant:
34                     result += VisitConstantExpression(func.Right as ConstantExpression); break;
35                 case EnumNodeType.MemberAccess:
36                     result += VisitMemberAccessExpression(func.Right as MemberExpression); break;
37                 case EnumNodeType.UndryOperator:
38                     result += VisitUnaryExpression(func.Right as UnaryExpression); break;
39                 case EnumNodeType.Call:
40                     result += VisitMethodCallExpression(func.Right as MethodCallExpression); break;
41                 default:
42                     throw new NotSupportedException("不支持的操作在二元操作处理中:" + rightType.GetDescription());
43             }
44 
45             result += ") ";
46             return result;
47         }

七.将Lambda表明式调换为Sql语句。整个类的入口点:

 1         /// <summary>
 2         /// 通过Lambda解析为Sql
 3         /// </summary>
 4         /// <param name="func"></param>
 5         /// <returns></returns>
 6         public static string GetSqlByExpression(Expression func)
 7         {
 8             var funcType = CheckExpressionType(func);
 9             switch (funcType)
10             {
11                 case EnumNodeType.BinaryOperator:
12                     return FormatSqlExpression(VisitBinaryExpression(func as BinaryExpression));
13                 case EnumNodeType.Constant:
14                     return FormatSqlExpression(VisitConstantExpression(func as ConstantExpression));
15                 case EnumNodeType.Call:
16                     return FormatSqlExpression(VisitMethodCallExpression(func as MethodCallExpression));
17                 case EnumNodeType.UndryOperator:
18                     return FormatSqlExpression(VisitUnaryExpression(func as UnaryExpression));
19                 case EnumNodeType.MemberAccess:
20                     return FormatSqlExpression(VisitMemberAccessExpression(func as MemberExpression));
21                 default:
22                     throw new NotSupportedException("不支持的操作在表达式处理中:" + funcType.GetDescription());
23             }
24         }