10.3 DataTable, DataSet, and Friends

Although the data-binding architecture is flexible enough to bind to any property of any object, the .NET Framework provides a group of classes (part of ADO.NET) that have much more extensive data-binding support. At the center of this family are the DataSet and DataTable classes in the System.Data namespace. These form the basis for the disconnected use of data from a database, and they are designed to allow flexible presentation of data while making efficient use of server resources.

All presentation of data from a database in Windows Forms is based around a disconnected modelclients connect to a database and retrieve all the data they require in a single step. They do not hold onto any server-side resources (such as cursors ) after this data has been retrieved, and can therefore release their connection to the database. (In practice, the connection will normally be returned to a pool rather than being freed completely.) This is a departure from previous data-binding models, where disconnected operation was strictly optional. This new model reduces the workload on the server, because at any given instant it has to deal with fewer clients , thus improving the scalability of the system.

The DataTable class allows a snapshot of part (or all) of a database table to be held on the client. The DataSet class can hold any number of DataTable objects, and can also contain information about relations between these tables. (Relations are represented as DataRelation objects.)

This rich client-side data representation allows us to present an extensive amount of data while only requiring a single round trip to the database. For example, we can build a user interface that shows lists of data and can then present detailed information for each item as it is selected without needing to make a further trip to the database.

Obviously, you will need to exercise a little restraint herealthough it is theoretically possible to build a complete snapshot of the entire database on the client, this will usually not be a sensible approach. But the ability to have client-side relational snapshots of small subsections of the database is very useful.

Although the DataSet and DataTable classes are typically used to store data that has been retrieved from a database, they can be used to store any kind of data. For example, you can also use XML as a data source with the DataSet class's ReadXml method, or you can build up tables from scratch in code.

We will now see how to use these classes and how to bind controls to them. We will start with a single table. Then we will add this to a DataSet along with some related tables to illustrate the use of relations in data binding.

10.3.1 Using the DataTable Class

The DataTable class represents tabular data such as would be returned from a SQL SELECT statement. A DataTable contains information about the names and types of each column in the table and a collection of rows. We usually fill DataTable objects with data from a database or an XML document, but it is also possible to build one from scratch in code, as shown in Examples Example 10-16 and Example 10-17.

Example 10-16. Building a DataTable from scratch using C#

 DataTable customers = new DataTable("Customers"); customers.Columns.Add("CustomerID", typeof(int)); customers.Columns.Add("FirstName", typeof(string)); customers.Columns.Add("LastName", typeof(string)); customers.PrimaryKey = new DataColumn[]      { customers.Columns["CustomerID"] }; customers.Rows.Add(new object[] { 1, "Homer", "Simpson" }); customers.Rows.Add(new object[] { 2, "Arthur", "Pewty" }); 

Example 10-17. Building a DataTable from scratch using VB

 Dim customers As New DataTable("Customers")  customers.Columns.Add("CustomerID", GetType(Integer))  customers.Columns.Add("FirstName", GetType(String))  customers.Columns.Add("LastName", GetType(String))  customers.PrimaryKey = new DataColumn() _              { customers.Columns("CustomerID") }  customers.Rows.Add(new object() { 1, "Homer", "Simpson" })  customers.Rows.Add(new object() { 2, "Arthur", "Pewty" }) 

This code creates a new table called Customers . Passing the name to the constructor is optional, but will be useful later on when we will be using multiple related tables. Next, we tell the DataTable what columns it will have, supplying a name and a Type object representing the data type for each column. We also tell the DataTable which of these columns acts as a primary key. (The DataTable supports compound keys, so its PrimaryKey property expects to be passed an array. Here we are just using our CustomerID column as the key, so we pass an array containing just one element.) The DataTable enforces the uniqueness of the primary keyif you attempt to add a row with a duplicate key to a DataTable , it will throw an exception.

Having supplied this metadata, we are now free to add the real data to the table by calling the Add method on the table's Rows property, as shown at the end of Examples Example 10-16 and Example 10-17. Note that because the table knows what columns it expects to see, it will check any new rows that are added. If the row data does not match the expected column types, the Add method will throw a System.ArgumentException exception.

We can now read the data back out from the table. The Rows property provides two ways of extracting rows. One is an indexer property, allowing individual rows to be accessed by number. (This is just an offset into the list of rows; it bears no relation to the primary key.) It also supports enumeration, allowing the C# foreach construct or the VB For Each ... Next construct to be used. Examples Example 10-18 and Example 10-19 illustrate both techniques. In either case, each row is represented by a DataRow object. The DataRow object also supplies indexer properties allowing columns in the row to be accessed either by index or by name.

Example 10-18. Retrieving data from a DataTable using C#

 DataRow firstRow = customers.Rows[0]; Console.WriteLine("First CustomerID is {0}",     firstRow["CustomerID"]); foreach (DataRow row in customers.Rows) {     Console.WriteLine("Customer {0} is called {1} {2}",         row["CustomerID"], row["FirstName"], row["LastName"]); } 

Example 10-19. Retrieving data from a DataTable using VB

 Dim row As DataRow Dim firstRow As DataRow = customers.Rows(0) Console.WriteLine("First CustomerID is {0}", _     firstRow("CustomerID")) For Each row in customers.Rows     Console.WriteLine("Customer {0} is called {1} {2}", _         row("CustomerID"), row("FirstName"), row("LastName")) Next 

More usefully, we can bind control properties to this table. This works in exactly the same way as it did for binding to a simple class, both for simple and complex binding. Example 10-20 shows the C# code that uses simple binding to bind the three column values to a Label control and two TextBox controls, using exactly the same technique shown in Example 10-3 for binding to a class property. (The VB code is identical, without the semicolons that terminate each statement.) Example 10-20 also uses complex binding to bind the entire table to a ListBox control, and again the technique is identical to that shown in Example 10-15we specify the data source and also the member that we would like to use for the list item text.

Example 10-20. Binding to a DataTable

 labelCustomerID.DataBindings.Add("Text", customers, "CustomerID"); textBoxFirstName.DataBindings.Add("Text", customers, "FirstName"); textBoxLastName.DataBindings.Add("Text", customers, "LastName"); listBoxCustomers.DataSource = customers; listBoxCustomers.DisplayMember = "LastName"; 

Figure 10-2 shows the form containing these controls in action. As before, the simple-bound controls (the Label and TextBox controls) always show the values for whichever item is currently selected in the ListBox control. This is because when a DataTable is used as a data source, the Windows Forms data-binding architecture creates a CurrencyManager as the associated binding manager, just as it did for the array source. As before, this keeps track of the currently selected item, and makes sure that all controls bound to that data source show values from that same current item.

Figure 10-2. A form with controls bound to a DataTable

Because the DataTable class is designed to be used for data binding, it automatically generates update notifications. So you do not need to take any steps to ensure that changes made to a table's contents will be reflected in any controls that are bound to that table. It even supplies an implementation of IBindingList . As mentioned earlier, this means that if rows are added to or removed from the list, or if an item in the table other than the currently selected one is modified, these changes will be reflected by any complex-bound controls.

10.3.2 Using the DataSet Class

Databases rarely consist of a single table. Applications that use a relational database will usually deal with multiple related tables. So when it comes to presenting data from the database in a client application, it follows that we will often want to present data from more than one table. The relations between tables are also likely to be important to the client application. The DataSet class can contain multiple DataTable objects, and can also contain DataRelation objects that describe the relations that exist between the tables.

In the previous section, our examples had just a single table, Customers . Let us now add a second table called Orders . This will hold a simple list of orders made by customers, as shown in Examples Example 10-21 and Example 10-22.

Example 10-21. Creating a second DataTable using C#

 orders = new DataTable("Orders"); orders.Columns.Add("OrderID", typeof(int)); orders.Columns.Add("CustomerID", typeof(int)); orders.Columns.Add("Product", typeof(string)); orders.Columns.Add("Quantity", typeof(int)); orders.PrimaryKey = new DataColumn[] { orders.Columns["OrderID"] }; orders.Rows.Add(new Object[] { 1, 1, "Donuts", 500 } ); orders.Rows.Add(new Object[] { 2, 1, "Cans of beer", 200 } ); orders.Rows.Add(new Object[] { 3, 2, "Pencils", 20 } ); 

Example 10-22. Creating a second DataTable using VB

 Dim orders As New DataTable("Orders")  orders.Columns.Add("OrderID", GetType(Integer))  orders.Columns.Add("CustomerID", GetType(Integer))  orders.Columns.Add("Product", GetType(String))  orders.Columns.Add("Quantity", GetType(Integer))  orders.PrimaryKey = New DataColumn() { orders.Columns("OrderID") }  orders.Rows.Add(new Object() { 1, 1, "Donuts", 500 } )  orders.Rows.Add(new Object() { 2, 1, "Cans of beer", 200 } )  orders.Rows.Add(new Object() { 3, 2, "Pencils", 20 } ) 

This table has its own primary key, OrderID . The Product and Quantity columns represent the item being ordered and the quantity required, respectively. But notice that there is also a CustomerID column. This will be used as a foreign key that relates rows in this table to rows in the Customers table. We establish this association by placing both tables into a DataSet object and creating a relation between them, as shown in Examples Example 10-23 and Example 10-24.

Example 10-23. Adding related tables to a DataSet using C#

 DataSet ds = new DataSet(); ds.Tables.Add(customers); ds.Tables.Add(orders); ds.Relations.Add("OrdersRelation",     customers.Columns["CustomerID"],     orders.Columns["CustomerID"]); 

Example 10-24. Adding related tables to a DataSet using VB

 Dim ds As New DataSet() ds.Tables.Add(customers) ds.Tables.Add(orders) ds.Relations.Add("OrdersRelation", _     customers.Columns("CustomerID"), _     orders.Columns("CustomerID")) 

This adds a named relation called OrdersRelation that defines the relationship between these two tables. Relations are directional, and by default, they will be traversed from the parent to the child. The first column passed to the Add method is considered to be the parent and the second column the child. So in this case, it will be possible to use the relation to find the orders that correspond to a customer row by calling the DataRow class's GetChildRows method. It is also possible to traverse the relation in the opposite direction using the GetParentRows method of the child row. In this particular case, it is not possible to set up the relation in the opposite direction, because the parent column is required to be unique. (In other words, the DataSet supports one-to-many relations, but not many-to-one relations.)

The name of a relation is not significant. It simply needs to be unique within the scope of the DataSet to which it applies. Although it is common for the relation to have the same name as the child table, as Example 10-23 shows, this is not a requirement.

Adding a relation to a DataSet sets up a constraintwith this relation in place, any row added to the Orders table will be checked, and if its CustomerID value does not have a corresponding entry in the Customers table with the same CustomerID , a System.Data.InvalidConstraintException will be thrown. (Be aware that the DataSet has no way of checking that the relations you create correspond to the relations in the database's underlying schema. It is up to you to make sure that you create the correct relations.)

While enforcing foreign key constraints is useful, there is a more interesting side effect of defining relations when data binding is being used. If a relation exists between two tables, it is possible to use this in conjunction with complex binding to show only those items from a child list that are related to the currently selected items in the parent list. So as Figure 10-3 shows, we could add a second list control to show all the orders for the currently selected customer.

Figure 10-3. Master/details binding

The Orders DataTable itself has three rows (see Examples Example 10-21 and Example 10-22), but only two are shown here. This is because the orders ListBox only shows items that correspond to the currently selected customer (i.e., orders whose CustomerID is equal to 1). The code to achieve this is very simple and is shown in C# in Example 10-25. (Once again, the VB code is identical except for the absence of the closing semicolons.)

Example 10-25. Complex binding through a relation

 listBoxOrders.DataSource = customers; listBoxOrders.DisplayMember = "OrdersRelation.Product"; 

Notice that although the DataSource property has been set to the Customers table, the ListBox is showing items from the Orders table. This is because the DisplayMember property has been set to OrdersRelation.Product . Setting DisplayMember to a string with a period in it indicates that relations are in use. Here we are indicating that we wish to bind through the OrdersRelation relation that we set up in Examples Example 10-23 and Example 10-24, and that we wish to show the Product column from the child table ( Orders ).

The complete code for creating these tables, establishing the relations, and binding the controls is shown in Examples Example 10-26 and Example 10-27.

Example 10-26. Display a master/details view using C#

 // Create the Customers table DataTable customers = new DataTable("Customers"); customers.Columns.Add("CustomerID", typeof(int)); customers.Columns.Add("FirstName", typeof(string)); customers.Columns.Add("LastName", typeof(string)); customers.PrimaryKey = new DataColumn[]     { customers.Columns["CustomerID"] }; customers.Rows.Add(new object[] { 1, "Homer", "Simpson" }); customers.Rows.Add(new object[] { 2, "Arthur", "Pewty" }); // Create the Orders table DataTable orders = new DataTable("Orders"); orders.Columns.Add("OrderID", typeof(int)); orders.Columns.Add("CustomerID", typeof(int)); orders.Columns.Add("Product", typeof(string)); orders.Columns.Add("Quantity", typeof(int)); orders.PrimaryKey = new DataColumn[]     { orders.Columns["OrderID"] }; orders.Rows.Add(new Object[] { 1, 1, "Donuts", 500 } ); orders.Rows.Add(new Object[] { 2, 1, "Cans of beer", 200 } ); orders.Rows.Add(new Object[] { 3, 2, "Pencils", 20 } ); // Add both tables to a DataSet and establish // the relation between their CustomerID columns DataSet ds = new DataSet(); ds.Tables.Add(customers); ds.Tables.Add(orders); ds.Relations.Add("OrdersRelation", customers.Columns["CustomerID"],     orders.Columns["CustomerID"]); // Bind the controls labelCustomerID.DataBindings.Add("Text", customers, "CustomerID"); textBoxFirstName.DataBindings.Add("Text", customers, "FirstName"); textBoxLastName.DataBindings.Add("Text", customers, "LastName"); listBoxCustomers.DataSource = customers; listBoxCustomers.DisplayMember = "LastName"; listBoxOrders.DataSource = customers; listBoxOrders.DisplayMember = "OrdersRelation.Product"; 

Example 10-27. Display a master/details view using VB

 ' Create the Customers table Dim customers As New DataTable("Customers")  customers.Columns.Add("CustomerID", GetType(Integer))  customers.Columns.Add("FirstName", GetType(String))  customers.Columns.Add("LastName", GetType(String))  customers.PrimaryKey = new DataColumn() _ { customers.Columns("CustomerID") }  customers.Rows.Add(new object() { 1, "Homer", "Simpson" })  customers.Rows.Add(new object() { 2, "Arthur", "Pewty" })  ' Create the Orders table Dim orders As New DataTable("Orders")  orders.Columns.Add("OrderID", GetType(Integer))  orders.Columns.Add("CustomerID", GetType(Integer))  orders.Columns.Add("Product", GetType(String))  orders.Columns.Add("Quantity", GetType(Integer))  orders.PrimaryKey = New DataColumn() { orders.Columns("OrderID") }  orders.Rows.Add(new Object() { 1, 1, "Donuts", 500 } )  orders.Rows.Add(new Object() { 2, 1, "Cans of beer", 200 } )  orders.Rows.Add(new Object() { 3, 2, "Pencils", 20 } )  ' Add both tables to a DataSet and establish ' the relation between their CustomerID columns Dim ds As New DataSet() ds.Tables.Add(customers) ds.Tables.Add(orders) ds.Relations.Add("OrdersRelation", _    customers.Columns("CustomerID"), _    orders.Columns("CustomerID")) ' Bind the controls labelCustomerID.DataBindings.Add("Text", customers, "CustomerID")  textBoxFirstName.DataBindings.Add("Text", customers, "FirstName")  textBoxLastName.DataBindings.Add("Text", customers, "LastName")  listBoxCustomers.DataSource = customers  listBoxCustomers.DisplayMember = "LastName"  listBoxOrders.DataSource = customers  listBoxOrders.DisplayMember = "OrdersRelation.Product" 

10.3.3 Populating a DataSet from a Database

The previous example builds DataSet and DataTable objects from scratch. However, most applications will fill these objects with data from a database. There is a class that automates this process, called the DataAdapter class, which is defined in the System.Data.Common namespace. It connects to a database, retrieves one or more tables of data, adds them to a DataSet , and then disconnects from the database. (Unfortunately, it cannot automatically retrieve relations from the database schema, so you must write code to set these up yourself.)

DataAdapter is an abstract base class, so you must use one of the concrete classes derived from it. The class you use will depend on what kind of database you are using. If you are using SQL Server, you will use the SqlDataAdapter class in the System.Data.SqlClient namespace. If you are using an OLE DB data source, you will use the OleDbDataAdapter class in the System.Data.OleDb namespace. But regardless of what database you use, the adapter classes all work in much the same way. Example 10-28 shows an example written in C#.

Example 10-28. Filling a DataSet with a DataAdapter

 string command = "SELECT * FROM Products"; string connect = "data source=.;initial catalog=Northwind;" +     "integrated security=SSPI"; SqlDataAdapter adapter = new SqlDataAdapter(command, connect); adapter.TableMappings.Add("Table", "Products"); DataSet ds = new DataSet(); adapter.Fill(ds); 

Here we are using the SQL Server data adapter, SqlDataAdapter , but the code would look almost identical for any other databasewe would just use, say, the OleDbDataAdapter class, or some vendor-specific adapter class. To create a data adapter, we simply need a connection string indicating which database will supply the data and a command to execute to retrieve the data. Here we are connecting to the Northwind database, a sample database shipped with SQL Server, and we are retrieving the entire contents of the Products table.

Calling the Fill method causes the data adapter to connect to the database, execute the command, copy the results into the DataSet supplied, and disconnect. This means that the database connection is in use for the smallest amount of time possible, which reduces the load on the server. We can then use the results at our leisure. In this case, the DataSet will only contain a single table because that is all the SQL command returned. (Data adapters can add multiple tables to a DataSet if executing an appropriate batch statement or stored procedure.) The adapter will use the metadata that comes back with the results to add all the appropriate columns to the table it creates, but by default it will call that table Table . So here we have added an entry to the adapter's TableMappings property indicating that instead of calling the table Table , it should call it Products . Note that if a table of the specified name already exists in the DataSet , the data adapter will attempt to add rows to it instead of creating a new table.

Remember that a DataSet can contain as many tables as you like. There are two ways of using data adapters to put multiple tables into a DataSet . One is to use more than one adapteryou can create a single data set and then pass it as the parameter to the Fill method on any number of different data adapters. As long as their TableMappings are set up so as not to collide, each will add its own table to the DataSet . However, if all the tables are coming from a single database, it is more efficient to merge all these into a single batch SQL statement, as shown in C# in Example 10-29.

Example 10-29. Reading multiple tables with a data adapter

 string connect = "data source=.;initial catalog=Northwind;" +     "integrated security=SSPI"; string command = "SELECT * FROM Customers; " +     "SELECT * FROM Orders; SELECT * FROM [Order Details]"; SqlDataAdapter adapter = new SqlDataAdapter(command, connect); adapter.TableMappings.Add("Table", "Customers"); adapter.TableMappings.Add("Table1", "Orders"); adapter.TableMappings.Add("Table2", "Order Details"); DataSet ds = new DataSet(); adapter.Fill(ds); 

This code is almost identical to Example 10-28. The only differences are that the command is now a batch of three SELECT statements, and there are now three table mappings. Note that when the SqlDataAdapter returns multiple tables, by default, it just names them sequentially as Table , Table1 , Table2 , etc.

The sample Northwind database has a similar structure to our previous example in that we have a table of customers and a related table of orders. (It is a little more complex because an order can consist of several items, contained in the Order Details table, and there is also a Products table to represent the kinds of items that can be ordered. But complexity aside, the same principles apply.) We will therefore want to represent the relations in the DataSet as before. Example 10-30 establishes the relations appropriate to these tables.

Example 10-30. Establishing relations

 ds.Relations.Add("CustomerOrdersRelation",     ds.Tables["Customers"].Columns["CustomerID"],     ds.Tables["Orders"].Columns["CustomerID"]); ds.Relations.Add("OrderDetailsRelation",     ds.Tables["Orders"].Columns["OrderID"],     ds.Tables["Order Details"].Columns["OrderID"]); 

Having done this, we can bind controls to this data in much the same way that we did when building our own DataTable objects from scratch. This is shown in Example 10-31. There are some minor differences from the original code shown in Examples Example 10-26 and Example 10-27 because the structure of information in the Northwind database is not quite the same as that in the earlier example. (Names are not separated into first and last names here. And because in Northwind, orders can consist of multiple items, we just show the order date in the orders ListBox instead of the product name.)

Example 10-31. Binding to data from a database

 labelCustomerID.DataBindings.Add("Text", ds, "Customers.CustomerID"); textBoxCompanyName.DataBindings.Add("Text", ds, "Customers.CompanyName"); textBoxContactName.DataBindings.Add("Text", ds, "Customers.ContactName"); listBoxCustomers.DataSource = ds; listBoxCustomers.DisplayMember = "Customers.CompanyName"; listBoxOrders.DataSource = ds; listBoxOrders.DisplayMember =     "Customers.CustomerOrdersRelation.OrderDate"; 

Also note that here we are using the DataSet itself as the data source and naming the table in the data member. Before, we were using the table itself as the data source. It doesn't matter which you use although you must be consistent, because otherwise you may cause the data-binding system to create two CurrencyManager objects, which could cause your controls to get out of sync. In this case, we are using the DataSet because it is more convenientthe data adapter created the tables for us automatically, so we don't have any references to them handy.

Figure 10-4 shows the modified user interface, displaying the data as retrieved from the database. As before, the orders ListBox will only show those orders for the item currently selected in the customer ListBox .

Figure 10-4. A form showing a master/details view from a database

10.3.4 Multiple Binding Contexts

By default, all controls that are bound to a single source share a binding manager. This is useful because it keeps those controls synchronized, and is the basis for the master/details views shown above. But what if you don't want this automatic synchronization? Sometimes it is useful to have two controls showing the same list. If these controls share a binding manager, they will always show the same item as being selectedchanging the current selection in one will automatically change the current selection in the other. To get around this, you must use multiple binding contexts.

A binding context is essentially a collection of binding managers. All controls are associated with a binding context, and it is through this context that they find the binding managers for any data sources they are bound to. By default, a control will simply use its parent's binding context, which means that controls will usually use their containing form's context. But this is easily changedyou can change a control's binding context by setting its BindingContext property.

Figure 10-5 shows a form with two binding contexts. It is similar to the form shown in Figure 10-4, except that it has an extra GroupBox control, which contains an extra set of controls bound to the same data source and properties as those on the top left. Notice that these are clearly using a different currency manager, because the currently selected item on the right is different from that on the left.

Figure 10-5. A form with two binding contexts

These extra controls are bound to the data source in exactly the same way as before, as shown in C# in Example 10-32.

Example 10-32. Binding a second set of controls

 labelOtherCustomerID.DataBindings.Add     ("Text", ds, "Customers.CustomerID"); textBoxOtherCompanyName.DataBindings.Add     ("Text", ds, "Customers.CompanyName"); textBoxOtherContactName.DataBindings.Add     ("Text", ds, "Customers.ContactName"); listBoxOtherCustomers.DataSource = ds; listBoxOtherCustomers.DisplayMember = "Customers.CompanyName"; 

To enable these controls to maintain their own position in the data source, independent of the other set, they will need their own currency manager. We must make sure that they have their own binding context. We could set the BindingContext property on each control, but it is simpler to exploit the fact that controls will use their parent's binding context by default. All these controls are children of the GroupBox , so we only have to set the binding on that, as shown in Example 10-33.

Example 10-33. Specifying a binding context

 groupBoxOther.BindingContext = new BindingContext(); 

This will cause the GroupBox and any of its children to use this newly created context for data binding. They will therefore all get their own binding manager (and hence their own notion of the current position) rather than using the one for the form's binding context.