Advanced Binding Samples

This section will show you some of the more complex data binding types that can be accomplished with ASP.NET. Remember that when the user clicks something on the page, a postback is created, and your controls can either be rebuilt from the database or rebuilt from ViewState. The performance impact of the samples shown here will not be discussed. You should be able to look at the samples and see where the performance issues might be for a production system (such as caching the data instead of retrieving it each time).

Header and Detail Forms

One of the most common data binding tasks, as you might have seen in the previous WinForms chapter, is displaying data that exists in a parent-child relationship. This next sample will show you how to create two DataGrids. The first DataGrid contains the parent data (in this case, Customers), and the second DataGrid contains the child data (in this case, Orders). Listings 31.6 and 31.7 show the creation of these linked DataGrids. An explanation of how it works follows the code.

Listing 31.6. An ASP.NET Page Demonstrating Two `DataGrid`s Linked by a Parent-Child Relationship

 <%@ Page language="c#" Codebehind="HeaderDetail.aspx.cs"     AutoEventWireup="false" Inherits="WebFormsBinding.HeaderDetail" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <title>HeaderDetail</title>   <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">   <meta name="CODE_LANGUAGE" Content="C#">   <meta name="vs_defaultClientScript" content="JavaScript">   <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body> <form  method="post" runat="server"> <table width="100%" border="0" cellspacing="2" cellpadding="2"> <tr> <td valign="top">   <asp:DataGrid  runat="server"     BorderColor="#999999" BorderStyle="None" BorderWidth="1px"     BackColor="White" CellPadding="3" GridLines="Vertical"     AutoGenerateColumns="False">     <SelectedItemStyle       Font-Bold="True" ForeColor="White"       BackColor="#008A8C"></SelectedItemStyle>     <AlternatingItemStyle BackColor="#DCDCDC"></AlternatingItemStyle>     <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>     <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"> </HeaderStyle>     <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>     <Columns>       <asp:ButtonColumn Text="Select" HeaderText="Select"         CommandName="cmdSelectCustomer"></asp:ButtonColumn>       <asp:BoundColumn HeaderText="Customer" DataField="ContactName"></asp:BoundColumn>       <asp:BoundColumn HeaderText="Company" DataField="CompanyName"></asp:BoundColumn>     </Columns>     <PagerStyle HorizontalAlign="Center"       ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>   </asp:DataGrid> </td> <td valign="top" align="left">   <asp:DataGrid  Runat="server" BorderColor="#999999"      BorderStyle="None" BorderWidth="1px"      BackColor="White" CellPadding="3" GridLines="Vertical" AutoGenerateColumns="False">   <SelectedItemStyle Font-Bold="True"     ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>   <AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>   <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>   <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"></HeaderStyle>   <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>   <PagerStyle HorizontalAlign="Center"     ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>   <Columns>     <asp:TemplateColumn HeaderText="Order ID">       <ItemTemplate>       <%# ((System.Data.DataRow)Container.DataItem)["OrderID"] %>       </ItemTemplate>     </asp:TemplateColumn>     <asp:TemplateColumn HeaderText="Employee">     <ItemTemplate>       <%# ((System.Data.DataRow)Container.DataItem)["EmployeeName"] %>     </ItemTemplate>   </asp:TemplateColumn> </Columns> </asp:DataGrid> </td> </tr> </table> </form> </body> </HTML>

The preceding ASP.NET code sets up two DataGrids. You can't see the parent-child relationship or the dynamic data binding without looking at the code-behind. The code-behind class is given in Listing 31.7.

Listing 31.7. The Code-Behind for the `HeaderDetail.aspx` Page

 using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; 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 WebFormsBinding {   /// <summary>   /// Summary description for HeaderDetail.   /// </summary>   public class HeaderDetail : System.Web.UI.Page   {     protected System.Web.UI.WebControls.DataGrid dgOrders;     protected System.Web.UI.WebControls.DataGrid dgCustomers;     private void Page_Load(object sender, System.EventArgs e)     {       LoadData();       DataBind();     }     private void LoadData()     {       SqlConnection conn = new SqlConnection(         "server=localhost; user id=sa; password=password; Initial Catalog=Northwind");       conn.Open();       SqlDataAdapter custDa =         new SqlDataAdapter("SELECT * FROM Customers", conn);       SqlDataAdapter orderDa = new SqlDataAdapter(         "SELECT o.CustomerID, o.OrderID, EmployeeName = e.FirstName "+         "' ' + e.LastName FROM Orders o " +         "INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID", conn);      DataSet ds=  new DataSet();      custDa.Fill(ds,"Customers");      orderDa.Fill(ds, "Orders");      DataRelation dr = new DataRelation("CustOrders",      ds.Tables["Customers"].Columns["CustomerID"],      ds.Tables["Orders"].Columns["CustomerID"]);      ds.Relations.Add( dr );     dgCustomers.DataSource = ds;     dgCustomers.DataMember = "Customers"; } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary>   private void InitializeComponent()   {     this.dgCustomers.ItemCommand +=       new System.Web.UI.WebControls.DataGridCommandEventHandler(         this.dgCustomers_ItemCommand);     this.Load += new System.EventHandler(this.Page_Load);   } #endregion private void dgCustomers_ItemCommand(object source,    System.Web.UI.WebControls.DataGridCommandEventArgs e) {   if (e.CommandName == "cmdSelectCustomer")   {     dgCustomers.SelectedIndex = e.Item.ItemIndex;     DataRow cust = ((DataSet)dgCustomers.DataSource).Tables["Customers"].Rows[     dgCustomers.SelectedIndex];     Response.Write("You selected " + cust["ContactName"].ToString());     DataRow[] custOrders = cust.GetChildRows("CustOrders");     dgOrders.DataSource = custOrders;     DataBind();   } } } }

One of the things you might notice is that every time the page loads, it populates the DataSet and creates the data relations. In a real-world scenario, you wouldn't make database round-trips that often. You could use caching mechanisms or make more complicated use of ViewState to avoid the round-trip. However, to keep the example clear and easy to follow, the DataSet is created every time. The key to how the sample works is that when the user clicks the select button on the form, the code changes the currently selected row (highlighting it in the output), and binds the child DataGrid to the array of child rows of the currently selected row. Figure 31.3 shows the page rendered by the preceding code.

Figure 31.3. A parent-child data relationship illustrated with two linked `DataGrid`s.

Cascading Header and Detail

An even more complex problem occurs when your child rows are themselves parents of yet deeper child rows. I apologize for using the Northwind database so much, but it is one database that most people with either SQL Server or MS Access on their machines already have. In the case of Northwind, the child rows of Orders are Order Items.

Rather than show you three DataGrids, this section will modify the sample so that it uses three list boxes. You'll notice that rather than grabbing all the rows at once and doing the rebinding on events, the code actually queries for the child data rather than using a child relationship. The reason for this is a state management issue. You need to be able to ensure that certain bits of information are available at the right times. To do this, the database is queried when additional child rows are needed. You could query the session state, application state, or a cache if you wanted to speed up the example. Listings 31.8 and 31.9 contain the code required to create three linked ListBox controls.

Listing 31.8. Three Simple `ListBox` Controls That Will Be Linked via Parent-Child Relationships

 <%@ Page language="c#" Codebehind="CascadeDetail.aspx.cs"     AutoEventWireup="false" Inherits="WebFormsBinding.CascadeDetail" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <title>CascadeDetail</title>   <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">   <meta name="CODE_LANGUAGE" Content="C#">   <meta name="vs_defaultClientScript" content="JavaScript">   <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body>   <form  method="post" runat="server">   <table width="100%" border="0" cellspacing="1" cellpadding="1">     <tr>       <td width="33%" valign="top">         <asp:ListBox  Runat="server"          Width="100%" AutoPostBack="True"></asp:ListBox>       </td>       <td width="34%" valign="top">         <asp:ListBox  Runat="server"           Width="100%" AutoPostBack="True"></asp:ListBox>       </td>       <td width="33%" valign="top">         <asp:ListBox  Runat="server" Width="100%"></asp:ListBox>       </td>     </tr>   </table> </form> </body> </HTML>

Listing 31.9. The Code-Behind Class That Makes the Three `ListBox` Binding Possible

[View full width]

 using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; 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 WebFormsBinding {   public class CascadeDetail : System.Web.UI.Page   {     protected System.Web.UI.WebControls.ListBox lstCustomers;     protected System.Web.UI.WebControls.ListBox lstOrders;     protected System.Web.UI.WebControls.ListBox lstOrderDetails;     private void Page_Load(object sender, System.EventArgs e)     {       if (!Page.IsPostBack)       {         SqlConnection conn = new SqlConnection(           "server=localhost; user id=sa; password=password; initial catalog=northwind;");         conn.Open();         DataSet ds = new DataSet();         SqlDataAdapter custDA = new SqlDataAdapter("SELECT * FROM Customers", conn );         custDA.Fill(ds, "Customers");         lstCustomers.DataSource = ds;         lstCustomers.DataMember = "Customers";         lstCustomers.DataTextField = "ContactName";         lstCustomers.DataValueField = "CustomerID";       }       DataBind();     }     #region Web Form Designer generated code     override protected void OnInit(EventArgs e)     {       //       // CODEGEN: This call is required by the ASP.NET Web Form Designer.       //       InitializeComponent();       base.OnInit(e);     }     /// <summary>     /// Required method for Designer support - do not modify     /// the contents of this method with the code editor.     /// </summary>     private void InitializeComponent()     {       this.lstCustomers.SelectedIndexChanged +=         new System.EventHandler(this.lstCustomers_SelectedIndexChanged);       this.lstOrders.SelectedIndexChanged +=         new System.EventHandler(this.lstOrders_SelectedIndexChanged);       this.Load += new System.EventHandler(this.Page_Load);     }     #endregion     private void lstCustomers_SelectedIndexChanged(object sender, System.EventArgs e)     {       string custID = lstCustomers.SelectedValue;       SqlConnection conn = new SqlConnection(         "server=localhost; user id=sa; password=password; initial catalog=northwind;");       conn.Open();       SqlDataAdapter ordersDA =         new SqlDataAdapter(           "SELECT * FROM Orders WHERE CustomerID='" + custID+"'", conn );       DataSet ds = new DataSet();       ordersDA.Fill(ds, "Orders");       lstOrders.DataSource = ds;       lstOrders.DataMember = "Orders";       lstOrders.DataValueField = "OrderID";       lstOrders.DataTextField = "OrderDate";       lstOrders.SelectedIndex =0;       lstOrderDetails.Items.Clear();       DataBind();     }     private void lstOrders_SelectedIndexChanged(object sender, System.EventArgs e)     {       string orderID = lstOrders.SelectedValue;       SqlConnection conn = new SqlConnection(         "server=localhost; user id=sa; password=password; initial catalog=northwind;");       conn.Open();       SqlDataAdapter detailDA =         new SqlDataAdapter("SELECT od.*, p.ProductName FROM [Order Details] od " +           " INNER JOIN Products p on od.ProductID = p.ProductID WHERE Order0" width="14" height="9" align="left" src="/books/1/238/1/html/2/images/ccc.gif" /> conn);       DataSet ds = new DataSet();       detailDA.Fill(ds, "OrderDetails");       lstOrderDetails.DataSource = ds;       lstOrderDetails.DataMember = "OrderDetails";       lstOrderDetails.DataValueField = "ProductID";       lstOrderDetails.DataTextField = "ProductName";       lstOrderDetails.SelectedIndex = 0;       DataBind();     }   } }

Basically what happens here is when the user clicks on a customer, the code retrieves the orders for that customer and places them in the second list box. The customer list remains intact because it is being copied from page view to page view via the ViewState mechanism. When you, the user, clicks on an order, the list of order details and items is retrieved from the database and bound to the third and final list box. Using this model, you can cascade parent/child relationships to any depth you like and are still able to retrieve the ID values of all the parents, even if the DataSet that originally generated the parents is not available.

Header and Detail Forms

Listing 31.6. An ASP.NET Page Demonstrating Two DataGrids Linked by a Parent-Child Relationship

Listing 31.7. The Code-Behind for the HeaderDetail.aspx Page

Figure 31.3. A parent-child data relationship illustrated with two linked DataGrids.