[ Team LiB ] Previous Section Next Section

14.5 Working with Data-Bound Controls

ADO.NET provides good support for "data-bound" objects; that is, objects that can be tied to a particular data set, such as one retrieved from a database by ADO.NET.

A simple example of a data-bound control is the DataGrid control provided with both Windows Forms and Web Forms.

14.5.1 Populating a DataGrid

In its simplest use, a DataGrid is easy to implement. Once again, first create a DataSet and then fill it from the Customers table of the Northwind database, but this time, rather than iterating through the rows of the data set and writing the output to a listbox, you can simply bind the Customers table in your data set to a DataGrid control.

To illustrate, alter Example 14-1 by deleting the listbox from the form you created and replace it with a DataGrid. The default name provided by the Visual Studio design tool is DataGrid1, but let's change it to CustomerDataGrid. After the data set is created and filled, bind the DataGrid through its DataSource property: 

CustomerDataGrid.DataSource=
    DataSet.Tables["Customers"].DefaultView;

Example 14-3 provides the complete source code for this example.

Example 14-3. Using a DataGrid control 
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace ProgrammingCSharpWindows.Form
{

   public class ADOForm3 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container 
         components;
      private System.Windows.Forms.DataGrid 
         CustomerDataGrid;

      public ADOForm3( )
      {
         InitializeComponent( );

         // set up connection and command strings
         string connectionString = "server=(local)\\NetSDK;" +
         "Trusted_Connection=yes; database=northwind";
         string commandString = 
            "Select CompanyName, ContactName, ContactTitle, "
            + "Phone, Fax from Customers";

         // create a data set and fill it
         SqlDataAdapter DataAdapter = 
            new SqlDataAdapter(commandString, connectionString);
         DataSet DataSet = new DataSet( );
         DataAdapter.Fill(DataSet,"Customers");

         // bind the DataSet to the grid
         CustomerDataGrid.DataSource=
            DataSet.Tables["Customers"].DefaultView;           
      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components == null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = 
            new System.ComponentModel.Container ( );
         this.CustomerDataGrid = 
            new System.Windows.Forms.DataGrid ( );
         CustomerDataGrid.BeginInit ( );
         CustomerDataGrid.Location = 
            new System.Drawing.Point (8, 24);
         CustomerDataGrid.Size = 
            new System.Drawing.Size (656, 224);
         CustomerDataGrid.DataMember = "";
         CustomerDataGrid.TabIndex = 0;
         CustomerDataGrid.Navigate += 
            new System.Windows.Forms.NavigateEventHandler 
            (this.dataGrid1_Navigate);
         this.Text = "Using the Data Grid";
         this.AutoScaleBaseSize = 
            new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (672, 273);
         this.Controls.Add (this.CustomerDataGrid);
         CustomerDataGrid.EndInit ( );
      }

      protected void dataGrid1_Navigate 
         (object sender, System.Windows.Forms.NavigateEventArgs ne)
      {

      }

      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm3( ));
      }
   }
}

The code is embarrassingly easy to implement and the results are quite impressive, as shown in Figure 14-3. Notice that every field in the record is represented by a column in the DataGrid, and that the titles of the columns are the names of the fields. All of this is the default behavior of the DataGrid.

Figure 14-3. Using the DataGrid
figs/pcsharp3_1403.gif

14.5.2 Customizing the DataSet

It is possible to control precisely every aspect of creating the DataSet, rather than using the default settings. In the previous examples, when you created the DataSet, you passed in a commandString and a connectionString: 

SqlDataAdapter DataAdapter = 
   new SqlDataAdapter(commandString, connectionString);

These were assigned internally to a SqlCommand object and a SqlConnection object, respectively. You can instead explicitly create these objects to gain finer control over their properties.

In this next example, you'll give the class four new members: 

private System.Data.SqlClient.SqlConnection myConnection;
private System.Data.DataSet myDataSet;
private System.Data.SqlClient.SqlCommand myCommand;
private System.Data.SqlClient.SqlDataAdapter DataAdapter;

The connection is created by instantiating a SqlConnection object with the connection string: 

string connectionString = "server=(local)\\NetSDK;" + 
    "Trusted_Connection=yes; database=northwind";         
myConnection = new System.Data.Sql.SqlConnection(connectionString);

and then it is opened explicitly: 

myConnection.Open( );

By hanging on to this connection, you can reuse it (as you'll see in a subsequent example) and you can also use its transaction support if needed.

Next, explicitly create the DataSet object and set one of its properties: 

myDataSet = new System.Data.DataSet( );
myDataSet.CaseSensitive=true;

Setting CaseSensitive to true indicates that string comparisons within DataTable objects are case-sensitive.

Next, explicitly create the SqlCommand object and give that new command object the connection object and the text for the command: 

myCommand = new System.Data.SqlClient.SqlCommand( )
myCommand.Connection=myConnection;
myCommand.CommandText = "Select * from Customers";

Finally, create the SqlDataAdapter object and assign to it the SqlCommand object you just established. Then tell the DataSet how to map the table columns, using the table you're searching, and instruct the SqlDataAdapter to fill the DataSet object: 

DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
DataAdapter.SelectCommand= myCommand;
DataAdapter.TableMappings.Add("Table","Customers");
DataAdapter.Fill(myDataSet);

That done, you're ready to fill the DataGrid (Note that this time I've used the default name for the DataGrid): 

dataGrid1.DataSource=
    myDataSet.Tables["Customers"].DefaultView;

Example 14-4 provides the complete source code.

Example 14-4. Customizing a DataSet 
namespace ProgrammingCSharpWindows.Form
{
   using System;
   using System.Drawing;
   using System.Collections;
   using System.ComponentModel;
   using System.Windows.Forms;
   using System.Data;
   using System.Data.SqlClient;

   public class ADOForm1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components;
      private System.Windows.Forms.DataGrid dataGrid1;



      // private System.Data.ADO.ADOConnection myConnection;
      private System.Data.SqlClient.SqlConnection myConnection;
      private System.Data.DataSet myDataSet;
      private System.Data.SqlClient.SqlCommand myCommand;
      private System.Data.SqlClient.SqlDataAdapter DataAdapter;
     
      public ADOForm1( )
      {
         InitializeComponent( );

         // create the connection object and open it
         string connectionString = "server=(local)\\NetSDK;" +
            "Trusted_Connection=yes; database=northwind";
         myConnection = new  
            System.Data.SqlClient.SqlConnection(connectionString);
         myConnection.Open( );

         // create the DataSet and set a property
         myDataSet = new System.Data.DataSet( );
         myDataSet.CaseSensitive=true;

         // create the SqlCommand  object and assign the
         // connection and the select statement
         myCommand = new System.Data.SqlClient.SqlCommand( );
         myCommand.Connection=myConnection;
         myCommand.CommandText = "Select * from Customers";

         // create the DataAdapter object and pass in the
         // SQL Command object and establish the table mappings
         DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
         DataAdapter.SelectCommand= myCommand;
         DataAdapter.TableMappings.Add("Table","Customers");

         // Tell the DataAdapter object to fill the DataSet
         DataAdapter.Fill(myDataSet);

         // display it in the grid
         dataGrid1.DataSource=
            myDataSet.Tables["Customers"].DefaultView;  
      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components == null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = new System.ComponentModel.Container ( );
         this.dataGrid1 = new System.Windows.Forms.DataGrid ( );
         dataGrid1.BeginInit ( );
         dataGrid1.Location = new System.Drawing.Point (24, 32);
         dataGrid1.Size = new System.Drawing.Size (480, 408);
         dataGrid1.DataMember = "";
         dataGrid1.TabIndex = 0;
         this.Text = "Using the Data Grid";
         this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (536, 501);
         this.Controls.Add (this.dataGrid1);
         dataGrid1.EndInit ( );
      }
 
      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm1( ));
      }
   }
}

The result of this is shown in Figure 14-4. Now that you have this control, you are in a position to get much fancier in your use of the grid.

Figure 14-4. Taking direct control of the DataGrid
figs/pcsharp3_1404.gif

14.5.3 Combining Data Tables

With the work you've done so far, it is easy now to build a grid that reflects the relationship between two or more tables. For example, you might like to examine all the orders that each customer has placed over some period of time.

Relational databases are built on the idea that one table relates to other tables. The relationship between Orders and Customers is that every order includes a CustomerID, which is a foreign key in Orders and a primary key in Customers. Thus, you have a one-to-many relationship, in which one customer can have many orders, but each order has exactly one customer. You'd like to be able to display this relationship in the grid.

ADO.NET makes this fairly easy, and you can build on the previous example. This time, you want to represent two tables, Customers and Orders, rather than just the Customers table. To do so, you need only a single DataSet object and a single Connection object, but you need two SqlCommand objects and two SqlDataAdapter objects.

After you create the SqlDataAdapter for Customers, just as you did in the previous example, go on to create a second command and adapter for Orders: 

myCommand2 = new System.Data.SqlClient.SqlCommand( );
DataAdapter2 = new System.Data.SqlClient.SqlDataAdapter( );
myCommand2.Connection = myConnection;
myCommand2.CommandText = "SELECT * FROM Orders";

Notice that DataAdapter2 can reuse the same connection as used by the earlier DataAdapter object. The new CommandText is different, of course, because you are searching a different table.

Next, associate the second SqlDataAdapter object with this new command and map its table to Orders. You can then fill the DataSet with the second table: 

DataAdapter2.SelectCommand = myCommand2;
DataAdapter2.TableMappings.Add ("Table", "Orders");
DataAdapter2.Fill(myDataSet);

You now have a single DataSet with two tables. You can display either or both of the tables, but in this example you'll do more. There is a relationship between these tables, and you want to display that relationship. Unfortunately, the DataSet is ignorant of the relationship, unless you explicitly create a DataRelation object and add it to the DataSet.

Start by declaring an object of type DataRelation:

System.Data.DataRelation dataRelation;

This relation will represent the relationship in the database between Customers.CustomerID and Orders.CustomerID. To model this, you need a pair of DataColumn objects: 

System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;

Each DataColumn must be assigned a column in the table within the DataSet: 

dataColumn1 = 
  myDataSet.Tables["Customers"].Columns["CustomerID"];
dataColumn2 = 
  myDataSet.Tables["Orders"].Columns["CustomerID"];

You're now ready to create the DataRelation object, passing into the constructor the name of the relationship and the two DataColumn objects: 

dataRelation = 
    new System.Data.DataRelation("CustomersToOrders", 
    dataColumn1, dataColumn2);

You can now add that relation to the DataSet: 

myDataSet.Relations.Add(dataRelation);

Next, create a DataViewManager object that provides a view of the DataSet for the DataGrid, and set the DataGrid.DataSource property to that view: 

DataViewManager DataSetView = 
   myDataSet.DefaultViewManager; 
dataGrid1.DataSource = DataSetView;

Finally, because the DataGrid now has more than one table, you must tell the grid which table is the "parent" table, or the one table to which many other tables can relate. Do this by setting the DataMember property as shown: 

dataGrid1.DataMember= "Customers";

Example 14-5 provides the complete source code for this process.

Example 14-5. Using a DataGrid with two tables 
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace ProgrammingCSharpWindows.Form
{
   using System.Data.SqlClient;

   public class ADOForm1 : System.Windows.Forms.Form
   {
      private System.ComponentModel.Container components;
      private System.Windows.Forms.DataGrid dataGrid1;

      // private System.Data.ADO.ADOConnection myConnection;
      private System.Data.SqlClient.SqlConnection myConnection;
      private System.Data.DataSet myDataSet;
      private System.Data.SqlClient.SqlCommand myCommand;
      private System.Data.SqlClient.SqlCommand myCommand2;
      private System.Data.SqlClient.SqlDataAdapter DataAdapter;
      private System.Data.SqlClient.SqlDataAdapter DataAdapter2;
     
    
      public ADOForm1( )
      {
         InitializeComponent( );


         // create the connection
         string connectionString = "server=(local)\\NetSDK;" + 
            "Trusted_Connection=yes; database=northwind";
         myConnection = new 
            System.Data.SqlClient.SqlConnection(connectionString);
         myConnection.Open( );

         // create the data set
         myDataSet = new System.Data.DataSet( );
         myDataSet.CaseSensitive=true;

         // set up the command and DataSet command for the first table
         myCommand = new System.Data.SqlClient.SqlCommand( );
         myCommand.Connection=myConnection;
         myCommand.CommandText = "Select * from Customers";
         DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
         DataAdapter.SelectCommand= myCommand;
         DataAdapter.TableMappings.Add("Table","Customers");
         DataAdapter.Fill(myDataSet);

         // set up the command and DataSet command for the second table
         myCommand2 = new System.Data.SqlClient.SqlCommand( );
         DataAdapter2 = new System.Data.SqlClient.SqlDataAdapter( );
         myCommand2.Connection = myConnection;
         myCommand2.CommandText = "SELECT * FROM Orders";
         DataAdapter2.SelectCommand = myCommand2;
         DataAdapter2.TableMappings.Add ("Table", "Orders");
         DataAdapter2.Fill(myDataSet);


         // establish the relationship between the tables
         System.Data.DataRelation dataRelation;
         System.Data.DataColumn dataColumn1;
         System.Data.DataColumn dataColumn2;
         dataColumn1 = 
            myDataSet.Tables["Customers"].Columns["CustomerID"];
         dataColumn2 = 
            myDataSet.Tables["Orders"].Columns["CustomerID"];
            
         dataRelation = 
            new System.Data.DataRelation(
            "CustomersToOrders", 
            dataColumn1, 
            dataColumn2);

         // add the relation object to the data set
         myDataSet.Relations.Add(dataRelation);

         // set up the grid's view and member data and display it
         DataViewManager DataSetView = 
            myDataSet.DefaultViewManager; 
         dataGrid1.DataSource = DataSetView;
         dataGrid1.DataMember= "Customers";
      }

      protected override void Dispose(bool disposing)
      {
         if (disposing)
         {
             if (components == null)
             {
                 components.Dispose( );
             }
         }
         base.Dispose(disposing);
      }

      private void InitializeComponent( )
      {
         this.components = new System.ComponentModel.Container ( );
         this.dataGrid1 = new System.Windows.Forms.DataGrid ( );
         dataGrid1.BeginInit ( );
         //@this.TrayHeight = 0;
         //@this.TrayLargeIcon = false;
         //@this.TrayAutoArrange = true;
         dataGrid1.Location = new System.Drawing.Point (24, 32);
         dataGrid1.Size = new System.Drawing.Size (480, 408);
         dataGrid1.DataMember = "";
         dataGrid1.TabIndex = 0;
         this.Text = "Multiple Tables";
         this.AutoScaleBaseSize = new System.Drawing.Size (5, 13);
         this.ClientSize = new System.Drawing.Size (536, 501);
         this.Controls.Add (this.dataGrid1);
         dataGrid1.EndInit ( );
      }

 
      public static void Main(string[] args) 
      {
         Application.Run(new ADOForm1( ));
      }
   }
}

The result is impressive. Figure 14-5 shows the grid with one customer chosen. The CustomersToOrders link is open under customer ID CACTU.

Figure 14-5. All the customers, with a CustomersToOrders link open
figs/pcsharp3_1405.gif

Clicking the link opens all the orders for that customer, as shown in Figure 14-6.

Figure 14-6. All the orders for the chosen customer
figs/pcsharp3_1406.gif
    [ Team LiB ] Previous Section Next Section