Stored procedures DataGrid paging and attention points

Source: Internet
Author: User
Tags join sort table name tostring
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;
}






Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.