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

原创 2007年10月02日 15:29:00

在进行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))

 

C#中DataGrid分页,修改,更新,删除功能的实现

 第一次写博客,不知道写写什么好,就写写自己最近做的东西把。a,实现分页的存储过程:首先新件一表: UserInfo,在这里我把DataGrid 的 PerPage=10CREATE PROCEDUR...
  • nanqing
  • nanqing
  • 2007年05月10日 17:45
  • 4258

SQL服务器上SQLserverAgent无法启动的解决方法 & SQL2000无法安装的解决办法

 SQL服务器上SQLserverAgent无法启动的解决方法     对于服务器上数据的备份工作是网管员日常工作中最重要的工作之一,SQLServerAgent服务使得我们可以用SQL上的“数据库维...

一个简单的C#类生成器

  • 2012年07月12日 11:24
  • 221KB
  • 下载

黑马程序员_学习日记55_628三层项目(用类封装用于拼接的where条件语句、写代码生成器)

一、用类封装用于拼接的where条件语句(三层实现多条件搜索) (一)   思想: 多条件搜索时拼接where条件语句,以前是在查询方法中用List和List分别存储where条件语句和查询参...

自制C#实体类生成器

利用一个下午吧,自己制作了一个实体类生成器。我们都知道,利用VS的封装字段的功能,只能一个一个的封闭,而且不能加上注释,所以自己制作了一个小程序,用来生成实体类。此程序,本人亲测,完全没有问题。 下...

18.C#:VS2010之EF框架添加edmx(自动跟踪实体生成器):映射到数据库表和代码实体类

在上一篇文章添加好EF数据库模型的基础上,为了生成表结构对应的标准类,使用自动跟踪实体生成器,这里主要记录文件命名注意事项 1.双击.edmx文件,在界面的空白处鼠标右击,选中“添加代码生成项”,选择...

条件Lamada树生成器

/// /// 条件表达式生成器 /// public static class PredicateBuilder { public st...

【C#】VS2010的Helloworld,输入输出、条件、循环、数组、类

C#是微软公司自己的东西。因此直接用Visual Studio直接来写就好。与Java比较类似,但C#来继承了C++的命名空间、运算符重载等东西。C#没有包的概念,通过命名空间实现Java的包。C#能...

一个搜索和展示热词和历史内容等筛选条件的类

package com.luxji.auction.app; import android.content.Context; import android.content.Intent; impor...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:C#中的一个条件生成器类
举报原因:
原因补充:

(最多只允许输入30个字)