SqlConditionList.cs 9.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Collections;
  6. namespace Ant.ORM.MakeSql
  7. {
  8. #region 定义牧举
  9. public enum SqlLogicOper
  10. {
  11. /// <summary>
  12. /// 并且
  13. /// </summary>
  14. AND, // AND
  15. /// <summary>
  16. /// 或者
  17. /// </summary>
  18. OR, // OR
  19. }
  20. public enum SqlCompareOper
  21. {
  22. /// <summary>
  23. /// 大于
  24. /// </summary>
  25. MORE_THAN, // >
  26. /// <summary>
  27. /// 小于
  28. /// </summary>
  29. LESS_THAN, // <
  30. /// <summary>
  31. /// 小于等于
  32. /// </summary>
  33. NOT_MORE_THAN, // <=
  34. /// <summary>
  35. /// 大于等于
  36. /// </summary>
  37. NOT_LESS_THAN, // >=
  38. /// <summary>
  39. /// 等于
  40. /// </summary>
  41. EQUAL, // =
  42. /// <summary>
  43. /// 不等于
  44. /// </summary>
  45. NOT_EQUAL, // <>
  46. /// <summary>
  47. /// 模糊匹配
  48. /// </summary>
  49. LIKE, // LIKE
  50. /// <summary>
  51. /// 不匹配
  52. /// </summary>
  53. NOT_LIKE, // NOT LIKE
  54. /// <summary>
  55. /// 包含
  56. /// </summary>
  57. IN, // IN
  58. /// <summary>
  59. /// 区间
  60. /// </summary>
  61. BETWEEN, // BETWEEN
  62. }
  63. #endregion
  64. /// <summary>
  65. /// SQL条件集合,
  66. /// 负责处理各个条件的合并和最终SQL的生成。
  67. /// </summary>
  68. public class SqlConditionList
  69. {
  70. // 缓存所有条件
  71. private ArrayList _condList = new ArrayList();
  72. public SqlConditionList()
  73. {
  74. }
  75. /// <summary>
  76. /// 添加一个条件
  77. /// </summary>
  78. /// <param name="cond">条件</param>
  79. public SqlConditionList Add(SqlCondition cond)
  80. {
  81. _condList.Add(cond);
  82. return this;
  83. }
  84. /// <summary>
  85. /// 添加一个条件
  86. /// </summary>
  87. /// <param name="condition">条件</param>
  88. /// <param name="logicOper">与前一个条件的关系</param>
  89. public SqlConditionList AddOrLogic(SqlCondition condition)
  90. {
  91. SqlCondition cond = null;
  92. if (_condList.Count > 0)
  93. {
  94. SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
  95. cond = Merge(lastCond, condition, SqlLogicOper.OR);
  96. }
  97. else
  98. {
  99. cond = condition;
  100. }
  101. //remove all exit condition
  102. this.Clear(cond);
  103. _condList.Add(cond);
  104. return this;
  105. }
  106. /// <summary>
  107. /// 添加一个条件
  108. /// </summary>
  109. /// <param name="condition">条件</param>
  110. /// <param name="logicOper">与前一个条件的关系</param>
  111. public SqlConditionList AddAndLogic(SqlCondition condition)
  112. {
  113. SqlCondition cond = null;
  114. if (_condList.Count > 0)
  115. {
  116. SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
  117. cond = Merge(lastCond, condition, SqlLogicOper.AND);
  118. }
  119. else
  120. {
  121. cond = condition;
  122. }
  123. //remove all exit condition
  124. this.Clear(cond);
  125. _condList.Add(cond);
  126. return this;
  127. }
  128. /// <summary>
  129. /// 合并两个条件,合二为一。
  130. /// </summary>
  131. /// <param name="cond1"></param>
  132. /// <param name="cond2"></param>
  133. /// <param name="logicOper"></param>
  134. /// <returns></returns>
  135. public static SqlCondition Merge(SqlCondition cond1, SqlCondition cond2, SqlLogicOper logicOper)
  136. {
  137. return new SqlConditionRelation(cond1, cond2, logicOper);
  138. }
  139. /// <summary>
  140. /// 生成SQL语句。
  141. /// </summary>
  142. /// <returns></returns>
  143. public string ToSql()
  144. {
  145. StringBuilder buff = new StringBuilder();
  146. foreach (SqlCondition cond in _condList)
  147. {
  148. buff.Append(cond.ToSql());
  149. }
  150. return buff.ToString();
  151. }
  152. /// <summary>
  153. /// 去除条件
  154. /// </summary>
  155. /// <param name="condition"></param>
  156. private void Clear(SqlCondition condition)
  157. {
  158. if (_condList.Contains(condition))
  159. {
  160. _condList.Remove(condition);
  161. }
  162. if (condition.GetType() == typeof(SqlConditionRelation))
  163. {
  164. SqlConditionRelation richCond =
  165. (SqlConditionRelation)condition;
  166. this.Clear(richCond._cond1);
  167. this.Clear(richCond._cond2);
  168. }
  169. }
  170. }
  171. /// <summary>
  172. /// SQL条件,也就是WHERE部分。
  173. /// </summary>
  174. public class SqlCondition
  175. {
  176. private static string[] _compareOpers = new string[] {
  177. " > ", " < ", " <= ", " >= ", " = ", " <> ",
  178. " LIKE ", " NOT LIKE ", " IN " , " BETWEEN "};
  179. private string _filedName;
  180. private object _value;
  181. private SqlCompareOper _compareOper;
  182. private string _templateName;
  183. protected SqlCondition()
  184. {
  185. }
  186. public SqlCondition(SqlCompareOper compareOper,
  187. string fieldName, object value)
  188. {
  189. _compareOper = compareOper;
  190. _filedName = fieldName;
  191. _value = value;
  192. }
  193. // 生成条件的SQL
  194. public virtual string ToSql()
  195. {
  196. if (_value == null)
  197. {
  198. throw new Exception("Can not parse SQL because value is null.");
  199. }
  200. StringBuilder buff = new StringBuilder();
  201. if (_compareOper == SqlCompareOper.IN)
  202. {
  203. if (!_value.GetType().IsSubclassOf(typeof(Array)))
  204. {
  205. throw new Exception("Can not parse [IN].");
  206. }
  207. buff.Append(_filedName);
  208. buff.Append(" IN (");
  209. Array arrVal = (Array)_value;
  210. foreach (object val in arrVal)
  211. {
  212. buff.Append(this.ToValueSqlString(val)).Append(",");
  213. }
  214. buff.Remove(buff.Length - 1, 1);
  215. buff.Append(") ");
  216. }
  217. else if (_compareOper == SqlCompareOper.BETWEEN)
  218. {
  219. if (!_value.GetType().IsSubclassOf(typeof(Array)))
  220. {
  221. throw new Exception("Can not parse [BETWEEN].");
  222. }
  223. Array arrVal = (Array)_value;
  224. if (arrVal.Length != 2)
  225. {
  226. throw new Exception("Can not parse [BETWEEN].");
  227. }
  228. buff.Append(" (");
  229. buff.Append(_filedName);
  230. buff.Append(" BETWEEN ");
  231. buff.Append(arrVal.GetValue(0));
  232. buff.Append(" AND ");
  233. buff.Append(arrVal.GetValue(1));
  234. buff.Append(") ");
  235. }
  236. else
  237. {
  238. buff.Append(_filedName);
  239. buff.Append(_compareOpers[(int)_compareOper]);
  240. buff.Append(this.ToValueSqlString(_value));
  241. }
  242. return buff.ToString();
  243. }
  244. /// <summary>
  245. ///
  246. /// </summary>
  247. /// <param name="value"></param>
  248. /// <returns></returns>
  249. private string ToValueSqlString(object value)
  250. {
  251. StringBuilder buff = new StringBuilder();
  252. if (value.GetType() == typeof(Int16)
  253. || value.GetType() == typeof(Int32)
  254. || value.GetType() == typeof(Int64)
  255. || value.GetType() == typeof(Decimal)
  256. || value.GetType() == typeof(Single)
  257. || value.GetType() == typeof(Double))
  258. {
  259. buff.Append(value);
  260. }
  261. else if (value.GetType() == typeof(Boolean))
  262. {
  263. if ((bool)value)
  264. {
  265. buff.Append(1);
  266. }
  267. else
  268. {
  269. buff.Append(0);
  270. }
  271. }
  272. else if (value.GetType() == typeof(DateTime))
  273. {
  274. string dValue = ((DateTime)value).
  275. ToString("yyyy-MM-dd hh:mm:ss.fff");
  276. buff.Append("'").Append(dValue).Append("'");
  277. }
  278. else
  279. {
  280. string sValue = value.ToString().Replace("'", "''");
  281. buff.Append("'").Append(sValue).Append("'");
  282. }
  283. return buff.ToString();
  284. }
  285. }
  286. /// <summary>
  287. /// 两个条件合并后的条件。
  288. /// </summary>
  289. public class SqlConditionRelation : SqlCondition
  290. {
  291. private static string[] _logicOpers = new string[] { " AND ", " OR " };
  292. internal SqlLogicOper _logicOper;
  293. internal SqlCondition _cond1;
  294. internal SqlCondition _cond2;
  295. internal SqlConditionRelation(SqlCondition cond1,
  296. SqlCondition cond2, SqlLogicOper logicOper)
  297. {
  298. _cond1 = cond1;
  299. _cond2 = cond2;
  300. _logicOper = logicOper;
  301. }
  302. public override string ToSql()
  303. {
  304. StringBuilder buff = new StringBuilder();
  305. buff.Append(_cond1.ToSql())
  306. .Append(_logicOpers[(int)_logicOper])
  307. .Append(_cond2.ToSql());
  308. if (_logicOper == SqlLogicOper.OR)
  309. {
  310. buff.Insert(0, " (");
  311. buff.Append(") ");
  312. }
  313. return buff.ToString();
  314. }
  315. }
  316. }