Sliverlight DataGrid 分页 数据库中分页，速度更快
先不说其他的，上代码：
在数据库执行这段存储过程
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
<UserControl
x:Class="Ehope.SLControls.Pager"
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">
<UserControl.Resources>
<Style x:Key="PagerButtonOuterStyle" TargetType="Button">
<Setter Property="Background" Value="#FF0000FF"/>
<Setter Property="Foreground" Value="#FF445ECE"/>
<Setter Property="Padding" Value="3"/>
<Setter Property="BorderThickness" Value="0.5"/>
<Setter Property="Template">
<Setter.Value>
<ControlTemplate TargetType="Button">
<Grid Margin="3,3,3,3" HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
<vsm:VisualStateManager.VisualStateGroups>
<vsm:VisualStateGroup x:Name="CommonStates">
<vsm:VisualState x:Name="Normal">
<Storyboard>
<ColorAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="00:00:00" Value="#FF0000FF"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="MouseOver">
<Storyboard>
<ColorAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="00:00:00" Value="#FF99A5D9"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Pressed">
<Storyboard>
<ColorAnimationUsingKeyFrames Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="0" Value="#FF99A5D9"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Disabled">
<Storyboard>
<DoubleAnimationUsingKeyFrames Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="Opacity">
<SplineDoubleKeyFrame KeyTime="0" Value=".55"/>
</DoubleAnimationUsingKeyFrames>
<DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="(Rectangle.RadiusX)">
<SplineDoubleKeyFrame KeyTime="00:00:00" Value="0"/>
</DoubleAnimationUsingKeyFrames>
<DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="(Rectangle.RadiusY)">
<SplineDoubleKeyFrame KeyTime="00:00:00" Value="0"/>
</DoubleAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
</vsm:VisualStateGroup>
<vsm:VisualStateGroup x:Name="FocusStates">
<vsm:VisualState x:Name="Focused">
<Storyboard>
<DoubleAnimationUsingKeyFrames Storyboard.TargetName="FocusVisualElement" Storyboard.TargetProperty="Opacity">
<SplineDoubleKeyFrame KeyTime="0" Value="1"/>
</DoubleAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Unfocused"/>
</vsm:VisualStateGroup>
</vsm:VisualStateManager.VisualStateGroups>
<Border x:Name="Background" Background="#FF0000FF" BorderBrush="{TemplateBinding BorderBrush}" CornerRadius="0,0,0,0" BorderThickness="0.5,0.5,0.5,0.5">
<Grid Margin="0,0,1,1" Background="#FFFFFFFF" Width="Auto" HorizontalAlignment="Stretch"/>
</Border>
<ContentPresenter HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}" Margin="{TemplateBinding Padding}" x:Name="contentPresenter" VerticalAlignment="{TemplateBinding VerticalContentAlignment}" Content="{TemplateBinding Content}" ContentTemplate="{TemplateBinding ContentTemplate}"/>
<Rectangle x:Name="DisabledVisualElement" IsHitTestVisible="false" Opacity="0" Fill="#FFFFFFFF" RadiusX="0" RadiusY="0" StrokeThickness="0.5"/>
<Rectangle Margin="1" x:Name="FocusVisualElement" IsHitTestVisible="false" Opacity="0" Stroke="#FF0000FF" StrokeThickness="0.5" RadiusX="0" RadiusY="0"/>
</Grid>
</ControlTemplate>
</Setter.Value>
</Setter>
</Style>
<Style x:Key="PagerButtonInnerStyle" TargetType="Button">
<Setter Property="Background" Value="#FF0000FF"/>
<Setter Property="Foreground" Value="#FF445ECE"/>
<Setter Property="Padding" Value="8,3,8,3"/>
<Setter Property="BorderThickness" Value="0.5"/>
<Setter Property="Template">
<Setter.Value>
<ControlTemplate TargetType="Button">
<Grid Margin="3,3,3,3" HorizontalAlignment="Stretch" VerticalAlignment="Stretch">
<vsm:VisualStateManager.VisualStateGroups>
<vsm:VisualStateGroup x:Name="CommonStates">
<vsm:VisualState x:Name="Normal">
<Storyboard>
<ColorAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="00:00:00" Value="#FF0000FF"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="MouseOver">
<Storyboard>
<ColorAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="00:00:00" Value="#FF99A5D9"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Pressed">
<Storyboard>
<ColorAnimationUsingKeyFrames Storyboard.TargetName="Background" Storyboard.TargetProperty="(Border.Background).(SolidColorBrush.Color)">
<SplineColorKeyFrame KeyTime="0" Value="#FF99A5D9"/>
</ColorAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Disabled">
<Storyboard>
<DoubleAnimationUsingKeyFrames Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="Opacity">
<SplineDoubleKeyFrame KeyTime="0" Value=".55"/>
</DoubleAnimationUsingKeyFrames>
<DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="(Rectangle.RadiusX)">
<SplineDoubleKeyFrame KeyTime="00:00:00" Value="0"/>
</DoubleAnimationUsingKeyFrames>
<DoubleAnimationUsingKeyFrames BeginTime="00:00:00" Duration="00:00:00.0010000" Storyboard.TargetName="DisabledVisualElement" Storyboard.TargetProperty="(Rectangle.RadiusY)">
<SplineDoubleKeyFrame KeyTime="00:00:00" Value="0"/>
</DoubleAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
</vsm:VisualStateGroup>
<vsm:VisualStateGroup x:Name="FocusStates">
<vsm:VisualState x:Name="Focused">
<Storyboard>
<DoubleAnimationUsingKeyFrames Storyboard.TargetName="FocusVisualElement" Storyboard.TargetProperty="Opacity">
<SplineDoubleKeyFrame KeyTime="0" Value="1"/>
</DoubleAnimationUsingKeyFrames>
</Storyboard>
</vsm:VisualState>
<vsm:VisualState x:Name="Unfocused"/>
</vsm:VisualStateGroup>
</vsm:VisualStateManager.VisualStateGroups>
<Border x:Name="Background" Background="#FF0000FF" BorderBrush="{TemplateBinding BorderBrush}" CornerRadius="0,0,0,0" BorderThickness="0.5,0.5,0.5,0.5">
<Grid Margin="0,0,1,1" Background="#FFFFFFFF" Width="Auto" HorizontalAlignment="Stretch"/>
</Border>
<ContentPresenter HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}" Margin="{TemplateBinding Padding}" x:Name="contentPresenter" VerticalAlignment="{TemplateBinding VerticalContentAlignment}" Content="{TemplateBinding Content}" ContentTemplate="{TemplateBinding ContentTemplate}"/>
<Rectangle x:Name="DisabledVisualElement" IsHitTestVisible="false" Opacity="0" Fill="#FFFFFFFF" RadiusX="0" RadiusY="0" StrokeThickness="0.5"/>
<Rectangle Margin="1" x:Name="FocusVisualElement" IsHitTestVisible="false" Opacity="0" Stroke="#FF0000FF" StrokeThickness="0.5" RadiusX="0" RadiusY="0"/>
</Grid>
</ControlTemplate>
</Setter.Value>
</Setter>
</Style>
</UserControl.Resources>
<Grid x:Name="LayoutRoot" Background="Transparent">
<StackPanel x:Name="spPager" Orientation="Horizontal" Margin="0,10,0,0" />
</Grid>
</UserControl>
Pager .xaml.cs
public partial class Pager : UserControl
{
#region Properties
/// <summary>
/// Total Number of Pages for this Pager Control
/// </summary>
public int PageCount { get; set; }
/// <summary>
/// Current Page Index
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// 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
/// </summary>
public int PageButtonCount { get; set; }
/// <summary>
/// EVent delegate for a specific Pager Button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public delegate void PagerButtonClick(object sender, RoutedEventArgs e);
/// <summary>
/// Event handler for a specific Pager Button
/// </summary>
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
/// <summary>
/// Renders a Button control for the Pager
/// </summary>
/// <param name="text">Text to display</param>
/// <param name="inner">Flag to denote if this refers to the inner buttons or the Previous/Next buttons</param>
/// <param name="enabled">Flag to denote if this button is enabled</param>
/// <returns>Button</returns>
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;
}
/// <summary>
/// Renders a selected Button or the Hellip (...) TextBlock
/// </summary>
/// <param name="text">Text to display</param>
/// <param name="border">Flag to denote if this button is to have a border</param>
/// <returns>UIElement (either a TextBlock or a Border with a TextBlock within)</returns>
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
};
}
}
/// <summary>
/// Build the Pager Control
/// </summary>
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
/// <summary>
/// Handles Pager Button click
/// Sets proper PageIndex for rendering
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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
<StackPanel x:Name="PagerPanel" Margin="8,0,8,7" Background="AliceBlue" RenderTransformOrigin="0.5,0.5" Height="39" VerticalAlignment="Bottom">
a.xaml.cs
Pager Pager = null;
/// <summary>
/// 分页查询的页索引初始值
/// </summary>
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）;
}
注意几个事项：
1，strWhere 的初始化值 为 NULL 不是””;
2, 查询字段 换行时加 @”字段”；
