存儲過程：
?
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元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
