SQL条件生成器类（C#）

电玩女神 2022-01-27 03:17 270阅读 0赞

在实际项目中，程序与数据库的交互离不开SQL，当然我们可以采用O/R或LINQ等技术来避开SQL的拼写，但是由于种种原因，在程序中拼写SQL还相当的多。在程序中拼写WHERE条件容易出错，而且判断条件复杂，后期维护困难。所以写了一个工具类为我们来干活。如下：

using System;  
using System.Collections;  
using System.Collections.Generic;  
using System.Text;  
using System.Data.SqlClient;

public enum SqlLogicOper\{  
    AND\_LOGIC,      // AND  
    OR\_LOGIC,       // 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  
\}

/// <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 AddLogic(SqlCondition condition,  
        SqlLogicOper logicOper)\{  
        SqlCondition cond = null;  
        if (\_condList.Count > 0)\{  
            SqlCondition lastCond = (SqlCondition)

\_condList\[\_condList.Count - 1\];  
            cond = Merge(lastCond, condition, logicOper);  
        \}  
        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);  
        \}  
    \}

/// <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();  
        \}

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>  
    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\_LOGIC)\{  
                buff.Insert(0, " (");  
                buff.Append(") ");  
            \}  
            return buff.ToString();  
        \}

\}  
\}

外部调用很简单，举个例子：

SqlConditionList.SqlCondition cond1 = new SqlConditionList.

SqlCondition(SqlCompareOper.LIKE, "USER\_NAME", "wjb");  
SqlConditionList.SqlCondition cond2 = new SqlConditionList.

SqlCondition(SqlCompareOper.EQUAL, "PASSWORD", "123");  
SqlConditionList.SqlCondition cond3 = new SqlConditionList.

SqlCondition(SqlCompareOper.BETWEEN, "CREATE\_DATE",

new DateTime\[\] \{ DateTime.Now.AddDays(-1), DateTime.Now \});  
SqlConditionList.SqlCondition cond4 = new SqlConditionList.

SqlCondition(SqlCompareOper.IN, "STATUS",

new Int32\[\] \{ 1, 3, 5, 7 \});

SqlConditionList condList = new SqlConditionList();  
condList.Add(cond1)  
        .AddLogic(cond4, SqlLogicOper.OR\_LOGIC)  
        .AddLogic(cond2, SqlLogicOper.AND\_LOGIC)  
        .AddLogic(cond3, SqlLogicOper.AND\_LOGIC);

string sql = condList.ToSql();

MessageBox.Show(sql);

本来工具类中还有ToTemplateSql() 和ToSqlParameter()两个方法，但鉴于篇幅无法贴出来。这两个方法主要目的是：ToTemplateSql()用于生成类似于 WHERE NAME = @NAME AND PASSWORD = @PASS 的SQL模版；ToSqlParameter()则用于返回SqlParameter对象列表，用于ADO.NET的Command之需。

出处：[http://blog.sina.com.cn/s/blog\_3d7bed6501000c85.html][http_blog.sina.com.cn_s_blog_3d7bed6501000c85.html]

[http_blog.sina.com.cn_s_blog_3d7bed6501000c85.html]: http://blog.sina.com.cn/s/blog_3d7bed6501000c85.html