您的位置：Mobile->wince调用多层架构中webservice的数据分页处理

wince调用多层架构中webservice的数据分页处理2007-10-15 来自：lizhe1985  [收藏到我的网摘]

来源：蒋智昊的专栏 - CSDNBlog

1.编写存储过程

ALTER procedure pGetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸/页大小
@PageIndex int = 1, -- 页码/页号
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(4000) = '', -- 查询条件 (注意: 不要加 where)
@TotalRecordCount int output, -- 总记录数
@PageCount int output -- 总页数
AS

declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(4000) -- 临时变量
declare @strOrder varchar(4000) -- 排序类型
declare @paramDefine nvarchar(1000)


/*---begin--数据表中总记录数--*/
set @strSQL = 'select @TotalRecordCount = count(*) from ' + @tblName + ' where 1=1'
if @strWhere != ''
set @strSQL = @strSQL + ' and ' + @strWhere
set @paramDefine = '@TotalRecordCount int output'
exec SP_EXECUTESQL @strSQL,@paramDefine,@TotalRecordCount output
/*---end--------------------*/

/*---begin--验证页大小-------*/
if @PageSize ＜ 0
set @PageSize = 0
if @PageSize ＞ @TotalRecordCount
set @PageIndex = @TotalRecordCount
/*---end---------------------*/

/*---begin--根据页大小计算总页数*/
if @TotalRecordCount % @PageSize = 0
set @PageCount = @TotalRecordCount / @PageSize
else
set @PageCount = @TotalRecordCount / @PageSize + 1
/*---end-----------------------*/

/*----begin--验证页号-----------*/
if @PageIndex ＜ 1
set @PageIndex = 1
if @PageIndex ＞ @PageCount
set @PageIndex = @PageCount
if @PageCount = 0
set @PageIndex = 1
/*----end-----------------------*/


if @OrderType != 0
begin
set @strTmp = '＜(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '＞(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere

set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end

exec (@strSQL)

2.IDAL

/// ＜summary＞
/// 获得数据列表-采用分页存储过程
/// ＜/summary＞
/// ＜param name="tblName"＞表名＜/param＞
/// ＜param name="fldName"＞字段＜/param＞
/// ＜param name="PageSize"＞页尺寸＜/param＞
/// ＜param name="PageIndex"＞页码＜/param＞
/// ＜param name="OrderType"＞设置排序类型, 非 0 值则降序＜/param＞
/// ＜param name="strWhere"＞查询条件 (注意: 不要加 where)＜/param＞
DataSet GetListByProc(string tblName, string fldName, int PageSize, int PageIndex, int OrderType, string strWhere, ref string TotalRecordCount, ref string PageCount);
3.SQLServerDAL



/// ＜summary＞
/// 获得数据列表-采用分页存储过程
/// ＜/summary＞
/// ＜param name="tblName"＞表名＜/param＞
/// ＜param name="fldName"＞字段＜/param＞
/// ＜param name="PageSize"＞页尺寸＜/param＞
/// ＜param name="PageIndex"＞页码＜/param＞
/// ＜param name="OrderType"＞设置排序类型, 非 0 值则降序＜/param＞
/// ＜param name="strWhere"＞查询条件 (注意: 不要加 where)＜/param＞
public DataSet GetListByProc(string tblName, string fldName, int PageSize, int PageIndex, int OrderType, string strWhere, ref string TotalRecordCount, ref string PageCount)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
new SqlParameter("@TotalRecordCount",SqlDbType.Int),
new SqlParameter("@PageCount",SqlDbType.Int)
};
parameters[0].Value = tblName;
parameters[1].Value = fldName;
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = OrderType;
parameters[5].Value = strWhere;
parameters[6].Direction = ParameterDirection.Output;
parameters[7].Direction = ParameterDirection.Output;
return DbHelperSQL.RunProcedure("pGetRecordByPage", parameters, "ds", ref TotalRecordCount, ref PageCount);
}
4.DbHelperSQL

///// ＜summary＞
///// 执行存储过程-返回参数
///// ＜/summary＞
///// ＜param name="storedProcName"＞存储过程名＜/param＞
///// ＜param name="parameters"＞存储过程参数＜/param＞
///// ＜param name="tableName"＞DataSet结果中的表名＜/param＞
///// ＜returns＞DataSet＜/returns＞
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, ref string TotalRecordCount, ref string PageCount)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
//总记录数
TotalRecordCount = sqlDA.SelectCommand.Parameters["@TotalRecordCount"].Value.ToString();
//总页数
PageCount = sqlDA.SelectCommand.Parameters["@PageCount"].Value.ToString();
return dataSet;
}
}


5.WebService



/// ＜summary＞
/// 获得数据列表-采用分页存储过程
/// ＜/summary＞
/// ＜returns＞＜/returns＞
/// ＜author＞bluedn＜/author＞
/// ＜date＞2007-09-30＜/date＞
[WebMethod(Description = "获得数据列表-采用分页存储过程")]
public DataSet GetListByProc(int PageIndex, ref string TotalRecordCount, ref string PageCount)
{
DataSet ds;
DataTable dt = this.getTblColInfo("BILL_NO", "单号", "150");
ds = IBILL_JOBTASK.GetListByProc("BILL_JOBTASK", "BILL_NO", 3, PageIndex, 0, "", ref TotalRecordCount, ref PageCount);
ds.Tables.Add(dt);

return ds;
}
6.Application--wince中应用



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlTypes;
using System.IO;
using System.Runtime;
using WS = RFApplication.RFWebService;

namespace RFApplication
{
public partial class FrmGetListByProc : Form
{
public FrmGetListByProc()
{
InitializeComponent();
}

#region 分页用到的变量
private int PageIndex = 1; //页码
private string TotalRecordCount = ""; //总记录数
private string PageCount = ""; //总页数
private bool PreFlag = true; //上下页翻页标识
#endregion

/// ＜summary＞
/// 上一页
/// ＜/summary＞
/// ＜author＞bluedn＜/author＞
/// ＜date＞2007-09-30＜/date＞
/// ＜param name="sender"＞＜/param＞
/// ＜param name="e"＞＜/param＞
private void btnPrev_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
using (WS.Service ws = new WS.Service())
{
ws.Url = PulbicFunc.WSurl;

//防止出现跳页
if (!PreFlag)
{
//当前页码等于总页数
if (PageIndex == int.Parse(PageCount))
{
PageIndex = PageIndex - 1;
}
//当前页码小于总页数
else
{
PageIndex = PageIndex - 2;
}
}

DataSet ds = ws.GetListByProc(PageIndex, ref TotalRecordCount, ref PageCount);

if (int.Parse(TotalRecordCount) == 0)
{
MessageBox.Show("没有任何单据信息", "提示");
return;
}

//总记录数
txtTotalRecordCount.Text = TotalRecordCount;
//总页数
txtPageCount.Text = PageCount;
//当前页数
txtPageIndex.Text = PageIndex.ToString();

PulbicFunc.vdFillData(ds, dgrdBill);

//翻页
if (PageIndex ＞ 1)
{
PageIndex = PageIndex - 1;
}

PreFlag = true;
}
Cursor.Current = Cursors.Default;
btnNext.Enabled = true;
}

/// ＜summary＞
/// 下一页
/// ＜/summary＞
/// ＜author＞jzh＜/author＞
/// ＜date＞2007-09-30＜/date＞
/// ＜lasteditdate＞2007-10-12＜/lasteditdate＞
/// ＜param name="sender"＞＜/param＞
/// ＜param name="e"＞＜/param＞
private void btnNext_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
using (WS.Service ws = new WS.Service())
{
ws.Url = PulbicFunc.WSurl;

//防止出现跳页
if (PreFlag)
{
if (PageIndex ==1)
{
//当前页码、总页数页都是1时，不能翻页
if (PageIndex == int.Parse(PageCount))
{
return;
}
else
{
PageIndex = PageIndex + 1;
}
}
else
{
PageIndex = PageIndex + 2;
}
}

DataSet ds = ws.GetListByProc(PageIndex, ref TotalRecordCount, ref PageCount);

//总记录数
txtTotalRecordCount.Text = TotalRecordCount;
//总页数
txtPageCount.Text = PageCount;
//当前页数
txtPageIndex.Text = PageIndex.ToString();

PulbicFunc.vdFillData(ds, dgrdBill);

//翻页
if (PageIndex ＜ int.Parse(PageCount))
{
PageIndex = PageIndex + 1;
}
PreFlag = false;
}
Cursor.Current = Cursors.Default;
}
}
}

推荐人评论

本文用实例介绍wince调用多层架构中webservice的数据分页处理的相关内容。

用户评论

正在载入评论列表...