C#中的一个条件生成器类

标签: c#stringnullobjectsqldate
1020人阅读 评论(0) 收藏 举报
分类:

在进行sql查询的时候,有时候要进行很多条件限制,自己来拼写SQLwhere条件容易出错,而且判断条件复杂,后期维护困难,基于这个原因我在一个小项目中写了一套生成sql条件的类。总共包括一个Condition类,与两个枚举型类型(LogicOper,CompareOper)
代码如下: 

public class Condition
{
    static string[] logicOpers = new string[] { "and", "or" };
    static string[] compareOpers = new string[] { ">", "<", "<=", ">=", "=", "<>", "like", "not like", "in" };

    string compareOper = null;
    string name = null;
    string templateName = null;
    string valType = null;
    object val = null;
    public Condition(CompareOper co, string valType, string name, object val)
    {
        this.compareOper = compareOpers[(int) co];
        this.name = name;
        templateName = name;
        this.valType = valType;
        this.val = val;
    }
    public Condition(CompareOper co, string valType, string name, object val, string templateName)
    {
        this.compareOper = compareOpers[(int) co];
        this.name = name;
        this.templateName = templateName;
        this.valType = valType;
        this.val = val;
    }
    public Condition() { }

    public string toSqlString()
    {
        string[] arr1 = (string[]) operaters.ToArray("".GetType());
        Condition[] arr2 = (Condition[]) conditions.ToArray((new Condition()).GetType());

        StringBuilder outStr = new StringBuilder();

        int count = 0;
        if (name != null && val != null)
        {
            outStr.Append(name);
            outStr.Append(" ");
            outStr.Append(compareOper);
            outStr.Append(" ");
            if (valType.ToLower() == "int"
                || valType.ToLower() == "float"
                || valType.ToLower() == "double"
                || valType.ToLower() == "bool"
                || valType.ToLower() == "number"
                )
            {
                outStr.Append(val);
            }
            else if (valType.ToLower() == "string")
            {
                string tmp = (string) val;
                outStr.Append("'" + tmp.Replace("'", "''") + "'");
            }
            else if (valType.ToLower() == "date")
            {
                DateTime dt = (DateTime) val;
                outStr.Append("'" + dt.ToString("yyyy-MM-dd") + "'");
            }
            else if (valType.ToLower() == "datetime")
            {
                DateTime dt = (DateTime) val;
                outStr.Append("'" + dt.ToString("yyyy-MM-dd hh:mm:ss.fff") + "'");
            }
            else
            {
                string tmp = val.ToString();
                outStr.Append("'" + tmp.Replace("'", "''") + "'");
            }
            count++;
        }

        if (arr1.Length > 0)
        {
            for (int i = 0; i < arr1.Length; i++)
            {
                if (arr2[i].toSqlTempletString() == "")
                    continue;
                count++;
                if ((name != null && val != null) || count > 1)
                {
                    outStr.Append(" ");
                    outStr.Append(arr1[i]);
                    outStr.Append(" ");
                }
                outStr.Append(arr2[i].toSqlString());
            }
        }
        if (count > 1)
        {
            outStr.Insert(0, '(');
            outStr.Append(')');
        }
        return outStr.ToString();
    }

    public string toSqlTempletString()
    {
        string[] arr1 = (string[]) operaters.ToArray("".GetType());
        Condition[] arr2 = (Condition[]) conditions.ToArray((new Condition()).GetType());

        StringBuilder outStr = new StringBuilder();

        int count = 0;
        if (name != null && val != null)
        {
            outStr.Append(name);
            outStr.Append(" ");
            outStr.Append(compareOper);
            outStr.Append(" @");
            outStr.Append(templateName);
            count++;
        }

        if (arr1.Length > 0)
        {
            for (int i = 0; i < arr1.Length; i++)
            {
                if (arr2[i].toSqlTempletString() == "")
                    continue;
                count++;
                if ((name != null && val != null) || count > 1)
                {
                    outStr.Append(" ");
                    outStr.Append(arr1[i]);
                    outStr.Append(" ");
                }
                outStr.Append(arr2[i].toSqlTempletString());
            }
        }
        if (count > 1)
        {
            outStr.Insert(0, '(');
            outStr.Append(')');
        }
        return outStr.ToString();
    }

    public SqlParameter[] getSqlParameters()
    {
        ArrayList tmp = new ArrayList();
        if (name != null && val != null)
        {
            tmp.Add(new SqlParameter("@" + templateName, val));
        }
        Condition[] arr = (Condition[]) conditions.ToArray((new Condition()).GetType());

        for (int i = 0; i < arr.Length; i++)
        {
            SqlParameter[] sps = arr[i].getSqlParameters();
            for (int j = 0; j < sps.Length; j++)
            {
                tmp.Add(sps[j]);
            }
        }
        return (SqlParameter[]) tmp.ToArray(new SqlParameter("", "").GetType());
    }

    ArrayList operaters = new ArrayList();
    ArrayList conditions = new ArrayList();

    public void addCondition(LogicOper lo, Condition c)
    {
        operaters.Add(logicOpers[(int) lo]);
        conditions.Add(c);
    }
}

public enum LogicOper : int
{
    and = 0,
    or = 1
}
public enum CompareOper : int
{
    moreThan = 0,
    lessThan = 1,
    notMoreThan = 2,
    notLessThan = 3,
    equal = 4,
    notEqual = 5,
    like = 6,
    notLike = 7,
    IN = 8
}

使用如下: 
Condition condition = new Condition(CompareOper.equal, "string", "name", "%kkp%");
Condition condition2 = new Condition(CompareOper.equal, "int", "id", 1024);
Condition condition3 = new Condition(CompareOper.like, "string", "nickName", "%'kkp'%");
Condition condition4 = new Condition(CompareOper.equal, "date", "age", DateTime.Now);
Condition condition5 = new Condition(CompareOper.equal, "datetime", "signTime", DateTime.Now);
Condition condition6 = new Condition();

condition.addCondition(LogicOper.or, condition2);
condition.addCondition(LogicOper.or, condition3);

condition6.addCondition(LogicOper.or, condition4);
condition6.addCondition(LogicOper.or, condition5);

condition6.addCondition(LogicOper.and, condition);

condition6.toSqlString();
condition6.toSqlTempletString();
condition6.getSqlParameters();

通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件,可以用于sql拼接方式使用。而toSqlTempletString()方式生成的是以参数形式的 sql条件,配合getSqlParameters()方法可以实现以参数传递的条件(相当于java中的prepareStatement实现)。 

condition6.toSqlString();
condition6.toSqlTempletString();

的结果分别是: 

(age = '2007-07-16' or signTime = '2007-07-16 02:06:02.667' and (name = '%kkp%' or id = 1024 or nickName like '%''kkp''%'))

(age = @age or signTime = @signTime and (name = @name or id = @id or nickName like @nickName))

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:896657次
    • 积分:14512
    • 等级:
    • 排名:第806名
    • 原创:552篇
    • 转载:102篇
    • 译文:1篇
    • 评论:92条
    最新评论