在数据库执行这段存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pager](
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(1000) = '*', ----要显示的字段列表
@pageSize int, ----每页显示的记录个数
@pageIndex int, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@strCondition nvarchar(1000), ----查询条件,不需where
@ID nvarchar(50) ----主表的主键
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @sqlSort nvarchar(200) ----存放临时生成的排序条件
Declare @intCounts int ----要移动的记录数
Declare @BeginID int ----开始的ID
Declare @EndID int ----结束的ID
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName
set @strID = ' From ' + @tblName + ' order by '+ @ID
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where ' + @strCondition
set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition
set @strID = ' From ' + @tblName + ' where ' + @strCondition + ' order by '+ @ID
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
--取得分页总数
if @Counts <= @pageSize
set @pageCount = 1
else
set @pageCount = (@Counts / @pageSize) + 1
--计算要移动的记录数
if @pageIndex = 1
set @intCounts = 1
else
begin
set @intCounts = (@pageIndex -1) * @pageSize + 1
end
--如果启始行记数超过总行数，退出
if @intCounts > @Counts
return
-----取得分页后此页的第一条记录的ID
set @strID = 'select @BeginID=' + @ID + ' ' + @strID
set rowcount @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@BeginID out
-----取得分页后此页的最后一条记录的ID
set @intCounts = @intCounts + @pageSize -1
print @intCounts
set rowcount @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@EndID out
------恢复系统设置-----
set rowcount 0
SET NOCOUNT OFF
------返回查询结果-----
if @strCondition is null
set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID + ' between ' + str(@BeginID) + ' and ' + str(@EndID)
else
set @strTmp = 'select ' + @sqlTmp + ' and ' + @ID +' between ' + str(@BeginID) + ' and ' + str(@EndID)
exec sp_executesql @strTmp
用法：(后台服务)
public void GetPageList(int PageIndex, string strWhere, out int PageCount, out int TotalCount)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.NVarChar, 200),
new SqlParameter("@fldName", SqlDbType.NVarChar, 1200),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@pageCount", SqlDbType.Int,4),
new SqlParameter("@Counts", SqlDbType.Int),
new SqlParameter("@strCondition", SqlDbType.NVarChar,1000),
new SqlParameter("@ID", SqlDbType.NVarChar,50)
};
parameters[0].Value = "表名";
parameters[1].Value = @"a,b,c";
parameters[2].Value = 8;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[4].Direction = ParameterDirection.Output;
parameters[5].Value = 0;
parameters[5].Direction = ParameterDirection.Output;parameters[6].Value = strWhere;
parameters[7].Value = "表ID";SqlDataReader reader = DbHelperSQL.RunProcedure("Pager", parameters);
if (reader != null)
{………………….
reader.Close();
}
PageCount = convert.toint32(parameters[4].Value.ToString());
TotalCount = convert.toint32(parameters[5].Value.ToString());
}
前台
新建 Pager .xaml
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
xmlns:vsm="clr-namespace:System.Windows;assembly=System.Windows"
Width="Auto" Height="Auto">
Pager .xaml.cs
public partial class Pager : UserControl
{
#region Properties
///
/// Total Number of Pages for this Pager Control
///
public int PageCount { get; set; }
///
/// Current Page Index
///
public int PageIndex { get; set; }
///
/// Number of buttons to render before/after current selection
/// For example, if = 2, buttons appear for a control with 100 pages
/// /// Button 5 selected:
/// Previous 1 2 3 4 (5) 6 7 ... 99 100 Next
/// Button 6 selected:
/// Previous 1 2 ... 4 5 (6) 7 8 ... 99 100 Next
/// Button 95 selected
/// Previous 1 2 ... 93 94 (95) 96 97 ... 99 100 Next
/// Button 96 selected
/// Previous 1 2 ... 94 95 (96) 97 98 99 100 Next
///
public int PageButtonCount { get; set; }
///
/// EVent delegate for a specific Pager Button
///
///
///
public delegate void PagerButtonClick(object sender, RoutedEventArgs e);
///
/// Event handler for a specific Pager Button
///
public event PagerButtonClick Click;
#endregion
public Pager()
{
InitializeComponent();
// Default Settings
PageCount = 10;
PageIndex = 1;
PageButtonCount = 3;
this.Loaded += new RoutedEventHandler(Pager_Loaded);
}
public Pager(int pageCount, int pageButtonCount)
{
InitializeComponent();
PageCount = pageCount;
PageIndex = 1;
PageButtonCount = pageButtonCount;
this.Loaded += new RoutedEventHandler(Pager_Loaded);
}
protected void Pager_Loaded(object sender, RoutedEventArgs e)
{
BuildPager();
}
#region Internal
///
/// Renders a Button control for the Pager
///
/// Text to display
/// Flag to denote if this refers to the inner buttons or the Previous/Next buttons
/// Flag to denote if this button is enabled
///
private Button BuildButton(string text, bool inner, bool enabled)
{
Button b = new Button()
{
Content = text,
Tag = text,
Style. = inner ? this.Resources["PagerButtonInnerStyle"] as Style. this.Resources["PagerButtonOuterStyle"] as Style,
Width = 45
};
if (inner == false)
b.Width = 75;
b.IsEnabled = enabled;
b.Click += new RoutedEventHandler(PagerButton_Click);
return b;
}
///
/// Renders a selected Button or the Hellip (...) TextBlock
///
/// Text to display
/// Flag to denote if this button is to have a border
///
private UIElement BuildSpan(string text, bool border)
{
if (border)
{
TextBlock t = new TextBlock()
{
Text = text,
TextAlignment = TextAlignment.Center,
VerticalAlignment = VerticalAlignment.Center,
Width = 30
};
Border b = new Border()
{
Margin = new Thickness(3, 3, 3, 3),
BorderThickness = new Thickness(0.5, 0.5, 0.5, 0.5),
BorderBrush = new SolidColorBrush(Color.FromArgb(255, 0, 0, 255)),
Width = 30,
Height = 22,
HorizontalAlignment = HorizontalAlignment.Center,
VerticalAlignment = VerticalAlignment.Center
};
b.Child = t;
return b;
}
else
{
return new TextBlock()
{
Text = text,
Width = 30,
Height = 22,
TextAlignment = TextAlignment.Center,
VerticalAlignment = VerticalAlignment.Bottom
};
}
}
///
/// Build the Pager Control
///
private void BuildPager()
{
this.spPager.Children.Clear();
if (PageCount > 1)
{
int min = PageIndex - PageButtonCount;
int max = PageIndex + PageButtonCount;
if (max > PageCount)
min -= max - PageCount;
else if (min < 1)
max += 1 - min;
// Previous Button
if (PageIndex > 1)
this.spPager.Children.Add(BuildButton("上一页", false, true));
else // Disabled State
this.spPager.Children.Add(BuildButton("上一页", false, false));
// Middle Buttons
bool needDiv = false;
for (int i = 1; i <= PageCount; i++)
{
if (i <= 2 || i > PageCount - 2 || (min <= i && i <= max))
{
string text = i.ToString(NumberFormatInfo.InvariantInfo);
if (i == PageIndex) // Currently Selected Index
this.spPager.Children.Add(BuildSpan(text, false));
else
this.spPager.Children.Add(BuildButton(text, true, true));
needDiv = true;
}
else if (needDiv)
{
// This will add the hellip (...) TextBlock
this.spPager.Children.Add(BuildSpan("...", false));
needDiv = false;
}
}
// Next Button
if (PageIndex < PageCount)
this.spPager.Children.Add(BuildButton("下一页", false, true));
else // Disabled State
this.spPager.Children.Add(BuildButton("下一页", false, false));
}
}
#endregion
#region Event Handlers
///
/// Handles Pager Button click
/// Sets proper PageIndex for rendering
///
///
///
protected void PagerButton_Click(object sender, RoutedEventArgs e)
{
Button b = e.OriginalSource as Button;
if (b.Tag.ToString() == "上一页")
{
PageIndex--;
}
else if (b.Tag.ToString() == "下一页")
{
PageIndex++;
}
else
{
int p = PageIndex;
int.TryParse(b.Tag.ToString(), out p);
PageIndex = p;
}
BuildPager();
Click(sender, e);
}
#endregion
}
调用页面
a.xaml
a.xaml.cs
Pager Pager = null;
///
/// 分页查询的页索引初始值
///
private int PageIndex = 1;
if (datagrid.ItemsSource != null)
{
datagrid.ItemsSource = null;
}
//第一次得到数据后加载分页控件
if (Pager == null)
{
Pager = new Pager(e.PageCount, 2);
Pager.Click += new Pager.PagerButtonClick(Pager_Click);
PagerPanel.Children.Clear();
PagerPanel.Children.Add(Pager);
}
//strWhere 是查询条件
if (GetPageList(PageIndex,strWhere）!= null)
{
datagrid.ItemsSource = GetPageList(PageIndex,strWhere）
}
private void Pager_Click(object sender, RoutedEventArgs e)
{
Button btn = e.OriginalSource as Button;
if (btn.Content.ToString() == "上一页")
PageIndex -= 1;
else if (btn.Content.ToString() == "下一页")
PageIndex += 1;
else
{
int.TryParse(btn.Content.ToString(), out PageIndex);
}
if (PageIndex <= 0)
PageIndex = 1;
datagrid.ItemsSource = GetPageList(PageIndex,strWhere）;
}