datagrid| Stored Procedures | Complete code for stored procedures in paging SQL Server
/*****************************************************************
* Stored Procedure name: Getcustomersdatapage
* Process Description: Universal Large Data set paging
* Incoming parameters:
* Outgoing Parameters:
* Modify Records
* Name Date modification type
* Nicklee 2005-1-17 New
*
*
*
*
*
******************************************************************/
--Gets the data for the specified page
CREATE PROCEDURE [Getcustomersdatapage]
@PageIndex INT,--page index, fetching from DataGrid
@PageSize INT,--page display number, fetching from DataGrid
@RecordCount INT out,--Returns the total number of records
@PageCount INT out,--Returns the page number of pages after pagination
@strGetFields nvarchar (1000)--columns that need to be queried
@tableName nvarchar (500),--table name
@ID nvarchar (100),--primary key, (primary key for table)
@strWhere nvarchar (1000) = ',--Query criteria (note: Do not add where)
@sortName nvarchar (m) = ' ASC ',--sort method
@orderName nvarchar (100)--Parent Query Sort method
As
declare @countSelect nvarchar (2000)
--Set up a statistical query statement
If Len (@strWhere) =0
--If no query criteria
Begin
Set @countSelect =n ' SELECT @CountRecord = COUNT (*) from ' + @tableName
End
Else
-otherwise
Begin
Set @countSelect =n ' SELECT @CountRecord = COUNT (*) from ' + @tableName + ' where ' + @strWhere
End
--Execute and return total
EXEC sp_executesql @countSelect, N ' @CountRecord int output ', @RecordCount output
SET @PageCount = CEILING (@RecordCount * 1.0/@PageSize)
SET NOCOUNT on
DECLARE @SQLSTR NVARCHAR (3000)
--The actual total page number is less than the current page number or the maximum page number
If @PageCount >=0
--If the page is more than 0 pages after pagination
Begin
If @PageCount <= @PageIndex and @PageCount >0-if the actual total number of pages is less than the number of pages in the DataGrid index
--or @PageCount =1
Begin
--Set to last page
Set @PageIndex = @PageCount-1
End
else if @PageCount <= @PageIndex and @PageCount =0
Begin
Set @PageIndex = 0;
End
End
If @PageIndex = 0 OR @PageCount <= 1--If the first page
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-if the last page
Begin
If Len (@strWhere) =0
Begin
SET @SQLSTR =n ' SELECT ' + @strGetFields + ' from ' + @tableName + ' where ' + @ID + ' +str ' (/* @RecordCount-*/@p Agesize * @PageIndex) + @ID + ' from ' + @tableName + ' ORDER BY ' + @orderName + @sortName + ') Order BY ' + @orderName + @sortName
End
Else
Begin
SET @SQLSTR =n ' SELECT ' + @strGetFields + ' from ' + @tableName + ' where ' + @ID + ' No 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--otherwise execute
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 ' + @ord Ername+ @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
Go
Calling Methods in asp.net
Calling functions #region
Binding Data
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" under Order Time ', torder.facname as ' factory ', torder.facordernum as ' factory order number ', torder.quantity as ' order number ', Torder.realquantity as ' actual shipments ', torder.reqtime as ' request shipping time ', torder.reptime as ' Shipment time ', tmaterial.matname as ' material ', Tmaterial.colname as ' color ', torder.leavequantity as ' not shipped ', torder.orderstatic as ' all shipments ', torder.orderdetail as ' remarks ' ';
/*torder.comname as ' company ', Torder.comordernum as ' company order number ', * *
Comm. SELECTCOMMAND.PARAMETERS.ADD (New SqlParameter ("@tableName", SqlDbType.NVarChar));
Comm. SELECTCOMMAND.PARAMETERS[5]. Value = "Torder left join Tstock in 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";
if (pageindex>=pagecount&&pagecount>0)
{
pageindex=pagecount-1;
}
else if (pageindex>=pagecount&&pagecount==0)
{
pageindex=0;
}
//
return datafill.mydateset;
}
<summary>
Control the status of a navigation button or number
</summary>
public void Setpagingstate ()
{
if (PageCount <= 1)//(RecordCount <= PageSize)//less than or equal to one page
{
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//More than one page
{
if (PageIndex = 0)//current is the first page
{
This. Menu1.items[0]. Enabled = false;
This. MENU1.ITEMS[1]. Enabled = false;
This. MENU1.ITEMS[2]. Enabled = true;
This. MENU1.ITEMS[3]. Enabled = true;
}
Focus on the decision before data is bound to the DataGrid
if (pageindex>=pagecount&&pagecount>0)
{
pageindex=pagecount-1;
}
else if (pageindex>=pagecount&&pagecount==0)
{
pageindex=0;
}
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.