The DataSet

The DataSet

The DataSet is often described as an in-memory "cache" of relational data. Typically, the information in the DataSet closely resembles the raw data in the underlying data source. As the data source changes, it becomes more work to insulate the client against these changes and to ensure that the DataSet is still using the expected field names, data types, and data representations. The DataSet provides tools that make all of this possible, but they won't help if you don't realize all the assumptions that the client may make about the data in a DataSet. Worst of all, if the client uses the wrong table or column names, the mistake won't appear at compile time, because ADO.NET can't verify the string-based lookup on a DataSet. Instead, it will lead to a potentially frustrating run-time error.

The DataSet also has some remarkable advantages. Most important, the DataSet is a near-universal language for exchanging data with .NET and third-party clients:

• The DataSet is a core part of the .NET Framework. That means that any .NET client can interpret a DataSet. You don't need to distribute and add a reference to a proprietary assembly.

• The DataSet comes equipped with the capability to serialize itself to XML, which makes it easy to store a DataSet in a file, transmit it to a remote object, and even exchange the information with a client written in another language.

The DataSet also provides some advanced functionality that would be difficult to implement in your own custom class:

• The DataSet enables you to easily navigate through collections of data. You can even add DataRelation objects to allow the client to browse through master-detail relationships.

• The DataSet tracks changes. This allows the client to receive a DataSet, make a batch of changes, and submit them back to a service provider object, which then performs the updates. Without a DataSet, the client must call the remote object repeatedly, once for each update.

The DataSet uses the collection-based structure shown in Figure 3-3.

Figure 3-3. The DataSet

Creating a DataSet

To fill the DataSet, you need to use a special bridge called a data adapter. The data adapter is a provider-specific object that has two roles in life. First of all, you use it to retrieve the results from a query and insert them into a DataTable object. If you want to update the data source, you use the data adapter again. This time it examines the contents of a DataTable and executes the required SQL commands to insert, delete, and modify the original records.

When creating a data adapter, you can use a constructor that accepts a command object, as shown in the following code:

 Dim Sql As String = "SELECT * FROM Customers" Dim con As New SqlConnection(ConnectionString) Dim cmd As New SqlCommand(Sql, con) Dim Adapter As New SqlDataAdapter(cmd) 

This command is placed in the SelectCommand property of the data adapter. Every data adapter can store references to four types of commands. It uses the Delete, Insert, and Update commands when changing the data source to correspond with DataTable changes, and the Select command when retrieving results from the data source and filling a DataTable. Figure 3-4 shows the data adapter's structure.

Figure 3-4. The data adapter

Using the data adapter is very straightforward. To fill a DataTable, you use the Fill method of the data adapter (for example, SqlDataAdapter.Fill). This method takes two parameters: a reference to the DataSet and the name of the table. If the named DataTable doesn't already exist in the DataSet, it will be created automatically.

 ' Create a new DataTable object named "Customers" in the DataSet ds. Adapter.Fill(ds, "Customers") 

If you want to fill multiple tables in a DataSet, you need to use the Fill method multiple times, once for each table. You'll also need to modify the SelectCommand property to enter the new query before each operation.

 ' Create a Customers table with the assigned command object. Adapter.Fill(ds, "Customers") ' Modify the command. Adapter.SelectCommand.CommandText = "SELECT * From ORDERS" ' Create a second Orders table in the same DataSet. Adapter.Fill(ds, "Orders") 

In a preceding example, we used an ArrayList of CustomerDetails objects with the GetCustomers method of a simple database component. This method can be tweaked to use a DataSet instead of the custom objects, as shown in Listing 3-10.

Listing 3-10 Returning a DataSet

 Public Function GetCustomers() As DataSet     Dim Sql As String = "SELECT * FROM Customers"     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand(Sql, con)     Dim Adapter As New SqlDataAdapter(cmd)     Dim ds As New DataSet()     Try         con.Open()         Adapter.Fill(ds, "Customers")     Catch Err As Exception         ' Use caller inform pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         con.Close()     End Try     Return ds End Function 

One interesting fact about this code is that only two statements need to be monitored for errors: opening the connection and filling the DataSet with the data adapter. The Fill method encapsulates all the logic needed to loop through the result set in a single line of code.

The client code requires no change. The DataSet can be bound to a Windows or an ASP.NET control just as easily as the ArrayList was in our earlier example. Listing 3-11 shows a data bound form with a DataSet.

Listing 3-11 A data bound form with a DataSet

 Public Class BoundForm     Inherits System.Windows.Forms.Form     ' (Windows designer code omitted.)     Private Sub BoundForm_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         Dim DB As New CustomerDB()         DataGrid1.DataSource = DB.GetCustomers().Tables(0)     End Sub End Class 

Note

In the GetCustomers example, a whole DataSet is returned even though it contains only one DataTable. This is the standard pattern. If you create a function that returns just a DataTable, it won't be able to work in a .NET XML Web service. The XML Web service serialization mechanism only supports the full DataSet object.

DataSet Indirection Through Column Mappings

One of the drawbacks to using a DataSet is that it strips away a layer of indirection between the data source and the client code. If you use a custom object such as CustomerDetails, for example, the data access code automatically assumes the responsibility of mapping the data source field names to the expected property names. Therefore, EmailAddress becomes Email, FullName becomes Name, and CustomerID becomes ID. The data class might also contain computed fields, adjust formatting, or replace a constant value in a field with a more descriptive enumerated value.

You can perform the same tasks by manually processing the DataSet after you fill it, but this makes it extremely difficult to update the data source based on DataSet changes. You would probably be forced to write additional code to examine the DataSet and perform the updates manually. This task is complicated, tedious, and prone to error.

Fortunately, ADO.NET provides one feature that can help you: column mappings. Column mappings are set at the data adapter level. They link a field in the data source with a differently named field in the DataSet. Listing 3-12 shows code that configures the data adapter to create a DataSet using the column names Email, Name, and ID.

Listing 3-12 Indirection with column mappings

 ' Create the new mapping, and attach it to the data adapter. Dim CustomerMap As DataTableMapping CustomerMap = Adapter.TableMappings.Add("Customers", "Customers") ' Define column mappings. CustomerMap.ColumnMappings.Add("CustomerID", "ID") CustomerMap.ColumnMappings.Add("FullName", "Name") CustomerMap.ColumnMappings.Add("EmailAddress", "Email") ' Fill the DataSet. Adapter.Fill(ds, "Customers") 

The great advantage of column mappings is that they are bidirectional. In other words, the data adapter knows not only how to translate the data source information into the appropriate columns when performing a query but also how to modify the corresponding columns when performing an update.

You could explore other possible techniques to make the DataSet more generic and less error-prone. One idea is to create a separate class that stores a list of mappings between column names and field indexes. Alternatively, you can use a custom DataSet-derived class that adds an enumeration for table names and columns.

 Public Class CustomerDS     Inherits System.Data.DataSet     Public Enum Fields         ID = 0         Name = 1         Email = 2         Password = 3     End Enum End Class 

Because this class derives from DataSet, you can use it in exactly the same way with the Fill method of the data adapter. Just modify the GetCustomers code to create the correct type of object:

 Dim ds As New CustomerDS() 

The client can then use the enumerated values by name, ensuring that any mistakes are caught at compile time:

 ' Display the name of the first record. MessageBox.Show(ds.Tables(0).Rows(0)(CustomerDS.Fields.Name)) 

As another benefit, index-based lookup is faster than name-based lookup. However, the obvious disadvantage is that you have now created a new custom class, and the client requires a reference to the appropriate assembly to use this type in its code. In other words, by extending the base .NET Framework classes, you might complicate deployment.

Note

ADO.NET also enables you to create strongly typed DataSets using the Visual Studio .NET IDE or the xsd.exe utility included with the .NET Framework. Typed DataSets are DataSet-derived classes that allow the client to access fields using property names (as in CustomerRow.CustomerID) rather than a string-based lookup (CustomerRow("CustomerID"). However, typed DataSets introduce all the problems of custom objects namely, you need to distribute the appropriate assembly to every client. That means that typed DataSets are most useful on the server side. If you try to use typed DataSets with clients in a distributed system, you will only add a new distribution headache.

Navigating the DataSet

The DataSet uses an elegant collection-based syntax whereby the programmer steps through DataTable and DataRow objects. For example, here's the code to step through the customer DataSet and add each customer's name to a list control:

 Dim Row As DataRow For Each Row In ds.Tables(0).Rows     lstNames.Items.Add(Row("Name")) Next 

The code in Listing 3-13 shows every field in every row of every table in a DataSet. It's written as a simple console application, but you could adapt it to display information in a Windows Forms page.

Listing 3-13 Navigating all tables and fields in a DataSet

 Dim Table As DataTable For Each Table In ds.Tables     Console.WriteLine(Table.TableName)     Dim Row As DataRow     For Each Row In Table.Rows         Console.WriteLine()         Dim Field As Object         For Each Field In Row.ItemArray             Console.Write(Field.ToString() & " ")         Next     Next Next 

Checking for Deleted Rows

In Listing 3-13, the code iterates through all the rows. If you've deleted some of the rows by using the DataRow.Delete method, however, this code can cause a problem. The catch is that deleted rows don't actually disappear from the DataTable.Rows collection if they did, ADO.NET wouldn't be able to delete them from the data source when you reconnected later and applied changes. Instead, the row remains but the DataRow.RowState property is set to Deleted. If you attempt to read a field value from a DataRow that is in this state, an exception is thrown.

To get around this idiosyncrasy, you can explicitly check the state of a row before you display it, as follows:

 Dim Row As DataRow For Each Row In ds.Tables(0).Rows     If Row.RowState <> DataRowState.Deleted         lstNames.Items.Add(Row("Name"))     End If Next 

When you successfully apply these changes to the data source (by reconnecting and using the data adapter's Update method), the rows are removed from the DataTable.Rows collection because they are no longer needed.

Relations

You can also navigate through a DataSet by using table relations. One possible use of this technique is to allow a client to show a master-detail view of the information, without needing to query the data source more than once.

To use a relation in a DataSet, you need to add a DataRelation object that represents the relationship to the DataSet.Relations collection. There is currently no way to retrieve this information directly from the data source. That means that even if a foreign key relationship is defined in your database, you need to define it manually in your DataSet to use it. Listing 3-14 illustrates the concept with a GetCustomersAndOrders method.

Listing 3-14 Returning a DataSet with a relation

 Public Function GetCustomersAndOrders() As DataSet     Dim SqlCustomers As String = "SELECT * FROM Customers"     Dim SqlOrders As String = "SELECT * FROM Orders"     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand(SqlCustomers, con)     Dim Adapter As New SqlDataAdapter(cmd)     Dim ds As New DataSet()     Try         con.Open()         Adapter.Fill(ds, "Customers")         Adapter.SelectCommand.CommandText = SqlOrders         Adapter.Fill(ds, "Orders")     Catch Err As Exception         ' Use caller inform pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         con.Close()     End Try     ' Define the relationship is closed. Note that this step is performed     '  after the connection is closed, because it doesn't     '  use any information from the data source.     Dim ParentCol, ChildCol As DataColumn     ParentCol = ds.Tables(0).Columns("CustomerID")     ChildCol = ds.Tables(1).Columns("CustomerID")     Dim Relation As New DataRelation("CustomersOrders", _                                      ParentCol, ChildCol)     ' Add the relationship to the DataSet.     ' This is the point where it takes effect, and an exception will     '  be thrown if the existing data violates the relationship.     ds.Relations.Add(Relation)     Return ds End Function 

The client can then move from a parent row to the child rows or from a child row to the parent row by using the DataRow.GetChildRows and DataRow.GetParentRow methods.

 Dim ParentRow, ChildRow As DataRow For Each ParentRow In ds.Tables("Customers").Rows     ' Process parent row.     For Each ChildRow In ParentRow.GetChildRows("CustomersOrders")         ' Process child row.     Next Next 

Listing 3-15 shows an implementation of this idea that fills a TreeView, which appears in Figure 3-5. The first level of nodes represents customers, and every customer node contains child nodes that represent orders.

Listing 3-15 Displaying related rows in a TreeView

 Public Class RelationForm     Inherits System.Windows.Forms.Form     ' (Designer code omitted.)     Private Sub RelationForm_Load(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load         Dim DB As New CustomerDB()         Dim ds As DataSet = DB.GetCustomersAndOrders()         Dim ParentRow, ChildRow As DataRow         Dim ParentNode, ChildNode As TreeNode         For Each ParentRow In ds.Tables("Customers").Rows             ParentNode = TreeView1.Nodes.Add(ParentRow("FullName"))             For Each ChildRow In _              ParentRow.GetChildRows("CustomersOrders")                 ParentNode.Nodes.Add(ChildRow("OrderID"))             Next         Next     End Sub End Class 

Figure 3-5. Using relations to fill a TreeView

After you define the relationship in the DataSet, you're bound by the same rules that govern any relationship: you can't delete a parent record if there are one or more linked child rows, and you can't create a child that references a nonexistent parent. Therefore, you might be prevented from adding a valid relationship to your DataSet if it contains only part of the data in the data source. One way to get around this problem is to create a DataRelation without creating the corresponding constraints. Simply use one of the Data­Relation constructors that accepts the Boolean createConstraints parameter, as shown here:

 ' Don't create any DataSet constraints. ' The DataRelation will be used for navigation only, ' not to enforce relational integrity. Dim Relation As New DataRelation("CustomersOrders", _                                  ParentCol, ChildCol, false) 

Embedding Data in a Control

Even though the TreeView doesn't support data binding, it's easy to populate it using DataRelation objects. You can extend this principle by embedding data in the control, using the Tag property supported by most .NET controls. The Tag property isn't used by .NET; instead, it's a container in which you can store any information you need in order to identify a control. For example, you can use the Tag property in a TreeNode object to track the unique ID that identifies the record in the data source. If the user asks to delete or modify this record, you can invoke the DeleteCustomer or UpdateCustomer method using the ID.

The Tag property isn't limited to simple numeric or text information. You can use it to store an entire object, including the DataRow that represents a given item. Consider Listing 3-16, for example, which stores the DataRow object for every order item.

Listing 3-16 Storing a DataRow in a TreeNode

 Private Sub RelationForm_Load(ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles MyBase.Load     Dim DB As New CustomerDB()     Dim ds As DataSet = DB.GetCustomersAndOrders()     Dim ParentRow, ChildRow As DataRow     Dim ParentNode, ChildNode As TreeNode     For Each ParentRow In ds.Tables("Customers").Rows         ParentNode = TreeView1.Nodes.Add(ParentRow("FullName"))         For Each ChildRow In _          ParentRow.GetChildRows("CustomersOrders")             Dim NewNode As New TreeNode()             NewNode.Text = ChildRow("OrderID")             NewNode.Tag = ChildRow             ParentNode.Nodes.Add(NewNode)         Next     Next End Sub 

When the user clicks a node, the TreeView.AfterSelect event handler (shown in Listing 3-17) retrieves this DataRow object and uses the field information to display the order date in a nearby text box, as shown in Figure 3-6.

Listing 3-17 Retrieving a DataRow from a TreeNode on selection

 Private Sub TreeView1_AfterSelect(ByVal sender As System.Object, _  ByVal e As System.Windows.Forms.TreeViewEventArgs) _  Handles TreeView1.AfterSelect     Dim SelectedRow As DataRow = CType(e.Node.Tag, DataRow)     If Not (SelectedRow Is Nothing) Then         ' We must convert the date to a DateTime object in order to         ' filter out just the date.         lblDisplay.Text = "This item was ordered on " & _          DateTime.Parse(SelectedRow("OrderDate")).ToShortDateString()         lblDisplay.Text &= Environment.NewLine         lblDisplay.Text &= "This item was shipped on " & _          DateTime.Parse(SelectedRow("ShipDate")).ToShortDateString()     End If End Sub 

Figure 3-6. Using embedded data objects in a control

Will this approach waste extra memory? It depends on how your application is designed. If you're already storing a long-term reference to the DataSet (for example, in a form-level variable), the Node.Tag property just contains a reference to the appropriate DataRow object in the DataSet, and no extra memory is used. Keep in mind that retaining information on the client is always better than retrieving it multiple times. Generally, Windows clients have plentiful memory for storing the data you need. The real costs are incurred when transferring information over the network or when using server resources to execute a time-consuming query.

Updating from a DataSet

The DataSet allows clients to record an entire batch of changes and commit them in a single operation by using the Update method of the data adapter. You can use this batch update ability to extend the CustomerDB class, adding a Submit­BatchChanges method, as demonstrated in Listing 3-18.

Listing 3-18 Submitting batch changes

 Public Function SubmitBatchChanges(ds As DataSet) As DataSet     ' We need to re-create the adapter with the original command object     ' and Select query. With that information, the SqlCommandBuilder      ' can generate the other data adapter commands.     Dim Sql As String = "SELECT * FROM Customers"     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand(Sql, con)     Dim Adapter As New SqlDataAdapter(cmd) 

     ' Generate insert, delete, and update commands.     Dim Builder As New SqlCommandBuilder(adapter)     Adapter.InsertCommand = Builder.GetInsertCommand()     Adapter.UpdateCommand = Builder.GetUpdateCommand()     Adapter.DeleteCommand = Builder.GetDeleteCommand()     Try         con.Open()         Adapter.Update(ds, "Customers")     Catch Err As Exception         ' Use caller inform pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         con.Close()     End Try     ' Return the updated DataSet.     Return ds   End Function 

When you call the Update method, the data adapter steps though the rows. For every changed row, it issues an update command. For every new row, it issues an insert command, and for every deleted row, it issues a delete command. This magic is possible because every DataRow actually stores information about its current value and the original value. The original value is the value that was retrieved from the data source (or the value that was committed the last time changes were applied with the Update method). The data adapter uses this information to locate the original row. It also uses the DataRow.RowState property to determine what action needs to be performed. For example, rows that must be inserted have the state DataRowState.Added.

After the change is applied, all the original values in the DataSet are updated to match the current values and the state of each row is set to DataRowState.Unchanged. This updated version of the DataSet is then sent back to the client. The client must use this updated version! Otherwise, the next time the client submits the DataSet for an update, the data adapter will try to reexecute the same changes.

The SubmitBatchChanges method shown earlier is not ideal for most distributed applications because it returns the entire DataSet. I'll introduce some more advanced optimizations in Chapter 12.

Note

The code in Listing 3-18 uses the helpful command builder. It examines the query that was used to fill the data adapter and generates the other commands required for inserting, deleting, and updating records. These commands closely resemble the direct Update, Delete, and Insert examples we used before except that they use the Where clause stringently, attempting to match every field. That means that if another user has changed any part of the record, the update will fail.

Update Issues

On paper, this approach looks phenomenal it consolidates changes into a single step and reduces network traffic and database hits because changes are performed en masse. In fact, many .NET books focus exclusively on the DataSet and this batch change technique when describing ADO.NET, assuming that this is the best and only approach.

However, ADO.NET conceals an unpleasant secret: this remarkable new technique is not suitable for all distributed applications. In fact, if you study Microsoft's platform samples, you'll have trouble finding an example in which the DataSet is used to retrieve information, let alone to update a data source. Why is that? Quite simply, the DataSet increases complexity and the potential for error if you don't code carefully. It also reduces performance, unless you replace the autogenerated command builder logic with your own custom command objects for inserting, deleting, and updating records.

The following sections discuss some of these problems. Taken together, these problems are far from trivial. However, if you have a client that connects only intermittently and needs to perform a significant amount of work with multiple tables, using the disconnected DataSet may be the best option.

The DataSet Is Large

With the current implementation of the SubmitBatchChanges method shown in Listing 3-18, the client is encouraged to submit the entire DataSet, which could contain dozens of untouched rows. Therefore, even though database operations are performed less frequently, the network could face an overall slowdown as more information than is required is being sent over the wire to a remote database component. The client can code around this problem by submitting only the changed row using the DataSet.GetChanges method. However, the server can't ensure that the client will take this approach.

Automatically Generated Commands Perform Poorly

By default, the Command Builder creates commands that attempt to select a row by matching every field. Consider the following example:

[View full width]
 "DELETE FROM Customers WHERE CustomerID=5 AND FullName='Matthew MacDonald' AND EmailAddress='[e-mail]' AND ..."  

This defensive approach won't perform as well as just selecting the record by its unique ID or using a stored procedure. And although you can customize the data adapter's update logic, it takes more work.

Concurrency Errors Are Common

Every enterprise application needs to consider concurrency problems, which can occur when two clients read and try to update the same information. Concurrency problems can affect any database code, but the problem is particularly common with the DataSet because the interval of time between retrieving the data and applying the changes is usually longer. Unfortunately, a single error can derail an entire batch of changes, and the problem won't be discovered until long after the user has made the change, when it might no longer be possible to correct it.

Update Problems Occur with Relationships

Changes aren't necessarily applied in the order you made them. That means that the data adapter might try to add a child row before adding the parent it refers to, which would generate an error from the data source if it has foreign key constraints defined. You can mitigate these problems to some extent by using the DataSet.GetChanges and DataTable.GetChanges methods to perform operations in the friendliest order.

Listing 3-19 illustrates how you might handle a DataSet with a parent Customers table and a child Orders table. Note that two different data adapters are used because different commands are needed to update each table.

Listing 3-19 Committing updates in phases

 ' Split the DataSet ds. Dim dsInserted, dsModified, dsDeleted As DataSet dsInserted = dtParent.GetChanges(DataRowState.Added) dsModified = dtParent.GetChanges(DataRowState.Modified) dsDeleted = dtParent.GetChanges(DataRowState.Deleted) ' Add records to the parent table. adapterCustomer.Update(dsInserted, "Customers") adapterCustomer.Update(dsModified, "Customers") ' Now that insertions have been applied, this is a good place to ' commit all the changes in the child table. adapterOrder.Update(ds.Tables("Orders")) ' Now that all child records are removed, it's safe to try to ' remove parent records. adapterCustomer.Update(dsDeleted, "Customers") 

Order of updates isn't the only problem with relationships. Another problem occurs with autogenerated IDs. Suppose, for example, that you want to add a new parent and a new child. The child needs to refer to the parent using its unique ID. But because this ID is generated by the data source when the record is inserted, it won't be available until the update has been performed, even though the parent has been created in your disconnected DataSet. Unfortunately, there are only two ways around this headache: update the data source with new parent records before you add any child records, or carefully customize your data logic.

Validation Is Compromised

Generally, DataSet validation is performed by handling DataTable events such as ColumnChanging, RowChanging, and RowDeleting. These events fire as values are being modified. Unfortunately, it's up to the client to handle these events and disallow invalid values. By the time the entire batch of changes is submitted to the data component, it's hard to track inappropriate changes. With custom components, on the other hand, you can add code directly in your data objects to disallow certain values. It's also much easier to validate a single Customer­Details object than a full DataSet.

Handling Errors

If you must use the batch update technique with a DataSet, the bare minimum is to catch any errors and report them to the user. Unfortunately, the standard Try/Catch block suffers from one serious problem: as soon as a single error occurs, the entire batch update is cancelled, potentially preventing other valid updates. To deal with this problem more flexibly, you need to handle the RowUpdate event or set the ContinueUpdateOnError property.

One common approach is to handle the RowUpdate event of the data adapter. This event provides a RowUpdatedEventArgs class that informs you if an error was encountered, indicates the relevant DataRow, and explains whether the attempted command was an Insert, Delete, or Update. If an error has occurred, you can then decide whether to allow this error to derail the entire update process (the default), or allow the data adapter to continue moving through the DataTable attempting updates. You signal that the update should continue by setting the RowUpdatedEventArgs.Status property to SkipCurrentRow when the RowUpdate event fires and an error is detected.

However, this technique is less suitable to a middle-tier component, which can't notify the user directly about any problems. Instead, a more useful approach is to set the data adapter's ContinueUpdateOnError property to True. If an error is encountered, the data adapter sets a text error message in the corresponding DataRow.RowError property. You can then use the Data­Table.GetErrors method to retrieve all the DataRow objects that caused update errors and return this collection to the client, who will then notify the user or take alternative action. You'll put this technique to use in Chapter 12.