Paging under large volumes of data

Pagination | Data for very large data models, it is wasteful to load the entire data source every time when paging is retrieved. The usual choice is to retrieve the chunk of the page size, rather than retrieve all the data, and then step into the current row.

This article demonstrates the asp.net of the DataGrid and SQL Server to achieve a large amount of data paging, in order to facilitate the implementation of the demo, the data table using the Northwind database's Orders table (830 records).

If there is a unique self indexed in the datasheet, and the field does not have a broken number phenomenon. The chunk data that retrieves the page size is very simple. This functionality can be achieved through simple SQL statements:
SELECT * FROM orders where OrderID between 10248 and 10253
Where the start number is: (CurrentPageIndex-1) * PageSize end number is: CurrentPageIndex * PageSize

Of course, this method is also available if the field is not very serious, and allows you to break the page by the number of pages per page.

It's more complicated if this field is broken, or if you need to sort the page by other criteria. First get the number that the page needs to display, and then follow that number to get the chunk data you need. It is easy to get block data based on numbers. However, it is not the order of the specified ID list to get the data sorted in the following way, and this time you will append an ORDER by command.
SELECT * FROM Orders where OrderID in (10248,10249,10250,10251,10252,10253) ORDER BY OrderID DESC
The number list you need to display for this page is much more complicated, and there are several scenarios:

Scenario One: Maintain a table that records the sort order of these numbers that need to be displayed. (This can be a temporary table or a physical table). The following illustrates the use of a global temporary table. This global temporary table records the number that needs to be displayed. Note the ordering by the sort order that needs to be displayed.
CREATE TABLE # #temptable (
IID int IDENTITY (1, 1) not NULL,
Mainid int not NULL
)

Insert # #temptable (mainid) Select OrderID from orders by OrderID Desc

SELECT * FROM # #temptable

DROP TABLE # #temptable-when actually executing, deleting all temporary tables is certainly no longer performed here.
This temporary table exists, and it is easy to get the chunk data that specifies the pagination. Look at the following code:
CREATE TABLE # #temptable (IID int IDENTITY (1, 1) not Null,mainid int not NULL)
Insert # #temptable (mainid) Select OrderID from orders by OrderID Desc
declare @PageSize int, @CurrPage int, @strSQL varchar, @IDStr varchar (1000)
Select @PageSize = 30
Select @CurrPage = 2
Select @IDStr = '
Select @IDStr = @IDStr + ltrim (RTrim (str (mainid))) + ', ' from # #temptable
where IID between (@CurrPage-1) * @PageSize + 1) and @CurrPage * @PageSize
If @IDStr <> '
Begin
Select @IDStr = Left (@IDStr, Len (@IDStr)-1)
End
Select @strSQL = ' SELECT * FROM Orders where OrderID in (' + @IDStr + ') Order by OrderID Desc '
EXEC (@strSQL)
drop TABLE # #temptable
Note: When you actually use this scenario, consider when to update the global temporary table, usually in the scheduled task, and update the summary table regularly.

Program two: Every time to go to the query, each get the latest numbering sequence. Since this temporary table does not exist, it takes a bit of skill to write a string that needs to display the number of the page, looking at the following code:
declare @PageSize int, @CurrPage int,
@topnum int, @previous int
Select @PageSize = 30
Select @CurrPage = 2
Select @topnum = @CurrPage * @PageSize
Select @previous = (@CurrPage-1) * @PageSize
DECLARE @i int, @IDStr nvarchar (+), @strSQL nvarchar (1000)
Select @i = 0
Select @strSQL = N ' '
Select @strSQL = @strSQL + N ' select Top ' +str (@topnum) + ' @i = @i + 1 '
Select @strSQL = @strSQL + N ', @IdStr = '
Select @strSQL = @strSQL + N ' case @i > ' +str (@previous) + ' then @IdStr + ltrim (RTrim (str (OrderID))) + ', '
Select @strSQL = @strSQL + n ' else n ' ' End '
Select @strSQL = @strSQL + N ' from Orders '
Select @strSQL = LTrim (RTrim (@strSQL)) + N ' ORDER by OrderID Desc '
Select @IdStr = N ' '
EXEC sp_executesql @strSQL, N ' @i int, @IdStr varchar output ', @i, @IdStr output
If Len (RTrim (LTrim (@IdStr)) > 0
Begin
Select @IdStr = Left (@IdStr, Len (@IdStr)-1)
End
Select @strSQL = ' SELECT * FROM Orders where OrderID in (' + @IDStr + ') '
EXEC (@strSQL)
ASP.net's DataGrid provides a way to use data from this partition. The DataGrid supports block operations through the allowcustompaging and VirtualItemCount properties. If AllowCustomPaging is true, the DataGrid does not calculate the starting position in the data model based on CurrentPageIndex. The DataGrid displays all the data in the data model, and the pager bar reports the current position as the CurrentPageIndex page of the (virtualitemcount+pagesize-1)/pagesize. The following example illustrates this feature.
protected void Binddatagrid (int currpage)
{
String strconn = "Data source= (local); integrated security=sspi;database=northwind";
Please verify that the machine name/aspnet users can access the Northwind database
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (strconn);
sqlparameter[] parms = new sqlparameter[] {
New SqlParameter ("@PageSize", SqlDbType.Int),
New SqlParameter ("@CurrPage", SqlDbType.Int),
New SqlParameter ("@SearchSql", sqldbtype.nvarchar,128),
New SqlParameter ("@Count", SqlDbType.Int),
};
Parms[0]. Value = datagrid1.pagesize;
PARMS[1]. Value = (currpage+1);
The page-splitting algorithm for a database The first page of the 1 DataGrid is 0.
PARMS[2]. Value = DBNull.Value;
PARMS[3]. Direction = ParameterDirection.Output;
PARMS[3]. Value = DBNull.Value;
DataSet DS = new DataSet ();
Try
{
IF (Conn. State!= ConnectionState.Open) Conn. Open ();
Cmd. Connection = conn;
Cmd.commandtext = "Selected_page_list";
Cmd.commandtype = CommandType.StoredProcedure;
if (parms!= null)
{
foreach (SqlParameter parm in parms)
Cmd. Parameters.Add (Parm);
}
SqlDataAdapter DA = new SqlDataAdapter (cmd);
DA. Fill (DS);
int aa = Convert.ToInt32 (parms[3]. Value.tostring ());
Cmd. Parameters.clear ();
if (currpage = 0)
{
Datagrid1.virtualitemcount = AA;
}
Datagrid1.currentpageindex = Currpage;
DataGrid1.DataSource = DS;
Datagrid1.databind ();
}
catch (Exception ewx)
{
Conn. Close ();
Response.Write (ewx. Message.tostring ());
Response.End ();
}
}

void Page_Load (Object sender, EventArgs E) {
if (! IsPostBack)
{
Binddatagrid (0);
The first time you open this page, access the first page of the paging
}
}

void Mydatagrid_page (Object sender, DataGridPageChangedEventArgs e) {
Binddatagrid (E.newpageindex);
}
You can also use this demo if you have a slightly modified table with more data. Below is the demo code download, and the demo code uses scenario two. Use the method to look at the Readme.txt file.

Download the entire demo code

Http://chs.gotdotnet.com/quickstart/aspplus/samples/webforms/ctrlref/webctrl/datagrid/doc_datagrid.aspx#paging
This demonstrates the use of this feature of the DataGrid (no partitioning data is obtained using stored procedures discussed in this article). If you are not familiar with this function of the DataGrid, see here first.

I recently encountered a large amount of data paging problem, after the discussion with CSDN netizens, feel more feasible solution is the above mentioned 2 kinds of programs, who has a better plan, or find the problem, please email contact me,

My email is: ghj1976@csdn.net