C# Expression 树转化为SQL语句（一）

　　　sql有有四中基本语句，分别是增删改查，在建立model后如何生成这四中sql语句，降低开发时间。

　　　我们先模拟出一张学生表：

 public class Student
        {
            public int id { get; set; }
            public string name { get; set; }
            public int math { get; set; } //数学成绩
            public DateTime createTime { get; set; }
        }

　　首先我们来看看增加，也就是插入语句。插入语句语法比较固定变化少通过泛型和反射可以直接生成。string类型和DateTime类型需要加单引号，其他类型不需要加。

public static void Main(string[] args)
        {
            Student stu = new Student
            {
                id = 1,
                name = "张三",
                matn = 59,
                createTime = DateTime.Now

            };
            string sql = CreateInsertSql(stu);
            Console.WriteLine(sql);
            Console.ReadLine();
        }
        public static string CreateInsertSql<T>(T model)
        {
            string sql = "Insert into {0}({1}) Values({2})";
            string table = string.Empty;            //表名
            List<string> member = new List<string>(); //全部列名
            List<string> member_value = new List<string>(); //全部的值
            Type type = typeof(T);
            table = type.Name;
            foreach (PropertyInfo item in type.GetProperties())
            {
                string name = item.Name;
                member.Add(name);
                object vaule = item.GetValue(model);
                string v_str = string.Empty;
                if (vaule is string)
                {
                    v_str = string.Format("'{0}'", vaule.ToString());
                }
                else if (vaule is DateTime)
                {
                    DateTime time = (DateTime)vaule;
                    v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else
                {
                    v_str = vaule.ToString();
                }
                member_value.Add(v_str);
            }
            sql = string.Format(sql, table, string.Join(",", member), string.Join(",", member_value));
            return sql;
        }

调试结果为：

　　接下来我们来看看其他三种简单sql 删，改，查。简单的分析一下：

删除：前面固定(delete from ) + 表名 + where条件

　　修改：前面固定(update) +表名 +set + 修改内容+ where条件

　　查找（根据model 我们默认查找全部字段）：前面固定（select * from ） + 表名 +where 条件

　　从上面可以看出来我们需要3中参数（表名，修改内容，where条件）表名非常简单，可以把类名作为表名，反射一个就可以得到，接下来就是修改内容和where条件，修改内容比较简单格式为 set a=a_value,b=b_value .........，where 条件较为复杂。

　　用Expression 表达式树是受EntityFrame的启发，有些不了解的可以看看EF的一些函数的定义。

　　现在就开始使用Expression表达式树来生产这2种sql语句。

　　Expression表达式树有一个基类是Expression，然后有非常都的类继承这个类，我们想获取继承类的名称和命名空间的的时候可以用：obj.GetType().Name 和obj.getType().Namespace来获取，这样便于调试。

　　1. update中的修改内容。假设我们要对学生的分数进行修改（改成60，让人及格），还有姓名（就是凑个字段），其他的数据不修改。

　　参数类型为： Expression<Func<Student, Student>> la = n => new Student { name = "李四", matn = 60 }; 我们要生成的sql为 name='李四'，math=60,为什么类型是这个可以去看看EF扩展的内容。

　 la类型是LambdaExpression，我们要解析的事la.Body 其类型为MemberInitExpression

 1   public static void Main(string[] args)
 2         {
 3             Expression<Func<Student, Student>> la = n => new Student { name = "李四", matn = 60 };
 4             Console.WriteLine(GetExpressStr((MemberInitExpression)(la.Body)));
 5             Console.ReadLine();
 6         }
 7         public static string GetExpressStr(MemberInitExpression exp)
 8         {
 9             string result = string.Empty;
10             List<string> member = new List<string>();
11             foreach (MemberAssignment item in exp.Bindings)
12             {
13                 string update = item.Member.Name + "=" + GetConstantStr((ConstantExpression)item.Expression);
14                 member.Add(update);
15             }
16             result = string.Join(",", member);
17             return result;
18         }
19         public static string GetConstantStr(ConstantExpression exp)
20         {
21             object vaule = exp.Value;
22             string v_str = string.Empty;
23             if (vaule is string)
24             {
25                 v_str = string.Format("'{0}'", vaule.ToString());
26             }
27             else if (vaule is DateTime)
28             {
29                 DateTime time = (DateTime)vaule;
30                 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
31             }
32             else
33             {
34                 v_str = vaule.ToString();
35             }
36             return v_str;
37         }

调试结果：

　　2.where条件

　　where条件是最麻烦的地方。之前的只有等于号，而where却有大于，小于，且或等等符号，不过Exresion基类中提供了NodeType 类型为ExpressionType，我们可以获取到对应的运算符。参数类型为

  Expression<Func<Student,bool>>   简单理解where条件是每条数据符合不符合，所以返回值为bool

  1 public static void Main(string[] args)
  2         {
  3             Expression<Func<Student,bool>> la =( n=>n.id > 1 && n.id <100 &&n.name !="张三" && n.matn >=60 && n.id != 50 && n.createTime != null);
  4             Console.WriteLine(DealExpress(la));
  5             Console.ReadLine();
  6         }
  7         public static string DealExpress(Expression exp)
  8         {
  9             if (exp is LambdaExpression )
 10             {
 11                 LambdaExpression l_exp = exp as LambdaExpression;
 12                 return   DealExpress(l_exp.Body);
 13             }
 14             if (exp is BinaryExpression)
 15             {
 16                 return DealBinaryExpression(exp as BinaryExpression);
 17             }
 18             if (exp is MemberExpression)
 19             {
 20                 return DealMemberExpression(exp as MemberExpression);
 21             }
 22             if (exp is ConstantExpression)
 23             {
 24                 return DealConstantExpression(exp as ConstantExpression);
 25             }
 26             if (exp is UnaryExpression)
 27             {
 28                 return DealUnaryExpression(exp as UnaryExpression);
 29             }
 30             return "";
 31         }
 32         public static string DealUnaryExpression(UnaryExpression exp)
 33         {
 34             return DealExpress(exp.Operand);
 35         }
 36         public static string DealConstantExpression(ConstantExpression exp)
 37         {
 38             object vaule = exp.Value;
 39             string v_str = string.Empty;
 40             if (vaule == null)
 41             {
 42                 return "NULL";
 43             }
 44             if (vaule is string)
 45             {
 46                 v_str = string.Format("'{0}'", vaule.ToString());
 47             }
 48             else if (vaule is DateTime)
 49             {
 50                 DateTime time = (DateTime)vaule;
 51                 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
 52             }
 53             else
 54             {
 55                 v_str = vaule.ToString();
 56             }
 57             return v_str;
 58         }
 59         public static string DealBinaryExpression(BinaryExpression exp)
 60         {
 61 
 62             string left = DealExpress(exp.Left);
 63             string oper = GetOperStr(exp.NodeType);
 64             string right = DealExpress(exp.Right);
 65             if (right == "NULL")
 66             {
 67                 if (oper == "=")
 68                 {
 69                     oper = " is ";
 70                 }
 71                 else
 72                 {
 73                     oper = " is not ";
 74                 }
 75             }
 76             return left + oper + right;
 77         }
 78         public static string DealMemberExpression(MemberExpression exp)
 79         {
 80              return exp.Member.Name;
 81         }
 82         public static string GetOperStr(ExpressionType e_type)
 83         {
 84             switch (e_type)
 85             {
 86                 case ExpressionType.OrElse: return " OR ";
 87                 case ExpressionType.Or: return "|";
 88                 case ExpressionType.AndAlso: return " AND ";
 89                 case ExpressionType.And: return "&";
 90                 case ExpressionType.GreaterThan: return ">";
 91                 case ExpressionType.GreaterThanOrEqual: return ">=";
 92                 case ExpressionType.LessThan: return "<";
 93                 case ExpressionType.LessThanOrEqual: return "<=";
 94                 case ExpressionType.NotEqual: return "<>";
 95                 case ExpressionType.Add: return "+";
 96                 case ExpressionType.Subtract: return "-";
 97                 case ExpressionType.Multiply: return "*";
 98                 case ExpressionType.Divide: return "/";
 99                 case ExpressionType.Modulo: return "%";
100                 case ExpressionType.Equal: return "=";
101             }
102             return "";
103         }

调试结果：

这些代码中一些漏洞，仅供大家参考学习，这些代码目前不能接受参数,如n=>n.id==m（int m=1）,下一篇将会对Expression表达式树的参数进行解析，欢迎大家指正。

更新2019-11-21

解决 bool 转数据库 bit 类型

  1   public static void Main(string[] args)
  2         {
  3             Expression<Func<Student, bool>> la = (n =>
  4                                                         n.id > 1
  5                                                         && n.id < 100 && n.name != "张三"
  6                                                         && n.math >= 60 && n.id != 50
  7                                                         && n.createTime != null
  8                                                         && n.delete
  9                                                         && n.IsDel == true
 10                                                         && !n.isAdmin
 11                                                         && 1 + 2 == 3
 12                                                         && 1 == 2);
 13 
 14             Expression<Func<Student, bool>> la2 = n=> 1+2==3;
 15 
 16             Console.WriteLine(DealExpress(la));
 17             Console.ReadLine();
 18         }
 19         public static string DealExpress(Expression exp)
 20         {
 21             if (exp is LambdaExpression)
 22             {
 23                 LambdaExpression l_exp = exp as LambdaExpression;
 24                 return DealBoolExp(l_exp.Body);
 25             }
 26             if (exp is BinaryExpression)
 27             {
 28                 return DealBinaryExpression(exp as BinaryExpression);
 29             }
 30             if (exp is MemberExpression)
 31             {
 32                 return DealMemberExpression(exp as MemberExpression);
 33             }
 34             if (exp is ConstantExpression)
 35             {
 36                 return DealConstantExpression(exp as ConstantExpression);
 37             }
 38             if (exp is UnaryExpression)
 39             {
 40                 return DealUnaryExpression(exp as UnaryExpression);
 41             }
 42             return "";
 43         }
 44         public static string DealBoolExp(Expression exp)
 45         {
 46             if (exp == null)
 47             {
 48                 var t = 1;
 49             }
 50             // 关于bool类型特殊处理
 51             if (exp is BinaryExpression) 
 52             {
 53                 return DealExpress(exp);
 54             }
 55             if (exp is MemberExpression) // n.isDelete 
 56             {
 57                 return DealMemberExpression(exp as MemberExpression) + "=1";
 58             }
 59             if (exp is UnaryExpression) //!n.isDelete
 60             {
 61                 return DealUnaryExpression(exp as UnaryExpression) + "<>1";
 62             }
 63             if (exp is ConstantExpression) //
 64             {
 65                 var str  = DealConstantExpression(exp as ConstantExpression);
 66                 return str == "1" ? "1==1" : "1!=1";
 67             }
 68             return "";
 69         }
 70 
 71 
 72         public static string DealUnaryExpression(UnaryExpression exp)
 73         {
 74             return DealExpress(exp.Operand);
 75         }
 76         public static string DealConstantExpression(ConstantExpression exp)
 77         {
 78             object vaule = exp.Value;
 79             string v_str = string.Empty;
 80             if (vaule == null)
 81             {
 82                 return "NULL";
 83             }
 84             if (vaule is string)
 85             {
 86                 v_str = string.Format("'{0}'", vaule.ToString());
 87             }
 88             else if (vaule is DateTime)
 89             {
 90                 DateTime time = (DateTime)vaule;
 91                 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
 92             }
 93             else if (vaule is Boolean)
 94             {
 95                 Boolean data = Convert.ToBoolean(vaule);
 96                 v_str = data? "1" : "0";
 97             }
 98             else
 99             {
100                 v_str = vaule.ToString();
101             }
102             return v_str;
103         }
104         public static string DealBinaryExpression(BinaryExpression exp)
105         {
106 
107             switch (exp.NodeType)
108             {
109                 case ExpressionType.OrElse:
110                 case ExpressionType.AndAlso:
111                     {
112                         string left = DealBoolExp(exp.Left);
113                         string oper = GetOperStr(exp.NodeType);
114                         string right = DealBoolExp(exp.Right);
115                         return left + oper + right;
116 
117                     }
118                     
119                 default:
120                     {
121                         string left = DealExpress(exp.Left);
122                         string oper = GetOperStr(exp.NodeType);
123                         string right = DealExpress(exp.Right);
124                         if (right == "NULL")
125                         {
126                             if (oper == "=")
127                             {
128                                 oper = " is ";
129                             }
130                             else
131                             {
132                                 oper = " is not ";
133                             }
134                         }
135                         return left + oper + right;
136                     }
137 
138 
139             }
140 
141           
142         }
143         public static string DealMemberExpression(MemberExpression exp)
144         {
145             var name = exp.Member.Name;
146             if (name == "delete")
147             {
148                 var t = name;
149             }
150             return exp.Member.Name;
151         }
152         public static string GetOperStr(ExpressionType e_type)
153         {
154             switch (e_type)
155             {
156                 case ExpressionType.OrElse: return " OR ";
157                 case ExpressionType.Or: return "|";
158                 case ExpressionType.AndAlso: return " AND ";
159                 case ExpressionType.And: return "&";
160                 case ExpressionType.GreaterThan: return ">";
161                 case ExpressionType.GreaterThanOrEqual: return ">=";
162                 case ExpressionType.LessThan: return "<";
163                 case ExpressionType.LessThanOrEqual: return "<=";
164                 case ExpressionType.NotEqual: return "<>";
165                 case ExpressionType.Add: return "+";
166                 case ExpressionType.Subtract: return "-";
167                 case ExpressionType.Multiply: return "*";
168                 case ExpressionType.Divide: return "/";
169                 case ExpressionType.Modulo: return "%";
170                 case ExpressionType.Equal: return "=";
171                
172             }
173             return "";
174         }

简单来讲 1。表达式整体必须是 BinaryExpression 这个类型 2. 如果符号是 OR 或And 那么左右2边也必须是 BinaryExpression

代码有些取巧应该有些不足之处，仅供大家参考

posted @ 2017-02-26 15:25 灵行寻燕阅读(20653) 评论(15) 编辑收藏举报

刷新页面返回顶部

灵行寻燕

C# Expression 树转化为SQL语句（一）

公告