where条件拼接查询

最近在做一个动态拼接where条件的查询，大概想到了以下几种方法：

1、代码内拼接查询条件，sql也是写在代码内的。

2、代码内拼接查询条件，sql写在存储过程内，将 where条件作为一个字符串传入存储过程。（这种需要在代码内过滤sql注入的问题）

3、查询条件的拼接放到存储过程内，在存储过程内对查询条件值进行参数化。

第一种：

　　不再书写

第二种：

第1步，先拼接where查询条件

            StringBuilder condition = new StringBuilder();

            if (param.Province > 0)
            {
                condition.AppendFormat(" AND Province={0}", param.Province);
            }
            if (param.City > 0)
            {
                condition.AppendFormat(" AND City={0}", param.City);
            }
            if (param.County > 0)
            {
                condition.AppendFormat(" AND County={0}", param.County);
            }        

第2步：

　　注意，Condition参数，将拼接的查询条件作为字符串传入，

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("Condition", condition.ToString());

第3步：

　　接下来看下存储过程。只看下关键的地方，将拼接好的where条件作为一个字符串传入存储过程，这种应该还是有sql注入的问题，因为第一步拼接的时候并没有对参数值进行参数化

SET @sql = 'SELECT @totalCount = COUNT(1)
                      FROM dbo.Base_Class cls
                      WHERE IsValid=1 ' + @condition;

第三种:

第1步：将所有用到的查询参数传给存储过程

                var parameters = new DynamicParameters();

                parameters.Add("Province", param.Province);
                parameters.Add("City", param.City);
                parameters.Add("County", param.County);

第2步：存储过程内拼接，并参数化（代码只留了关键部分，并不能保证编译通过）

PROCEDURE [dbo].[p_GetList]@P_Province INT,
    @P_City INT,
    @P_County INT,AS
BEGINDECLARE @Sql NVARCHAR(MAX) ,@SqlCondition NVARCHAR(MAX) ,
        @Parm NVARCHAR(MAX) ,
SET @Sql='';
    SET @SqlCondition='';
    SET @Parm='';
IF(@P_Province>0)
    BEGIN
        SET @SqlCondition += ' AND Province=@P_Province'
    END
    SET @Parm += '@P_Province int,';
     
    IF(@P_City>0)
    BEGIN
        SET @SqlCondition += ' AND City=@P_City'
    END
    SET @Parm += '@P_City int,';
    
    IF(@P_County>0)
    BEGIN
        SET @SqlCondition += ' AND County=@P_County'
    END            
    SET @Parm += '@P_County int,';
       
 　　--这个参数展示了，怎么拼接like这样的查询条件
    IF(@P_SchoolName IS NOT NULL)
    BEGIN
        SET @SqlCondition += ' AND Name like ''%''+@P_SchoolName+''%'''
    END
    SET @Parm += '@P_SchoolName NVARCHAR(100),';
    
    IF (@Parm<>'')
    BEGIN
        SET @Parm= SUBSTRING(@Parm,1,len(@Parm)-1)
    END
SET @Sql = 'SELECT COUNT(1)
                      FROM TableName
                      WHERE IsValid=1 ' + @SqlCondition;
            
    SET @Sql = '
            SELECT 
                 *
            FROM TableName
            WHERE IsValid=1 ' + @SqlCondition;


        EXEC sp_executesql @Sql,@Parm,@P_Province        =    @P_Province,
                           @P_City            =    @P_City, 
                           @P_County          =    @P_County

END

　　有个地方需要注意，使用 sp_executesql  时， 第二个参数@pram的拼接并不是和 查询条件同时进行的，查询条件只有输入值时才拼接，而@pram 将所有的查询条件中的参数拼接。如果查询条件中没有用到某个参数，但在sp_executesql 的输入参数中过多的指定参数，也是可以的。这样就解决了  查询条件参数个数不固定的情况下，无法解决给 sp_executesql 输入参数赋值的问题，因为不知道要给多少个参数赋值。

      一开始也是这么做的，但可能因为其他错误导致报“过多的指定参数”的错误，以为这种方式不可以，最终还是通过了。

　　第一种的弊端也显而易见，改一次结构就要修改代码，第二种可读性一般但需要解决注入的问题，第三种可读性较差但好像又找不出毛病。不知道有没有其他更好的方式解决这种需要拼接sql才能处理不固定查询条件的方法。

　　写完了，要去刷牙吃早饭咯。