Patement under large data volume
For very large data models, the page retrieves, each time you load the entire data source is very wasteful. The usual selection is to retrieve the data of the block area of the page size, rather than retrieving all the data, and then perform the current row.
This article demonstrates ASP.NET's DataGrid and SQL Server to achieve paging under large amounts of data. In order to facilitate demonstration, the data table uses the ORDERS table (830 records) of the Northwind database.
If there is a unique self-increment index in the data table, and this field does not have a break-up page. The block area data that retrieves the page size is very simple. This feature can be implemented by simple SQL statements:
Td.code {Padding: 0, 10, 0, 10; Border-style: Solid; Border-Width: 1; Border-Bottom: 0; Border-Top: 0; Border-Right: 0; Border-Color: CCCCCC; Background-color: fffee}
Select * from Orders where ORDERID BETWEEN 10248 and 10253
Among them, start numbers: (CURRENTPAGEINDEX - 1) * Pagesize end number: CurrentPageIndex * Pagesize
Of course, if this field break is not very serious, and allows not to be very strict, according to count of each page, such a method can be used.
If this field breaks the sign, or you need to sort the paging according to other conditions, it is more complicated. First get the number you need to display this page, then follow this number to get the required block area data. Getting block area data based on numbers is simple. However, the data sorting is obtained in the following manner is not in the order sequence in the specified ID, this is also attached to the order by command.
Select * from Orders Where Orderid in (10248, 10249, 10250, 10251, 10252, 10253) Order by OrderID DESC
The list of numbers that you need to display this page is more complicated, and there are multiple solutions:
Solution 1: Maintain a table, this table records these numbered sorting sequences that need to be displayed. (This table can be a temporary table or a physical table). The following demonstrates the use of a global temporary table. This global temporary table records you need to display. Pay attention to sorting, the Order By here is a 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 Order by OrderID DESC
Select * from ## temptable
Drop Table ## Temptable - When actually executed, delete all temporary tables must not be performed here.
This temporary table exists, which is very simple to get the block data specified by the paging. Look at the following code:
Create Table ## Temptable (IID IdenTentity (1, 1) Not Null, Mainid Int Not NULL
Insert ## temptable (mainid) Select Orderid from Orders Order by OrderID DESC
Declare @PageSize Int, @ currpage int, @ strarsql varchar (2000), @ idstr varchar (1000)
SELECT @Pagesize = 30
SELECT @Currpage = 2select @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 program, you should also consider when updating this global temporary table, usually in planning tasks, and update this summary table.
Solution 2: Every time you go, you get the latest number of numbered order each time. Since there is no such temporary table, you need some strokes that need to display the number of the page, look 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 (500), @ strarsql 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 when @i> ' str (@previous) ' Ten @IDSTR LTRIM (RTRIM (Strim (Strim (StriD))) '', '' '
Select @strsql = @strsql n'Else n '' 'end'
Select @strsql = @strsql n'from ots'
Select @strsql = ltrim (RTRIM (@strsql)) n 'Order by Orderid Desc' SELECT @IDSTR = n ''
EXEC SP_EXECUTESQL @ strsql, n '@ i int, @ idstr varchar (500) 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 method of using this partitioned data. DataGrid support block area operations via AllowCustomPaging and VirtualItemcount properties. If AllowCustomPaging is true, the DataGrid does not calculate the start display location in the data model according to the CurrentPageIndex computing data model. DataGrid displays all the data in the data model, and the page navigation bar reports the current location as (VirtualItemcount PageSize-1) / PageSize CurrentPageIndex page. The following example illustrates this feature.
Protected Void BinddataGrid (int curpage)
{
String strconn = "data source = (local); integrated security = sspi; database = northwind";
/ / Confirm that the machine name / ASPNET user can access the Northwind database
SQLCommand cmd = new sqlcommand ();
SqlConnection Conn = New SqlConnection (STRCONN);
Sqlparameter [] PARMS = New Sqlparameter [] {
New Sqlparameter ("@ Pagesize", SqldbType.It),
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);
// Database Paging Algorithm The first page is 1 DataGrid's first page 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);
// Open this page for the first time, access the first page of the page
}
}
Void MyDataGrid_page (Object Sender, DataGridPageChangeDeventArgs E) {
BindDataGrid (E.NEWPAGEINDEX);
}
If you have more data, you can use this demo. The under the presentation code is downloaded, and the presentation code is used. Use the readme.txt file using the method.
Whole demo code download
Http://chs.gotdotnet.com/quickstart/aspplus/samples/webforms/ctrlref/webctrl/datagrid/doc_datagrid.aspx#paging This function is demonstrated with this feature that uses DataGrid (not the use of stored procedures discussed in this article) . If this feature to DataGrid is not familiar, please see here first.
I have recently encountered the problem of big data distribution. After discussing with 9CBS netizens, I feel that the more feasible solution is the two programs mentioned above, who has a better solution, or find questions, please contact me, thanks
My email is: ghj1976@9cbs.net
