Using. Net for sorting and paging to solve big data problems

Using. Net for sorting and paging to solve big data problems

I. Overview:

The DataGrid is often used in Web applications. However, the DataGrid Control performs sorting by arch. The paging function is unrealistic for tables with a large number of records, for example, if a table contains 0.2 million records, all records must be retrieved based on the order of the DataGrid and the principle of paging, this poses a challenge to the server's memory and response speed. This paper provides a solution to retrieve the required and qualified records from the database, which greatly improves the response time and reduces the memory overhead.

Ii. functions:

1. display the data retrieved from the database in the specified format (displayed using the DataGrid Control) on the page ).

2. pagination of records (the page must be displayed in the database, that is, the page on which records are imported and the number of records on each page is returned)

3. Click the DataGrid header to display the displayed records in ascending order, instead, retrieve the specified page records that meet the conditions in the database in the specified order)

4. You can click the checkbox box of the DataGrid to select all or all items, and Support Group operations (such as deletion and transfer)

Iii. Constraints

1. A database table must have a keyword segment or a unique field.

4. Production Process (example ):

1. database structure:

This example uses the SQL Server database northwind as an example. The table is MERs.
CodeCompilation process:

1. First, the user adds a new form.

2. Place a five linkbutton control on the form and set the ID to lkbdel, linkbuttonpreview, linkbuttonnext, linkbuttonlast, and linkbuttonfirst. Set the text attribute of each control to "delete ", "Homepage", "Previous Page", "Next page", and "last page" are placed in the same position. A DataGrid Control is also placed on the form and the control name is dglist.

3. Set the autosortint attribute of the dglist control to true, the autogenaratecolumes attribute to false, the datakeyfield attribute to customerid, and the width attribute to 100%. Set the columns of the dglist control as follows:

<Columns>
<Asp: templatecolumn>
<Headerstyle width = "10px"> <Headertemplate>
<Input id = "chkselectall" type = "checkbox" onclick = "checkallorno (this)"> </ASP: checkbox>
</Headertemplate>
<Itemtemplate>
<Asp: checkbox id = "chkselect" runat = "server"> </ASP: checkbox>
</Itemtemplate>
</ASP: templatecolumn>
<Asp: boundcolumn datafield = "customerid" sortexpression = "customerid" headertext = "ID"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "companyName" sortexpression = "companyName" headertext = "customer name"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "contactname" sortexpression = "contactname" headertext = "contact name"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "contacttitle" sortexpression = "contacttitle" headertext = ""> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "phone" sortexpression = "phone" headertext = "phone"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "fax" sortexpression = "fax" headertext = "fax"> </ASP: boundcolumn>
</Columns>
The first column is required. You can add or delete other columns as needed.
In the preceding code, When you click the header checkbox, the function of Selecting All or not selecting all display items is implemented by the Javascript checkallorno (this) function. The specific function body is as follows:

<SCRIPT>
Function checkallorno (o)
{
VaR A = O;
While (true)
{
VaR A = A. parentelement;
If (A = NULL)
{
Break;
}
If (A = "undefined ")
{
A = NULL;
Break;
}
If (A. tagname = "table ")
{
Break;
}
}
If (! = NULL)
{
For (I = 0; I <A. Rows. length; I ++)
{
For (j = 0; j <A. Rows [I]. cells [0]. Children. length; j ++)
{
VaR var1 = A. Rows [I]. cells [0]. Children [J];
If (var1.tagname = "input ")
{
If (var1.type = "checkbox ")
{
Var1.checked = O. checked;
}
}
}
}

}
}
</SCRIPT>
Set the sortcommand of dglist to dglist_sortcommand () to view the original code.

4. Set the click event of lkbdel as follows: lkbdel_click (Object sender, system. eventargs e). Of course, you can name the event name at will, and set the click events of linkbuttonpreview, linkbuttonnext, linkbuttonlast, and linkbuttonfirst to the same function (for how to set events, see msdn ). Function Name: dglist_sortcommand (Object source, system. Web. UI. webcontrols. datagridsortcommandeventargs e). For the specific implementation of each event, seeSource code, Put two variables in the class as follows:

// This is the number of records per page each time.
Private int recordperpage = 6;
// This variable is used for database connection
Protected string m_cnnstr = "Data Source = Robert \ Robert; initial catalog = northwind; user id = sa; Pwd = ";
5. Add the following code to the page_load event:

Private void page_load (Object sender, system. eventargs E)
{
If (! This. ispostback)
{
/* This. viewstate ["totalrecord"]
* Used to record the total number of records
**/
This. viewstate ["totalrecord"] = gettatolrecordnumber ();
/* This. viewstate ["totalrecord"]
* Used to record the number of records currently displayed
**/
This. viewstate ["currentpg"] = 1;
/* This. viewstate ["sort"]
* Stores the current sort string
**/
This. viewstate ["sort"] = "";
// Initialize the status of the export row.
Setguide (null );
// Displays the record for the first time.
Showdata ();
}
}
Gettatolrecordnumber () is the total number of records retrieved, setguide (null) is the status of the buttons related to pagination, showdata () is used to set the dglist control data source, for the specific implementation of these two functions, see the source code.

Aspx original code:

<% @ page Language = "C #" codebehind = "pagination. aspx. CS "autoeventwireup =" false "inherits =" sortandpagination. pagination "%>
<% @ register tagprefix =" iewc "namespace =" Microsoft. web. UI. webcontrols "assembly =" Microsoft. web. UI. webcontrols, version = 1.0.2.226, culture = neutral, publickeytoken = 31bf3856ad364e35 "%>



pagination </ title>

While (true)
{
VaR A = A. parentelement;
If (A = NULL)
{
Break;
}
If (A = "undefined ")
{
A = NULL;
Break;
}
If (A. tagname = "table ")
{
Break;
}
}
If (! = NULL)
{
For (I = 0; I <A. Rows. length; I ++)
{
For (j = 0; j <A. Rows [I]. cells [0]. Children. length; j ++)
{
VaR var1 = A. Rows [I]. cells [0]. Children [J];
If (var1.tagname = "input ")
{
If (var1.type = "checkbox ")
{
Var1.checked = O. checked;
}
}
}
}

}
}
</SCRIPT>
</Head>
<Body ms_positioning = "gridlayout">
<Form ID = "form1" method = "Post" runat = "server">
<Font face = "">
<Table cellspacing = "1" cellpadding = "1" width = "100%" border = "1">
<Tr>
<TD align = "center" colspan = "2"> <strong> <font size = "4"> about sorting, paging, example of selecting and deleting multiple records </font> </strong>
</TD>
</Tr>
<Tr>
<TD align = "right" colspan = "2"> <span onclick = "return window. Confirm ('Do you really want to delete the option? ') "> <Asp: linkbutton id =" lkbdel "runat =" server "> Delete </ASP: linkbutton> </span> </TD>
</Tr>
<Tr>
<TD colspan = "2"> <asp: DataGrid id = "dglist" runat = "server" datakeyfield = "customerid" width = "100%" allowsorting = "true"
Autogeneratecolumns = "false" pagesize = "20">
<Columns>
<Asp: templatecolumn>
<Headerstyle width = "10px"> <Headertemplate>
<Input id = "chkselectall" type = "checkbox" onclick = "checkallorno (this)"> </ASP: checkbox>
</Headertemplate>
<Itemtemplate>
<Asp: checkbox id = "chkselect" runat = "server"> </ASP: checkbox>
</Itemtemplate>
</ASP: templatecolumn>
<Asp: boundcolumn datafield = "customerid" sortexpression = "customerid" headertext = "ID"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "companyName" sortexpression = "companyName" headertext = "customer name"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "contactname" sortexpression = "contactname" headertext = "contact name"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "contacttitle" sortexpression = "contacttitle" headertext = ""> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "phone" sortexpression = "phone" headertext = "phone"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "fax" sortexpression = "fax" headertext = "fax"> </ASP: boundcolumn>
</Columns>
</ASP: DataGrid> </TD>
</Tr>
<Tr>
<TD align = "right" colspan = "2">
<Table id = "Table1" cellspacing = "1" cellpadding = "1" border = "0">
<Tr>
<TD nowrap> <asp: linkbutton id = "linkbuttonfirst" runat = "server"> homepage </ASP: linkbutton> </TD>
<TD> | </TD>
<TD nowrap> <asp: linkbutton id = "linkbuttonpreview" runat = "server"> previous page </ASP: linkbutton> </TD>
<TD> | </TD>
<TD nowrap> <asp: linkbutton id = "linkbuttonnext" runat = "server"> next page </ASP: linkbutton> </TD>
<TD> | </TD>
<TD nowrap> <asp: linkbutton id = "linkbuttonlast" runat = "server"> last page </ASP: linkbutton> </TD>
</Tr>
</Table>
</TD>
</Tr>
</Table>
</Font>
</Form>
</Body>
</Html>
Aspx. CS original code:

Using system;
Using system. collections;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. Web;
Using system. Web. sessionstate;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. htmlcontrols;

Namespace sortandpagination
{
/// <Summary>
/// Examples of sorting, paging, and selection and deletion of multiple records
/// </Summary>
Public class pagination: system. Web. UI. Page
{
Protected system. Web. UI. webcontrols. DataGrid dglist;
Protected system. Web. UI. webcontrols. linkbutton lkbdel;
Protected system. Web. UI. webcontrols. linkbutton linkbuttonpreview;
Protected system. Web. UI. webcontrols. linkbutton linkbuttonnext;
Protected system. Web. UI. webcontrols. linkbutton linkbuttonlast;
Protected system. Web. UI. webcontrols. linkbutton linkbuttonfirst;

// This is the number of records per page each time.
Private int recordperpage = 6;
// This variable is used for database connection
Protected string m_cnnstr = "Data Source = devserver-1; initial catalog = northwind; user id = sa; Pwd = ";

Private void page_load (Object sender, system. eventargs E)
{
If (! This. ispostback)
{
/* This. viewstate ["totalrecord"]
* Used to record the total number of records
**/
This. viewstate ["totalrecord"] = gettatolrecordnumber ();
/* This. viewstate ["totalrecord"]
* Used to record the number of records currently displayed
**/
This. viewstate ["currentpg"] = 1;
/* This. viewstate ["sort"]
* Stores the current sort string
**/
This. viewstate ["sort"] = "";

// Initialize the status of the export row.
Setguide (null );
// Displays the record for the first time.
Showdata ();
}
}

# Code generated by region web Form Designer
Override protected void oninit (eventargs E)
{
//
// Codegen: This call is required by the ASP. NET web form designer.
//
Initializecomponent ();
Base. oninit (E );
}

/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. lkbdel. Click + = new system. eventhandler (this. lkbdel_click );
This. dglist. sortcommand + = new system. Web. UI. webcontrols. datagridsortcommandeventhandler (this. dglist_sortcommand );
This. linkbuttonfirst. Click + = new system. eventhandler (this. guid_click );
This. linkbuttonpreview. Click + = new system. eventhandler (this. guid_click );
This. linkbuttonnext. Click + = new system. eventhandler (this. guid_click );
This. linkbuttonlast. Click + = new system. eventhandler (this. guid_click );
This. Load + = new system. eventhandler (this. page_load );

}
# Endregion

Private void showdata ()
{
String order = This. viewstate ["sort"]. tostring ();
// Construct the SQL sorting string
If (order! = "")
{
Order = "order by" + order + "";
}
Int startnum = (INT) This. viewstate ["currentpg"] * recordperpage;
Int inttemnum = startnum-recordperpage;
String l_ SQL = "select * from (select top" + startnum. tostring ()
+ "* From MERs" + order + ")"
+ "Where a. customerid not in (select top" + inttemnum. tostring ()
+ "Customerid from customers" + order + ")";

System. Data. sqlclient. sqlconnection
L_cnn = new system. Data. sqlclient. sqlconnection (this. m_cnnstr );
System. Data. sqlclient. sqldataadapter
L_apd = new system. Data. sqlclient. sqldataadapter (l_ SQL, l_cnn );
System. Data. datatable l_dt = new datatable ("TEM ");
Rochelle apd.fill (Rochelle DT );

This. dglist. datasource = l_dt;
This. dglist. databind ();

}

/// <Summary>
/// This is used to obtain the total number of records in the database.
/// </Summary>
/// <Returns> Number of returned records </returns>
Private int gettatolrecordnumber ()
{
System. Data. sqlclient. sqlconnection
L_cnn = new system. Data. sqlclient. sqlconnection (this. m_cnnstr );
Rochelle cnn.open ();
System. Data. sqlclient. sqlcommand
Rochelle scd = maid ();
Rochelle scd.commandtext = "select count (*) from MERs ";
System. Data. sqlclient. sqldatareader
L_sdr = l_scd.executereader ();
L_sdr.read ();
// Retrieve the total number of records
Int l_count = l_sdr.getint32 (0 );
Rochelle cnn.close ();
Return l_count;
}

Private void dglist_sortcommand (Object source, system. Web. UI. webcontrols. datagridsortcommandeventargs E)
{
String l_currentsort = E. sortexpression;
// Record the sorting string
This. viewstate ["sort"] = l_currentsort;
Foreach (system. Web. UI. webcontrols. datagridcolumn l_dgc in this. dglist. columns)
{
/* Determine which column to sort
So that the second click is in another order.
**/
If (l_dgc.sortexpression = l_currentsort)
{
L_currentsort = l_currentsort.trim ();
// Here, the field is simply separated from the sorting keyword. You can use a regular expression to make it better.
String [] l_arr_str = l_currentsort.split (New char [] {''});
If (l_arr_str.length = 1) // if there is no sort keyword, it is thought to be in ascending order. Next time, it should be changed to descending order.
{
L_dgc.sortexpression = l_arr_str [0] + "DESC ";
}
Else
{
If (l_arr_str [l_arr_str.Length-1]. tolower () = "ASC") // if it is in ascending order, change to descending order
{
L_dgc.sortexpression = l_arr_str [0] + "DESC ";
}
Else // if it is in descending order, it is changed to ascending order
{
L_dgc.sortexpression = l_arr_str [0] + "ASC ";
}
}
Break; // Changes the sorting Header
}
}
// Retrieve records from the database again and display them in order.
Try
{
Showdata ();
}
Catch (exception ex)
{
This. response. Write ("<font color = Red>" + ex. Message + "</font> ");
}
}

Private void guid_click (Object sender, system. eventargs E)
{
Try
{
Setguide (sender );
Showdata ();
}
Catch (exception ex)
{
This. response. Write ("<font color = Red>" + ex. Message + "</font> ");
}
}

/// <Summary>
/// This is used to display the status of each element in the export row bar.
/// </Summary>
/// <Param name = "sender"> indicates the button of the line element that is pressed. If it is not triggered by the line bar, a null value is passed. </param>
Private void setguide (Object sender)
{
Int m_itotalpage = convert. toint32 (system. math. ceiling (convert. todouble (INT) This. viewstate ["totalrecord"])/convert. todouble (this. recordperpage )));
If (m_itotalpage <(INT) This. viewstate ["currentpg"])
This. viewstate ["currentpg"] = 1;
If (sender = This. linkbuttonfirst)
{
This. viewstate ["currentpg"] = 1;
}
If (sender = This. linkbuttonnext)
{
This. viewstate ["currentpg"] = (INT) This. viewstate ["currentpg"] + 1;
}
If (sender = This. linkbuttonpreview)
{
This. viewstate ["currentpg"] = (INT) This. viewstate ["currentpg"]-1;
}
If (sender = This. linkbuttonlast)
{
This. viewstate ["currentpg"] = m_itotalpage;
}
This. linkbuttonfirst. Enabled =! (INT) This. viewstate ["currentpg"] = 1 );
This. linkbuttonpreview. Enabled =! (INT) This. viewstate ["currentpg"] = 1 );
This. linkbuttonlast. Enabled =! (INT) This. viewstate ["currentpg"] = m_itotalpage );
This. linkbuttonnext. Enabled =! (INT) This. viewstate ["currentpg"] = m_itotalpage );
}

Private void lkbdel_click (Object sender, system. eventargs E)
{
/*
* The following table lists the records to be deleted.
* Because customerid is of the char type,
* Use the following format to collect the string so that you can use it when creating a delete statement.
* "'Ddfg', 'ccfe ', 'fddd '"
**/
String l_str_id = "";
Foreach (system. Web. UI. webcontrols. datagriditem l_dgl in this. dglist. Items)
{
// Chkselect is the ID of each record. Remember, this is the server ID, not the client ID.
System. Web. UI. Control l_ct = l_dgl.cells [0]. findcontrol ("chkselect ");
If (l_ct! = NULL)
{
// The following statement determines whether to record items in the checkbox box primary selection
If (system. Web. UI. webcontrols. checkbox) l_ct). Checked)
{
If (l_str_id = "")
Rochelle str_id = "'" + this. dglist. datakeys [l_dgl.itemindex]. tostring () + "'";
Else
Rochelle str_id + = ", '" + this. dglist. datakeys [l_dgl.itemindex]. tostring () + "'";
}
}
}
/*
* The following statement deletes the selected ID. "customerid" is the keyword of customerid.
**/
Try
{
If (l_str_id! = "")
{
System. Data. sqlclient. sqlconnection
L_cnn = new system. Data. sqlclient. sqlconnection (this. m_cnnstr );
Rochelle cnn.open ();
System. Data. sqlclient. sqlcommand
Rochelle scd = maid ();
Rochelle scd.commandtext = "delete from MERs where customerid in ("
+ L_str_id + ")";
Rochelle scd.executenonquery ();
}
/*
* After deleting a record, you need to recalculate the number of records.
* And re-display records.
**/
This. viewstate ["totalrecord"] = gettatolrecordnumber ();
Setguide (null );
Showdata ();
}
Catch (exception ex) // This is an error reported after an error occurs
{
This. response. Write ("<font color = Red>" + ex. Message + "</font> ");
}
}
}
}