强大的DataGrid组件[6]_调用存储过程服务端分页——Silverlight学习笔记[14]
准备工作：
1）测试项目的建立
2）创建测试用数据库
详情请参考我的强大的DataGrid组件[2]_数据交互之ADO.NET Entity Framework——Silverlight学习笔记[10]。
建立存储过程：
存储过程1——DataPager：
Create PROCEDURE dbo.DataPager
(
@pagesize int,
@currentpage int
)
AS
declare @totalnum int
set @totalnum = (select count(EmployeeID) from Employee)
if @currentpage <= ceiling(convert(real,@totalnum)/convert(real,@pagesize))
select * from
(select TOP (@pagesize) * FROM (SELECT TOP (@pagesize * @currentpage) * from Employee ORDER BY EmployeeID ASC) as A ORDER BY EmployeeID DESC) as B ORDER BY EmployeeID ASC
存储过程2——GetTotalPagers：
Create PROCEDURE dbo.GetTotalPagers
@pagesize int
AS
declare @totalnum int
set @totalnum = (select count(EmployeeID) from Employee)
return (ceiling(convert(real,@totalnum)/convert(real,@pagesize)))
创建Linq to SQL 数据模型类
基本操作参见强大的DataGrid组件[3]_数据交互之Linq to SQL——Silverlight学习笔记[11]。本教程需要添加的新内容如下：
打开Server Explorer，将刚才建立的两个存储过程拖入至EmployeesModel.dbml所在的右侧窗口中。（如下图）
按Ctrl+Shift+B进行项目的编译。
建立Silverlight-enabled WCF Service数据通信服务
操作步骤参见强大的DataGrid组件[3]_数据交互之Linq to SQL——Silverlight学习笔记[11]。然而，必须将下述代码添加至EmployeeInfoWCFService.svc.cs文件中。
using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;
using EmployeesContext;
using EmployeesEntities;
namespace datapager
{
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class EmployeesInfoService
{
[OperationContract]//得到总分页数
public int GetTotalPagers(int PageSize)
{
EmployeesModelDataContext db = new EmployeesModelDataContext();
return db.GetTotalPagers(PageSize);
}
[OperationContract]//得到分页内容
public List<DataPagerResult> GetPagedEmployeesInfo(int PageSize, int CurrentPage)
{
EmployeesModelDataContext db = new EmployeesModelDataContext();
return db.DataPager(PageSize, CurrentPage).ToList();
}
// Add more operations here and mark them with [OperationContract]
}
}
按Ctrl+Shift+B进行项目的编译。
创建SilverlightClient界面及组件代码
MainPage.xaml文件代码：
<UserControl
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:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="SilverlightClient.MainPage"
d:DesignWidth="320" d:DesignHeight="240">
<Grid x:Name="LayoutRoot" Background="White" Width="320" Height="240">
<data:DataGrid x:Name="dgEmployee" Margin="8,8,8,64" Width="304" Height="168"/>
<data:DataPager x:Name="dpEmployee" Height="24" Margin="8,0,8,40" VerticalAlignment="Bottom" Background="#FFD7E2EE" FontSize="13.333" Width="304"/>
</Grid>
</UserControl>
MainPage.xaml.cs文件代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Data.Services.Client;
using SilverlightClient.EmployeesInfoService;
namespace SilverlightClient
{
public partial class MainPage : UserControl
{
int PageSize = 2;//设定分页大小
List<int> itemCount = new List<int>();//用于DataPager的数据提供
public MainPage()
{
InitializeComponent();
//注册事件触发处理
this.Loaded += new RoutedEventHandler(MainPage_Loaded);
this.dpEmployee.PageIndexChanged += new EventHandler<EventArgs>(dpEmployee_PageIndexChanged);
}
void MainPage_Loaded(object sender, RoutedEventArgs e)
{
//页面初始化
EmployeesInfoServiceClient webClient = new EmployeesInfoServiceClient();
webClient.GetPagedEmployeesInfoAsync(PageSize, 1);
webClient.GetPagedEmployeesInfoCompleted += new EventHandler<GetPagedEmployeesInfoCompletedEventArgs>(webClient_GetPagedEmployeesInfoCompleted);
GeneralDataPagerContent();//生成DataPager的数据提供
}
void dpEmployee_PageIndexChanged(object sender, EventArgs e)//DataPager的当前页发生改变时的处理
{
EmployeesInfoServiceClient webClient = new EmployeesInfoServiceClient();
webClient.GetPagedEmployeesInfoAsync(PageSize, dpEmployee.PageIndex+1);//索引值从0开始，所以要加1
webClient.GetPagedEmployeesInfoCompleted += new EventHandler<GetPagedEmployeesInfoCompletedEventArgs>(webClient_GetPagedEmployeesInfoCompleted);
}
void webClient_GetPagedEmployeesInfoCompleted(object sender, GetPagedEmployeesInfoCompletedEventArgs e)
{
dgEmployee.ItemsSource = e.Result;
}
//生成DataPager的数据提供，这里学习使用了园友小庄的做法
void GeneralDataPagerContent()
{
EmployeesInfoServiceClient webClient = new EmployeesInfoServiceClient();
webClient.GetTotalPagersAsync(PageSize);
webClient.GetTotalPagersCompleted +=
new EventHandler<GetTotalPagersCompletedEventArgs>(webClient_GetTotalPagersCompleted);
}
void webClient_GetTotalPagersCompleted(object sender, GetTotalPagersCompletedEventArgs e)
{
int totalpagers = e.Result;
for (int i = 1; i <= totalpagers; i++) itemCount.Add(i);
PagedCollectionView pcv = new PagedCollectionView(itemCount);
pcv.PageSize = 1;
dpEmployee.Source = pcv;
}
}
}
最终效果图：
