本文主要探讨，客户端如何通过TCP通信，根据分页信息从服务器获取到相关数据
通常情况下，数据可能很多，比如几千或者几万条，并不适合一次性从服务器获取。
我们只从服务器获取当前页的数据 和数据库中记录总数以便我们可以在客户端计算出页数 ，当用户点击“上一页”或者“下一页”时，再获取相应页的数据
如下图：
双击上图中的某一项，再打开详细页面，如下图：
下面我们从头开始创建一个分页显示的Demo
通信框架采用来自英国开源的networkcomms2.3.1版本
数据库为sql2005
第一步，在数据库中建表
Create Table PlDocs( ID int identity(1000,1) primary key, Title nvarchar(200), Description nvarchar(500))
ID 是流水号 主键
Title 公文名称
Description 公文的详细描述
第二步：打开 vs 2010 创建工程文件
为了简单，基于上一篇文章 基于TCP通信的客户端断线重连 创建的工程文件继续
添加2个新类库
第三步：打开CodeSmith模板 创建相关的“存储过程”，数据层代码 逻辑层代码 模板从此文中下载 【模板下载】分享我所使用的数据库框架
(1):第一个模板的生成的存储过程如下：
存储过程
大家可以看到上面自动生成的存储过程中，包含分页存储过程，就是这一个，后面我们会用到
CREATE PROCEDURE [dbo].PlDocs_SelectPage -- Author: msdc -- Created: 2015-2-28 -- Last Modified: 2015-2-28 @PageNumber int, @PageSize int AS DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize SET @PageUpperBound = @PageLowerBound + @PageSize + 1 CREATE TABLE #PageIndex ( IndexID int IDENTITY (1, 1) NOT NULL, ID Int ) BEGIN INSERT INTO #PageIndex ( ID ) SELECT [ID] FROM [dbo].[PlDocs] -- WHERE -- ORDER BY END SELECT t1.* FROM [dbo].[PlDocs] t1 JOIN #PageIndex t2 ON t1.[ID] = t2.[ID] WHERE t2.IndexID > @PageLowerBound AND t2.IndexID < @PageUpperBound ORDER BY t2.IndexID DROP TABLE #PageIndex GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
PageNumber 是第几页
PageSize 是每页显示多少条
(2):继续使用模板生成数据层 (生成的数据层代码 与第一步生成的存储过程 是自动关联的）(命名空间需要手动改一下)
// Author: msdc
// Created: 2015-2-28
// Last Modified: 2015-2-28
using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using mojoPortal.Data;
namespace mojoPortal.Data
{
public static class DBPlDocs
{
/// <summary>
/// Gets the connection string for read.
/// </summary>
/// <returns></returns>
private static string GetReadConnectionString()
{
return ConfigurationManager.AppSettings["MSSQLConnectionString"];
}
/// <summary>
/// Gets the connection string for write.
/// </summary>
/// <returns></returns>
private static string GetWriteConnectionString()
{
if (ConfigurationManager.AppSettings["MSSQLWriteConnectionString"] != null)
{
return ConfigurationManager.AppSettings["MSSQLWriteConnectionString"];
}
return ConfigurationManager.AppSettings["MSSQLConnectionString"];
}
/// <summary>
/// Inserts a row in the PlDocs table. Returns new integer id.
/// </summary>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <returns>int</returns>
public static int Create(
string title,
string description)
{
SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "PlDocs_Insert", 2);
sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, 200, ParameterDirection.Input, title);
sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, 500, ParameterDirection.Input, description);
int newID = Convert.ToInt32(sph.ExecuteScalar());
return newID;
}
/// <summary>
/// Updates a row in the PlDocs table. Returns true if row updated.
/// </summary>
/// <param name="id"> id </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <returns>bool</returns>
public static bool Update(
int id,
string title,
string description)
{
SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "PlDocs_Update", 3);
sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, 200, ParameterDirection.Input, title);
sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, 500, ParameterDirection.Input, description);
int rowsAffected = sph.ExecuteNonQuery();
return (rowsAffected > 0);
}
/// <summary>
/// Deletes a row from the PlDocs table. Returns true if row deleted.
/// </summary>
/// <param name="id"> id </param>
/// <returns>bool</returns>
public static bool Delete(
int id)
{
SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), "PlDocs_Delete", 1);
sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
int rowsAffected = sph.ExecuteNonQuery();
return (rowsAffected > 0);
}
/// <summary>
/// Gets an IDataReader with one row from the PlDocs table.
/// </summary>
/// <param name="id"> id </param>
public static IDataReader GetOne(
int id)
{
SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "PlDocs_SelectOne", 1);
sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
return sph.ExecuteReader();
}
/// <summary>
/// Gets a count of rows in the PlDocs table.
/// </summary>
public static int GetCount()
{
return Convert.ToInt32(SqlHelper.ExecuteScalar(
GetReadConnectionString(),
CommandType.StoredProcedure,
"PlDocs_GetCount",
null));
}
/// <summary>
/// Gets an IDataReader with all rows in the PlDocs table.
/// </summary>
public static IDataReader GetAll()
{
return SqlHelper.ExecuteReader(
GetReadConnectionString(),
CommandType.StoredProcedure,
"PlDocs_SelectAll",
null);
}
/// <summary>
/// Gets a page of data from the PlDocs table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
int pageNumber,
int pageSize,
out int itemCount)
{
itemCount = GetCount();
SqlParameterHelper sph = new SqlParameterHelper(GetReadConnectionString(), "PlDocs_SelectPage", 2);
sph.DefineSqlParameter("@PageNumber", SqlDbType.Int, ParameterDirection.Input, pageNumber);
sph.DefineSqlParameter("@PageSize", SqlDbType.Int, ParameterDirection.Input, pageSize);
return sph.ExecuteReader();
}
}
}
数据层代码数据层还用到了基本SQL 操作类，每次使用复制过来即可
(3):继续使用模板生成逻辑层代码
逻辑层中有2个类：(命名空间需要手动改一下)
至此，模板的工作完成，工程文件目前的状态如下：
using System;
using System.Collections.Generic;
using System.Text;
using ProtoBuf;
namespace App.Business
{
[ProtoContract]
public class PageInfoContract
{
//当期页是哪一页
[ProtoMember(1)]
public int PageIndex { get; set; }
//每页显示多少条
[ProtoMember(2)]
public int PageSize { get; set; }
public PageInfoContract() { }
public PageInfoContract(int pageIndex, int pageSize)
{
this.PageIndex = pageIndex;
this.PageSize = pageSize;
}
}
}
PageInfoContract 当前是第几页 每页显示多少条using System;
using System.Collections.Generic;
using System.Text;
using ProtoBuf;
using System.ComponentModel;
namespace App.Business
{
/// <summary>
/// 此次返回的列表中的公文 和数量
/// </summary>
///
[ProtoContract]
public class ResNDocsContract
{
//列表中的公文
[ProtoMember(1)]
public IList<PlDocs> PlDocs { get; set; }
//数量
[ProtoMember(2)]
public int Count { get; set; }
//列表中公文数量为0时 不加入此项则报错
[DefaultValue(false), ProtoMember(3)]
private bool IsEmptyList
{
get { return PlDocs != null && PlDocs.Count == 0; }
set { if (value) { PlDocs = new List<PlDocs>(); } }
}
public ResNDocsContract() { }
public ResNDocsContract(IList<PlDocs> plDocs, int count)
{
this.PlDocs = plDocs;
this.Count = count;
}
}
}
根据信息获取到的公文客户端相关代码：
//总记录数
private int recordCount;
public int RecordCount
{
get { return recordCount; }
set { recordCount = value; }
}
//存放返回的公文
private IList<PlDocs> docList;
//当前页
private int pageIndex = 1;
//最多是多少页
private int MaxPage = 0;
//每页显示的条数
private int PageSize = 12;
//绑定数据到Listview中
public void BindData(IList<PlDocs> theDocs)
{
listView1.Items.Clear();
listView1.Columns.Clear();
listView1.View = View.Details; //新增的 详细方式
listView1.AllowColumnReorder = true;
listView1.FullRowSelect = true;
listView1.GridLines = true;
listView1.Columns.Add("ID", 80, HorizontalAlignment.Left);
listView1.Columns.Add("标题", 385, HorizontalAlignment.Left);
foreach (PlDocs theDoc in theDocs)
{
ListViewItem li = new ListViewItem();
li.SubItems[0].Text = theDoc.ID.ToString();
li.SubItems.Add(theDoc.Title);
listView1.Items.Add(li);
}
//当前的页面数
label5.Text = "当前第" + pageIndex + "页";
PageTX.Text = pageIndex.ToString();
}
//初始获取公文
private void GetDocs()
{
//包含分页大小 当前页 的信息
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
//发送信息给服务器，并获取相应的返回值
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
//获取到的公文列表
docList = DocsContract.PlDocs;
//公文的条数
recordCount = DocsContract.Count;
//在ListView中显示相关的数据
BindData(docList);
//计算出总页数
if (recordCount % PageSize == 0)
{
MaxPage = this.RecordCount / PageSize;
}
else
{
MaxPage = this.RecordCount / PageSize + 1;
}
ZongShu.Text = "共" + MaxPage + "页" + " | " + "总记录数：" + recordCount;
//以下代码作用 让ListView中内容的显示更好看一点 默认的显示太挤
ImageList imgList = new ImageList();
imgList.ImageSize = new Size(1, 25);//分别是宽和高
listView1.SmallImageList = imgList;
}
//首页
private void btnFirst_Click(object sender, EventArgs e)
{
pageIndex = 1;
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
//发送信息给服务器，并获取相应的返回值
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
docList = DocsContract.PlDocs;
recordCount = DocsContract.Count;
BindData(docList);
}
//上一页
private void preBtn_Click(object sender, EventArgs e)
{
if (this.pageIndex > 1)
{
--this.pageIndex;
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
docList = DocsContract.PlDocs;
recordCount = DocsContract.Count;
BindData(docList);
}
}
//跳转到下一页
private void NextBtn_Click(object sender, EventArgs e)
{
if (pageIndex >= MaxPage)
{
MessageBox.Show("到达最后一页");
}
else
{
++this.pageIndex;
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
//发送信息给服务器，并获取相应的返回值
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
docList = DocsContract.PlDocs;
recordCount = DocsContract.Count;
BindData(docList);
}
}
//跳转到最后一页
private void LastBtn_Click(object sender, EventArgs e)
{
pageIndex = MaxPage;
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
//发送信息给服务器，并获取相应的返回值
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
docList = DocsContract.PlDocs;
recordCount = DocsContract.Count;
BindData(docList);
}
//跳转到某页
private void GoBtn_Click(object sender, EventArgs e)
{
if (System.Text.RegularExpressions.Regex.IsMatch(PageTX.Text.Trim(), @"^[0-9]+$"))
{
pageIndex = int.Parse(PageTX.Text.Trim());
if (pageIndex > MaxPage)
{
MessageBox.Show("第" + pageIndex + "页不存在");
}
else
{
PageInfoContract contract = new PageInfoContract(pageIndex, PageSize);
//发送信息给服务器，并获取相应的返回值
ResNDocsContract DocsContract = connection.SendReceiveObject<ResNDocsContract>("GetAllDocs", "ResGetAllDocs", 5000, contract);
docList = DocsContract.PlDocs;
recordCount = DocsContract.Count;
BindData(docList);
}
}
else
{
MessageBox.Show("请输入数字! ");
}
}
客户端相关代码数据操作类和存储过程，本Demo中不需要再修改。
如果您的使用环境中，增加了关键字或者类别的设定，数据操作类和存储过程需要进行相关的修改.
到这里 示例就完成了 我们看一下界面：
下载地址
www.networkcomms.cn编辑
3161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
