自己动手写ORM（02）：Sql生成器实现

上一节中鄙人通过解析表达式树生成Sql碎片，其中我也把解析表达式类代码贴了出来，文章发布之后我对ExpressionAnalyzer类做了些改动，下面我还会将代码贴出来，废话不多说，直接进入今天的主题。

实体类设计：

　　首先，我觉得要想直接通过实体类生成Sql语句，那么你可能要知道这个实体类对应数据库表中的主键和外键是什么，在此我加入了两个特性来标识主键和外键关系。如下

/// <summary>

/// 外键表特性（导航属性）

/// </summary>

public class GuidanceAttribute : System.Attribute

{

/// <summary>

/// 依赖字段

/// </summary>

public string DepandField { get; set; }

}

public class MessageAttribute : System.Attribute

{

/// <summary>

/// 链接字符串名

/// </summary>

public string ConStr { get; set; }

}

public class PrimaryKeyAttribute : System.Attribute

{

}

　　实体类如下：

 1 using LC.Model;

 2 using LC.Model.Attribute;

 3 using System;

 4 using System.Collections.Generic;

 5 using System.Linq;

 6 using System.Text;

 7 using System.Threading.Tasks;

 8 using LC.Model.Common;

 9 using LC.Test;

10 using LC.Test.Model;

11

12 namespace LC.Model

13 {

14     [Message(ConStr = "sql")]

15     public class SYS_User

16     {

17         [PrimaryKey]

18         public string Id { get; set; }

19         public string UserName { get; set; }

20

21         public Guid? RoleId { get; set; }

22         public Guid? CityId { get; set; }

23         [Guidance(DepandField = "RoleId")]

24         public SYS_Role Role { get; set; }

25

26         [Guidance(DepandField = "CityId")]

27         public City City { get; set; }

28

29         public int Gender { get; set; }

30

31         public bool Deleted { get; set; }

32

33         public DateTime CreateTime { get; set; }

34     }

35 }

　　那么在生成sql的过程中，我需要知道实体所对应表的主键和外键关系，此关系我保存在类TableInfo中：

1  public class TableInfo

2     {

3         public string ConStr { get; set; }

4         public string RName { get; set; }

5         public string TableName { get; set; }

6         public string PrimaryKey { get; set; }

7         public Dictionary<string, TableInfo> ForeignRefs { get; set; }

8     }

　　具体怎么将实体类转换成TableInfo，在此我暂时使用反射，不过多解释，因为暂时还不知道有什么方法不用反射获取特性。

　　有了TableInfo，我们就可以轻松的生成Sql了。

查询命令生成器设计：

　　下面上图，这是我用画图工具话的，不要问我为什么，因为电脑没有建模工具。。。

　　直接上代码：

 1     public interface ICreator

 2     {

 3         SqlDebris Debris { get; set; }

 4     }

 5

 6     public class BaseCreator : ICreator

 7     {

 8         public SqlDebris Debris { get; set; }

 9         public BaseCreator()

10         {

11             Debris = new SqlDebris();

12         }

13     }

14     public interface IQuery : ICreator

15     {

16         void CreateFrom(AnalysisTable data, TableInfo tableInfo);

17         void CreateSelect(AnalysisData data);

18         void CreateWhere(AnalysisData data);

19         void AppendOrder(AnalysisData data, OrderTypeEnum orderType);

20         void AppendSkip(int count);

21         void AppendTake(int count);

22         void GetCount();

23     }

　　下面是QueryCreator的实现：

  1  public class QueryCreator : BaseCreator, IQuery

  2     {

  3         public virtual void CreateSelect(AnalysisData data)

  4         {

  5             var primaryTable = data.Table;

  6             StringBuilder sb = new StringBuilder();

  7             //默认查询全部列

  8             if (data.StackList.Count <= 0)

  9             {

 10                 sb.AppendFormat("[{0}].*", primaryTable.RName);

 11             }

 12             else

 13             {

 14                 //查询部分列

 15                 for (int i = 0; i < data.StackList.Count; i += 3)

 16                 {

 17                     sb.AppendFormat("{0} {1} {2},", data.StackList[i], data.StackList[i + 1], data.StackList[i + 2]);

 18                 }

 19                 sb.Remove(sb.Length - 1, 1);

 20             }

 21             Debris.Select = sb.ToString();

 22         }

 23         public virtual void CreateFrom(AnalysisTable anlyTable, TableInfo tableInfo)

 24         {

 25             if (null == anlyTable)

 26             {

 27                 throw new BusinessException(BusinessRes.TableCanNotBeEmpty);

 28             }

 29             //默认排序信息

 30             if (string.IsNullOrEmpty(Debris.Order))

 31             {

 32                 Debris.Order = string.Format("[{0}].{1} {2}", anlyTable.RName, tableInfo.PrimaryKey, System.Enum.GetName(typeof(OrderTypeEnum), OrderTypeEnum.ASC));

 33             }

 34             StringBuilder sb = new StringBuilder();

 35             sb.AppendFormat("[{0}] AS [{1}]", anlyTable.Name, anlyTable.RName);

 36             AppendLeftJoinTables(anlyTable, tableInfo, sb);

 37             Debris.From = sb.ToString();

 38         }

 39         public virtual void CreateWhere(AnalysisData data)

 40         {

 41             if (data == null || data.StackList.Count() <= 0)

 42             {

 43                 return;

 44             }

 45             Debris.Where = string.Join(" ", data.StackList);

 46         }

 47         public virtual void AppendSkip(int count)

 48         {

 49             Debris.Skip = count;

 50         }

 51         public virtual void AppendTake(int count)

 52         {

 53             Debris.Take = count;

 54         }

 55         public virtual void AppendOrder(AnalysisData data, OrderTypeEnum orderType)

 56         {

 57             if (data.StackList.Count <= 0)

 58             {

 59                 return;

 60             }

 61             var field = data.StackList.First();

 62             StringBuilder sb = new StringBuilder(Debris.Order);

 63             if (string.IsNullOrEmpty(Debris.Order))

 64             {

 65                 sb.AppendFormat("{0} {1}", field, System.Enum.GetName(typeof(OrderTypeEnum), orderType));

 66                 Debris.Order = sb.ToString();

 67                 return;

 68             }

 69             sb.AppendFormat(",{0} {1}", field, System.Enum.GetName(typeof(OrderTypeEnum), orderType));

 70             Debris.Order = sb.ToString();

 71         }

 72         public void GetCount()

 73         {

 74             Debris.Select = "COUNT(1)";

 75         }

 76

 77         private KeyValuePair<string, TableInfo> GetForeignReference(TableInfo tInfo, string rName)

 78         {

 79             var keyValue = tInfo.ForeignRefs.Where(u => u.Value.RName == rName).FirstOrDefault();

 80             if (string.IsNullOrEmpty(keyValue.Key))

 81             {

 82                 foreach (var item in tInfo.ForeignRefs)

 83                 {

 84                     var foreignTable = GetForeignReference(item.Value, rName);

 85                     if (!string.IsNullOrEmpty(keyValue.Key))

 86                     {

 87                         return foreignTable;

 88                     }

 89                 }

 90

 91             }

 92

 93             return keyValue;

 94         }

 95         private void AppendLeftJoinTables(AnalysisTable anlyTable, TableInfo tableInfo, StringBuilder sb)

 96         {

 97             ///添加关系表信息

 98             foreach (var item in anlyTable.leftJoins)

 99             {

100                 var _foreignRef = GetForeignReference(tableInfo, item.RName);

101                 if (string.IsNullOrEmpty(_foreignRef.Key))

102                 {

103                     throw new BusinessException(BusinessRes.WhitoutThisForeignReference);

104                 }

105                 sb.AppendFormat(" LEFT JOIN [{0}] AS [{1}] ON [{1}].{2}=[{3}].{4} ", item.Name, item.RName, _foreignRef.Value.PrimaryKey, anlyTable.RName, _foreignRef.Key);

106                 AppendLeftJoinTables(item, _foreignRef.Value, sb);

107             }

108         }

109     }

　　到此为止，生成的Sql依然是不完整的Sql碎片，我们需要将碎片合并成Sql语句，我写了一个工具类如下：

　　保存碎片类：

 1 public class SqlDebris

 2     {

 3         public string Where { get; set; }

 4         #region 查询

 5         public string From { get; set; }

 6         public string Select { get; set; }

 7         public string Order { get; set; }

 8         public int Skip { get; set; }

 9         public int? Take { get; set; }

10         #endregion

11         #region 修改

12         public string Set { get; set; }

13         public string Update { get; set; }

14         #endregion

15         #region 新增

16

17         public string InsertTable { get; set; }

18         public string Columns { get; set; }

19         public string Values { get; set; }

20

21         #endregion

22     }

　　通过下面工具类生成完整Sql语句：

 1 public class CommandTextHelper

 2     {

 3         public static string GetSelectCommandText(ICreator creator)

 4         {

 5             var debris = creator.Debris;

 6             StringBuilder sb = new StringBuilder();

 7             if (debris.Take == null)

 8             {

 9                 sb.Append(" SELECT * FROM (");

10             }

11             else

12             {

13                 //分页

14                 sb.AppendFormat(" SELECT TOP {0} * FROM (", debris.Take);

15             }

16             sb.AppendFormat("SELECT {0},ROW_NUMBER() OVER(ORDER BY  {2} ) as ROWNUMBER  FROM {1}", debris.Select, debris.From, debris.Order);

17             //条件

18             if (!string.IsNullOrEmpty(debris.Where))

19             {

20                 sb.AppendFormat(" WHERE {0} ", debris.Where);

21             }

22             sb.Append(") as NEW_TABLE ");

23             sb.AppendFormat(" WHERE ROWNUMBER>{0}", debris.Skip);

24             return sb.ToString();

25         }

26

27         public static string GetCountCommandText(ICreator creator)

28         {

29             var debris = creator.Debris;

30             StringBuilder sb = new StringBuilder();

31

32             sb.AppendFormat("SELECT {0} FROM {1}", debris.Select, debris.From);

33             //条件

34             if (!string.IsNullOrEmpty(debris.Where))

35             {

36                 sb.AppendFormat(" WHERE {0} ", debris.Where);

37             }

38             return sb.ToString();

39         }

40

41         public static string GetUpdateCommandText(ICreator creator)

42         {

43             var debris = creator.Debris;

44             StringBuilder sb = new StringBuilder();

45             sb.AppendFormat("UPDATE {0} ", debris.Update);

46             sb.AppendFormat("SET {0} ", debris.Set);

47             sb.AppendFormat("WHERE {0}", debris.Where);

48             return sb.ToString();

49         }

50         public static string GetInsertCommandText(ICreator creator)

51         {

52             var debris = creator.Debris;

53             StringBuilder sb = new StringBuilder();

54             sb.AppendFormat("INSERT INTO {0}", debris.InsertTable);

55             sb.AppendFormat("({0}) ", debris.Columns);

56             sb.AppendFormat("VALUES({0})", debris.Values);

57             return sb.ToString();

58         }

59         public static string GetDeleteCommandText(ICreator creator)

60         {

61             var debris = creator.Debris;

62             StringBuilder sb = new StringBuilder();

63             sb.AppendFormat("DELETE FROM {0}", debris.From);

64             sb.AppendFormat(" WHERE {0}", debris.Where);

65             return sb.ToString();

66         }

67     }

　　下面是CommandFactory<TEntity>:

1  public class CommandFactory<TEntity>

2     {

3         protected TableInfo _tableInfo;

4         public CommandFactory()

5         {

6             _tableInfo = new Mapper().MapToTable(typeof(TEntity));

7         }

8     }

下面是QueryCommand<TEntity>实现:

 1  public class QueryCommand<TEntity> : CommandFactory<TEntity>

 2     {

 3         //sql碎片生成器

 4         private IQuery _creator;

 5         //查询参数

 6         private Dictionary<string, object> _params;

 7

 8         private AnalysisTable _table;

 9

10         public QueryCommand()

11         {

12             _creator = new QueryCreator();

13         }

14         public QueryCommand<TEntity> Where(Expression<Func<TEntity, bool>> exp)

15         {

16             var retData = new ExpressionAnalyzer(exp).GetAnalysisResult();

17             _creator.CreateWhere(retData);

18             _params = retData.ParamList;

19             _table = retData.Table;

20             return this;

21         }

22         public QueryCommand<TEntity> OrderBy(Expression<Func<TEntity, object>> exp)

23         {

24             _creator.AppendOrder(new ExpressionAnalyzer(exp).GetAnalysisResult(), OrderTypeEnum.ASC);

25             return this;

26         }

27         public QueryCommand<TEntity> OrderByDescding(Expression<Func<TEntity, object>> exp)

28         {

29             _creator.AppendOrder(new ExpressionAnalyzer(exp).GetAnalysisResult(), OrderTypeEnum.DESC);

30             return this;

31         }

32         public QueryCommand<TEntity> ThenBy(Expression<Func<TEntity, object>> exp)

33         {

34             return OrderBy(exp);

35         }

36         public QueryCommand<TEntity> ThenByDescding(Expression<Func<TEntity, object>> exp)

37         {

38             return OrderByDescding(exp);

39         }

40         public QueryCommand<TEntity> Skip(int count)

41         {

42             _creator.AppendSkip(count);

43             return this;

44         }

45         public QueryCommand<TEntity> Take(int count)

46         {

47             _creator.AppendTake(count);

48             return this;

49         }

50         public Command<TResult> GetSelectCommand<TResult>(Expression<Func<TEntity, TResult>> exp)

51         {

52             var _result = new ExpressionAnalyzer(exp, _table).GetAnalysisResult();

53             _creator.CreateSelect(_result);

54             _creator.CreateFrom(_result.Table, _tableInfo);

55             return new Command<TResult>()

56             {

57                 ConStr = _tableInfo.ConStr,

58                 CommandText = CommandTextHelper.GetSelectCommandText(_creator),

59                 Params = _params

60             };

61         }

62         public Command<int> GetCountCommand(Expression<Func<TEntity, bool>> exp)

63         {

64             var retData = new ExpressionAnalyzer(exp).GetAnalysisResult();

65             _creator.CreateWhere(retData);

66             _creator.GetCount();

67             _creator.CreateFrom(retData.Table, _tableInfo);

68             return new Command<int>()

69             {

70                 CommandText = CommandTextHelper.GetCountCommandText(_creator),

71                 ConStr = _tableInfo.ConStr,

72                 Params = retData.ParamList

73             };

74         }

75     }

　　下面是测试结果仅供参考：

 1         [TestMethod]

 2         public void TestQueryCommand()

 3         {

 4             var param = new SYS_User()

 5             {

 6                 UserName = "lichun",

 7                 RoleId = Guid.NewGuid()

 8             };

 9             QueryCommand<SYS_User> f = new QueryCommand<SYS_User>();

10             var command = f.Where(u => u.City.Name == "cengdu" && u.Role.Config.Name.Contains(param.UserName)).GetSelectCommand(u => new

11             {

12                 u.Id,

13                 u.Role.Name

14             });

15             ShowCommand(command);

16         }

17

18

19         public void ShowCommand<TResult>(Command<TResult> command)

20         {

21 　　　　　　　Console.WriteLine("链接字符串：");

22             Console.WriteLine(command.ConStr);

23             Console.WriteLine("----------------------------------");
　　　　　　　　　Console.WriteLine("Sql命令：");

24             Console.WriteLine(command.CommandText);

25

26             Console.WriteLine("----------------------------------");

27             if (command.Params != null)

28             {
　　　　　　　　　　　Console.WriteLine("参数：");

29                 foreach (var item in command.Params)

30                 {

31

32                     Console.WriteLine("{0}  →  {1}", item.Key, item.Value);

33                 }

34             }

35

36         }

测试结果如下：

　　修改的ExpressionAnalyzer如下：

  1  public class ExpressionAnalyzer

  2     {

  3         /// <summary>

  4         /// 表达式所有参数集合

  5         /// </summary>

  6         private Dictionary<string, object> _params;

  7         /// <summary>

  8         /// 命名参数别名

  9         /// </summary>

 10         private const string _argName = "TAB";

 11         /// <summary>

 12         /// 解析结果

 13         /// </summary>

 14         private AnalysisData _resultData;

 15

 16         public ExpressionAnalyzer()

 17         {

 18             _resultData = new AnalysisData();

 19             _params = new Dictionary<string, object>();

 20         }

 21         public ExpressionAnalyzer(LambdaExpression exp, AnalysisTable table = null)

 22             : this()

 23         {

 24             if (table != null)

 25             {

 26                 _resultData.Table = table;

 27             }

 28

 29             if (exp != null)

 30             {

 31                 AppendParams(GetChildValue(exp.Body), _params);

 32                 foreach (var item in exp.Parameters)

 33                 {

 34                     AnalysisTables(item);

 35                 }

 36                 AnalysisExpression(exp.Body, true);

 37             }

 38         }

 39

 40

 41

 42         public AnalysisData GetAnalysisResult()

 43         {

 44             return _resultData;

 45         }

 46

 47

 48         /// <summary>

 49         /// 解析表达式

 50         /// </summary>

 51         /// <param name="exp"></param>

 52         /// <param name="isLeftChild"></param>

 53         private void AnalysisExpression(Expression exp, bool isLeftChild = true)

 54         {

 55             switch (exp.NodeType)

 56             {

 57                 case ExpressionType.AndAlso:

 58                     _resultData.StackList.Add("(");

 59                     AnalysisExpression(GetChildExpression(exp));

 60                     _resultData.StackList.Add(")");

 61                     _resultData.StackList.Add("AND");

 62                     _resultData.StackList.Add("(");

 63                     AnalysisExpression(GetChildExpression(exp, false), false);

 64                     _resultData.StackList.Add(")");

 65                     break;

 66                 case ExpressionType.OrElse:

 67                     _resultData.StackList.Add("(");

 68                     AnalysisExpression(GetChildExpression(exp));

 69                     _resultData.StackList.Add(")");

 70                     _resultData.StackList.Add("OR");

 71                     _resultData.StackList.Add("(");

 72                     AnalysisExpression(GetChildExpression(exp, false), false);

 73                     _resultData.StackList.Add(")");

 74                     break;

 75                 case ExpressionType.Equal:

 76                     AnalysisExpression(GetChildExpression(exp));

 77                     _resultData.StackList.Add("=");

 78                     AnalysisExpression(GetChildExpression(exp, false), false);

 79                     break;

 80                 case ExpressionType.NotEqual:

 81                     AnalysisExpression(GetChildExpression(exp));

 82                     _resultData.StackList.Add("!=");

 83                     AnalysisExpression(GetChildExpression(exp, false), false);

 84                     break;

 85                 case ExpressionType.GreaterThanOrEqual:

 86                     AnalysisExpression(GetChildExpression(exp));

 87                     _resultData.StackList.Add(">=");

 88                     AnalysisExpression(GetChildExpression(exp, false), false);

 89                     break;

 90                 case ExpressionType.GreaterThan:

 91                     AnalysisExpression(GetChildExpression(exp));

 92                     _resultData.StackList.Add(">");

 93                     AnalysisExpression(GetChildExpression(exp, false), false);

 94                     break;

 95                 case ExpressionType.LessThan:

 96                     AnalysisExpression(GetChildExpression(exp));

 97                     _resultData.StackList.Add("<");

 98                     AnalysisExpression(GetChildExpression(exp, false), false);

 99                     break;

100                 case ExpressionType.LessThanOrEqual:

101                     AnalysisExpression(GetChildExpression(exp));

102                     _resultData.StackList.Add("<=");

103                     AnalysisExpression(GetChildExpression(exp, false), false);

104                     break;

105                 case ExpressionType.Call:

106                     var imExp = exp as MethodCallExpression;

107                     AnalysisExpression(imExp.Object, true);

108                     _resultData.StackList.Add("LIKE");

109                     if (imExp.Arguments.Count > 0)

110                     {

111                         var arg0 = imExp.Arguments[0] as MemberExpression;

112                         _resultData.StackList.Add("'%'+");

113                         AnalysisExpression(imExp.Arguments[0], false);

114                         _resultData.StackList.Add("+'%'");

115                     }

116                     break;

117                 case ExpressionType.MemberAccess:

118                     if (isLeftChild)

119                     {

120                         AnalysisTables(exp);

121                         var mberExp = exp as MemberExpression;

122                         var parentName = GetExpressionName(mberExp.Expression);

123                         if (!string.IsNullOrEmpty(parentName))

124                         {

125                             _resultData.StackList.Add(string.Format("[{0}].{1}", parentName, GetExpressionName(exp)));

126                             break;

127                         }

128                         _resultData.StackList.Add(GetExpressionName(exp));

129                     }

130                     else

131                     {

132                         var paramName = GetParamName(exp);

133                         _resultData.ParamList.Add(paramName, _params[paramName]);

134                         _resultData.StackList.Add(paramName);

135                     }

136                     break;

137                 case ExpressionType.Constant:

138                     var constent = exp as ConstantExpression;

139                     if (constent.Value == null)

140                     {

141                         var op = _resultData.StackList.ElementAt(_resultData.StackList.Count - 1);

142                         _resultData.StackList.RemoveAt(_resultData.StackList.Count - 1);

143                         if (string.Equals(op, "="))

144                         {

145                             _resultData.StackList.Add("IS NULL");

146                         }

147                         else

148                         {

149                             _resultData.StackList.Add("IS NOT NULL");

150                         }

151                         break;

152                     }

153                     if (constent.Value.GetType() == typeof(String))

154                     {

155                         _resultData.StackList.Add(string.Format("'{0}'", constent.Value));

156                         break;

157                     }

158                     if (constent.Value.GetType() == typeof(bool))

159                     {

160                         if (_resultData.StackList.Count > 0)

161                         {

162                             var value = Convert.ToBoolean(constent.Value);

163                             _resultData.StackList.Add(string.Format("{0}", value ? "1" : "0"));

164                         }

165

166                         break;

167                     }

168                     _resultData.StackList.Add(string.Format("{0}", constent.Value));

169                     break;

170                 case ExpressionType.Convert:

171                     var uExp = exp as UnaryExpression;

172                     AnalysisExpression(uExp.Operand, isLeftChild);

173                     break;

174                 case ExpressionType.New:

175                     var newExp = exp as NewExpression;

176                     //解析查询字段

177                     for (int i = 0; i < newExp.Arguments.Count; i++)

178                     {

179                         AnalysisExpression(newExp.Arguments[i]);

180                         _resultData.StackList.Add("AS");

181                         _resultData.StackList.Add(string.Format("'{0}'", newExp.Members[i].Name));

182                     }

183                     break;

184                 case ExpressionType.Parameter:

185                     throw new BusinessException(BusinessRes.SelectObjectMastBeAnNewObject);

186                 //AnalysisExpression(Expression.New(exp.Type));

187                 //break;

188                 default:

189                     break;

190             }

191

192         }

193         /// <summary>

194         /// 获取孩子节点

195         /// </summary>

196         /// <param name="exp"></param>

197         /// <param name="getLeft"></param>

198         /// <returns></returns>

199         private Expression GetChildExpression(Expression exp, bool getLeft = true)

200         {

201             var className = exp.GetType().Name;

202             switch (className)

203             {

204                 case "BinaryExpression":

205                 case "LogicalBinaryExpression":

206                     var bExp = exp as BinaryExpression;

207                     return getLeft ? bExp.Left : bExp.Right;

208                 case "PropertyExpression":

209                 case "FieldExpression":

210                     var mberExp = exp as MemberExpression;

211                     return mberExp;

212                 case "MethodBinaryExpression":

213                     var mbExp = exp as BinaryExpression;

214                     return getLeft ? mbExp.Left : mbExp.Right;

215                 case "UnaryExpression":

216                     var unaryExp = exp as UnaryExpression;

217                     return unaryExp;

218                 case "ConstantExpression":

219                     var cExp = exp as ConstantExpression;

220                     return cExp;

221                 case "InstanceMethodCallExpressionN":

222                     var imExp = exp as MethodCallExpression;

223                     return imExp;

224                 default:

225                     return null;

226             }

227         }

228         /// <summary>

229         /// 获取变量名

230         /// </summary>

231         /// <param name="exp"></param>

232         /// <param name="isLeftChild"></param>

233         /// <returns></returns>

234         private string GetExpressionName(Expression exp)

235         {

236             var className = exp.GetType().Name;

237             switch (className)

238             {

239                 case "PropertyExpression":

240                 case "FieldExpression":

241                     var mberExp = exp as MemberExpression;

242                     return string.Format("{0}", mberExp.Member.Name);

243                 case "TypedParameterExpression":

244                     return _argName;

245                 default:

246                     return string.Empty;

247             }

248         }

249         /// <summary>

250         /// 获取参数名

251         /// </summary>

252         /// <param name="exp"></param>

253         /// <param name="isLeftChild"></param>

254         /// <returns></returns>

255         private string GetParamName(Expression exp)

256         {

257             var className = exp.GetType().Name;

258             switch (className)

259             {

260                 case "PropertyExpression":

261                 case "FieldExpression":

262                     var mberExp = exp as MemberExpression;

263                     return string.Format("@{0}", mberExp.Member.Name);

264                 case "TypedParameterExpression":

265                     var texp = exp as ParameterExpression;

266                     return string.Format("@{0}", texp.Name);

267                 default:

268                     return string.Empty;

269             }

270         }

271         /// <summary>

272         /// 解析表信息

273         /// </summary>

274         /// <param name="exp"></param>

275         /// <param name="refTable">引用表</param>

276         private void AnalysisTables(Expression exp, AnalysisTable refTable = null)

277         {

278             var className = exp.GetType().Name;

279             switch (className)

280             {

281                 case "PropertyExpression":

282                 case "FieldExpression":

283                     var mberExp = exp as MemberExpression;

284                     if (IsDefaultType(mberExp.Type))

285                     {

286                         AnalysisTables(mberExp.Expression);

287                         break;

288                     }

289                     var _tampTab = GetTableByRName(_resultData.Table, mberExp.Member.Name);

290                     if (_tampTab == null)

291                     {

292                         _tampTab = new AnalysisTable()

293                          {

294                              RName = mberExp.Member.Name,

295                              Name = mberExp.Type.Name,

296                              TableType = mberExp.Type

297

298                          };

299                         AnalysisTables(mberExp.Expression, _tampTab);

300                     }

301                     if (refTable != null)

302                     {

303                         _tampTab.leftJoins.Add(refTable);

304                     }

305                     break;

306                 case "TypedParameterExpression":

307                     //命名参数表达式

308                     var texp = exp as ParameterExpression;

309                     if (!IsDefaultType(texp.Type))

310                     {

311                         if (null == _resultData.Table)

312                         {

313                             _resultData.Table = new AnalysisTable()

314                              {

315                                  RName = _argName,

316                                  Name = texp.Type.Name,

317                                  TableType = texp.Type

318                              };

319                         }

320                         if (refTable != null)

321                         {

322                             _resultData.Table.leftJoins.Add(refTable);

323                         }

324                     }

325                     break;

326                 default:

327                     break;

328             }

329         }

330         /// <summary>

331         /// 遍历树，深度优先

332         /// </summary>

333         /// <param name="table"></param>

334         /// <param name="rName"></param>

335         /// <returns></returns>

336         private AnalysisTable GetTableByRName(AnalysisTable table, string rName)

337         {

338             var _tempTable = table;

339             if (_tempTable.RName == rName)

340             {

341                 return _tempTable;

342             }

343             foreach (var item in _tempTable.leftJoins)

344             {

345                 _tempTable = GetTableByRName(item, rName);

346                 if (_tempTable != null)

347                 {

348                     return _tempTable;

349                 }

350             }

351             return null;

352         }

353         /// <summary>

354         /// 解析获取表达式的值

355         /// </summary>

356         /// <param name="exp"></param>

357         /// <param name="leftChild"></param>

358         /// <returns></returns>

359         private object GetChildValue(Expression exp)

360         {

361             var className = exp.GetType().Name;

362             switch (className)

363             {

364                 case "BinaryExpression":

365                 case "LogicalBinaryExpression":

366                     var lExp = exp as BinaryExpression;

367                     var ret = GetChildValue(lExp.Left);

368                     if (IsNullDefaultType(ret))

369                     {

370                         ret = GetChildValue(lExp.Right);

371                     }

372                     return ret;

373                 case "MethodBinaryExpression":

374                     var mbExp = exp as BinaryExpression;

375                     var ret1 = GetChildValue(mbExp.Left);

376                     if (IsNullDefaultType(ret1))

377                     {

378                         ret1 = GetChildValue(mbExp.Right);

379                     }

380                     return ret1;

381

382                 case "PropertyExpression":

383                 case "FieldExpression":

384                     var mberExp = exp as MemberExpression;

385                     return GetChildValue(mberExp.Expression);

386                 case "ConstantExpression":

387                     var cExp = exp as ConstantExpression;

388                     return cExp.Value;

389                 case "UnaryExpression":

390                     var unaryExp = exp as UnaryExpression;

391                     return GetChildValue(unaryExp.Operand);

392                 case "InstanceMethodCallExpressionN":

393                     var imExp = exp as MethodCallExpression;

394                     if (imExp.Arguments.Count > 0)

395                     {

396                         return GetChildValue(imExp.Arguments[0]);

397                     }

398                     return null;

399                 default:

400                     return null;

401             }

402

403         }

404         /// <summary>

405         /// 初始化所有参数

406         /// </summary>

407         /// <param name="paramObj"></param>

408         private void AppendParams(object paramObj, Dictionary<string, object> _params)

409         {

410             if (IsNullDefaultType(paramObj))

411             {

412                 return;

413             }

414             if (_params == null)

415             {

416                 _params = new Dictionary<string, object>();

417             }

418             foreach (var item in paramObj.GetType().GetProperties())

419             {

420                 if (IsDefaultType(item.PropertyType))

421                 {

422                     var value = item.GetValue(paramObj, null);

423                     if (value != null)

424                     {

425                         _params.Add(string.Format("@{0}", item.Name), value);

426                     }

427                     continue;

428                 }

429

430                 AppendParams(item.GetValue(paramObj), _params);

431             }

432

433             foreach (var item in paramObj.GetType().GetFields())

434             {

435                 if (IsDefaultType(item.FieldType))

436                 {

437                     var value = item.GetValue(paramObj);

438                     if (value != null)

439                     {

440                         _params.Add(string.Format("@{0}", item.Name), value);

441                     }

442                     continue;

443                 }

444                 AppendParams(item.GetValue(paramObj), _params);

445             }

446         }

447         public Dictionary<string, object> GetParams(object paramObj)

448         {

449             Dictionary<string, object> dicParams = new Dictionary<string, object>();

450             AppendParams(paramObj, dicParams);

451             return dicParams;

452         }

453         /// <summary>

454         /// 判断是否是系统默认基本类型

455         /// </summary>

456         /// <param name="type"></param>

457         /// <returns></returns>

458         private bool IsNullDefaultType(object obj)

459         {

460             if (obj == null)

461             {

462                 return true;

463             }

464             return IsDefaultType(obj.GetType());

465         }

466         private bool IsDefaultType(Type type)

467         {

468             string defaultType = @"String|Boolean|Double|Int32|Int64|Int16|Single|DateTime|Decimal|Char|Object|Guid";

469

470             Regex e = new Regex(defaultType, RegexOptions.IgnoreCase);

471             if (type.Name.ToLower().Contains("nullable"))

472             {

473                 if (type.GenericTypeArguments.Count() > 0)

474                 {

475                     return e.IsMatch(type.GenericTypeArguments[0].Name);

476                 }

477             }

478             return e.IsMatch(type.Name);

479         }

480     }

　　共同学习共同进步，表达有些乱，希望各位大神多多包涵。

巴特西

自己动手写ORM（02）：Sql生成器实现

最新文章

热门文章