Create a pageable, sorted DataGrid
Introduction
One of the biggest use of Microsoft® ASP.NET is to easily display data in a web page. ASP.NET contains three data web controls (DataGrid, DataList, and Repeater), each control is designed for extensive data. In these three data web controls, the most commonly used DataGrid, mainly due to its convenient built-in function set. Specifically, as long as you set a few attributes and create two event handles, DataGrid can provide sorting, paging, or editing support. For the difference between the three data web controls, and when to choose which control details, be sure to read the Deciding When to Use the DataGrid, DataList or Repeater.
Although the DataGrid that creates can be sorted or pageable is very simple, it is more difficult to create DataGrid that can be sorted and sorted. In this article, let's take a look at how to create the steps necessary for sorting and pageable DataGrid and then discuss the two functions to a DataGrid. The first two contents will explore how to add sorting features and paging features to the DataGrid Web control; if you have been proficient in this area, please skip to the last section, this section will explore how both functions are bonded to a DataGrid.
Back to top
Create sorted DataGrid
Before you start creating a silenced DataGrid, you first need a DataGrid that only displays some data. In order to achieve the purpose of this article, let's create a simple DataGrid that makes it display the contents of the Products table in the Northwind database.
Note The Northwind database is a standard database that came with Microsoft database products such as Microsoft SQL Server (TM), and Microsoft Access.
Next, create a new ASP.NET web page called SortableDataGrid.aspx, then start adding a DataGrid and sets its ID attribute to DGProducts. Next, set the AutoGenerateColumns property to false and configure the DataGrid to use three BoundColumn to display the ProductName, Unitprice, and UnitsStock fields. The remaining unique operation is to query the database and bind the query result with the DataGrid. The following code shows the Page Load event handler and a custom method BindData (), in order to make everything is normal, these code should be added to the code hidden class.
Private Void Page_Load (Object Sender, System.EventArgs E)
{
Binddata ();
}
Private void binddata ()
{
// connect to the database
SqlConnection MyConnection = New SqlConnection
Connection String
);
// Retrieve the SQL Query Results and Bind It to the Repeater
String SQL_QUERY = "SELECT ProductName, Unitprice, UnitsInstock"
"From products";
Sqlcommand mycommand = new sqlcommand (sql_query, myconnection); myconnection.open ();
DGProducts.DataSource = mycommand.executeReader ();
DGProducts.DATABIND ();
MyConnection.Close ();
}
Please note that in order to make these code work normally, you need to import system.data.sqlclient namespace in the code hidden class, but also change the connection string in the SQLConnection constructor to your database connection string. After entering this code, build a solution, and then access the web page via a web browser to test it. Figure 1 shows the screen snapshot of SortableDataGrid.aspx (of course, you can use the "Auto Set Format" tool in Microsoft Visual Studio .NET, making the DataGrid look more attractive.)
Figure 1. SortableDataGrid.aspx viewed by the browser
To make the DataGrid in SortableDataGrid.aspx can be sorted, first set the DataGrid's allowsort property to true. When this property is set to True, the DataGrid will present the title of each column in lineButton, which is the title of the title of each column as a hyperlink. End users simply click on the hyperlink of the corresponding column header, you can specify which column to be sorted to the DataGrid results.
When you click the hyperlink of the column, the ASP.NET web page will be raised, and the DataGrid's SortCommand event will be stimulated. As a developer of the ASP.NET web page, we are responsible for creating an event handler for DataGrid's SortCommand events and connect DataGrid's SortCommand events with this created event handler. The event handler is responsible for determining which column of end users should sort the data, then reordbound the underlying data, and rebound it with the DataGrid.
Before writing the code of the SortCommand event handler, we must first add this event handler. Tools such as Visual Studio .NET will make this step greatly simplify. Simply click DGProducts DataGrid in the Designer to edit its properties. Next, click the "Lightning" icon at the top of the "Properties" pane to view the DataGrid event. Scroll to the SortCommand event, then enter the name of the event handler to be associated with the event, Visual Studio .NET will complete the rest! Figure 2 shows the list of events in the Properties pane.
Figure 2. Creating an event handler for the SortCommand event for DataGrid
As shown in Figure 2, I have decided to name this event handler DGProducts_Sort, as long as any legal function name is sufficient. Once the name of an event handler is entered in the appropriate event text box, Visual Studio .NET will automatically create the function enclosure of the event handler in the code hidden class, and link the event with the event handler.
If you are not using the Visual Studio .NET, you will need to manually perform the following two steps. First create the enclosure of the event handler, as shown below:
Private void dgproducts _sort (Object Source,
DataGridsortCommandEventArgs E)
{
// We'll Add the code here in a bit!}
Next, the DataGrid's SortCommand event is closely linked to the event handler, which can be done by one of the following two methods:
• Add OnSortCommand = "DGProducts_Sort" in DataGrid declaration (ie, add it to
If you are using Microsoft Visual Studio? Net, the syntax is as follows:
AddHandler DGProducts.SortCommand, Addressof DGProducts_Sort
After creating the event handler and contacts the DataGrid's SortCommand event, you can start adding the code for the event handler. In essence, we need to determine which column of end users should sort the results and re-query the database to retrieve the results in order required. By checking the SortExpression property of the DataGridSortCommandEventArgs parameter in the DGProducts_Sort event handler, you can determine the columns that cause the return after the end user clicks.
Note that each DataGrid column has a sortexpression value associated with it. When the DataGrid's AutoGeneateColumns property is set to TRUE, a SortExpression value is automatically assigned to each column, which is equal to the name of the DataSource field displayed by the column. When AutoGenerateColumns is set to False, the value of the sortexpression property must be explicitly specified. If there is no SortExpression property for a particular column, the title of the column will not be displayed as a hyperlink; therefore, end users will not be sorted by this particular column. To prove this, just set the sortexpression attribute of the first two boundcolumn columns. Although the SortExpression property can be set to any value, but set the first two BoundColumn's sortexpression to the same value as the DataField property. In this way, the DataGrid declaration should be as follows:
AutogenerateColumn = "false" ...> Headertext = "Product Name" Sortexpression = "Productname" > Sortexpression = "unitprice" > Headertext = "Units in stock" dataformatstring = "{0: D}"> Now, in the DGProducts_Sort event handler, we need to determine the value of the sortexpression, then restore the data to the DataGrid in the correct sort order. There are many ways to achieve this, the simplest is to re-query all the data sorted by the field specified by sortexpression. This is not necessarily the most efficient method, but it is a method of accepting only 77 recorded Products tables. To make the above reinquerry, you can modify the binddata () method to accept string parameters, that is, the column name as the result sort. This update version of BindData () is displayed below: Private void binddata String ORDERBY ) { // connect to the database SqlConnection MyConnection = New SqlConnection Connection String ); // Retrieve The SQL Query Results and Bind It To The DataGrid String SQL_QUERY = "SELECT ProductName, Unitprice, UnitsInstock" "From products order by" ORDERBY ; Sqlcommand mycommand = new sqlcommand (sql_query, myconnection); MyConnection.open (); DGProducts.DataSource = mycommand.executeReader (); DGProducts.DATABIND (); MyConnection.Close (); } With New Version of Binddata (), Ourdgproducts_Sort Event Handler Requires Only Line of Code, Which Can Be Seen Below: Private void DGProducts_sort (Object Source, System.Web.ui.WebControls.DataGridsortCommandEventArgs E) { BindData (E.Sortexpression); } The remaining unique work is to update the Page Load event handler. First, when the page does not return, simply call the BindData () method, this is due to the later finale, the DGProducts_Sort event handler will make BindData () calls. Second, the Page Load event handler uses the old version of the BindData () method, which does not use any input parameters - we need to update it so that it is the field name to be sorted by DataGrid. Private Void Page_Load (Object Sender, System.EventArgs E) { IF (! page.ispostback); Binddata ("ProductName"); } After all of these changes are performed, the solution is constructed and test it. Figure 3 shows the SortableDataGrid.aspx at first accesses, and Figure 4 shows the web page after the user clicks the "Unit Price" header superlink. Note that in these two figures, DataGrid cannot be sorted by "Units In stock" column, because we did not provide this BoundColumn's sortexpression attribute. Figure 3. DataGrid sorted by "Product Name" column Figure 4. DataGrid sorted by "Unit Price" column Back to top Create a pageable DataGrid Like creating can be sorted, you want to create a pageable DataGrid, it is best to create a DataGrid that only displays data. Since we have completed this in the first part of the previous section, I don't want to make too many discussions here, will only mention the ASP.NET web page that is named PageableDataGrid.aspx, and from We begin to discuss the SortableDataGrid.aspx web page before adding the sort function, copy the DataGrid declaration in the HTML section, and the code of the Page_Load and BindData () methods in the code hidden class. Although the Page Load event handler can be replicated one byword, it is necessary to make a little change to the binddata () method. Specifically, please do not bind SqlDataReader to DataGrid, you need to use DataTable or DataSet. We will discuss why you have to do this. Before adding paging support to DataGrid, you must realize that DataGrid has two forms of paging support: • Default paging • Custom paging These paging models have their own advantageous: The default pagination ratio is easy to implement, but custom paging provides better performance. The above advantages and disadvantages are because if you use the default paging, when each user navigates to another page each time, you always have to bind the entire DataSource that needs paging to DataGrid. When using the default paging, the DataGrid itself is responsible for determining how many records in DataSource and what records need to display DataSource. Instead, the custom paging requires only the DataSource to be bound to the DataGrid only when the records are displayed in the current data source. The default paging is easy to implement because when the user navigates from one page data to the next page, you don't have to think about it at all. That is to say, you only need to bind the results of the SQL query to the DataGrid, let DataGrid determine which records you want to display. When using custom paging, you must use a smart SQL statement or complex stored procedure to select an accurate recordset to display on a specific page. Compared to the default paging, custom paging provides better performance, because it only accesss records that need to be displayed on a specific data page. When using the default paging, each time the user views another page of data, all records will be retrieved. In addition, the default paging requires you to bind DataTable or DataSet objects to DataGrid; that is, you can't use DataReader because DataGrid needs to determine how many records in DataSource can determine how many page data exists. In this article, we focus on using the default paging, because it is easier to implement. However, there is no loss on the default paging only, because we will discuss technology used to create sorting, pageable DataGrid in the next section, and this technology is the effect of default paging and custom paging As good. The first step in creating a paged DataGrid using the default paging model is to set the data of the DataGrid to True. When this property is set to True, it will display an navigation interface at the bottom of the DataGrid by default. The navigation interface allows the end user to gradually read the various data pages displayed in the DataGrid. By default, the navigation interface uses LinkButtons to present the previous group and the next set of hyperlinks. When the end user clicks a hyperlink, the ASP.NET web page will be raised, and the DataGrid's pageIndexchanged event will be excited. We are responsible for creating an event handler for this event to update the DataGrid's CurrentPageIndex property and resin the data to the DataGrid. The CurrentPageIndEx property of DataGrid indicates the data page to display. In addition to this attribute, there are some other attributes that are closely related to paging: • PageSize - This property indicates how many records are displayed on each page, and the default is 10. • PageCount - This property indicates the total number of data pages. Therefore, the PageIndexchanged event handler simply sets the currentPageIndex property to the user selected by the user, then resin DataGrid (by calling binddata ()), it is as simple as it! You can confirm the page index to view by reference to the NewPageIndex property of the DataGridPageChangeDeventArgs object that is passed to the event handler. When calling the DataGrid DataBind () method in bindData (), DataGrid will use the CurrentPageIndex property to determine the data page to display. Next, the DataGrid will use which records to be calculated using the number of records to be displayed on each page should be displayed as the first record of the page. Then, the DataGrid will navigate to the record and display the record and the next Pagesize record. Let us take a time to do the following: Use the methods discussed in the previous section to add an event handler named DGProducts Page to the DataGrid's PageIndexchanged event. The source code for this event handler is as follows: Private void DGProducts_page (Object Source, System.Web.ui.WebControls.DataGridPageChangeDeventargs E) { DGPRoducts.currentPageIndex = E.NewpageIndex; Binddata (); } Similarly, as in the SortableDataGrid.aspx web page, we just want the Page Load Event Handle to bind the data to the DataGrid while loading the first page, while the binding is not binding in the future faster. The following code shows the updated Page Load Event Handle and BindData () method. Note that binddata () methods bind DataTable to DataGrid instead of SqlDataReader. Recall that when you use the default paging, you can't use DataReader, but you must use DataTable or DataSet; if you use DataReader, you will result in an exception. Can't use DataReader because DataGrid must be able to determine how many records in DataSource can determine how many pages have been made. Because DataReader supports only access, DataGrid cannot determine how many records have been included. Therefore, the default paging must use objects that determine their records, such as DataTable or DataSet. Private Void Page_Load (Object Sender, System.EventArgs E) { IF (! page.ispostback) Binddata (); } Private void binddata () { // connect to the database SqlConnection MyConnection = New SqlConnection Connection String ); // Retrieve The SQL Query Results and Bind It To The DataGrid String SQL_QUERY = "SELECT ProductName, Unitprice, UnitsInstock" "From products"; Sqlcommand mycommand = new sqlcommand (sql_query, myconnection); // use a datatable? € _ Required for default paging Sqldataadapter myadapter = new sqldataadapter (MyCommand); DataTable myTable = new dataable (); myadapter.fill (MyTable); DGProducts.DataSource = MyTable; DGProducts.DATABIND (); MyConnection.Close (); } Figure 5 shows the first visit when the first visit is sent. It shows the top ten records of the Products table. Figure 6 shows the second page of data, which is displayed after the user is displayed on the navigation interface in Figure 5. Figure 5. Display the first page data. Figure 6. Display the second page of data. Create sorting, pageable DataGrid So far, we have seen how to create a sorted DataGrid and a pageable DataGrid. Now, we turn the attention to a combination of these two functions to a can be sorted, can be siberous DATAGRID. The difficulty combining these two functions into a DataGrid is that each method uses different forms of binddata () methods. Sorted DataGrid incomes a string parameter into binddata () to indicate which of the columns to be sorted as sorting basis. On the contrary, the pageable DataGrid is not doing so. The first attempt has made this difference aglyly, use the BindData () version of the string parameter and let the DataGrid's PageIndexchanged event handler is always incoming some predefined string values. For example, because the PageIndexchanged event handler must call binddata () and incorporate the string, you can use the following code for the event handler: Private void DGProducts_page (Object Source, System.Web.ui.WebControls.DataGridPageChangeDeventargs E) { DGPRoducts.currentPageIndex = E.NewpageIndex; Binddata ("ProductName"); } However, this method does not work. Consider the following series of events: • Users accessed the page, which displays the first page of the PRODUCTNAME field. • The user clicks the "Unit Price" Title Super Link, which will display the first page of the "Unit Price" sorted. • User Click> Navigation hyperlink, which will request the next page of data, resulting in a PageIndexChanged event handler to retrieve the data sorted by ProductName. Therefore, the user now sees the second page sorted by ProductName instead of the second page sorted by Unitprice. When you call BindData () from the PageIndexchanged event handler, we need some way to remember the fields of sorting. When a server to and from the use of ASP.NET, you can save the status through many ways; however, because we only need to save this information for the current web page, do not need to be saved for the current user, so the best way is in the web. The user wants to store the specified basis in the ViewState page. In-depth discussion ViewState has far exceeded the scope of this article. However, we can spend a little time, discussing it slightly it is used in the use of can be sorted, pageable DataGrid. First, you have to know all the controls in the ASP.NET web page have a ViewState, and in the WEB form (
