silverlight + wcf(json格式) + sqlserver存储过程分页
silverlight并没有提供现成的分页控件，百度了一圈，也没有发现aspx中好用的类似AspNetPager成熟控件，网上现有的一些分页代码，很多也是基于1.0版本的，silverlight2.0的并不多，自个儿琢磨了一下，发现自己弄一个也并非难事，思路和主要代码分享如下:
1.通用的“海量”数据分页存储过程
在做aspx开发时我已经用存储过程分页多年，这个东东是通用的(不管前端用什么语言来做)，而且性能也不错，所以这里就直接套过来用了，百度一下"分页存储过程"会有N多版本，这里也给出我自己写的一个版本(仅适用于sqlserver 2005及以上版本)
分页存储过程-- =============================================
-- Author: <杨俊明-菩提树下的杨过>
-- Create date: <2006-11-05>
-- Description: <高效分页存储过程，仅适用于Sql2005>
-- Notes: <排序字段强烈建议建索引>
-- =============================================
CREATE Procedure [dbo].[up_Page2005]
@TableName varchar(500), --表名
@Fields nvarchar(MAX) = '*', --字段名(全部字段为*)
@OrderField nvarchar(MAX), --排序字段(必须!支持多字段)
@sqlWhere nvarchar(MAX) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@TotalPage int output --返回总页数
as
begin
Begin Tran --开始事务
Declare @sql nvarchar(MAX);
Declare @totalRecord int;
--计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as RowId,' + @Fields + ' from ' + @TableName + ' with (nolock)'
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as RowId,' + @Fields + ' from ' + @TableName + ' with (nolock) where ' + @SqlWhere
--处理页数超出范围情况
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where RowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @sql ;
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
end
Code-- =============================================
-- Author: <杨俊明-菩提树下的杨过>
-- Create date: <2006-11-05>
-- Description: <高效分页存储过程，仅返回总页数和总记录数，仅适用于Sql2005>
-- Notes: <排序字段强烈建议建索引>
-- =============================================
CREATE Procedure [dbo].[up_Page2005_Statistic]
@TableName varchar(500), --表名
@Fields nvarchar(MAX) = '*', --字段名(全部字段为*)
@OrderField nvarchar(MAX), --排序字段(必须!支持多字段)
@sqlWhere nvarchar(MAX) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@TotalPage int output --返回总页数
as
begin
Declare @sql nvarchar(MAX);
Declare @totalRecord int;
--计算总记录数
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
print 'TotalRecord=' + Convert(nvarchar(50),@totalRecord);
print 'TotalPage=' + Convert(nvarchar(50),@totalPage);
Return @totalRecord ---返回记录总数
end
注:第二个是直接在第一个基础上简化得来的，仅返回总页数和总记录数；方便某些不需要返回实体数据，仅需要知道总页数和总记录数的特殊情况调用
2.利用wcf直接或间接调用存储过程，返回json数据
wcf能返回json数据已经不是什么新鲜事儿了，这里就不多说了，贴出主要代码(直接拿我以前封装好的工具库中的一个方法示例一下，大家知道意思就可以了)
Code/// <summary>
/// 调用分页存储过程，返回Json数据
/// </summary>
/// <param name="pTable"></param>
/// <param name="pWhere"></param>
/// <param name="pFields"></param>
/// <param name="pOrder"></param>
/// <param name="pPageSize"></param>
/// <param name="pPageIndex"></param>
/// <param name="pTotalPage"></param>
/// <param name="pTotalRecord"></param>
/// <returns></returns>
[WebInvoke(Method = "*", ResponseFormat = WebMessageFormat.Json, UriTemplate = "SelectPageData?pTable={pTable}&pWhere={pWhere}&pFields={pFields}&pOrder={pOrder}&pPageSize={pPageSize}&pPageIndex={pPageIndex}&pTotalPage={pTotalPage}&pTotalRecord={pTotalRecord}")]
[OperationContract]
public Stream SelectPageData(string pTable, string pWhere, string pFields, string pOrder, int pPageSize,int pPageIndex,int pTotalPage,int pTotalRecord)
{
return GetStream(GetJsonData(Database.GetDataTableForPage(pTable,pFields,pOrder,pWhere,pPageSize,pPageIndex,pTotalPage,pTotalRecord)));
}
/// <summary>
/// 调用分页存储过程，返回总页数和总记录数
/// </summary>
/// <param name="pTable"></param>
/// <param name="pWhere"></param>
/// <param name="pFields"></param>
/// <param name="pOrder"></param>
/// <param name="pPageSize"></param>
/// <param name="pPageIndex"></param>
/// <param name="pTotalPage"></param>
/// <param name="pTotalRecord"></param>
/// <returns></returns>
[WebInvoke(Method = "*", ResponseFormat = WebMessageFormat.Json, UriTemplate = "SelectPageDataStatistic?pTable={pTable}&pWhere={pWhere}&pFields={pFields}&pOrder={pOrder}&pPageSize={pPageSize}&pPageIndex={pPageIndex}&pTotalPage={pTotalPage}&pTotalRecord={pTotalRecord}")]
[OperationContract]
public Stream SelectPageDataStatistic(string pTable, string pWhere, string pFields, string pOrder, int pPageSize, int pPageIndex, int pTotalPage, int pTotalRecord)
{
Database.GetTotalPageAndTotalRecordForPage(pTable, pFields, pOrder, pWhere, pPageSize, pPageIndex, out pTotalPage,out pTotalRecord);
string _Result = "{'TotalPage':'" + pTotalPage.ToString() + "','RecordCount':'" + pTotalRecord + "'}";
return GetStream(_Result);
}
其中有二个方法GetStream和GetJsonData，主要用于将字符串转换为流，以及将格式化Json字符串
Codeprivate Stream GetStream(string str)
{
MemoryStream ms = new MemoryStream();
StreamWriter sw = new StreamWriter(ms);
sw.AutoFlush = true;
sw.Write(str);
ms.Position = 0;
WebOperationContext.Current.OutgoingResponse.ContentType = "text/plain";
return ms;
}
public static string GetJsonData(DataTable dt)
{
string _Result = Utils.CreateJsonParameters(dt);//CreateJsonParameters是我工具库里的一个方法，用于将DataTable转化为json字符串
if (_Result.IsNullStr())
{
_Result = "[{}]";
}
else
{
if (_Result.StartsWith("{ \"Head\":["))
{
_Result = _Result.Replace("{ \"Head\":[", "[");
}
if (_Result.EndsWith("]}"))
{
_Result = _Result.Trim("}");
}
}
return _Result;
}
3.前面二步弄完了，基本上就可以动手写silverlight代码了
xaml前端文件:
Code<UserControl
xmlns:basics="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"
x:Class="WcfTest.DataGrid"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
>
<Grid x:Name="LayoutRoot" Background="White" ShowGridLines="False">
<Grid.RowDefinitions>
<RowDefinition>
</RowDefinition>
<RowDefinition Height="25">
</RowDefinition>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition>
</ColumnDefinition>
<ColumnDefinition>
</ColumnDefinition>
</Grid.ColumnDefinitions>
<data:DataGrid x:Name="dg" AutoGenerateColumns="False" Grid.Column="0" Grid.Row="0" Grid.ColumnSpan="2">
<data:DataGrid.Columns>
<data:DataGridTextColumn Header="F_ID"
Binding="{Binding F_ID}" />
<data:DataGridTextColumn Header="F_ClassName"
Binding="{Binding F_ClassName}" />
<data:DataGridTextColumn Header="F_RootId"
Binding="{Binding F_RootId}" />
<data:DataGridTextColumn Header="F_Depth"
Binding="{Binding F_Depth}" />
<data:DataGridTextColumn Header="F_Type"
Binding="{Binding F_Type}" />
</data:DataGrid.Columns>
</data:DataGrid>
<TextBlock x:Name="txtStat" Text="共有X条记录，第X页/共X页，X/每页" Grid.Row="1" Grid.Column="0" VerticalAlignment="Center" Margin="5,0,0,0" ></TextBlock>
<StackPanel Grid.Column="1" Grid.Row="1" HorizontalAlignment="Right" Orientation="Horizontal" VerticalAlignment="Center" Height="22">
<Button x:Name="btnFirst" Content="首页" Click="btnFirst_Click" ></Button>
<Button x:Name="btnPrev" Content="上页" Margin="5,0,0,0" Click="btnPrev_Click"></Button>
<Button x:Name="btnNext" Content="下页" Margin="5,0,0,0" Click="btnNext_Click"></Button>
<Button x:Name="btnLast" Content="末页" Margin="5,0,0,0" Click="btnLast_Click" ></Button>
<TextBox x:Name="txtPageIndex" Text="1" Margin="5,0,0,0" Width="30" KeyUp="txtPageIndex_KeyUp"></TextBox>
<Button x:Name="btnPageGo" Content="Go" Margin="5,0,5,0" Click="btnPageGo_Click"></Button>
</StackPanel>
</Grid>
</UserControl>
讲解一下:用Grid布局，先分上下二行，第一行放一个DataGrid控件(silverlight2新增的控件!),第二行分二列，左列显示统计信息，右列显示翻页按钮
后端cs代码:
Codeusing System;
using System.Collections.Generic;
using System.Json;
using System.Net;
using System.Windows.Controls;
using System.Windows.Media;
using JIMMY.TOOLS.Silverlight;
namespace WcfTest
{
public partial class DataGrid : UserControl
{
protected string pTable = "", pWhere = "", pFields = "", pOrderField = "", pBaseUri = "http://localhost:7055/Demo.svc/";
protected int pPageSize = 10, pPageIndex = 1, pTotalPage = 0, pTotalRecord = 0;
public DataGrid()
{
InitializeComponent();
#region 初始化查询参数
pTable = "T_Class";
//pWhere = "F_Type='place' And F_Depth=0";
pWhere = "";
pFields = "F_ID,F_ClassName,F_Depth,F_RootId,F_Type";
pOrderField = "F_RootId";
#endregion
//开始查询
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 加载分页数据
/// </summary>
void LoadData()
{
Uri serviceUri = new Uri(pBaseUri + "SelectPageData?pTable=" + pTable.UrlEncode() + "&pWhere=" + pWhere.UrlEncode() + "&pFields=" + pFields.UrlEncode() + "&pOrder=" + pOrderField.UrlEncode() + "&pPageSize=" + pPageSize.ToString().UrlEncode() + "&pPageIndex=" + pPageIndex.ToString().UrlEncode() + "&pTotalPage=" + pTotalPage.ToString().UrlEncode() + "&pTotalRecord=" + pTotalRecord.ToString().UrlEncode());
WebClient downloader = new WebClient();
downloader.OpenReadCompleted += new OpenReadCompletedEventHandler(LoadDataCompleted);
downloader.OpenReadAsync(serviceUri);
txtStat.Text = "数据加载中
";txtStat.Foreground = new SolidColorBrush(Color.FromArgb(255, 255, 0, 0));
}
/// <summary>
/// 加载分页数据的回调函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void LoadDataCompleted(object sender, OpenReadCompletedEventArgs e)
{
if (e.Error == null)
{
try
{
JsonArray _Arr = (JsonArray)JsonArray.Load(e.Result);
List<Data> _List = new List<Data>();
for (int i = 0; i < _Arr.Count; i++)
{
_List.Add(new Data() { F_ClassName = _Arr[i]["F_ClassName"], F_Depth = int.Parse(_Arr[i]["F_Depth"]), F_ID = new Guid(_Arr[i]["F_ID"]), F_RootId = int.Parse(_Arr[i]["F_RootId"]), F_Type = _Arr[i]["F_Type"] });
}
dg.ItemsSource = _List;
}
catch (Exception ex)
{
}
}
else
{
}
}
/// <summary>
/// 加载数据的总页数/总记录条数
/// </summary>
void LoadDataStatistic()
{
Uri serviceUri = new Uri(pBaseUri + "SelectPageDataStatistic?pTable=" + pTable.UrlEncode() + "&pWhere=" + pWhere.UrlEncode() + "&pFields=" + pFields.UrlEncode() + "&pOrder=" + pOrderField.UrlEncode() + "&pPageSize=" + pPageSize.ToString().UrlEncode() + "&pPageIndex=" + pPageIndex.ToString().UrlEncode() + "&pTotalPage=" + pTotalPage.ToString().UrlEncode() + "&pTotalRecord=" + pTotalRecord.ToString().UrlEncode());
WebClient downloader = new WebClient();
downloader.OpenReadCompleted += new OpenReadCompletedEventHandler(LoadDataStatisticCompleted);
downloader.OpenReadAsync(serviceUri);
}
/// <summary>
/// 加载数据的总页数/总记录条数--回调函数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void LoadDataStatisticCompleted(object sender, OpenReadCompletedEventArgs e)
{
if (e.Error == null)
{
try
{
JsonValue _json = JsonObject.Load(e.Result);
pTotalPage = int.Parse(_json["TotalPage"]);
pTotalRecord = int.Parse(_json["RecordCount"]);
txtStat.Text = "共有" + pTotalRecord + "条记录，第" + pPageIndex.ToString() + "页/共" + pTotalPage + "页，" + pPageSize.ToString() + "/每页";
txtPageIndex.Text = pPageIndex.ToString();
txtStat.Foreground = new SolidColorBrush(Color.FromArgb(255, 0, 0, 0));
}
catch (Exception ex)
{
}
}
else
{
}
}
/// <summary>
/// 上页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPrev_Click(object sender, System.Windows.RoutedEventArgs e)
{
pPageIndex = pPageIndex <= 1 ? 1 : pPageIndex - 1;
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 下页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNext_Click(object sender, System.Windows.RoutedEventArgs e)
{
pPageIndex = pPageIndex >= pTotalPage ? pTotalPage : pPageIndex + 1;
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 首页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnFirst_Click(object sender, System.Windows.RoutedEventArgs e)
{
pPageIndex = 1;
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 末页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnLast_Click(object sender, System.Windows.RoutedEventArgs e)
{
pPageIndex = pTotalPage;
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 跳转到指定页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPageGo_Click(object sender, System.Windows.RoutedEventArgs e)
{
LoadPageIndexData();
}
private void LoadPageIndexData()
{
int _currentPageIndex = 1;
if (!int.TryParse(txtPageIndex.Text, out _currentPageIndex))
{
txtPageIndex.Text = _currentPageIndex.ToString();
}
if (_currentPageIndex >= pTotalPage)
{
_currentPageIndex = pTotalPage;
txtPageIndex.Text = _currentPageIndex.ToString();
}
if (_currentPageIndex <= 1)
{
_currentPageIndex = 1;
txtPageIndex.Text = _currentPageIndex.ToString();
}
pPageIndex = _currentPageIndex;
LoadData();
LoadDataStatistic();
}
/// <summary>
/// 输入页数回车后，查询指定页数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtPageIndex_KeyUp(object sender, System.Windows.Input.KeyEventArgs e)
{
if (e.Key == System.Windows.Input.Key.Enter)
{
LoadPageIndexData();
}
}
}
/// <summary>
/// 返回数据的类
/// </summary>
public class Data
{
public Guid F_ID { get; set; }
public string F_ClassName { get; set; }
public int F_RootId { get; set; }
public int F_Depth { get; set; }
public string F_Type { get; set; }
}
}
主要思路:
定义一组变量变于保存查询参数(包括当前第几页，每页多少条记录之类)，然后利用WebClient访问wcf，将json数据download回来，成功后解析为json对象，再转化为强类型的List<Data>，最后绑定完事
运行效果图:
作者：菩提树下的杨过
出处：http://yjmyzz.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
出处：http://yjmyzz.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
