using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Collections; namespace Ant.ORM.MakeSql { #region 定义牧举 public enum SqlLogicOper { /// /// 并且 /// AND, // AND /// /// 或者 /// OR, // OR } public enum SqlCompareOper { /// /// 大于 /// MORE_THAN, // > /// /// 小于 /// LESS_THAN, // < /// /// 小于等于 /// NOT_MORE_THAN, // <= /// /// 大于等于 /// NOT_LESS_THAN, // >= /// /// 等于 /// EQUAL, // = /// /// 不等于 /// NOT_EQUAL, // <> /// /// 模糊匹配 /// LIKE, // LIKE /// /// 不匹配 /// NOT_LIKE, // NOT LIKE /// /// 包含 /// IN, // IN /// /// 区间 /// BETWEEN, // BETWEEN } #endregion /// /// SQL条件集合, /// 负责处理各个条件的合并和最终SQL的生成。 /// public class SqlConditionList { // 缓存所有条件 private ArrayList _condList = new ArrayList(); public SqlConditionList() { } /// /// 添加一个条件 /// /// 条件 public SqlConditionList Add(SqlCondition cond) { _condList.Add(cond); return this; } /// /// 添加一个条件 /// /// 条件 /// 与前一个条件的关系 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; } /// /// 添加一个条件 /// /// 条件 /// 与前一个条件的关系 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; } /// /// 合并两个条件,合二为一。 /// /// /// /// /// public static SqlCondition Merge(SqlCondition cond1, SqlCondition cond2, SqlLogicOper logicOper) { return new SqlConditionRelation(cond1, cond2, logicOper); } /// /// 生成SQL语句。 /// /// public string ToSql() { StringBuilder buff = new StringBuilder(); foreach (SqlCondition cond in _condList) { buff.Append(cond.ToSql()); } return buff.ToString(); } /// /// 去除条件 /// /// 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); } } } /// /// SQL条件,也就是WHERE部分。 /// 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(); } /// /// /// /// /// 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(); } } /// /// 两个条件合并后的条件。 /// 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(); } } }