Working with the DataGrid

for RuBoard

Working with the `DataGrid`

The DataGrid Web control displays data in a grid, where each record is displayed horizontally. To see a DataGrid in action, see Figure 11.2 at the end of this section. The DataGrid generates an HTML table with the columns of data matching the columns of data returned from the data source.

Figure 11.2. The appearance of the `DataGrid` control from Listing 11.3.

graphics/11fig02.jpg

You've seen the DataGrid used quite a bit in this book thus far mainly because the DataGrid can automatically generate columns without configuration. This makes it invaluable when you'd like to display data quickly on a Web form for debug purposes or to display a database resultset without obscuring the data access code.

Because the DataGrid requires only a simple tag to display a potentially complicated DataTable , it's perfect for quick examples such as Listing 11.3. The example in Listing 11.3 returns a list of orders from the Northwind database and displays them on the page, as you can see in Figure 11.2.

Listing 11.3 A Simple `DataGrid` Example

 <% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)             LoadDataGrid(orders)         End Sub         Private Sub LoadDataGrid( _                          myDataGrid as System.Web.UI.WebControls.DataGrid)            Dim conn as New SqlConnection("Initial Catalog=Northwind;" + _                                        "Server=(local);UID=sa;PWD=;")            Dim cmd as New SqlCommand("SELECT OrderID, " + _                                      "CustomerID, " + _                                      "OrderDate, " + _                                      "ShipName " + _                                      "FROM Orders", conn)            conn.Open()            myDataGrid.DataSource = cmd.ExecuteReader()            myDataGrid.DataBind()            conn.Close()         End Sub     </script> </HEAD> <BODY> <h1>A Simple DataGrid Example</h1> <hr> <form runat="server" id=form1 name=form1>    <asp:DataGrid id="orders" runat="server"></asp:DataGrid> </form> <hr> </BODY> </HTML>

If you've been following along with the examples in this book thus far, Listing 11.3 should seem very familiar. The Page_Load() method in lines 11 “15 passes the name of the DataGrid to the LoadDataGrid() method. The LoadDataGrid() method in lines 17 “33 builds a SQL query, retrieves some orders from the Northwind database, and then binds them to the DataGrid Web control created on line 43, as you've seen many times before.

Though automatically generating columns is good for debugging/demonstration purposes, it is rarely useful for displaying data to a user in an application. Luckily, the DataGrid has several options for formatting the appearance of individual columns.

You can define the appearance of individual columns in a DataGrid by using the BoundColumn control. You can see an example of this in Listing 11.4. Note that most of the code is the same as the example in Listing 11.3. However, the DataGrid tag on the Web form itself has a new <columns> section. Within that section are several BoundColumn controls. Intuitively enough, the HeaderText refers to the text displayed at the top of that column. The DataField property refers to the name of the database field being bound to that column.

A DataFormatString property can be specified for the column as well. This enables you to format the appearance of the column data itself. The format codes here are the same ones used by the String.Format() method. A complete listing of these codes can be found in the Microsoft .NET Framework documentation. However, some very common strings are "{0:d} " , which formats the string as a date, and "{0:c} " , which formats the data as currency (using the user's localized monetary appearance).

Listing 11.4 Showing a List of Database Orders Using the `DataGrid`

 <% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)             LoadDataGrid(orders)         End Sub         Private Sub LoadDataGrid( _                          myDataGrid as System.Web.UI.WebControls.DataGrid)            Dim conn as New SqlConnection("Initial Catalog=Northwind;" + _                                        "Server=(local);UID=sa;PWD=;")            Dim cmd as New SqlCommand("SELECT OrderID, " + _                                      "CustomerID, " + _                                      "OrderDate, " + _                                      "ShipName " + _                                      "FROM Orders", conn)            conn.Open()            myDataGrid.DataSource = cmd.ExecuteReader()            myDataGrid.DataBind()            conn.Close()         End Sub     </script> </HEAD> <BODY> <h1>Order Administration - List</h1> <hr> <form runat="server">     <asp:DataGrid id="orders" width="90%"                   GridLines="Vertical" cellpadding="4" cellspacing="0"                   Font-Name="Verdana" Font-Size="8pt" ShowFooter="true"                   BorderColor="SaddleBrown" BackColor="PapayaWhip"                   AutoGenerateColumns="false" runat="server">         <Columns>             <asp:BoundColumn HeaderText="Order ID" DataField="OrderID" />             <asp:BoundColumn HeaderText="Customer ID" DataField="CustomerID" />             <asp:BoundColumn HeaderText="ShipTo Name" DataField="ShipName" />             <asp:BoundColumn HeaderText="Order Date" DataField="OrderDate"  DataFormatString="{0:d} " />             <asp:HyperLinkColumn Text="Show Order Details"                  DataNavigateUrlField="OrderID"                  DataNavigateUrlFormatString="orderdetails.aspx?OrderID={0} " />         </Columns>     </asp:DataGrid> </form> <hr> </BODY> </HTML>

Notice the last of the bound columns in Listing 11.4. It's a special type of bound column called a HyperLinkColumn . As you might guess, this generates a column of hyperlinks . This is used primarily to wire up a list screen to a detail screen. For instance, Listing 11.4 displays a list of orders, as you can see from Figure 11.3. Each order can consist of several items purchased. Notice how each item in the HyperLinkColumn specifies a URL that links to a Web form called orderdetails.aspx, and passes the OrderID . This gives the next screen all the information it needs to display details for the order.

Figure 11.3. The appearance of the `DataGrid` control from Listing 11.3.

graphics/11fig03.jpg

Listing 11.5 displays the code necessary to display the details of each order. At the beginning of the Page_Load event, the OrderID is accepted and then passed to a method that calls a stored procedure that returns all details for a particular OrderID . The stored procedure is provided for you in Listing 11.6.

Listing 11.5 Showing Order Details Using the `DataGrid` Control

 <% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="VB" runat="server" >         Sub Page_Load(Source as Object, E as EventArgs)             'Get OrderID             Dim OrderID As Integer = Int32.Parse(Request.Params("OrderID"))             LoadGridData( OrderID, orderdetails )         End Sub         Private Sub LoadGridData( orderID as Int32, _                             myDataGrid as System.Web.UI.WebControls.DataGrid )            Dim conn as New SqlConnection("Initial Catalog=Northwind;" + _                                        "Server=(local);UID=sa;PWD=;")            Dim cmd as New SqlCommand("Exec Order_GetDetails " + _                                       orderID.ToString(), conn)            conn.Open()            myDataGrid.DataSource = cmd.ExecuteReader()            myDataGrid.DataBind()            conn.Close()         End Sub     </script> </HEAD> <BODY> <h1>Order Administration - Details</h1> <hr> <form runat="server">     <asp:DataGrid id="orderdetails" width="90%" BorderColor="SaddleBrown"                   BackColor="PapayaWhip" GridLines="Vertical" cellpadding="4"                   cellspacing="0" Font-Name="Verdana" Font-Size="8pt"                   ShowFooter="true" AutoGenerateColumns="false"                   runat="server">       <Columns>         <asp:BoundColumn HeaderText="Order ID" DataField="OrderID" />         <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" />         <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice"                 DataFormatString="{0:c} " />         <asp:BoundColumn HeaderText="Quantity" DataField="Quantity" />         <asp:BoundColumn HeaderText="Discount Received" DataField="Discount" />         </Columns>     </asp:DataGrid> </form> <hr> </BODY> </HTML>

Listing 11.6 Stored Procedure for Showing Order Details

 CREATE PROCEDURE Order_GetDetails (     @OrderID int ) AS SELECT     OrderID,     Products.ProductName,     [Order Details].UnitPrice,     Quantity,     Discount FROM     [Order Details]     INNER JOIN Products on     [Order Details].ProductID = Products.ProductID WHERE     OrderID = @OrderID

The query in Listing 11.5 uses an INNER JOIN . As you recall from Hour 4, this is necessary because only the ProductID is stored in the Order Details table. To get the product name, we have to join the Products table and then access the ProductName field.

When run, the Web form in Listing 11.5 will look much like the one in Figure 11.4. Notice that the Order Details table stores each product ordered as a different record. Therefore, by using the stored procedure from Listing 11.6, we can easily create an itemized list of all products in a particular order.

Figure 11.4. Listing order details using the `DataGrid` control from Listing 11.5.

graphics/11fig04.jpg

for RuBoard