最近項目使用的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]@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 outGO

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", SqlDbType.NVarChar,128),                    new SqlParameter("@Sort", SqlDbType.NVarChar,512),                    new SqlParameter("@PageIndex", SqlDbType.Int),                    new SqlParameter("@PageSize", SqlDbType.Int),                    new SqlParameter("@Fields", SqlDbType.NVarChar,1024),                    new SqlParameter("@Where", SqlDbType.NVarChar,1024),                    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);        }