Stored procedures DataGrid paging and attention points

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 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" 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";

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>
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;
}

else if (PageIndex = = PageCount-1)//current is last page
{
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//Middle page
{
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= "No. 0 page Total 0 pages per page" +pagesize.tostring () + "Total" +recordcount.tostring () + "section";
}
Else
{
Lab_pagecount.text= "First" + (PageIndex + 1). ToString () + "page Total" +pagecount.tostring () + "page per page" +pagesize.tostring () + "Total" +recordcount.tostring () + "section";
}
}

#endregion

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;
}