最近项目使用的SqLServer分页存储过程及调用封装代码

概述存储过程： USE [RS]GO/****** Object: StoredProcedure [dbo].[UP_Paging] Script Date: 05/30/2013 17:43:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[UP_Paging]

存储过程：

USE [RS]GO/****** Object:  StoredProcedure [dbo].[UP_Paging]    Script Date: 05/30/2013 17:43:23 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[UP_Paging]	@tables nvarchar(512),--表名,多张表是请使用 tA a inner join tB b On a.AID = b.AID	@PK nvarchar(128)='',--主键，可以带表头 a.AID	@Sort nvarchar(512) = '',--排序字段	@PageIndex int = 1,--开始页码	@PageSize int = 10,--页大小	@FIElds nvarchar(1024) = '*',--读取字段	@Where nvarchar(1024) = NulL,--Where条件	@RecordCount int output --返回总条数ASDECLARE @strFilter nvarchar(4000)declare @sql nvarchar(4000)IF @Where IS NOT NulL AND @Where != ''  BEGIN   SET @strFilter = ' WHERE ' + @Where + ' '  ENDELSE  BEGIN   SET @strFilter = ''  ENDif @Sort = ''  set @Sort = @PK + ' DESC 'IF @PageIndex < 1  SET @PageIndex = 1if @PageIndex = 1 --第一页提高性能begin   set @sql = 'select top ' + str(@PageSize) +' '+@FIElds+ '  from ' + @tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort  print @sqlend else  begin  	DECLARE @START_ID varchar(50)	--页开始索引	DECLARE @END_ID varchar(50)		--页结束索引	SET @START_ID = convert(varchar(50),(@PageIndex - 1) * @PageSize + 1)	SET @END_ID = convert(varchar(50),@PageIndex * @PageSize)		set @sql =  ' SELECT '+@FIElds+ 				' FROM '+				' ('+					' SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum,'+@FIElds+ 					' FROM '+@tables+' ' +@strFilter+				' ) AS D'+				' WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort  ENDEXEC (@sql)--总条数set @recordCount=0;set @sql = N'SELECT  @recordCount=Count(1) FROM ' + @tables + @strFilterEXEC sp_executesql @sql,N'@recordCount int out',@RecordCount out	GO

C#调用代码：

/// <summary>        /// 通用分页        /// </summary>        /// <param name="pi"></param>        public virtual voID Pager(RPageInfo pi)        {            /*存储过程             CREATE PROCEDURE UP_Paging	            @tables nvarchar(512),多张表是请使用 tA a inner join tB b On a.AID = b.AID	            @PK nvarchar(128)='',--主键，可以带表头 a.AID	            @Sort nvarchar(512) = '',--排序字段	            @PageIndex int = 1,--开始页码	            @PageSize int = 10,--页大小	            @FIElds nvarchar(1024) = '*',--读取字段	            @Where nvarchar(1024) = NulL,--Where条件	            @RecordCount int output --返回总条数            AS             */            sqlParameter[] parameters = {					new sqlParameter("@tables",sqlDbType.NVarChar,512),new sqlParameter("@PK",128),new sqlParameter("@Sort",new sqlParameter("@PageIndex",sqlDbType.Int),new sqlParameter("@PageSize",new sqlParameter("@FIElds",1024),new sqlParameter("@Where",new sqlParameter("@RecordCount",sqlDbType.Int)			};            parameters[0].Value = pi.tablename;            parameters[1].Value = pi.Pk;            parameters[2].Value = pi.sort;            parameters[3].Value = pi.PageIndex;            parameters[4].Value = pi.PageSize;            parameters[5].Value = pi.FIElds;            parameters[6].Value = pi.Fwhere;            parameters[7].Direction = ParameterDirection.Output;            Datatable data = DbHelpersql.RunProcedure("UP_Paging",parameters,"ds").tables[0];            pi.Data = data;            pi.RecordCount = Convert.ToInt32(parameters[7].Value);            pi.PageCount = (long)Math.Ceiling(pi.RecordCount/(pi.PageSize+0.0));        }

RPageInfo封装的分页信息，代码如下：

/// <summary>    /// 分页信息类    /// </summary>    public class RPageInfo    {        private String _tablename;        /// <summary>        /// 表名,可以是子查询,但必须如下:        ///     (select * from Dept) as model        /// </summary>        public String tablename        {            get { return _tablename; }            set { _tablename = value; }        }        private String _pk=string.Empty;        /// <summary>        /// 主键或者其它字段        /// </summary>        public String Pk        {            get { return _pk; }            set { _pk = value; }        }        private string _sort = string.Empty;        /// <summary>        /// 排序，例如：字段1 ASC,字段2 DESC        /// 主键和排序字段是互斥的        /// </summary>        public string Sort        {            get { return _sort; }            set { _sort = value; }        }        private string _fIElds="*";        /// <summary>        /// 要显示的字段,默认为*        /// </summary>        public string FIElds        {            get { return _fIElds; }            set { _fIElds = value; }        }        private string _fwhere = string.Empty;        /// <summary>        /// 筛选条件        /// </summary>        public string Fwhere        {            get { return _fwhere; }            set { _fwhere = value; }        }        private int _pageIndex = 1;        /// <summary>        /// 页号        /// </summary>        public int PageIndex        {            get { return _pageIndex; }            set { _pageIndex = value; }        }        private int _pageSize = 10;        /// <summary>        /// 页大小        /// </summary>        public int PageSize        {            get { return _pageSize; }            set { _pageSize = value; }        }        private long _recordCount;        /// <summary>        /// 总条数        /// </summary>        public long RecordCount        {            get { return _recordCount; }            set { _recordCount = value; }        }        private long _pageCount;        /// <summary>        /// 总页数        /// </summary>        public long PageCount        {            get { return _pageCount; }            set { _pageCount = value; }        }        private Datatable _data;        /// <summary>        /// 结果集        /// </summary>        public Datatable Data        {            get { return _data; }            set { _data = value; }        }    }

使用代码：

/// <summary>        /// 获得医院信息        /// </summary>        /// <param name="pi"></param>        /// <param name="isCityHospital">是否县级医院</param>        /// <param name="hospitalname">医院名称</param>        public voID GetHospitals(RPageInfo pi,bool? isCityHospital,string hospitalname)        {            pi.tablename = "(SELECT * FROM [HOSPITAL] WHERE 1=1 ";            if (isCityHospital!=null && isCityHospital.Value)            {                pi.tablename += " AND [LEVEL]=1";            }            else if (isCityHospital != null && !isCityHospital.Value)            {                pi.tablename += " AND [LEVEL]=0";            }            if (hospitalname != null && hospitalname.Trim() != string.Empty)            {                pi.tablename += " AND [HOSTPITALname] liKE '%" + hospitalname + "%'";            }            pi.tablename += ") AS MODEL ";            pi.Pk = "HospitalID";            base.Pager(pi);        }

总结

以上是内存溢出为你收集整理的最近项目使用的SqLServer分页存储过程及调用封装代码全部内容，希望文章能够帮你解决最近项目使用的SqLServer分页存储过程及调用封装代码所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。