| In a previous chapter, you saw how to use the user-interface data components to bind a grid to a data source. You created OleDbDataAdapter and OleDbConnection objects and then used the menus to have the OleDbAdapter generate a typed DataSet for you. Although you can use these steps, you might want to bypass the overhead of the typed DataSet and bind the DataGrid to a simple DataSet that you create in code. As a matter of fact, you can bind a DataGrid to at least four different data objects: DataSet DataTable DataView DataReader The only one of these that you haven't encountered yet is the DataView object. This object "wraps" itself around a DataTable and provides a sortable, filterable view of the data in the DataTable. When you want to work with a DataView, you have two choices. You can retrieve the DefaultView property of a DataTable (every DataTable provides a single default DataView object), like this: Dim dv As DataView ' Assuming that ds is a DataSet containing at ' least one table dv = ds.Tables(0).DefaultView If you want to create a new DataView object, you can write code like this: Dim dv As DataView ' Assuming that ds is a DataSet containing at ' least one table dv = New DataView(ds.Tables(0)) If you want to sort the data in your DataGrid, you'll need to interact with a DataView object that's the object that allows you to sort the data in the DataTable that's providing the data to the DataGrid. To get started, create the page shown in Figure 16.1 by following these steps: -
Select Project, Add Web Form. -
Set the name of this form to CategorySales.aspx. -
Add controls and set properties as shown in Table 16.2. Table 16.2. Add These Controls (and Properties) to Your CategorySales.aspx Page | Control Type | Property | Value | | Label | ID | Label1 | | | Text | Sales By Category | | | Font.Bold | True | | | Font.Size | Large | | DataGrid | ID | grdCatSales | | | AllowPaging | True | | | AllowSorting | True | | | AutoGenerateColumns | False | | Label | ID | lblCategory | | | Text | (Delete the text in the property value.) | | Hyperlink | ID | hypHome | | | Text | Home | | | NavigateURL | Main.aspx | Three of these properties bear some discussion: AllowPaging (set to True) turns on paging for the DataGrid control and, by default, adds paging indicators in the control's footer. Paging requires adding some code, however, so you'll still need to write some code to make the paging happen. AllowSorting (set to True) allows you to sort the data in the grid by clicking column headers. Turning this on adds links to the column headers, but you still have to write the code yourself to sort the data filling the grid. It's important to note that in order to sort the data displayed, you don't sort the grid; instead, you sort the data filling the grid and then rebind the grid to its data source. AutoGenerateColumns (set to False) turns off the DataGrid control's feature that retrieves the schema for your data from its data source. In this example, you'll want to explicitly control this behavior, so you're turning this feature off. TIP Throughout this chapter, you'll use tools provided by Visual Studio .NET to alter the layout and formatting of your grid. Under the covers, all these tools are doing for you is adding HTML to the page designer. At any point, you can switch to HTML view and check out what the design-time tools have done for you. There's no magic here you could accomplish all the same goals yourself, simply by typing into the HTML editor.
Formatting the DataGrid Control On its own, the layout of the DataGrid control is awfully drab. You can easily format the control, using predefined layouts, by taking advantage of the Auto Format option on the control's context menu. To set the formatting for your DataGrid control, follow these steps: -
Right-click the DataGrid control. -
Select Auto Format from the context menu. -
From the Auto Format dialog box, select a format that you like (see Figure 16.2). Figure 16.2. Select from the supplied formats or set properties individually to provide your own.
-
Click OK to apply the format to your DataGrid control. Once you've finished, your page should look something like Figure 16.3. Figure 16.3. Your layout should look something like this.
TIP If you check out properties for the DataGrid control in the Properties window, you'll notice that using Auto Format set a number of properties for you. You can always override the formatting you've just applied, changing any individual property value. You'll also find tools to change individual formatting settings in the Property Builder, which you'll see in the next section.
Creating the Columns You'll need to add columns to your grid, of course, to show data from the DataGrid control's data source. You've seen, in an earlier chapter, how to bind the DataGrid control to a typed DataSet. In this chapter, you'll do the work manually, and you'll work through the steps of adding all the functionality that's required. To get started adding columns, follow these steps: -
Right-click the DataGrid control and select Property Builder from the context menu. This dialog box enables you to modify the behavior of the DataGrid control. -
On the first page, as shown in Figure 16.4, make sure that the Show Header and Allow Sorting options are selected. Figure 16.4. The grdCatSales Properties dialog box allows you to modify the behavior of the DataGrid control without having to write code.
-
Select the Columns page in the left pane of the dialog box. -
In the Available Columns list, expand the Button Column node. Click the Select item and then click the > button to add the Select column to the DataGrid control. When you're done, the page should look like Figure 16.5. (You can alter most of the ButtonColumn properties on this page, if you want to experiment, but you should not change the Command Name property code later on in the chapter expects this name to be Select.) Figure 16.5. Add the Select column to your grid.
Next, you'll add the three bound columns. -
In the Available Columns list, select Bound Column. Click the > button three times to add three bound columns to the grid. -
In turn, select each of the three new Bound Column items in the Selected Columns list and modify properties as shown in Table 16.3. When you're done, the dialog box should look like Figure 16.6. Figure 16.6. After adding the four columns, you're ready to bind the data.
Table 16.3. Add These Properties for the Three Bound Columns | Bound Column Number | Property | Value | | 1 | Header Text | Category ID | | | Sort Expression | CategoryID | | | Data Field | CategoryID | | 2 | Header Text | Product Name | | | Sort Expression | ProductName | | | Data Field | ProductName | | 3 | Header Text | Product Sales | | | Sort Expression | ProductSales | | | Data Field | ProductSales | -
Click OK when you're done to close the dialog box and apply your changes. -
Note that the DataGrid control now displays the Select column as well as three DataBound columns with the correct headings. Although you only used two column types, in the Available Columns list, you'll find four different types of columns, as described in Table 16.4. Table 16.4. You Can Add Four Different Types of Columns to the DataGrid Control | Column Type | Description | | Bound | Each of these columns is bound to the data source filling the DataGrid control. | | Button | Button columns can look like normal buttons or like hyperlinks. When you use one of the button types listed here, the DataGrid control will raise an event (and you can add code to handle this event) on which type of button you've clicked. The Select type raises the SelectCommand event. The Edit, Update, and Cancel types raise the EditCommand, UpdateCommand, and CancelCommand events, respectively. Finally, the Delete type raises the DeleteCommand event. | | Hyperlink | Creates a hyperlink column. It is up to you to fill in the NavigateURL property to indicate to where you wish to navigate when the user clicks this hyperlink. | | Template | Allows you to supply your own HTML template that controls the behavior for this column. You could, for example, display each item in the column as a DropDownList control. Using a Template column requires modifying HTML by hand, and you'll see more of this technique in the chapter describing the Repeater control. | Hooking Up the Data In order to display data in your control, you'll need to write the code that retrieves the data and binds the DataGrid control to that data. Modifying the Page_Load Procedure The Page_Load procedure runs every time the page loads both the first time and on subsequent postbacks to the page. As you've seen previously, you can use the IsPostback property of the page to determine whether the page is in a postback situation and then take different actions if so. In this case, you only need to load the DataGrid control with data on the first "pass." After that, requests for data can retrieve the DataSet from a Session variable. TIP Although the optimization of storing the DataSet into a Session variable isn't required and perhaps isn't even always desired in some cases it works well. If your data is relatively static and is unlikely to change while you're working with the page, storing the DataSet in a Session variable can save on network traffic and on roundtrips to the database server. For this particular example, it's debatable whether storing the DataSet is ideal. However, it does show off the possibilities and how to use this technique.
To modify the Page_Load procedure, follow these steps: -
Double-click your page, on the page itself (not on any control). This loads the code editor with the Page_Load procedure ready to edit. -
Modify the procedure so that it looks like this: Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If Not Page.IsPostBack Then CategoryLoad() GridLoad() End If End Sub This procedure requires the CategoryLoad and GridLoad procedures, and you'll add those in the following sections. Loading the Category Data The CategoryLoad procedure does the work of loading the data from the data source and stores the data in a Session variable. (Storing the DataSet means that you don't have to go back to the database server to retrieve it each time you post back to the page.) Beneath the Page_Load procedure, add the following procedure: Private Sub CategoryLoad() Dim strSQL As String Dim strConn As String ' Set up SQL and Connection strings. strSQL = "SELECT * FROM [Sales By Category]" strConn = Session("ConnectString").ToString ' Create DataSet and ' store DataSet into Session variable Session("DS") = DataHandler.GetDataSet(strSQL, strConn) End Sub This procedure uses the Sales By Category view provided by SQL Server to retrieve summary information about sales, by category. Once the code has set up the SQL and connection strings, it calls the GetDataSet method of the DataHandler class (you've seen this method and this class in previous chapters). Then, this code is to place the DataSet object in a Session variable: Session("DS") = DataHandler.GetDataSet(strSQL, strConn) TIP A Session variable can be any object, referenced by a name ("DS", in this case). Here, you've placed a DataSet object in the Session state and managed by the ASP.NET page framework. You can retrieve the DataSet object later in your code, but because the Session variable comes back to you as an object, you'll need to cast it back to being a DataSet using the CType function.
Loading the DataGrid Control In order to display data in the DataGrid control, you must take three steps: -
Retrieve the data. -
Set the control's DataSource property. -
Call the control's DataBind method. The GridLoad procedure handles all three of these tasks. Add the following procedure immediately below the CategoryLoad procedure: Private Sub GridLoad() Dim dv As DataView ' Get default view from stored DataSet dv = CType(Session("DS"), DataSet). _ Tables(0).DefaultView ' Fill in DataSource and bind to data. grdCatSales.DataSource = dv grdCatSales.DataBind() End Sub Before we begin digging into this procedure, it's important to remember that you can bind a DataGrid control to a number of different types of objects. In this example, the code binds the DataGrid control to a DataView object this may seem like overkill, because you could just as easily bind the control to the DataSet object you placed into a Session variable earlier. Because you're going to add sorting capabilities later, you'll need to bind the DataGrid control to a DataView object. GridLoad first retrieves the Session variable, DS, and converts it back to a DataSet object. Then, the code retrieves the DefaultView property of the first table within the DataSet object: dv = CType(Session("DS"), DataSet). _ Tables(0).DefaultView Then, as promised, the code sets the DataSource property and calls the DataBind method of the DataGrid control: grdCatSales.DataSource = dv grdCatSales.DataBind()  | If you've followed the steps carefully up to now, you should be able to test out your page: |
-
In the Solution Explorer, select CategorySales.aspx. -
Right-click and select Build and Browse from the context menu. You should see the grid, displaying formatted rows of data from the SQL Server view. (Close the browser window when you're done.) The remainder of the chapter focuses on adding functionality to the DataGrid control, including column formatting, paging, and sorting. |
