123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339 |
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Collections;
- namespace Ant.ORM.MakeSql
- {
- #region 定义牧举
- public enum SqlLogicOper
- {
- /// <summary>
- /// 并且
- /// </summary>
- AND, // AND
- /// <summary>
- /// 或者
- /// </summary>
- OR, // OR
- }
- public enum SqlCompareOper
- {
- /// <summary>
- /// 大于
- /// </summary>
- MORE_THAN, // >
- /// <summary>
- /// 小于
- /// </summary>
- LESS_THAN, // <
- /// <summary>
- /// 小于等于
- /// </summary>
- NOT_MORE_THAN, // <=
- /// <summary>
- /// 大于等于
- /// </summary>
- NOT_LESS_THAN, // >=
- /// <summary>
- /// 等于
- /// </summary>
- EQUAL, // =
- /// <summary>
- /// 不等于
- /// </summary>
- NOT_EQUAL, // <>
- /// <summary>
- /// 模糊匹配
- /// </summary>
- LIKE, // LIKE
- /// <summary>
- /// 不匹配
- /// </summary>
- NOT_LIKE, // NOT LIKE
- /// <summary>
- /// 包含
- /// </summary>
- IN, // IN
- /// <summary>
- /// 区间
- /// </summary>
- BETWEEN, // BETWEEN
- }
- #endregion
- /// <summary>
- /// SQL条件集合,
- /// 负责处理各个条件的合并和最终SQL的生成。
- /// </summary>
- public class SqlConditionList
- {
- // 缓存所有条件
- private ArrayList _condList = new ArrayList();
- public SqlConditionList()
- {
- }
- /// <summary>
- /// 添加一个条件
- /// </summary>
- /// <param name="cond">条件</param>
- public SqlConditionList Add(SqlCondition cond)
- {
- _condList.Add(cond);
- return this;
- }
- /// <summary>
- /// 添加一个条件
- /// </summary>
- /// <param name="condition">条件</param>
- /// <param name="logicOper">与前一个条件的关系</param>
- public SqlConditionList AddOrLogic(SqlCondition condition)
- {
- SqlCondition cond = null;
- if (_condList.Count > 0)
- {
- SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
- cond = Merge(lastCond, condition, SqlLogicOper.OR);
- }
- else
- {
- cond = condition;
- }
- //remove all exit condition
- this.Clear(cond);
- _condList.Add(cond);
- return this;
- }
- /// <summary>
- /// 添加一个条件
- /// </summary>
- /// <param name="condition">条件</param>
- /// <param name="logicOper">与前一个条件的关系</param>
- public SqlConditionList AddAndLogic(SqlCondition condition)
- {
- SqlCondition cond = null;
- if (_condList.Count > 0)
- {
- SqlCondition lastCond = (SqlCondition)_condList[_condList.Count - 1];
- cond = Merge(lastCond, condition, SqlLogicOper.AND);
- }
- else
- {
- cond = condition;
- }
- //remove all exit condition
- this.Clear(cond);
- _condList.Add(cond);
- return this;
- }
- /// <summary>
- /// 合并两个条件,合二为一。
- /// </summary>
- /// <param name="cond1"></param>
- /// <param name="cond2"></param>
- /// <param name="logicOper"></param>
- /// <returns></returns>
- public static SqlCondition Merge(SqlCondition cond1, SqlCondition cond2, SqlLogicOper logicOper)
- {
- return new SqlConditionRelation(cond1, cond2, logicOper);
- }
- /// <summary>
- /// 生成SQL语句。
- /// </summary>
- /// <returns></returns>
- public string ToSql()
- {
- StringBuilder buff = new StringBuilder();
- foreach (SqlCondition cond in _condList)
- {
- buff.Append(cond.ToSql());
- }
- return buff.ToString();
- }
- /// <summary>
- /// 去除条件
- /// </summary>
- /// <param name="condition"></param>
- private void Clear(SqlCondition condition)
- {
- if (_condList.Contains(condition))
- {
- _condList.Remove(condition);
- }
- if (condition.GetType() == typeof(SqlConditionRelation))
- {
- SqlConditionRelation richCond =
- (SqlConditionRelation)condition;
- this.Clear(richCond._cond1);
- this.Clear(richCond._cond2);
- }
- }
- }
- /// <summary>
- /// SQL条件,也就是WHERE部分。
- /// </summary>
- public class SqlCondition
- {
- private static string[] _compareOpers = new string[] {
- " > ", " < ", " <= ", " >= ", " = ", " <> ",
- " LIKE ", " NOT LIKE ", " IN " , " BETWEEN "};
- private string _filedName;
- private object _value;
- private SqlCompareOper _compareOper;
- private string _templateName;
- protected SqlCondition()
- {
- }
- public SqlCondition(SqlCompareOper compareOper,
- string fieldName, object value)
- {
- _compareOper = compareOper;
- _filedName = fieldName;
- _value = value;
- }
- // 生成条件的SQL
- public virtual string ToSql()
- {
- if (_value == null)
- {
- throw new Exception("Can not parse SQL because value is null.");
- }
- StringBuilder buff = new StringBuilder();
- if (_compareOper == SqlCompareOper.IN)
- {
- if (!_value.GetType().IsSubclassOf(typeof(Array)))
- {
- throw new Exception("Can not parse [IN].");
- }
- buff.Append(_filedName);
- buff.Append(" IN (");
- Array arrVal = (Array)_value;
- foreach (object val in arrVal)
- {
- buff.Append(this.ToValueSqlString(val)).Append(",");
- }
- buff.Remove(buff.Length - 1, 1);
- buff.Append(") ");
- }
- else if (_compareOper == SqlCompareOper.BETWEEN)
- {
- if (!_value.GetType().IsSubclassOf(typeof(Array)))
- {
- throw new Exception("Can not parse [BETWEEN].");
- }
- Array arrVal = (Array)_value;
- if (arrVal.Length != 2)
- {
- throw new Exception("Can not parse [BETWEEN].");
- }
- buff.Append(" (");
- buff.Append(_filedName);
- buff.Append(" BETWEEN ");
- buff.Append(arrVal.GetValue(0));
- buff.Append(" AND ");
- buff.Append(arrVal.GetValue(1));
- buff.Append(") ");
- }
- else
- {
- buff.Append(_filedName);
- buff.Append(_compareOpers[(int)_compareOper]);
- buff.Append(this.ToValueSqlString(_value));
- }
- return buff.ToString();
- }
- /// <summary>
- ///
- /// </summary>
- /// <param name="value"></param>
- /// <returns></returns>
- private string ToValueSqlString(object value)
- {
- StringBuilder buff = new StringBuilder();
- if (value.GetType() == typeof(Int16)
- || value.GetType() == typeof(Int32)
- || value.GetType() == typeof(Int64)
- || value.GetType() == typeof(Decimal)
- || value.GetType() == typeof(Single)
- || value.GetType() == typeof(Double))
- {
- buff.Append(value);
- }
- else if (value.GetType() == typeof(Boolean))
- {
- if ((bool)value)
- {
- buff.Append(1);
- }
- else
- {
- buff.Append(0);
- }
- }
- else if (value.GetType() == typeof(DateTime))
- {
- string dValue = ((DateTime)value).
- ToString("yyyy-MM-dd hh:mm:ss.fff");
- buff.Append("'").Append(dValue).Append("'");
- }
- else
- {
- string sValue = value.ToString().Replace("'", "''");
- buff.Append("'").Append(sValue).Append("'");
- }
- return buff.ToString();
- }
- }
- /// <summary>
- /// 两个条件合并后的条件。
- /// </summary>
- public class SqlConditionRelation : SqlCondition
- {
- private static string[] _logicOpers = new string[] { " AND ", " OR " };
- internal SqlLogicOper _logicOper;
- internal SqlCondition _cond1;
- internal SqlCondition _cond2;
- internal SqlConditionRelation(SqlCondition cond1,
- SqlCondition cond2, SqlLogicOper logicOper)
- {
- _cond1 = cond1;
- _cond2 = cond2;
- _logicOper = logicOper;
- }
- public override string ToSql()
- {
- StringBuilder buff = new StringBuilder();
- buff.Append(_cond1.ToSql())
- .Append(_logicOpers[(int)_logicOper])
- .Append(_cond2.ToSql());
- if (_logicOper == SqlLogicOper.OR)
- {
- buff.Insert(0, " (");
- buff.Append(") ");
- }
- return buff.ToString();
- }
- }
- }
|