DataSet Object Model and Designer

Many ADO.NET applications have one or more dataset objects at their core . This section begins with an overview of the DataSet object model. It also includes a short example of how to specify multitable datasets with a graphical designer. The section concludes with a collection of sample code that illustrates the breadth of capabilities the DataSet object model provides.

Overview

Datasets are multifaceted data stores that characterize the flexibility and genius of the .NET Framework data access model. You can think of the dataset as a mini-database in memory. A dataset is not just a flat array of joined columns from one or more data sources. A dataset can contain one or more tables. These tables can come from a single database, multiple databases, or even a mixture of sources, such as a saved XML document and a database. You can also programmatically create tables inside a dataset and populate them with data that a user enters from a form or some other source. In addition to containing multiple tables, a dataset can contain objects representing the structure of the data within and between tables, a capability that can be very convenient . For example, the DataSet object s Relations property offers a built-in way of discovering the tables participating in a relationship. The Relations property can point at a relationship between a parent table and a child table. Your applications can use this parent-child relationship to automatically return the rows in a child table that match a row in a parent table, such as the line items belonging to an order or the orders belonging to a customer.

Figure 7-6 presents an overview of the DataSet object model. As you can see, a dataset holds two collections ”one for tables and one for relationships between tables. The elements in the Tables collection property are DataTable objects. A DataTable object holds a tabular array of data as well as information describing the data, such as data types for the columns in a table. A DataTable object can hold all the columns and rows from an Access table or a subset of those columns or rows. You can also use this object to hold the column values in a query. The DataTable in a dataset can hold a snapshot of the contents of an Access database object. Use the OleDbDataAdapter object to synchronize the contents of the DataTable object with its Access database object counterpart .

Figure 7-6: The DataSet object model

You can access the contents of a DataTable object through its Rows collection property. The Rows collection contains DataRow elements. The column values within a row are available as an array. For example, the expression tbl1.Rows(0)(0) denotes the first column value in the first row for the DataTable that the tbl1 variable points at. The Columns collection property contains DataColumn objects. These objects contain metadata about the columns of a DataTable , such as the data types for columns, whether a column autoincrements, and whether a column must have unique values. Think of the Rows collection as corresponding to the Datasheet view of an Access table and the Columns collection as the counterpart to an Access table s Design view. A single table can expose itself from multiple perspectives.

The Constraints collection property denotes two types of constraints for a DataTable object. The UniqueConstraint object indicates that the values in a column of a table must be distinct from one another. You can create an instance of the UniqueConstraint object by setting the Unique property for a DataColumn object to True . The ForeignKeyConstraint object is the other type of item in the Constraints collection of a DataTable . This constraint specifies how updates and deletes to one DataTable affect another related DataTable (for example, whether or not they cascade). You can add a ForeignKeyConstraint object directly with its own constructor ( New method) or indirectly by adding a DataRelation object to the Relations collection for a DataSet object.

The Relations collection property for a DataSet object contains DataRelation objects. This type of object specifies the parent-child relationships between the DataTable objects within a dataset. Specifying DataRelation objects between tables can speed your access to rows from a child table that match the currently selected row from a parent table.

You can use a graphical designer (the XML Designer) to build datasets in a Visual Studio .NET project based on an Access database. Choose Project, Add New Item. Then, select DataSet from the Templates pane of the Add New Item dialog box. The dialog box automatically names successive datasets: Dataset1.xsd, Dataset2.xsd, and so on. However, you can override this default naming convention with a new entry in the Name box. The XML Designer opens with an empty dataset and displays the dataset name , such as Dataset1.xsd, on its tab. Click the Server Explorer link on the designer if Server Explorer is not already visible. Then, expand the Data Connections folder, and within that folder, expand the connection for the database that you want to serve as the basis for one or more DataTable objects in your dataset. Drag over the tables and queries from the connection to the XML Designer. Both tables and queries from a connection pointing at an Access database can serve as the database source for DataTable objects within the dataset. The graphical design shows the column names along with their data types in a table headed by the table name.

The first paragraph in this Overview section stated that specifying a relationship between two DataTable objects allows you to designate a parent-child relationship between a pair of data sources, such as two DataTable objects based on the Orders and Order Details tables in the Northwind database. Designating this parent-child relationship graphically is beyond the scope of this chapter because it involves more familiarity with the XML Designer used to create datasets than this chapter assumes. The Chapter Appendix, XML for Visual Studio .NET Access Developers, reviews the use of the XML Designer in detail, including specifying the Relations property for a dataset. I personally find using Visual Basic .NET code a more convenient way to specify and use a parent- child relationship between a pair of DataTable objects. The next section, Demonstrating Code Syntax, includes an introductory sample illustrating coding techniques for creating and processing a parent-child relationship. The Processing Parent-Child Relationships section in Chapter 9 fully explores coding issues for creating and using parent-child relationships.

Demonstrating Code Syntax

Form6 in the OleDbObjectSamples projects illustrates various design principles for using datasets with the OLE DB .NET Data Provider. Figure 7-7 shows Form6 after it initially opens. The Text property values for the buttons indicate the order in which to click the buttons . For example, you click the top button first. Then, you click the button immediately below it. Next, go back and click the top button again. In addition, you must click the third button before clicking the last button because the event procedure for the third button creates a DataTable object that the event procedure for the last button references.

Figure 7-7: The sample used to demonstrate dataset coding conventions requires that the user click the buttons in the right order.

The module behind Form6 has six procedures: a form load event procedure, four click event procedures for the buttons, and the familiar ConnectToNorthwind procedure. If you want a form in a Windows application to show data in its controls when the form opens, you must include in the form load event procedure the code to connect to a data source and populate a dataset that is bound to form controls or at least serves as a source for unbound form controls. This form does not show data in a form control. Nevertheless, the form load event procedure illustrates one approach to filling a DataTable object in a dataset. The variable pointing at the dataset has the name das1 , and Orders is the name of the DataTable . The form load event procedure concludes by formatting the buttons on the form. The click event procedures for Button1 through Button4 have the following roles:

Display metadata about the table(s) in the das1 dataset associated with the module behind Form6 ( Button1 ).
Add a second DataTable object ( OrderDetails ) to the das1 dataset ( Button2 ).
Add a DataRelation object to the das1 dataset with the Orders DataTable as the parent for the OrderDetails DataTable ( Button3 ).

Calculate the total extended price for the child rows in the OrderDetails DataTable that match the currently selected row in the Orders DataTable ( Button4 ).

Note

This application illustrates the relevance of two of my favorite coding mottos: real programmers don t allow blanks in object names, and real programmers use arrays whenever necessary. Avoiding blanks in object names avoids all the odd programming conventions for dealing with spaces in object names. Arrays make sense in any situation where a process returns more than a single scalar value at a time. That s the case in the click event procedure for Button4 , which uses the GetChildRows method to extract the child rows matching a parent row. Each row can have multiple columns, and a set of child rows can have one or more rows. Using an array is more efficient than creating a new temporary dataset to hold the rows returned by the GetChildRows method.

Before the listing for the form load event procedure, you will see two module-level object declarations. One of these is for the das1 variable, which points at a dataset that holds DataTable objects for the application. This variable appears in the form load event procedure as well as all the click event procedures. The cnn1 variable appears in the form load event procedure and the click event procedure for Button2 as well as the ConnectToNorthwind procedure. Both the form load event procedure and the click event procedure need the cnn1 object to instantiate Command objects for defining result sets that they use to populate DataTable objects in the das1 dataset.

The form load event procedure starts by defining a Command object ( cmd1 ) that the procedure ultimately assigns to the SelectCommand property for the dap1 OleDbDataAdapter object. This assignment equips dap1 to populate a DataTable object ( Orders ) in the das1 dataset. The form load event procedure actually creates the DataTable object because the procedure fills the DataTable object for the first time. See the OleDbDataAdapter section earlier in this chapter for sample code and commentary on refilling an existing DataTable object in a dataset. After populating the das1 dataset, the procedure formats the buttons by specifying Text property values and designating Size and Left property settings.

The Button1_Click procedure illustrates how to iterate through the items in the Tables collection for a dataset. The MsgBox statement inside the loop displays the name, number of columns, and number of rows for each DataTable object in the Tables collection. If you click Button1 initially, the button s click event procedure will report metadata for a single table, the Orders DataTable object. If you click Button1 after clicking Button2 , the Button1_Click procedure reports metadata for the Orders and OrderDetails DataTable objects.

The Button2_Click procedure demonstrates an approach to adding a DataTable object ( OrderDetails ) to a dataset that already contains a DataTable object. The procedure parallels the form load event procedure steps for adding the Orders DataTable object. The main point to note is that the Button2_Click procedure uses a separate OleDbDataAdapter ( dap2 ). This is convenient because a DataAdapter object can have only a single value for the SelectCommand property. This property determines the data source that you use to fill a DataTable object. Although it is possible to dynamically assign this property at run time, you reduce the complexity of your code by using a second data adapter. Notice that the CommandText property for the cmd1 object in the procedure encloses the Order Details table name in square brackets ([]). The brackets are necessary because the Order Details table in the Access database includes a blank within its name. By naming the local DataTable OrderDetails , you avoid the requirement to specify the internal blank with an escape code (_x0020_).

The Button3_Click procedure shows you how to add a DataRelation object to a dataset. The DataRelation object specifies a parent-child relationship between the Orders DataTable object and the OrderDetails DataTable object, with the Orders DataTable object serving as the parent. The column of OrderID values in each DataTable object allows the .NET Framework to link the two DataTable objects. With a DataRelation object, you first define the object, and then you add it to the Relations collection for a dataset.

The Button4_Click procedure illustrates how to process the rows in a child DataTable object that match a current selection in a parent DataTable object. This sample selects a row in the Orders DataTable object, and the procedure goes on to compute and accumulate the extended price for each line item in the child DataTable object that matches the parent DataTable . The procedure begins by pointing tbl1 at the Orders DataTable object and tbl2 at the OrderDetails DataTable object. Next, the procedure declares several variables to hold the matching child rows and to loop through them. Then, the procedure invokes the GetChildRows method to return an array of DataRow objects. The ary_Rows variable points at the array. This array contains as many columns as the SelectCommand property for dap1 specifies and a separate row for each line item in the order specified by the currently designated row in the Orders DataTable object. The currently designated row is determined by the Rows method, and in this case is the second row in the Orders DataTable object, which has an index of 1 and corresponds to an OrderID value of 10249. The For Next loop iterates through the line items for this order. The expression within the loop computes the extended price for each matching row and accumulates the values in the dec1 variable. The procedure concludes by displaying a message box that shows the extended price and the OrderID for the currently selected row in the Orders DataTable .

 Dim das1 As New System.Data.DataSet() Dim cnn1 As _ System.Data.OleDb.OleDbConnection = _ ConnectToNorthwind() Private Sub Form6_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Create a Command object based on the Orders table. Dim str1 As String = _ "SELECT OrderID, OrderDate FROM Orders " Dim cmd1 As _ New System.Data.OleDb.OleDbCommand( _ str1, cnn1) Instantiate the dap1 DataAdapter object and assign the Command object to the SelectCommand property for the dap1 DataAdapter object. Dim dap1 As New _ System.Data.OleDb.OleDbDataAdapter() dap1.SelectCommand = cmd1 Fill the das1 DataSet object with the Orders table. dap1.Fill(das1, "Orders") Set Button Text property values. Button1.Text = "Click me first and third" Button2.Text = "Click me second" Button3.Text = "Click me fourth" Button4.Text = "Click me fifth" Size and position Button1. Button1.Size = New System.Drawing.Size(125, Button1.Height) Button1.Left = 85 Align and size other buttons relative to Button1. Button2.Size = _ New System.Drawing.Size(Button1.Width, Button2.Height) Button2.Left = Button1.Left Button3.Size = _ New System.Drawing.Size(Button1.Width, Button3.Height) Button3.Left = Button1.Left Button4.Size = _ New System.Drawing.Size(Button1.Width, Button4.Height) Button4.Left = Button1.Left End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Declare DataTable variable. Dim tbl1 As DataTable Iterate through the tables in das1 dataset. For Each tbl1 In das1.Tables MsgBox(tbl1.TableName & " has " & _ tbl1.Columns.Count.ToString & " columns" & _ " and " & tbl1.Rows.Count & " rows.", , _ "Metadata in Das1") Next End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Instantiate a new DataAdapter object. Dim dap2 As New System.Data.OleDb.OleDbDataAdapter() Instantiate cmd1 OleDbCommand object and assign SQL statement and connection in the constructor. Dim str1 As String = _ "SELECT OrderID, ProductID, UnitPrice, " & _ "Quantity, Discount FROM [Order Details]" Dim cmd1 As _ New System.Data.OleDb.OleDbCommand( _ str1, cnn1) Assign the Command object to the SelectCommand property for the dap2 DataAdapter object. dap2.SelectCommand = cmd1 Fill the das1 DataSet object with the OrderDetails table. dap2.Fill(das1, "OrderDetails") End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Define parent and child columns for a DataRelation object. Dim parentcol As DataColumn = _ das1.Tables("Orders").Columns("OrderID") Dim childcol As DataColumn = _ das1.Tables("OrderDetails").Columns("OrderID") Instantiate a new DataRelation object. Dim drl1 As DataRelation drl1 = New _ DataRelation("OrdersOrderDetails", _ parentcol, childcol) Add the DataRelation object to the Relations collection for das1. das1.Relations.Add(drl1) End Sub Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click Point DataTable variable at the parent table (Orders). Dim tbl1 As DataTable Dim drl1 As DataRelation = _ das1.Relations("OrdersOrderDetails") tbl1 = drl1.ParentTable Point another DataTable variable at the child table (Order Details). Dim tbl2 As DataTable = drl1.ChildTable Declare variables for looping through child rows. Dim drw1 As DataRow Dim ary_Rows() As DataRow Dim int1 As Integer Dim col1 As DataColumn Dim int2 As Integer Dim dec1 As Decimal Specify row in Orders table for which to accumulate extended prices for line items. drw1 = tbl1.Rows(1) Return array of child rows matching selected parent row. ary_Rows = drw1.GetChildRows(tbl1.ChildRelations(0)) Loop through child rows to accumulate extended price for line items. For int1 = 0 To ary_Rows.GetUpperBound(0) dec1 += ary_Rows(int1)(2) * _ ary_Rows(int1)(3) * (1 - ary_Rows(int1)(4)) Next Display result. int2 = drw1(0) MsgBox("Extended Price = " & _ dec1.ToString & " for Order ID " & _ int2.ToString & ".", , _ "Computed value from Child Table") End Sub Function ConnectToNorthwind() As System.Data.OleDb.OleDbConnection Designate string for connection to Northwind database. Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 += _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" Instantiate and assign the connection string to the Connection object. Dim cnn1 As New System.Data.OleDb.OleDbConnection() cnn1.ConnectionString = str1 Return the Connection object. Return cnn1 End Function