asp.net防sql注入分页查询参数代码

使用方法：

 

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;

 

代码如下

    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();
        }

        /**////

        /// 主键
        ///

        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; }
        }

        /**////

        /// 当前页数
        ///

        public int pageindex
        {
            get { return _pageindex; }
            set { _pageindex = value; }
        }

 

        /**////

        /// 分页大小
        ///

        public int pagesize
        {
            get { return _pagesize; }
            set { _pagesize = value; }
        }

 

        /**////

        /// 生成缓存key
        ///

        ///
        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);
        }

 

        /**////

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

        ///
        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);
        }

        /**////

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

        ///
        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 temps教程ql =
                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;
        }

        /**////

        /// 生成分页查询语句
        ///

        ///
        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));
        }
    }