详解传参时的SQL多条件模糊查询（VS2012）

开发环境

VS2012
C#
ASP.NET Webform

模糊查询必备知识

1. SQL SELECT 语句
2. SQL WHERE 子句
3. SQL AND & OR 运算符
4. SQL LIKE 操作符
5. SQL 通配符

错误代码

情况一

/**作者：jurbo 时间：2016/8/6

         * BOM设定页面需要的查询方法

         * 通过wo_no工单编号，item_name料号，operation_seq_num制程，获取ModelBom对象的列表集合

         * 注意：此处因为要将数据库字段int型进行模糊查询，比如operation_seq_num，故传参时传string而不是int，后面进行数据库上的动态转换

         **/

        public List<ModelBom> getBomBySome(string wo_no, string item_name, string operation_seq_num)

        {

            //完整查询内容

            string sqlAll = "";

            //* from wms_pn 后的内容，即查询条件

            string sqlTail = "";

            //当wo_no有值时

            if (string.IsNullOrWhiteSpace(wo_no) == false )

            {

                sqlTail += "AND wo_no LIKE '%'+@wo_no+'%' ";

            }

            //当item_name有值时

            if (string.IsNullOrWhiteSpace(item_name) == false )

            {

                sqlTail += "AND item_name LIKE %@item_name% ";

            }

            //当operation_seq_num有值时

            if (string.IsNullOrWhiteSpace(operation_seq_num) == false)

            {

                sqlTail += "AND CONVERT(NVARCHAR(10),operation_seq_num) LIKE '%'+@operation_seq_num+'%' ";

            }

            //不包含条件查询时

            if (sqlTail.Length <= 0)

            {

                sqlAll = "SELECT * FROM wms_requirement_operation ";

            }

            //包含条件查询时

            else

            {

                sqlAll = "SELECT * FROM wms_requirement_operation WHERE 1=1 " + sqlTail;

            }

            DB.connect();

            SqlParameter[] parameters = {

                    new SqlParameter("wo_no", wo_no),

                    new SqlParameter("item_name", item_name),

                    new SqlParameter("operation_seq_num", operation_seq_num),

                };

            DataSet ds = DB.select(sqlAll, parameters);

            List<ModelBom> ModelBomlist = null;

            if (ds.Tables[0].Rows.Count > 0)   //如果存在一行及以上数据

            {

                ModelBomlist = new List<ModelBom>(ds.Tables[0].Rows.Count);

                foreach (DataRow DateSetRows in ds.Tables[0].Rows)

                {

                    ModelBomlist.Add(toModel(DateSetRows));

                }

                return ModelBomlist;

            }

            else

            {

                return ModelBomlist;

            }

        }

抛出错误

原因分析

一开始只想着连接语句，忘记了And操作符用于在 WHERE 子语句中把两个或多个条件结合起来。所以此时的SQL语句是不符合语法的，是查不出结果的

故想加一个where条件 什么样的条件是恒成立的，需要一个恒成立条件才能继续执行and连接后得语句

where 1=1 是恒成立的

正确代码

/**作者：jurbo 时间：2016/8/6

         * BOM设定页面需要的查询方法

         * 通过wo_no工单编号，item_name料号，operation_seq_num制程，获取ModelBom对象的列表集合

         * 注意：此处因为要将数据库字段int型进行模糊查询，比如operation_seq_num，故传参时传string而不是int，后面进行数据库上的动态转换

         **/

        public List<ModelBom> getBomBySome(string wo_no, string item_name, string operation_seq_num)

        {

            //完整查询内容

            string sqlAll = "";

            //* from wms_pn 后的内容，即查询条件

            string sqlTail = "";

            //当wo_no有值时

            if (string.IsNullOrWhiteSpace(wo_no) == false )

            {

                sqlTail += "AND wo_no LIKE '%'+@wo_no+'%' ";

            }

            //当item_name有值时

            if (string.IsNullOrWhiteSpace(item_name) == false )

            {

                sqlTail += "AND item_name LIKE '%'+@item_name+'%' ";

            }

            //当operation_seq_num有值时

            if (string.IsNullOrWhiteSpace(operation_seq_num) == false)

            {

                sqlTail += "AND CONVERT(NVARCHAR(10),operation_seq_num) LIKE '%'+@operation_seq_num+'%' ";

            }

            //不包含条件查询时

            if (sqlTail.Length <= 0)

            {

                sqlAll = "SELECT * FROM wms_requirement_operation ";

            }

            //包含条件查询时

            else

            {

                sqlAll = "SELECT * FROM wms_requirement_operation WHERE 1=1 " + sqlTail;

            }

            DB.connect();

            SqlParameter[] parameters = {

                    new SqlParameter("wo_no", wo_no),

                    new SqlParameter("item_name", item_name),

                    new SqlParameter("operation_seq_num", operation_seq_num),

                };

            DataSet ds = DB.select(sqlAll, parameters);

            List<ModelBom> ModelBomlist = null;

            if (ds.Tables[0].Rows.Count > 0)   //如果存在一行及以上数据

            {

                ModelBomlist = new List<ModelBom>(ds.Tables[0].Rows.Count);

                foreach (DataRow DateSetRows in ds.Tables[0].Rows)

                {

                    ModelBomlist.Add(toModel(DateSetRows));

                }

                return ModelBomlist;

            }

            else

            {

                return ModelBomlist;

            }

        }

难点（注意事项）

1. And操作符用于在 WHERE 子语句中把两个或多个条件结合起来，故需要一个恒成立的where语句才能进行and语句后得操作。

   where 1=1 恒成立

2. 传参进行模糊查询时，需要对%进行处理

   可以这样处理，用字符拼接符+连接起来
   AND item_name LIKE ‘%’+@item_name+’%’

3. 传的参数如果是int类型时，
   如果继续采用 AND item_name LIKE ‘%’+@item_name+’%’这样的写法，会报错，大意是无法把int型转换成string型

   查询资料，发现mysql内部提供一个动态转换类型的方法CONVERT()

   但是直接这样写AND CONVERT(NVARCHAR(10),operation_seq_num) LIKE ‘%’+@operation_seq_num+’%’，仍然会报错

   但如果前台传的是string类型的参数（哪怕这个字段在数据库里是int型的），这样处理后，就可以进行int型模糊查询