- Hour 11: Using the Built-In ASP.NET List Controls
- Working with the Repeater
- Working with the DataGrid
- Working with the DataList
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
){kind=link}
NOTE
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.
Figure 11.2 The appearance of the DataGrid control from Listing 11.3.
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.
){kind=link}
Figure 11.3 The appearance of the DataGrid control from Listing 11.3.
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
NOTE
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.
){kind=link}
Figure 11.4 Listing order details using the DataGrid control from Listing 11.5.