SqlHelper简单实现（通过Expression和反射）9.Sql表述对象SqlSession

此类是整个SqlHelper的另一个核心，基本思想就是通过EntityHelper，ObjectHelper和ExpressionHelper获取拼接Select语句的全部元素，拼接出完整Select语句。

 1 using System.Collections.Generic;
 2 using RA.DataAccess.Common;
 3 
 4 namespace RA.DataAccess
 5 {
 6     public partial class SqlSession<T>
 7     {
 8         public SqlSession()
 9         {
10             Fields = EntityHelper.GetFields<T>(false);
11             Field = EntityHelper.GetFiledString<T>();
12             TableName = EntityHelper.GetTableName<T>();
13             PrimaryKey = EntityHelper.GetPrimaryKey<T>();
14         }
15         /// <summary>
16         /// 字段，用逗号隔开
17         /// </summary>
18         public string Field { get; set; }
19         /// <summary>
20         /// 主键
21         /// </summary>
22         public string PrimaryKey { get; set; }
23         /// <summary>
24         /// 表名
25         /// </summary>
26         public string TableName { get; set; }
27         /// <summary>
28         /// 字段列表
29         /// </summary>
30         public List<string> Fields { get; set; }
31         /// <summary>
32         /// 条件表达式
33         /// </summary>
34         public string WhereStr { get; set; } = "";
35         /// <summary>
36         /// 是否聚合
37         /// </summary>
38         public bool IsDistinct { get; set; }
39         /// <summary>
40         /// 排序
41         /// </summary>
42         public string OrderByStr { get; set; }
43         /// <summary>
44         /// 连表字符串
45         /// </summary>
46         public string JoinStr { get; set; }
47         /// <summary>
48         /// 完整sql
49         /// </summary>
50         public string SqlExpression
51         {
52             get
53             {
54                 var sql = "SELECT $distinct " + Field + " FROM " + TableName + " $join$where$orderby";
55 
56                 sql = sql.Replace("$distinct", IsDistinct ? "DISTINCT" : "");
57                 sql = sql.Replace("$join", string.IsNullOrEmpty(JoinStr) ? "" : JoinStr);
58                 sql = sql.Replace("$where", string.IsNullOrEmpty(WhereStr) ? "" : "WHERE " + WhereStr);
59                 sql = sql.Replace("$orderby", string.IsNullOrEmpty(OrderByStr) ? "" : "ORDER BY " + OrderByStr);
60                 return sql;
61             }
62         }
63     }
64 }

  1 using System;
  2 using System.Linq.Expressions;
  3 using RA.DataAccess.Common;
  4 
  5 namespace RA.DataAccess
  6 {
  7     public partial class SqlSession<T> where T : class
  8     {
  9         #region Where操作
 10         private void WhereSqlFromExpression(Expression func)
 11         {
 12             if (WhereStr != "")
 13             {
 14                 WhereStr = WhereStr + "AND " + ExpressionHelper.GetSqlByExpression(func) + " ";
 15             }
 16             else
 17             {
 18                 WhereStr = ExpressionHelper.GetSqlByExpression(func) + " ";
 19             }
 20         }
 21         /// <summary>
 22         /// Where操作，适用于单表查询(exp代表的元素的查询)
 23         /// </summary>
 24         /// <param name="func">表达式</param>
 25         public void Where(Expression<Func<T, bool>> func)
 26         {
 27             WhereSqlFromExpression(func.Body);
 28         }
 29         /// <summary>
 30         /// Where操作，适用于单表查询(Target中元素的查询)
 31         /// </summary>
 32         /// <typeparam name="Target">要查询的另一个表的实体</typeparam>
 33         /// <param name="func"></param>
 34         public void Where<Target>(Expression<Func<Target, bool>> func)
 35         {
 36             WhereSqlFromExpression(func.Body);
 37         }
 38         /// <summary>
 39         /// Where操作，适用于联表查询时的where语句(exp和T元素的关系查询)
 40         /// </summary>
 41         /// <typeparam name="Target"></typeparam>
 42         /// <param name="func"></param>
 43         public void Where<Target>(Expression<Func<Target, T, bool>> func)
 44         {
 45             WhereSqlFromExpression(func.Body);
 46         }
 47         /// <summary>
 48         /// Where操作，适用于多联表时的where语句（TSource和Target元素之间的关系查询）
 49         /// </summary>
 50         /// <typeparam name="TSource"></typeparam>
 51         /// <typeparam name="Target"></typeparam>
 52         /// <param name="func"></param>
 53         public void Where<TSource, Target>(Expression<Func<TSource, Target, bool>> func)
 54         {
 55             WhereSqlFromExpression(func.Body);
 56         }
 57 
 58         /// <summary>
 59         /// Where操作，适用于多联表时的where语句（多条件，跨表）
 60         /// </summary>
 61         /// <typeparam name="T0"></typeparam>
 62         /// <typeparam name="T1"></typeparam>
 63         /// <typeparam name="T2"></typeparam>
 64         /// <param name="func"></param>
 65         public void Where<T0, T1, T2>(Expression<Func<T0, T1, T2, bool>> func)
 66         {
 67             WhereSqlFromExpression(func.Body);
 68         }
 69 
 70         /// <summary>
 71         /// Where操作，适用于多联表时的where语句（多条件，跨表）
 72         /// </summary>
 73         /// <typeparam name="T0"></typeparam>
 74         /// <typeparam name="T1"></typeparam>
 75         /// <typeparam name="T2"></typeparam>
 76         /// <typeparam name="T3"></typeparam>
 77         /// <param name="func"></param>
 78         public void Where<T0, T1, T2, T3>(Expression<Func<T0, T1, T2, T3, bool>> func)
 79         {
 80             WhereSqlFromExpression(func.Body);
 81         }
 82 
 83         /// <summary>
 84         /// Where操作，适用于多联表时的where语句（多条件，跨表）
 85         /// </summary>
 86         /// <typeparam name="T0"></typeparam>
 87         /// <typeparam name="T1"></typeparam>
 88         /// <typeparam name="T2"></typeparam>
 89         /// <typeparam name="T3"></typeparam>
 90         /// <typeparam name="T4"></typeparam>
 91         /// <param name="func"></param>
 92         public void Where<T0, T1, T2, T3, T4>(Expression<Func<T0,T1,T2,T3,T4,bool>> func)
 93         {
 94             WhereSqlFromExpression(func.Body);
 95         }
 96         #endregion
 97 
 98         #region 排序
 99         /// <summary>
100         /// 按照DESC排序
101         /// </summary>
102         /// <param name="func"></param>
103         public void OrderByDescending(Expression<Func<T, object>> func)
104         {
105             OrderByStr = ExpressionHelper.GetSqlByExpression(func.Body) + "DESC ";
106         }
107 
108         /// <summary>
109         /// 排序
110         /// </summary>
111         /// <param name="func"></param>
112         public void OrderBy(Expression<Func<T, object>> func)
113         {
114             OrderByStr = ExpressionHelper.GetSqlByExpression(func.Body) + " ";
115         }
116         #endregion
117 
118         #region 连接
119         /// <summary>
120         ///join表链接(exp和Target表相连时使用此方法)
121         /// </summary>
122         /// <typeparam name="Target"></typeparam>
123         /// <param name="func"></param>
124         public void Join<Target>(Expression<Func<Target,T,bool>> func)
125         {
126             var targetfields = "," + EntityHelper.GetFiledString<Target>();
127             Field += targetfields;
128             JoinStr += "INNER JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
129             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
130         }
131 
132         /// <summary>
133         /// join表连接(TSource和Target表相连时使用此方法)
134         /// </summary>
135         /// <typeparam name="TSource"></typeparam>
136         /// <typeparam name="Target"></typeparam>
137         /// <param name="func"></param>
138         public void Join<TSource, Target>(Expression<Func<TSource, Target, bool>> func)
139         {
140             if (!Field.Contains(EntityHelper.GetFiledString<TSource>()))
141             {
142                 throw new NotSupportedException("联表时还没有联接: " + EntityHelper.GetTableName<TSource>());
143             }
144             var targetfields = "," + EntityHelper.GetFiledString<Target>();
145             Field += targetfields;
146             JoinStr += "INNER JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
147             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
148         }
149         #endregion
150 
151         #region 左连接
152         /// <summary>
153         /// 左连接
154         /// </summary>
155         /// <typeparam name="Target"></typeparam>
156         /// <param name="func"></param>
157         public void LeftJoin<Target>(Expression<Func<Target, T, bool>> func)
158         {
159             Field += "," + EntityHelper.GetFiledString<Target>();
160             JoinStr += "LEFT JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
161             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
162         }
163 
164         /// <summary>
165         /// 左连接（条件中不包括exp）
166         /// </summary>
167         /// <typeparam name="TSource"></typeparam>
168         /// <typeparam name="Target"></typeparam>
169         /// <param name="func"></param>
170         public void LeftJoin<TSource, Target>(Expression<Func<TSource, Target, bool>> func)
171         {
172             if (!Field.Contains(EntityHelper.GetFiledString<TSource>()))
173             {
174                 throw new NotSupportedException("联表时还没有联接: " + EntityHelper.GetTableName<TSource>());
175             }
176             Field += "," + EntityHelper.GetFiledString<Target>();
177             JoinStr += "LEFT JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
178             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
179         }
180         #endregion
181 
182         #region 右连接
183         /// <summary>
184         /// 右连接
185         /// </summary>
186         /// <typeparam name="Target"></typeparam>
187         /// <param name="func"></param>
188         public void RightJoin<Target>(Expression<Func<Target, T, bool>> func)
189         {
190             Field += "," + EntityHelper.GetFiledString<Target>();
191             JoinStr += "RIGHT JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
192             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
193         }
194 
195         /// <summary>
196         /// 右连接（条件中不包括exp）
197         /// </summary>
198         /// <typeparam name="TSource"></typeparam>
199         /// <typeparam name="Target"></typeparam>
200         /// <param name="func"></param>
201         public void RightJoin<TSource, Target>(Expression<Func<TSource, Target, bool>> func)
202         {
203             if (!Field.Contains(EntityHelper.GetFiledString<TSource>()))
204             {
205                 throw new NotSupportedException("联表时还没有联接: " + EntityHelper.GetTableName<TSource>());
206             }
207             Field += "," + EntityHelper.GetFiledString<Target>();
208             JoinStr += "RIGHT JOIN " + EntityHelper.GetTableName<Target>() + " ON ";
209             JoinStr += ExpressionHelper.GetSqlByExpression(func.Body) + " ";
210         }
211         #endregion
212 
213         /// <summary>
214         /// 排重
215         /// </summary>
216         public void Distinct()
217         {
218             IsDistinct = true;
219         }
220     }
221 }

posted on 2016-11-28 14:39 Kakura 阅读(769) 评论(0) 编辑收藏举报

刷新页面返回顶部

Kakura

SqlHelper简单实现（通过Expression和反射）9.Sql表述对象SqlSession

导航

公告