原文:

      前些时间看了玉开兄的“”这篇文章，才突然想起某个项目也是使用了累似的通用分页存储过程。使用这种通用的存储过程进行分页查询，想要防SQL注入，只能对输入的参数进行过滤，例如将一个单引号“'”转换成两个单引号“''”，但这种做法是不安全的，厉害的黑客可以通过编码的方式绕过单引号的过滤，要想有效防SQL注入，只有参数化查询才是最终的解决方案。但问题就出在这种通用分页存储过程是在存储过程内部进行SQL语句拼接，根本无法修改为参数化的查询语句，因此这种通用分页存储过程是不可取的。但是如果不用通用的分页存储过程，则意味着必须为每个具体的分页查询写一个分页存储过程，这会增加不少的工作量。

      经过几天的时间考虑之后，想到了一个用代码来生成参数化的通用分页查询语句的解决方案。代码如下：

    public class PagerQuery

    {

        private int _pageIndex;

        private int _pageSize = 20;

        private string _pk;

        private string _fromClause;

        private string _groupClause;

        private string _selectClause;

        private string _sortClause;

        private StringBuilder _whereClause;

        public DateTime DateFilter = DateTime.MinValue;

        protected QueryBase()

        {

            _whereClause = new StringBuilder();

        }

        /**//// <summary>

        /// 主键

        /// </summary>

        public string PK

        {

            get { return _pk; }

            set { _pk = value; }

        }

        public string SelectClause

        {

            get { return _selectClause; }

            set { _selectClause = value; }

        }

        public string FromClause

        {

            get { return _fromClause; }

            set { _fromClause = value; }

        }

        public StringBuilder WhereClause

        {

            get { return _whereClause; }

            set { _whereClause = value; }

        }

        public string GroupClause

        {

            get { return _groupClause; }

            set { _groupClause = value; }

        }

        public string SortClause

        {

            get { return _sortClause; }

            set { _sortClause = value; }

        }

        /**//// <summary>

        /// 当前页数

        /// </summary>

        public int PageIndex

        {

            get { return _pageIndex; }

            set { _pageIndex = value; }

        }

        /**//// <summary>

        /// 分页大小

        /// </summary>

        public int PageSize

        {

            get { return _pageSize; }

            set { _pageSize = value; }

        }

        /**//// <summary>

        /// 生成缓存Key

        /// </summary>

        /// <returns></returns>

        public override string GetCacheKey()

        {

            const string keyFormat = "Pager-SC:{0}-FC:{1}-WC:{2}-GC:{3}-SC:{4}";

            return string.Format(keyFormat, SelectClause, FromClause, WhereClause, GroupClause, SortClause);

        }

        /**//// <summary>

        /// 生成查询记录总数的SQL语句

        /// </summary>

        /// <returns></returns>

        public string GenerateCountSql()

        {

            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(" from {0}", FromClause);

            if (WhereClause.Length > 0)

                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))

                sb.AppendFormat(" group by {0}", GroupClause);

            return string.Format("Select count(0) {0}", sb);

        }

        /**//// <summary>

        /// 生成分页查询语句，包含记录总数

        /// </summary>

        /// <returns></returns>

        public string GenerateSqlIncludeTotalRecords()

        {

            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(SelectClause))

                SelectClause = "*";

            if (string.IsNullOrEmpty(SortClause))

                SortClause = PK;

            int start_row_num = (PageIndex - 1)*PageSize + 1;

            sb.AppendFormat(" from {0}", FromClause);

            if (WhereClause.Length > 0)

                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))

                sb.AppendFormat(" group by {0}", GroupClause);

            string countSql = string.Format("Select count(0) {0};", sb);

            string tempSql =

                string.Format(

                    "WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4};",

                    SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));

            return tempSql + countSql;

        }

        /**//// <summary>

        /// 生成分页查询语句

        /// </summary>

        /// <returns></returns>

        public override string GenerateSql()

        {

            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(SelectClause))

                SelectClause = "*";

            if (string.IsNullOrEmpty(SortClause))

                SortClause = PK;

            int start_row_num = (PageIndex - 1)*PageSize + 1;

            sb.AppendFormat(" from {0}", FromClause);

            if (WhereClause.Length > 0)

                sb.AppendFormat(" where 1=1 {0}", WhereClause);

            if (!string.IsNullOrEmpty(GroupClause))

                sb.AppendFormat(" group by {0}", GroupClause);

            return

                string.Format(

                    "WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY {0}) as row_number,{1}{2}) Select * from t where row_number BETWEEN {3} and {4}",

                    SortClause, SelectClause, sb, start_row_num, (start_row_num + PageSize - 1));

        }

    }

 

使用方法：

 

PagerQuery query = new PagerQuery();

query.PageIndex = 1;

    query.PageSize = 20;

    query.PK = "ID";

    query.SelectClause = "*";

    query.FromClause = "TestTable";

    query.SortClause = "ID DESC";

    if (!string.IsNullOrEmpty(code))

    {

     query.WhereClause.Append(" and ID= @ID");

    }

a) GenerateCountSql ()方法生成的语句为：

Select count(0) from TestTable Where 1=1 and ID= @ID

b) GenerateSql()方法生成的语句为：

WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY ECID DESC) as row_number, * from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20

c) GenerateSqlIncludetTotalRecords()方法生成的语句为：

WITH t AS (SELECT ROW_NUMBER() OVER(ORDER BY E.ECID DESC) as row_number,* from TestTable where 1=1 and ID= @ID) Select * from t where row_number BETWEEN 1 and 20;Select count(0) from ECBasicInfo where 1=1 and ID= @ID;

注意：以上代码生成的SQL语句是曾对SQL SERVER 2005以上版本的，希望这些代码对大家有用