Advanced Binding Samples

The next section will take you through the process of creating some complex header/detail forms using Windows Forms data binding, parent/child relationships within DataSets, and (of course) the BindingContext and CurrencyManager classes.

Header/Detail Forms

A header/detail form is a scenario in which a grid is bound to a data source. In addition, there is a grid bound to some child relationship. For example, if you are looking at a grid of customers and you select a customer row, you would expect the child grid (containing that customer's orders) to automatically refresh to display just the orders for that customer. This is an example of a header/detail form, where the header is the grid bound to the parent rows and the detail grid is bound to the child rows of each row to which the header grid is bound.

To create the sample in Listing 30.4, two grids, called dgCustomers and dgOrders, respectively, are dropped onto a form. Then a Get Data button is added to fetch information from the database to populate the grids. The tricky part here is that data is fetched from two different queries into the same table, and then a parent-child relationship is created between those tables. This relationship is what enables the header/detail grid binding.

Listing 30.4. A Form That Demonstrates Header/Detail Grid Binding

 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace AdvGridBinding {   public class Form1 : System.Windows.Forms.Form   {     private System.Windows.Forms.Button btnGetData;     private System.Windows.Forms.DataGrid dgCustomers;     private System.Windows.Forms.DataGrid dgOrders;     private System.ComponentModel.Container components = null;     public Form1()     {       InitializeComponent();     }     /// <summary>     /// Clean up any resources being used.     /// </summary>     protected override void Dispose( bool disposing )     {      if( disposing )      {         if (components != null)         {           components.Dispose();         }       }       base.Dispose( disposing );     }     // Windows Forms designer code cut out for clarity     /// <summary>     /// The main entry point for the application.     /// </summary>     [STAThread]     static void Main()     {        Application.Run(new Form1());     }     private void btnGetData_Click(object sender, System.EventArgs e)     {       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);       SqlDataAdapter ordersDA = new SqlDataAdapter("SELECT * FROM Orders", conn);       custDA.Fill(ds, "Customers");       ordersDA.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";       dgOrders.DataSource = ds;       dgOrders.DataMember = "Customers.CustOrders";       BindingContext[ dgCustomers.DataSource, "Customers" ].CurrentChanged +=         new EventHandler(Form1_CurrentChanged);     }     private void Form1_CurrentChanged(object sender, EventArgs e)     {       CurrencyManager cm =        (CurrencyManager) BindingContext[ dgCustomers.DataSource,           "Customers" ];       DataRowView drv = (DataRowView)cm.Current;       dgOrders.CaptionText = string.Format("{0}'s Order History",       drv["ContactName"]);     }   } } 

When retrieving the data, you can see that the code uses two DataAdapters so that the results of two different SQL queries can be placed into the same DataSet by specifying unique table names as destinations. If you don't specify a destination table name, the SqlDataAdapter will name the first table Table, the second Table1, and so on.

For the parent/child relationship to work properly, both grids must be bound to the same data source and differ by only the name of the data member. For example, if you bound the dgCustomers grid to the Customers table with no data member instead of to the DataSet using Customers as a data member, the child grid would never be updated with currency changes.

Finally, just to show you some more uses for the BindingContext and CurrencyManager, the detail grid's CaptionText is changed to indicate the name of the customer who owns the list of orders in the grid. This shows how, with just a small amount of effort, you can add little touches to your applications that will increase the usability of your application and the depth of its user experience.

Cascading Header/Detail

Cascading header/detail forms refers to the situation in which each row in a detail grid is also the parent row to another grid. This cascading effect of parents that are details that are parents is often daunting and overwhelming for programmers to implement. It was especially difficult before .NET was available when most people turned to third-party grid controls to accomplish this task.

The code in Listing 30.5 is a modified version of the previous sample. The difference is that a third grid has been added to the form. This grid displays the list of items that belong to a specific order (which in turn belongs to a specific customer). To keep things interesting, inner join is performed to obtain the product name of each item in the order. This should add to your impression of how powerful and flexible Windows Forms data binding can be.

Listing 30.5. A Modified Advanced Grid Binding Form That Illustrates a Cascade or Waterfall of Parent/Detail Records

 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace AdvGridBinding {   public class Form1 : System.Windows.Forms.Form   {     private System.Windows.Forms.Button btnGetData;     private System.Windows.Forms.DataGrid dgCustomers;     private System.Windows.Forms.DataGrid dgOrders;     private System.Windows.Forms.DataGrid dgOrderDetails;     private System.ComponentModel.Container components = null;     public Form1()     {       InitializeComponent();     }     /// <summary>     /// Clean up any resources being used.     /// </summary>     protected override void Dispose( bool disposing )     {       if( disposing )       {         if (components != null)         {           components.Dispose();         }       }       base.Dispose( disposing );     } // Windows Forms designer code excluded for clarity   /// <summary>   /// The main entry point for the application.   /// </summary>   [STAThread]   static void Main()   {     Application.Run(new Form1());   }   private void btnGetData_Click(object sender, System.EventArgs e)   {     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);     SqlDataAdapter ordersDA = new SqlDataAdapter("SELECT * FROM Orders", conn);     SqlDataAdapter orderitemsDA = new SqlDataAdapter(       "SELECT p.ProductName, od.ProductId, od.UnitPrice, od.Quantity, " +       "od.Discount, od.OrderID " +       "FROM [Order Details] od INNER JOIN Products p ON " +       "od.ProductId = p.ProductID", conn);     custDA.Fill(ds, "Customers");     ordersDA.Fill(ds, "Orders");     orderitemsDA.Fill(ds,"OrderItems");     DataRelation dr =       new DataRelation("CustOrders", ds.Tables["Customers"].Columns["CustomerID"],     ds.Tables["Orders"].Columns["CustomerID"]);     ds.Relations.Add( dr );     dr = new DataRelation("OrderDetails", ds.Tables["Orders"].Columns["OrderID"],     ds.Tables["OrderItems"].Columns["OrderID"]);     ds.Relations.Add( dr );     dgCustomers.DataSource = ds;     dgCustomers.DataMember = "Customers";     dgOrders.DataSource = ds;     dgOrders.DataMember = "Customers.CustOrders";     dgOrderDetails.DataSource = ds;     dgOrderDetails.DataMember = "Customers.CustOrders.OrderDetails";     BindingContext[ dgCustomers.DataSource, "Customers" ].CurrentChanged +=       new EventHandler(Form1_CurrentChanged);     BindingContext[ dgOrders.DataSource, "Customers.CustOrders"].CurrentChanged +=       new EventHandler(CustOrders_CurrentChanged);     conn.Close();   }   private void Form1_CurrentChanged(object sender, EventArgs e)   {     string custName;     CurrencyManager cm = (CurrencyManager) BindingContext[ dgCustomers.DataSource,       "Customers" ];     DataRowView drv = (DataRowView)cm.Current;     custName = drv["ContactName"].ToString();     dgOrders.CaptionText = string.Format("{0}'s Order History",       custName);   }   private void CustOrders_CurrentChanged(object sender, EventArgs e)   {     CurrencyManager cm = (CurrencyManager)BindingContext[ dgOrders.DataSource,       "Customers.CustOrders"];     DataRowView drv = (DataRowView)cm.Current;     DataRow customer = drv.Row.GetParentRow("CustOrders");     dgOrderDetails.CaptionText = string.Format("{0}'s Order on {1} ({2})",       customer["ContactName"], ((DateTime)drv["OrderDate"]).ToShortDateString(),       drv["OrderID"]);   }  } } 

One of the more subtle things taking place in the new event handler for the second grid is that because the DataSet has built-in relationships, the DataRowView instance can be used to get at the underlying row, which can then obtain a reference to its parent via the GetParentRow method. When you compile and run the preceding Windows Forms application, you will be presented with the form shown in Figure 30.3.

Figure 30.3. A cascading header/detail data binding application.