<script language="javascript" src="httP://www.chinaitpower.com/images/ad.js" charset="utf-8" type="text/javascript"> </script> |
sqlserver中的存储过程完整代码 /***************************************************************** * 存储过程名: GetCustomersDataPage * 过程描述: 通用大数据集分页 摘要:[原创]存储过程DataGrid分页及注意点
sqlserver中的存储过程完整代码 /***************************************************************** * 存储过程名: GetCustomersDataPage * 过程描述: 通用大数据集分页 * 传入参数: * 传出参数: * 修改记录 * 姓名 日期 修改类型 * NickLee 2005-1-17 新建 * * * * * ******************************************************************/ -- 获取指定页的数据www.yestar2000chinai tp ow er.com55vuo
CREATE PROCEDURE [GetCustomersDataPage] @PageIndex INT, --页面索引,从datagrid中获取 @PageSize INT, --页面显示数量,从datagrid中获取 @RecordCount INT OUT, --返回记录总数 @PageCount INT OUT, --返回分页后页数 @strGetFields nvarchar(1000), -- 需要查询的列 @tableName nvarchar(500) , --表名称 @ID nvarchar(100), --主键,(为表的主键) @strWhere nvarchar(1000) ='', -- 查询条件 (注意: 不要加 where) @sortName nvarchar(50) =' asc ' , --排序方式 @orderName nvarchar(100) --父级查询排序方式www.yestar2000chinai tp ow er.com55vuo
AS declare @countSelect nvarchar(2000) --设置统计查询语句 if len(@strWhere) =0 --如果没有查询条件 begin set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName end else --否则 begin set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere end --执行并返回总数 exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) www.yestar2000chinai tp ow er.com55vuo
SET NOCOUNT ONwww.yestar2000chinai tp ow er.com55vuo
DECLARE @SQLSTR NVARCHAR(3000) --实际总共的页码小于当前页码 或者 最大页码 if @PageCount>=0 --如果分页后页数大于0 begin if @PageCount<=@PageIndex and @PageCount>0 --如果实际总共的页数小于datagrid索引的页数 --or @PageCount=1 begin --设置为最后一页 set @PageIndex=@PageCount-1 end else if @PageCount<=@PageIndex and @PageCount=0 begin set @PageIndex=0; end endwww.yestar2000chinai tp ow er.com55vuo
IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页 begin if len(@strWhere) =0 begin SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName end end ELSE IF @PageIndex = @PageCount - 1 --如果为最后一页 begin if len(@strWhere) =0 begin SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+'ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+'ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName end end ELSE --否则执行 begin if len(@strWhere) =0 begin SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName end end EXEC (@SQLSTR) set nocount off GOwww.chinai tp ow er.com采集不要钱55vuowxzPa 在asp.net中调用方法 #region 调用函数 //绑定数据www.yestar2000chinai tp ow er.com55vuo
private void DataGridDataBind() { DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);www.yestar2000chinai tp ow er.com55vuo
DataGrid1.VirtualItemCount = RecordCount; DataGrid1.DataSource = ds; DataGrid1.DataBind(); // GridExpand(this.DataGrid1,2); SetPagingState(); }www.yestar2000chinai tp ow er.com55vuo
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;www.yestar2000chinai tp ow er.com55vuo
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;www.yestar2000chinai tp ow er.com55vuo
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 ";www.yestar2000chinai tp ow er.com55vuo
comm.Fill(dataFill.myDateSet);www.yestar2000chinai tp ow er.com55vuo
recordCount = (int)comm.SelectCommand.Parameters[2].Value; pageCount = (int)comm.SelectCommand.Parameters[3].Value;www.yestar2000chinai tp ow er.com55vuo
if(pageIndex>=pageCount&&pageCount>0) { PageIndex=pageCount-1; } else if(pageIndex>=pageCount&&pageCount==0) { PageIndex=0; } // return dataFill.myDateSet;www.yestar2000chinai tp ow er.com55vuo
}www.yestar2000chinai tp ow er.com55vuo
/// <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; }www.yestar2000chinai tp ow er.com55vuo
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="第0页 共0页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条"; } else { lab_PageCount.Text="第"+(PageIndex + 1).ToString()+"页 共"+PageCount.ToString()+"页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条"; } }www.yestar2000chinai tp ow er.com55vuo
#endregionwww.chinai tp ow er.com采集不要钱55vuowxzPa 重点在数据对datagrid绑定前进行判定 if(pageIndex>=pageCount&&pageCount>0) { PageIndex=pageCount-1; } else if(pageIndex>=pageCount&&pageCount==0) { PageIndex=0; }www
* 传入参数: * 传出参数: * 修改记录 * 姓名 日期 修改类型 * NickLee 2005-1-17 新建 * * * * * ******************************************************************/ -- 获取指定页的数据www.yestar2000chinai tp ow er.com55vuo CREATE PROCEDURE [GetCustomersDataPage] @PageIndex INT, --页面索引,从datagrid中获取 @PageSize INT, --页面显示数量,从datagrid中获取 @RecordCount INT OUT, --返回记录总数 @PageCount INT OUT, --返回分页后页数 @strGetFields nvarchar(1000), -- 需要查询的列 @tableName nvarchar(500) , --表名称 @ID nvarchar(100), --主键,(为表的主键) @strWhere nvarchar(1000) ='', -- 查询条件 (注意: 不要加 where) @sortName nvarchar(50) =' asc ' , --排序方式 @orderName nvarchar(100) --父级查询排序方式www.yestar2000chinai tp ow er.com55vuo AS declare @countSelect nvarchar(2000) --设置统计查询语句 if len(@strWhere) =0 --如果没有查询条件 begin set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName end else --否则 begin set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere end --执行并返回总数 exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) www.yestar2000chinai tp ow er.com55vuo SET NOCOUNT ONwww.yestar2000chinai tp ow er.com55vuo DECLARE @SQLSTR NVARCHAR(3000) --实际总共的页码小于当前页码 或者 最大页码 if @PageCount>=0 --如果分页后页数大于0 begin if @PageCount<=@PageIndex and @PageCount>0 --如果实际总共的页数小于datagrid索引的页数 --or @PageCount=1 begin --设置为最后一页 set @PageIndex=@PageCount-1 end else if @PageCount<=@PageIndex and @PageCount=0 begin set @PageIndex=0; end endwww.yestar2000chinai tp ow er.com55vuo IF @PageIndex = 0 OR @PageCount <= 1 --如果为第一页 begin if len(@strWhere) =0 begin SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName end end ELSE IF @PageIndex = @PageCount - 1 --如果为最后一页 begin if len(@strWhere) =0 begin SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+'ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+'ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName end end ELSE --否则执行 begin if len(@strWhere) =0 begin SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName end else begin SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName end end EXEC (@SQLSTR) set nocount off GOwww.chinai tp ow er.com采集不要钱55vuowxzPa 在asp.net中调用方法 #region 调用函数 //绑定数据www.yestar2000chinai tp ow er.com55vuo private void DataGridDataBind() { DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);www.yestar2000chinai tp ow er.com55vuo DataGrid1.VirtualItemCount = RecordCount; DataGrid1.DataSource = ds; DataGrid1.DataBind(); // GridExpand(this.DataGrid1,2); SetPagingState(); }www.yestar2000chinai tp ow er.com55vuo 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;www.yestar2000chinai tp ow er.com55vuo 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;www.yestar2000chinai tp ow er.com55vuo 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 ";www.yestar2000chinai tp ow er.com55vuo comm.Fill(dataFill.myDateSet);www.yestar2000chinai tp ow er.com55vuo recordCount = (int)comm.SelectCommand.Parameters[2].Value; pageCount = (int)comm.SelectCommand.Parameters[3].Value;www.yestar2000chinai tp ow er.com55vuo if(pageIndex>=pageCount&&pageCount>0) { PageIndex=pageCount-1; } else if(pageIndex>=pageCount&&pageCount==0) { PageIndex=0; } // return dataFill.myDateSet;www.yestar2000chinai tp ow er.com55vuo }www.yestar2000chinai tp ow er.com55vuo /// <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; }www.yestar2000chinai tp ow er.com55vuo 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="第0页 共0页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条"; } else { lab_PageCount.Text="第"+(PageIndex + 1).ToString()+"页 共"+PageCount.ToString()+"页 每页"+PageSize.ToString()+"条 共"+RecordCount.ToString()+"条"; } }www.yestar2000chinai tp ow er.com55vuo #endregionwww.chinai tp ow er.com采集不要钱55vuowxzPa 重点在数据对datagrid绑定前进行判定 if(pageIndex>=pageCount&&pageCount>0) { PageIndex=pageCount-1; } else if(pageIndex>=pageCount&&pageCount==0) { PageIndex=0; }www |