文章 合集 资源 图书 工具 捐赠

C#实现数据库查询条件组件

本文会实现常用的数据库查询条件组件，可以在数据修改、删除、查询等组件中应用。MySQL数据库相关准备知识可以参考如下资源：

《MySQL数据库应用》合集网址：http://caohuayu.com/article/Group.aspx?id=3

《数据查询》文章网址：http://caohuayu.com/article/Article.aspx?id=a261005

条件类型与条件关系

支持的条件类型使用tCondType枚举定义，如下面的代码（cfs/data/tCondType.cs）。

C#
namespace cfx.data
{
    public enum tCondType
    {
        Group = 0,
        Equal = 1,
        NotEqual = 2,
        Greater = 3,
        GreaterEqual = 4,
        Less = 5,
        LessEqual = 6,
        Between = 7,
        InList = 8,
        IsNull = 9,
        IsNotNull = 10,
        Like = 11,
        Prefix = 12,
        Suffix = 13,
        Direct = 14
    }
}

条件类型比较容易理解，需要注意Group为条件组，由多个子条件组成，并可以指定子条件关系（与、或）；Direct类型直接使用SQL语句定义查询条件，可以作为自定义查询条件的终极解决方案。

条件的关系使用tRelation枚举类型定义，如下面的代码（cfx/data/tRelation.cs）。

C#
namespace cfx.data
{
    public enum tRelation
    {
        And =1,
        Or =2
    }
}

tRelation枚举中只定义了与（And）、或（Or）关系。实际应用中，如果需要对条件取反，可以在条件语句前添加not关键字，这也是SQL的通用语法。如“f1='user01' and f2=0”条件取反可以使用“not(f1='user01' and f2=0)”。

条件类

tCond类定义条件要素和生成方法，代码如下（/cfx/data/tCond.cs）。

C#
using System.Collections.Generic;

namespace cfx.data
{
    public class tCond
    {
        public string Field { get; set; }
        public tCondType Type { get; set; }
        public object[] Value { get; set; }
        // 子条件关系
        public tRelation SubRelation { get; set; }
        public List<tCond> SubCond { get; set; }
        //
        private tCond() { }
        //
        //Group = 0,
        public static tCond Group(tRelation r, params tCond[] cond)
        {
            return new tCond()
            {
                Type = tCondType.Group,
                SubRelation = r,
                SubCond = new List<tCond>(cond)
            };
        }
        //Equal = 1,
        public static tCond Equal(string field,object val)
        {
            return new tCond()
            {
                Type = tCondType.Equal,
                Field = field,
                Value = new object[] { val }
            };
        }
        //NotEqual = 2,
        public static tCond NotEqual(string field, object val)
        {
            return new tCond()
            {
                Type = tCondType.NotEqual,
                Field = field,
                Value = new object[] { val }
            };
        }
        //Greater = 3,
        public static tCond Greater(string field, object val)
        {
            return new tCond()
            {
                Type = tCondType.Greater,
                Field = field,
                Value = new object[] { val }
            };
        }
        //GreaterEqual = 4,
        public static tCond GreaterEqual(string field, object val)
        {
            return new tCond()
            {
                Type = tCondType.GreaterEqual,
                Field = field,
                Value = new object[] { val }
            };
        }
        //Less = 5,
        public static tCond Less(string field, object val)
        {
            return new tCond()
            {
                Type = tCondType.Less,
                Field = field,
                Value = new object[] { val }
            };
        }
        //LessEqual = 6,
        public static tCond LessEqual(string field, object val)
        {
            return new tCond()
            {
                Type = tCondType.LessEqual,
                Field = field,
                Value = new object[] { val }
            };
        }
        //Between = 7,
        public static tCond Between(string field, object val1,object val2)
        {
            return new tCond()
            {
                Type = tCondType.Between,
                Field = field,
                Value = new object[] { val1,val2 }
            };
        }
        //InList = 8,
        public static tCond InList(string field, params object[] val)
        {
            return new tCond()
            {
                Type = tCondType.InList,
                Field = field,
                Value = val
            };
        }
        //IsNull = 9,
        public static tCond IsNull(string field)
        {
            return new tCond()
            {
                Type = tCondType.IsNull,
                Field = field
            };
        }
        //IsNotNull = 10,
        public static tCond IsNotNull(string field)
        {
            return new tCond()
            {
                Type = tCondType.IsNotNull,
                Field = field
            };
        }
        //Like = 11,
        public static tCond Like(string field,string val)
        {
            return new tCond()
            {
                Type = tCondType.Like,
                Field = field,
                Value = new object[] {val }
            };
        }
        //Prefix = 12,
        public static tCond Prefix(string field, string val)
        {
            return new tCond()
            {
                Type = tCondType.Prefix,
                Field = field,
                Value = new object[] { val }
            };
        }
        //Suffix = 13,
        public static tCond Suffix(string field, string val)
        {
            return new tCond()
            {
                Type = tCondType.Suffix,
                Field = field,
                Value = new object[] { val }
            };
        }
        //Direct = 14
        public static tCond Direct(string sql)
        {
            return new tCond()
            {
                Type = tCondType.Direct,
                Field = sql
            };
        }
        //
    }
}

代码中，首先定义了条件的属性，包括：

  • Type，tCondType枚举类型，定义条件的类型。
  • Field，string类型，指定查询字段名称。在Group类型中不使用，在Direct类型中保存条件语句。
  • Value，定义为object[]数组，保存条件对应的数据。
  • SubCond，定义为List<tCond>类型，保存子条件，只用于Group类型。
  • SubRelation，定义为tRelation枚举类型，确定子条件的关系，只用于Group类型。

tCond类还定义了私有构造函数，也就是说，在tCond类的外部不能使用构造函数创建tCond类的实例。创建tCond类实例时，必须使用其中的一系列静态方法，这些静态方法创建了各种类型的条件，并根据条件类型指定相关的属性值。

生成条件SQL语句（MySQL）

前面已经创建了查询条件的基本模型类，实际工作中，需要将tCond对象转换为具体的数据库查询条件语句，生成MySQL数据库查询条件语句使用tMySqlCondBuilder类，定义如下（cfx/data/mysql/tMySqlCondBuilder.cs）。

C#
using System.Collections.Generic;
using System.Text;

namespace cfx.data.mysql
{
    public class tMySqlCondBuilder
    {
        private StringBuilder mySql = new StringBuilder();
        private List<object> myArg = new List<object>();
        private int myCounter = 0;
        //
        private tMySqlCondBuilder() { }
        //
        public static string Get(tCond cond, out List<object> arg)
        {
            tMySqlCondBuilder builder = new tMySqlCondBuilder();
            //
            builder.Build(cond);
            //
            arg = builder.myArg;
            return builder.mySql.ToString();
        }
        // 
        private void Build(tCond cond)
        {
            if (cond == null) return;
            switch (cond.Type)
            {
                case tCondType.Group:
                    {
                        string r = (cond.SubRelation == tRelation.And) ? " and " : " or ";
                        mySql.Append("(");
                        Build(cond.SubCond[0]);
                        for(int i = 1; i < cond.SubCond.Count; i++)
                        {
                            mySql.Append(r);
                            Build(cond.SubCond[i]);
                        }
                        mySql.Append(")");
                    }
                    break;
                case tCondType.Equal:
                    {
                        mySql.AppendFormat("`{0}`=?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.NotEqual:
                    {
                        mySql.AppendFormat("`{0}`<>?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Greater:
                    {
                        mySql.AppendFormat("`{0}`>?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.GreaterEqual:
                    {
                        mySql.AppendFormat("`{0}`>=?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Less:
                    {
                        mySql.AppendFormat("`{0}`<?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.LessEqual:
                    {
                        mySql.AppendFormat("`{0}`<=?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Between:
                    {
                        mySql.AppendFormat("`{0}` between ?cond{1} and ?cond{2}",
                            cond.Field, myCounter++, myCounter++);
                        myArg.Add(cond.Value[0]);
                        myArg.Add(cond.Value[1]);
                    }
                    break;
                case tCondType.InList:
                    {
                        mySql.AppendFormat("`{0}` in(?cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                        for(int i = 1; i < cond.Value.Length; i++)
                        {
                            mySql.AppendFormat(",?cond{0}",myCounter++);
                            myArg.Add(cond.Value[i]);
                        }
                        mySql.Append(")");
                    }
                    break;
                case tCondType.IsNull:
                    {
                        mySql.AppendFormat("`{0}` is null", cond.Field);
                    }
                    break;
                case tCondType.IsNotNull:
                    {
                        mySql.AppendFormat("`{0}` is not null", cond.Field);
                    }
                    break;
                case tCondType.Like:
                    {
                        List<string> fld = cond.Field.ReSplit( @"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if(fld.Count==0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("(`{0}` like '%{1}%'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or `{0}` like '%{0}%'", fld[0], key[i]);
                            // 其它字段
                            for(int f = 1; f<fld.Count; f++)
                            {
                                for(int k=0;k<key.Count;k++)
                                    mySql.AppendFormat("or `{0}` like '%{0}%'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Prefix:
                    {
                        List<string> fld = cond.Field.ReSplit(@"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if (fld.Count == 0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("(`{0}` like '{1}%'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or `{0}` like '{0}%'", fld[0], key[i]);
                            // 其它字段
                            for (int f = 1; f < fld.Count; f++)
                            {
                                for (int k = 0; k < key.Count; k++)
                                    mySql.AppendFormat("or `{0}` like '{0}%'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Suffix:
                    {
                        List<string> fld = cond.Field.ReSplit(@"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if (fld.Count == 0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("(`{0}` like '%{1}'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or `{0}` like '%{0}'", fld[0], key[i]);
                            // 其它字段
                            for (int f = 1; f < fld.Count; f++)
                            {
                                for (int k = 0; k < key.Count; k++)
                                    mySql.AppendFormat("or `{0}` like '%{0}'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Direct:
                    {
                        mySql.AppendFormat("({0})",cond.Field);
                    }
                    break;
            }
        }
        //
    }
}

代码中，tMySqlCondBuilder类只有一个公共成员，即Get()方法，参数一为tCond对象；参数二为输出参数，保存了条件语句中的参数数据。

tMySqlCondBuilder类中mySql对象用于保存条件语句，myArg对象用于保存条件中的参数数据，myCounter变量则作为参数的计数器。

主要的工作方法是Build()，其参数为tCond对象，可以递归调用。Build()方法中，会根据不同的条件类型分别生成条件语句，并将语句添加到mySql对象，条件中的参数使用问号（?）定义，命名使用cond加0开始的序号（使用myCounter计数），对应的参数数据会添加到myArg对象。

对于条件组，通过递归调用Build()方法生成子条件语句，统一添加到mySql对象中，并根据子条件关系使用and或or关键字连接。条件组的语句会使用一对圆括号组织。

比较条件、between条件、in条件比较简单，字段名使用反单引号定义，对应的数据参数使用问号（?）定义，并使用cond加序号命名，对应的数据会从条件的Value属性添加到myArg对象。

IsNull和IsNotNull条件不需要参数，直接使用字段定义。

需要注意like、前缀（prefix）和后缀（suffix）条件，它们本质上都是使用like条件，不同的是%通配符的位置，like条件在关键字前后都使用了%通配符，前缀条件是在关键字后面使用%通配符，后缀条件是在关键字前面使用%通配符。处理这三种查询条件时没有使用参数传递数据，为防止SQL注入，还会对字段和关键字进行处理，字段名使用逗号（两边可能有空格）分割，然后使用反单引号定义字段名，关键字使用逗号和空白字符分割，最终形成多个like条件组成的条件组，各条件之间为或（or）关系。整个条件组会使用一对圆括号组织。

最后是Direct类型条件，会将条件语句（Field属性）添加到mySql对象，并使用一对圆括号组织。

下面的代码，我们在Program.cs文件中测试tCond与tMySqlCondBuilder类的使用。

C#
using System;
using System.Collections.Generic;
using cfx.data;
using cfx.data.mysql;

namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            tCond cond = tCond.Group(tRelation.Or,
                tCond.Equal("f1", "user01"),
                tCond.Greater("f2", 0),
                tCond.InList("f4", "111", "222", "333"));
            List<object> arg;
            string condSql = tMySqlCondBuilder.Get(cond, out arg);
            //
            Console.WriteLine(condSql);
            for (int i = 0; i < arg.Count; i++)
            {
                Console.WriteLine(arg[i]);
            }
        }
    }
}

代码执行结果如下图所示。

测试tMySqlCondBuilder类

代码中定义了一个条件组，子条件关系为或（or），三个条件分别是：

  • f1字段等于"user01"。
  • f2大于0。
  • f4字段的值为"111"、"222"或"333"。

第一行输出显示了条件的MySQL语句，然后通过for循环分别显示了条件的5个参数数据。

生成条件SQL语句（SQL Server）

下面（cfs/data/sql/tSqlCondBuilder.cs）是根据tCond类生成SQL Server数据库查询条件语句的代码。

C#
using System.Collections.Generic;
using System.Text;

namespace cfx.data.sql
{
    public class tSqlCondBuilder
    {
        private StringBuilder mySql = new StringBuilder();
        private List<object> myArg = new List<object>();
        private int myCounter = 0;
        //
        private tSqlCondBuilder() { }
        //
        public static string Get(tCond cond, out List<object> arg)
        {
            tSqlCondBuilder builder = new tSqlCondBuilder();
            //
            builder.Build(cond);
            //
            arg = builder.myArg;
            return builder.mySql.ToString();
        }
        // 
        private void Build(tCond cond)
        {
            if (cond == null) return;
            switch (cond.Type)
            {
                case tCondType.Group:
                    {
                        string r = (cond.SubRelation == tRelation.And) ? " and " : " or ";
                        mySql.Append("(");
                        Build(cond.SubCond[0]);
                        for(int i = 1; i < cond.SubCond.Count; i++)
                        {
                            mySql.Append(r);
                            Build(cond.SubCond[i]);
                        }
                        mySql.Append(")");
                    }
                    break;
                case tCondType.Equal:
                    {
                        mySql.AppendFormat("[{0}]=@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.NotEqual:
                    {
                        mySql.AppendFormat("[{0}]<>@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Greater:
                    {
                        mySql.AppendFormat("[{0}]>@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.GreaterEqual:
                    {
                        mySql.AppendFormat("[{0}]>=@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Less:
                    {
                        mySql.AppendFormat("[{0}]<@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.LessEqual:
                    {
                        mySql.AppendFormat("[{0}]<=@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                    }
                    break;
                case tCondType.Between:
                    {
                        mySql.AppendFormat("[{0}] between @cond{1} and @cond{2}",
                            cond.Field, myCounter++, myCounter++);
                        myArg.Add(cond.Value[0]);
                        myArg.Add(cond.Value[1]);
                    }
                    break;
                case tCondType.InList:
                    {
                        mySql.AppendFormat("[{0}] in(@cond{1}", cond.Field, myCounter++);
                        myArg.Add(cond.Value[0]);
                        for(int i = 1; i < cond.Value.Length; i++)
                        {
                            mySql.AppendFormat(",@cond{0}",myCounter++);
                            myArg.Add(cond.Value[i]);
                        }
                        mySql.Append(")");
                    }
                    break;
                case tCondType.IsNull:
                    {
                        mySql.AppendFormat("[{0}] is null", cond.Field);
                    }
                    break;
                case tCondType.IsNotNull:
                    {
                        mySql.AppendFormat("[{0}] is not null", cond.Field);
                    }
                    break;
                case tCondType.Like:
                    {
                        List<string> fld = cond.Field.ReSplit( @"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if(fld.Count==0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("([{0}] like '%{1}%'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or [{0}] like '%{0}%'", fld[0], key[i]);
                            // 其它字段
                            for(int f = 1; f<fld.Count; f++)
                            {
                                for(int k=0;k<key.Count;k++)
                                    mySql.AppendFormat("or [{0}] like '%{0}%'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Prefix:
                    {
                        List<string> fld = cond.Field.ReSplit(@"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if (fld.Count == 0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("([{0}] like '{1}%'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or [{0}] like '{0}%'", fld[0], key[i]);
                            // 其它字段
                            for (int f = 1; f < fld.Count; f++)
                            {
                                for (int k = 0; k < key.Count; k++)
                                    mySql.AppendFormat("or [{0}] like '{0}%'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Suffix:
                    {
                        List<string> fld = cond.Field.ReSplit(@"\s*,\s*");
                        List<string> key = cond.Value[0].ToString().ReSplit(@"\s*,\s*|\s+");
                        if (fld.Count == 0 || key.Count == 0)
                        {
                            mySql.Append("1=1");
                        }
                        else
                        {
                            // 第一个字段
                            mySql.AppendFormat("([{0}] like '%{1}'", fld[0], key[0]);
                            for (int i = 1; i < key.Count; i++)
                                mySql.AppendFormat("or [{0}] like '%{0}'", fld[0], key[i]);
                            // 其它字段
                            for (int f = 1; f < fld.Count; f++)
                            {
                                for (int k = 0; k < key.Count; k++)
                                    mySql.AppendFormat("or [{0}] like '%{0}'", fld[f], key[k]);
                            }
                            //
                            mySql.Append(")");
                        }
                    }
                    break;
                case tCondType.Direct:
                    {
                        mySql.AppendFormat("({0})",cond.Field);
                    }
                    break;
            }
        }
        //
    }
}

代码中，tSqlCondBuilder类与tMySqlCondBuilder类区别在于，tSqlCondBuilder类中，SQL Server数据库对象名称使用一对方括号定义，参数使用@符号定义。下面的代码，在Program.cs文件中演示了tSqlCondBuilder类的应用。

C#
using System;
using System.Collections.Generic;
using cfx.data;
using cfx.data.sql;

namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            tCond cond = tCond.Group(tRelation.Or,
                tCond.Equal("f1", "user01"),
                tCond.Greater("f2", 0),
                tCond.InList("f4", "111", "222", "333"));
            List<object> arg;
            string condSql = tSqlCondBuilder.Get(cond, out arg);
            //
            Console.WriteLine(condSql);
            for (int i = 0; i < arg.Count; i++)
            {
                Console.WriteLine(arg[i]);
            }
        }
    }
}

代码执行结果如下图所示。

测试tSqlCondBuilder类

豫ICP备11002860-1

公安机关备案41152102000002号

http://caohuayu.com    本网站及微信公众号内容均为原创，转载请注明出处