存儲過程:
?
USE [RS] GO /****** Object: StoredProcedure [dbo].[UP_Paging] Script Date: 05/30/2013 17:43:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER 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 --返回總條數 AS DECLARE @strFilter nvarchar(4000) declare @sql nvarchar(4000) IF @Where IS NOT NULL AND @Where != '' BEGIN SET @strFilter = ' WHERE ' + @Where + ' ' END ELSE BEGIN SET @strFilter = '' END if @Sort = '' set @Sort = @PK + ' DESC ' IF @PageIndex < 1 SET @PageIndex = 1 if @PageIndex = 1 --第一頁提高性能 begin set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + ' ORDER BY '+ @Sort print @sql end 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 END EXEC (@sql) --總條數 set @recordCount=0; set @sql = N'SELECT @recordCount=Count(1) FROM ' + @Tables + @strFilter EXEC 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", 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); }
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
