Working with .NET DataSets

This chapter is from the book

Sams Teach Yourself ADO.NET in 21 Days

Learn More Buy

This chapter is from the book

This chapter is from the book 

Sams Teach Yourself ADO.NET in 21 Days

Learn More Buy

Manipulating Multiple DataSets

After a DataSet has been populated, you often need to use it in conjunction with other DataSet objects. In this section, you'll learn how to create a new DataSet based on an existing one as well as merge it with other DataSet objects.

Copying and Cloning

As you can see from Table 3.1, the DataSet object supports both Copy and Clone instance methods. Neither method is overloaded or accepts any arguments; however, the former copies both the data and structure of the DataSet and returns a new DataSet, whereas the latter copies only the structure.

As you might imagine, you should use the Copy method when you need to maintain the original DataSet and pass a copy to a process that may modify it. For example, Listing 3.4 shows an AddCriteria method that is passed in a DataSet and that then copies the DataSet and adds to it a new DataTable. The result is that the original DataSet remains unaffected, and a new DataSet with the new table will be returned from the method.

Listing 3.4 Copying a DataSet. This method copies and then alters the new DataSet.

Public Function AddCriteria(ByVal ds As DataSet) As DataSet

 Dim criteria As DataTable
 Dim dsCopy As DataSet

 dsCopy = ds.Copy

 criteria = dsCopy.Tables.Add("Criteria")

 ' Define the columns
 With criteria
  .Columns.Add("ISBN", GetType(String))
  .Columns.Add("Title", GetType(String))
  .Columns.Add("Author", GetType(String))
  .Columns.Add("PubDate", GetType(Date))
  .Columns.Add("CatID", GetType(System.Guid))
 End With

 Return dsCopy

End Function

The Clone method works similarly but copies only the structure of the DataSet. You can use it, for example, to create a DataSet that will hold similar information but is not yet populated, as shown in the following code snippet:

Dim samsTitles As DataSet

samsTitles = titles.Clone()

In this case, titles is assumed to be a DataSet that might or might not have been previously populated with titles. However, the new DataSet will contain only Sams titles, so its structure can be populated by cloning titles.

Although you can use the Copy method to copy an entire DataSet, it is usually more important to create a new DataSet based on a subset of the rows from an existing DataSet. This can be easily done using the GetChanges method. This overloaded method is exposed by both the DataSet and DataTable objects. It returns either a new DataSet or DataTable that includes all the changes made to the object since the last time AcceptChanges was invoked, or the set of changes based on one or more of the five values from the DataRowState enumeration.

For example, Listing 3.5 is a template that shows how a client application could use the GetChanges method to create a new DataSet that contains only the rows that have been modified by the user and then send those changes to a data access object.

Listing 3.5 Filtering rows. This method filters the rows that are sent to the data access object using the GetChanges method.

Public Sub SaveChanges(ByVal ds As DataSet)

 Dim dsErrors As DataSet

 If ds.HasChanges(DataRowState.Modified) Then

  Try
   Dim bus As New DAObject()

   ' Call the business object
   dsErrors = bus.Update(ds.GetChanges(DataRowState.Modified))

  Catch e As DAException
   ' Handle exception here
  End Try

 End If
End Sub

Note that in Listing 3.5, the HasChanges method of the DataSet object is also overloaded and can be passed a value from the DataRowState enumeration. In this case, it checks to see whether any rows have been modified and if so, instantiates the data access object and calls its Update method. The Update method accepts a DataSet that is created and populated on the fly with only changed data using the GetChanges method. Using this technique increases efficiency because passing only changed rows to the data access object results in less data being copied between tiers in your application. After updating the underlying data source (typically using a data adapter), the data access object may throw its own exception if an error occurs. It may, alternatively, pass back a DataSet object that contains the rows that caused errors or perhaps the original DataSet with values calculated by the data source (for example, primary key values or computed columns). You'll learn more about the GetChanges method and its usefulness on Day 5.

Merging DataSets

In addition to creating subsets of a DataSet, you can also merge the contents of two DataSet objects using the overloaded Merge method. This is particularly useful when you want to retrieve data to the client in smaller chunks or merge updated data returned from a middle-tier object with existing data on the client.

The Merge method can merge an array of DataRow objects, a DataTable object, or an entire DataSet into the current DataSet, defined as the one on which the method is called. Along the way, the method accepts optional arguments that specify whether rows from the merged data overwrite changes made to the current rows (the default) and what action to take if the schema of the merged rows doesn't match that of the current DataSet. To give you a feel for how the Merge method works, consider the code in Listing 3.6.

Listing 3.6 Merging a DataSet. This code merges several DataSets together using various overloaded signatures.

Dim books As DataSet
Dim books1 As DataSet
Dim books2 As DataSet
Dim books3 As DataSet

books = GetTitles("Sams")

books.Tables(0).Rows(0).Item("Description") = "Interesting book"

books1 = GetTitles("Sams")

books.Merge(dsBooks1, True)

books2 = GetTitles("IDG")

books.Merge(dsBooks2.Tables(0).Select("Author = 'Krumm, Rob'"))

books3 = GetTitles("Wrox")
books3.Tables(0).Columns.Add(New DataColumn("Pre-release", _
 GetType(System.Boolean)))

books.Merge(dsBooks3, False, MissingSchemaAction.Add)

Although the code in Listing 3.6 is contrived, it provides a good overview of how the Merge method works. First, assume that the GetTitles method returns a DataSet populated with the titles for the publisher passed in as the argument. In this case, the code first populates the books DataSet with the 44 titles for Sams in the database. The code then modifies the Description column of the first book to a String value in the next statement before retrieving the Sams titles again into a different DataSet called books1. The Merge method of books is then called and is passed both the DataSet to merge with books and an argument that indicates that previous changes to books will be preserved. Because the two DataSets contain the same set of primary keys, the Merge method will compare the key values and overlay the new data from books1 onto the rows in books while preserving any modified data in books. If the second argument were set to False, the rows from dsBooks1 would overwrite the books data completely. The end result is that books (or, more appropriately, the DataTable in books) still contains 44 rows, one of which has been modified.

The code then retrieves the 22 titles from IDG into books2 and then uses the Select method to merge the returned array of DataRow objects into books. In this case, the author used in the filter expression has authored one book, and so books will now contain 45 rows.

Finally, the 49 Wrox titles are retrieved and placed in books3. In this case, the DataTable that contains the Wrox titles is modified to include a new Boolean column called Pre-release. When the Merge method is called to merge the Wrox titles into books, the third argument specifies the action to take if the Merge method finds that the schemas differ. The default, as is specified here, is to simply add the new column to the existing schema using MissingSchemaAction.Add. In cases where the data being merged comes from a trading partner or other organization, you might want to be alerted to the fact that the schemas do not match. To do so, use the MissingSchemaAction.Error value and a DataException will be thrown.

Handling Merge Errors

Merging data can cause errors to occur in two different ways. First, as you'll learn tomorrow, a DataSet can contain constraints including primary and foreign keys. Just as in a relational database, these constraints can be violated as data is merged into a DataSet that already contains data. For example, if data is loaded into a table that has a foreign key constraint on another table in the DataSet, and one or more rows do not contain valid foreign key values, the constraint will be violated.

During the merge process, the constraints are disabled and then re-enabled at the end of the merge. At that time, if the constraints can't be set due to errors, a ConstraintException will be thrown and the EnforceConstraints property will be reset to False, and the rows that contained errors are marked as such. Of course, if the EnforceConstraints property is set to False before the Merge method is called, the constraints are not re-enabled and so no errors will occur until the property is set to True.

Secondly, because the Merge method uses the primary key information in the tables being merged to match up rows that should be merged, the primary keys must be identical in the tables. If the primary keys differ in the number of columns that make up the key, an ArgumentException will be thrown. However, if the key length is the same but the columns differ, a ConstraintException will be thrown. In both cases, the MergeFailed event shown in Table 3.1 also will be fired. To capture the MergeFailed event, you can use the C# event handling syntax you learned yesterday or dynamic event handling syntax in VB as shown in Listing 3.7.

Listing 3.7 Adding an event handler. This listing shows how to add a handler for the MergeFailed event.

AddHandler dsBooks.MergeFailed, AddressOf HandleMergeErrors

Try
 books.Merge(books1)
Catch e As ArgumentException
 ' Key lengths are different
Catch e1 As ConstraintException
 ' Keys have different columns
End Try


Private Sub HandleMergeErrors(ByVal sender As Object, _
 ByVal e As MergeFailedEventArgs)
 ' Called before Catch block is entered above
 Console.WriteLine(e.Conflict)
 Console.WriteLine(e.Table.TableName)
End Sub

In Listing 3.7, the AddHandler statement in VB .NET is used to hook the MergeFailed event of the books DataSet object to the delegate returned by the AddressOf operator. The delegate then points to the HandleMergeErrors method that accepts the standard object that produced the error (in this case, the DataSet) and an object of type MergeFailedEventArgs. The MergeFailedEventArgs object exposes the Conflict and Table properties that can then be inspected to return the particular error message and the table in which the error occurred, respectively.