在网上找过几个存储过程分页,觉得NickLee的最好用,原帖点这里,这里转一下,也修改了一些错误
在asp.net中调用方法:
#region 声明
//----------------------------------------------------------------------
//
//
// 作者: 李淼(Nick.Lee)
//
// 存储过程DataGrid分页及注意点
//
//
// boyorgril@msn.com
//
//----------------------------------------------------------------------
#endregion
private void DataGridDataBind()
{
DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);
DataGrid1.VirtualItemCount = RecordCount;
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
// GridExpand(this.DataGrid1,2);
SetPagingState();
}
private DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)
{
dataFill.ConString=System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];
dataFill.sqlClientDataSet("GetCustomersDataPage");
System.Data.SqlClient.SqlDataAdapter comm=dataFill.mySqlAdapter;
comm.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));
comm.SelectCommand.Parameters[0].Value = pageIndex;
comm.SelectCommand.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));
comm.SelectCommand.Parameters[1].Value = pageSize;
comm.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));
comm.SelectCommand.Parameters[2].Direction = ParameterDirection.Output;
comm.SelectCommand.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));
comm.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;
comm.SelectCommand.Parameters.Add(new SqlParameter("@strGetFields",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[4].Value ="tOrder.orderTime as '下订单时间',tOrder.facName as '工厂',tOrder.facOrderNum as '工厂订单号',tOrder.quantity as '定单数',tOrder.realQuantity as '实际出货数',tOrder.reqTime as '要求出货时间',tOrder.repTime as '出货时间',tMaterial.matName as '材料',tMaterial.colName as '颜色',tOrder.leaveQuantity as '未出货数',tOrder.orderStatic as '全部出货',tOrder.orderDetail as '备注' ";
/*tOrder.comName as '公司',tOrder.comOrderNum as '公司订单号',*/
comm.SelectCommand.Parameters.Add(new SqlParameter("@tableName",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[5].Value =" tOrder left join tStock on tOrder.stoID=tStock.stoID left join tMaterial on tStock.matID=tMaterial.matID ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@ID",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[6].Value =" tOrder.orderID ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@orderName",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[7].Value =" tMaterial.matName ";
comm.SelectCommand.Parameters.Add(new SqlParameter("@strWhere",SqlDbType.NVarChar));
comm.SelectCommand.Parameters[8].Value =" facName='"+en1.decyrpt(this.Request.QueryString["facName"].ToString())+"' and facOrderNum='"+en1.decyrpt(this.Request.QueryString["facNum"].ToString())+"' ";
// comm.Parameters.Add(new SqlParameter("@sortName",SqlDbType.NVarChar));
// comm.Parameters[8].Value =" desc ";
comm.Fill(dataFill.myDateSet);
recordCount = (int)comm.SelectCommand.Parameters[2].Value;
pageCount = (int)comm.SelectCommand.Parameters[3].Value;
if(pageIndex>=pageCount&&pageCount>0)
{
PageIndex=pageCount-1;
}
else if(pageIndex>=pageCount&&pageCount==0)
{
PageIndex=0;
}
//
return dataFill.myDateSet;
}
/// <summary>
/// 控制导航按钮或数字的状态
/// </summary>
public void SetPagingState()
{
if( PageCount <= 1 )//( RecordCount <= PageSize )//小于等于一页
{
this.Menu1.Items[0].Enabled = false;
this.Menu1.Items[1].Enabled = false;
this.Menu1.Items[2].Enabled = false;
this.Menu1.Items[3].Enabled = false;
}
else //有多页
{
if( PageIndex == 0 )//当前为第一页
{
this.Menu1.Items[0].Enabled = false;
this.Menu1.Items[1].Enabled = false;
this.Menu1.Items[2].Enabled = true;
this.Menu1.Items[3].Enabled = true;
}
else if( PageIndex == PageCount - 1 )//当前为最后页
{
this.Menu1.Items[0].Enabled = true;
this.Menu1.Items[1].Enabled = true;
this.Menu1.Items[2].Enabled = false;
this.Menu1.Items[3].Enabled = false;
}
else //中间页
{
this.Menu1.Items[0].Enabled = true;
this.Menu1.Items[1].Enabled = true;
this.Menu1.Items[2].Enabled = true;
this.Menu1.Items[3].Enabled = true;
}
}
if(RecordCount == 0)
{
lab_PageCount.Text="第页共页每页"+PageSize.ToString()+"条共"+RecordCount.ToString()+"条";
}
else
{
lab_PageCount.Text="第"+(PageIndex + 1).ToString()+"页共"+PageCount.ToString()+"页每页"+PageSize.ToString()+"条共"+RecordCount.ToString()+"条";
}
}
#endregion
//重点在数据对datagrid绑定前进行判定
if(pageIndex>=pageCount&&pageCount>0)
{
PageIndex=pageCount-1;
}
else if(pageIndex>=pageCount&&pageCount==0)
{
PageIndex=0;
}