asp:Feature

LANGUAGES: VB.NET

ASP.NET VERSIONS: 1.x

Typed DataSets

Knowing the Strengths and Weaknesses Will Help You Avoid the Common Pitfalls

By Hilton Giesenow

In his article Efficient Coding With Strongly Typed DataSets , John Papa discusses the common practice of extending standard ADO.NET DataSets through auto-generated derived classes that wrap much of the complexity and inefficiencies one faces with raw DataSets (MSDN Magazine, December 2004). Papa s article highlights some of the benefits of this approach, as well as some of the caveats. However, there are a number of essential points that developers and architects need to be aware of both in going down this route and actually negotiating the path. This article covers various enhancements and additional benefits to this approach, as well as important caveats and potential pitfalls (and how they can be avoided) that are generally less well covered but are nonetheless important.

Before We Get Started

As any fan of DotNetRocks! will tell you, there are a number of individuals who are ardently against typed DataSets. Similarly (as is usually the case), there are a number of others who, if they do not necessarily swear by them at least use these extensions quite regularly and extensively in enterprise applications. This article does not attempt to make a judgment either way. From my perspective and experience, typed DataSets, like a plethora of other technologies and techniques, are appropriate in certain instances and inappropriate in others. They are certainly not a panacea for elegantly combining data and business logic, but they can certainly be beneficial (for example, in rapid prototyping, and they have formed the basis of many business systems).

Furthermore, this article is not intended to be an exhaustive discussion of the workings of DataSets, data tables, data access, or any of the topics we will examine. When it is mentioned that an object supports a method or a behavior, this is not to say that this is the only supported item or even that it is the most favored path. The goal here is to assist you in informed decision making and troubleshooting.

Inherits DataSet / : DataSet

Typed DataSets, at a simple level, are essentially simply classes that derive from System.Data.DataSet. As a result, if one seeks to utilize them, one needs to have at least a brief understanding of the base class. The DataSet is in certain ways the successor to the earlier ADO Recordset object. It is in effect a temporary container to retrieve and store data.

There are some essential differences, however, between the Recordset and the DataSet. Without traveling too much along well-traveled paths, let s examine some key points. DataSets are disconnected in that they are autonomous of the data source. They can be freely traversed, transported, and changed without affecting or being connected to the original database from which they were populated. In addition, they are able to store information in separate heterogeneous DataTables. Moreover, relationships can be formed between these DataTables, and one can effectively hold an in-memory mini-relational database with all the benefits this provides (constraints, referential integrity, cascading updates, etc.). Finally, DataSets can store before and after versions of an individual row for the changes that have been made to it.

Strongly Typed DataSets

John Papa covers the differences and the benefits of typed versus untyped DataSets quite well, so we shan t go into too much detail here. In essence, a strongly typed DataSet is, as previously mentioned, a class that wraps a regular DataSet to enable it to be more easily used and more functional in a specific situation. For instance, it allows a developer to access a customer name using IntelliSense:

OrderInformation.Customer(0).FirstName

instead of:

dsOrderInformation.Tables("Customer").Rows(0)("FirstName")

Having worked with the Web Matrix ASP.NET IDE I can comfortably say the more IntelliSense, the better! One also gains strong typing of one s properties. This makes determining the type of data one is working with instant and provides us with type safety. It also eliminates the scope for typing errors in referring to items by name. This applies not only to the data but to the structure as well, so that:

DataSet1.Tables(0).Columns("FirstName")

becomes:

Payments.Customers.FirstNameColumn

Visual Designer

There are a few ways that you can create a typed DataSet. The command line tool XSD.exe is one of these. However, one of the simplest ways is to use Visual Studio.NET and click Add New | DataSet from the project options and simply drag a table out of Server Explorer onto the DataSet s design surface. Papa mentioned that one of the headaches of typed DataSets is having to propagate database schema changes forward to this schema, but this is required for most approaches to developing and maintaining business objects. The use of the Visual Designer is in fact far easier than having to code these changes by hand and, because of this, the business object it creates is actually less brittle in some ways.

The Visual Designer also lets the developer drag and drop fields between tables to create relationships in much the same way that one does in SQL Server Enterprise Manager or Access. One key difference here is that it will default the parent item to the one that was dragged onto the other. In contrast, Enterprise Manager and Access infer from the fact that the one field is a key that it is most likely to be the parent.

The designer also lets you specify whether or not to cascade updates and deletes down to the children. The options that will work most effectively in our later examples are to cascade only updates, as Figure 1 shows.


Figure 1: Specifying relationships between elements in the Visual Designer.

Annotations

One feature of typed DataSets that many developers find particularly useful is their augmentation through Annotations. These are extensions to the typed DataSet XML definition that provide a variety of enhanced features. To enable Annotations one needs to add the following namespace declaration into the root xs:schema element of the xsd:

xmlns:codegen="urn:schemas-microsoft-com:xml-msprop"

Annotations provide a variety of features, such as improved naming and null values.

Improved Naming. Annotations allow the developer to specify the names for a singular object (i.e., a DataRow) and a collection of the objects (i.e., a DataTable), as well as for the methods to access the parent from a child (and vice versa). For example, if one has Driver and Car tables on the schema DriversAndCarsSchema, then accessing the Drivers for a Car would look like this:

myDriversAndCarsSchema2.Cars(0).GetDrivers()

instead of this:

myDriversAndCarsSchema.Car(0).GetDriverRows()

And adding an item would look like this:

myDriversAndCarsSchema2.Cars.AddCar(myCar)

instead of this:

myDriversAndCarsSchema.Car.AddCarRow(myCar)

This becomes far more unwieldy if one uses prefixes for one s tables; it can be used on a field level, as well.

Null Values. Annotations allow the developer to specify values and behaviors based on whether or not a Null value exists in the field. One can, for example, specify a replacement value of (not set) if a string is Null or an empty constructor for other reference types.

See the References section at the end of this article for further information on Annotations.

Web Services

In .NET 1.1, DataSets override the specified serialization and always serialize to XML. Part of the thinking behind this, one can assume, is so that they can be easily used with Web services and Web methods. Combined with some of the features previously discussed, many developers have seen an advantage in passing DataSets back and forth between layers via Web services. In addition, if one of the Web methods either accepts or returns a typed DataSet, the XSD schema that drives it is also passed along, so that the downstream client is able to use the feature set, as well (this is, of course, assuming Visual Studio.NET is also being used by the client developer). However, there are a number of drawbacks and potential pitfalls to this approach. Some of these are discussed below.

Payload. The DataSet structure itself is quite large. If the schema contains the definition for the structure of and relationships between five or six tables, but only the data for one or two tables, the entire schema is still transferred every time. In addition, it is particularly easy to transmit unnecessary data back along with the import data. For instance, if one has three rows in a table that need to be propagated back to the data store, one may also find oneself transmitting the 250 rows in the lookup table, as well. This reference data is entirely unnecessary on the server that originally transmitted it. However, if one simply removes it by clearing the lookup table, one can expect a System.Data.InvalidConstraintException. A simple solution is to disable constraints on the DataSet by setting EnforceConstraints = False and then clearing the lookup table before transmission. Unfortunately, this will only work if the Web service itself does not require these lookups - or at least expects to have to re-add them, which is not an assumption a service consumer can usually make.

Unnecessary Calls. It is not uncommon for an application to have a single form that allows the user to both view a record in detail and edit it as well. The developer may then conclude that when the user is done with this form he or she would like to have their changes saved, and so the DataSet will be transmitted back to the server to update the data store. However, no actual update may have occurred at all, and thus the entire call is unnecessary. When this is assessed in tandem with the earlier point, and we remember that the DataSet is not insubstantial in size, the importance of this problem is further highlighted. However, it is easily circumvented by simply checking the HasChanges property of the DataSet. If this is false, no call is required.

For HasChanges to properly reflect the state of the data it is important that we actually check the form values against the original row values manually. Even if the FullName field in the row and the FullName textbox on the form both contain Alfreds Futterkiste , if one simply sets Customers(0).FullName = txtFullName.Text the record will be marked as Updated and HasChanges will be True. One needs to compare each form field against the original row value before making a change to the DataSet.

Annotations. Annotations were discussed earlier and we looked at some of the powerful extensions they provide to typed DataSets. However, there is one rather large quirk when they are combined with Web services - while the schema for the typed DataSet is transferred to the client when the Web reference is created, the annotations do not follow. One can see this by browsing to the Web service .asmx and appending ?schema=[SchemaName] (where SchemaName is the name of the schema we want to see). This is particularly annoying because one naturally populates the DataSet directly from the database to actually work with it in the business layer. One works with the objects downstream from this and if one has gone through a Web services layer, then one loses these extensions where one likely needs them the most.

Perhaps I have simply not found the solution to the above problem. If anyone has more information please feel free to contact me.

Interoperability. Many people have an initial positive reaction to the idea of the combination of Web services and either typed or untyped DataSets. The feeling is that this is a perfect transport mechanism for business objects that is now interoperable across platforms, as well. However, this view has been refuted by analyzing the fact that the schemas generated by the DataSets are non-standard. In addition, the before and after changes that the DataSet stores actually alter the XML structure. Figure 2 shows the underlying XML for a DataSet that does not yet have any changes.

<?xml version="1.0" standalone="yes" ?>

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"

 xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

 <DriversAndCarsSchema2 xmlns="http://tempuri.org/DriversAndCarsSchema.xsd">

  <Cars diffgr:id="Cars1" msdata:rowOrder="0">

   <CarID>1</CarID>

   <CarName>Toyota Conquest</CarName>

  </Cars>

  <Cars diffgr:id="Cars2" msdata:rowOrder="1">

   <CarID>2</CarID>

   <CarName>Audi TT</CarName>

  </Cars>

 </DriversAndCarsSchema2>

</diffgr:diffgram>

Figure 2: Underlying XML for a DataSet without changes.

Subsequent to the modifications, the XML representation of the diffgram (the XML view of before and after changes) changes as per Figure 3. This further complicates parsing and interpreting this data. There are arguments against this view because, ultimately, one is still dealing with XML - but it is nonetheless a valid point if this approach is attempted. The dataset-with-Web services debate still rages on; for example, see the References section for a recent entry in Rockford Lhotka s blog, and one MSDN Library solution that makes use of the xml any tag.

<?xml version="1.0" standalone="yes" ?>

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"

 xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

 <DriversAndCarsSchema2 xmlns="http://tempuri.org/DriversAndCarsSchema.xsd">

  <Cars diffgr:id="Cars1" msdata:rowOrder="0" diffgr:hasChanges="modified">

   <CarID>1</CarID>

   <CarName>Toyota Conquest 2</CarName>

  </Cars>

  <Cars diffgr:id="Cars2" msdata:rowOrder="1">

   <CarID>2</CarID>

   <CarName>Audi TT</CarName>

  </Cars>

 </DriversAndCarsSchema2>

 <diffgr:before>

  <Cars diffgr:id="Cars1" msdata:rowOrder="0"

   xmlns="http://tempuri.org/DriversAndCarsSchema.xsd">

   <CarID>1</CarID>

   <CarName>Toyota Conquest</CarName>

  </Cars>

 </diffgr:before>

</diffgr:diffgram>

Figure 3: Underlying XML for a DataSet after changes.

RowErrors. One of the features of the DataSet is that each column or row can store its own error. This is achieved through the ColumnError and RowError String properties, respectively, and it allows, in concept at least, for error information to be transferred back and forth along with the data. In practice, this is a known bug (see KB 818587; http://support.microsoft.com/default.aspx?scid=kb;en-us;818587) with the DataSet in the current versions of the framework for which a fix is available. Without the fix, if the RowError property is set, then the DataSet returns a null (Nothing in VB.NET) value back to the Web service client. This is, I might add, a particularly annoying bug for the developer to encounter and attempt to debug.

Populating the Typed DataSet with Data Access Application Block V2

The Patterns & Practices group has done some absolutely outstanding work in the Application Block frameworks. These Blocks simplify and abstract best-practices approaches and patterns to a variety of important tasks (see more at the Patterns & Practices Web site, http://www.microsoft.com/resources/practices/default.mspx). One of the more well known and commonly used is the Data Access Application Block (DAAB). As with many of the others, it is available on both the MSDN and GotDotNet sites. However, the GotDotNet workspace contains the updated versions of the component (version 3), while the MSDN site still only provides version 2 for download. What is most important for us is that version 2 contains a variety of bugs that are resolved in version 3, particularly with regard to typed DataSets. One of the most important of these relates to populating more than one table in a typed DataSet. The first table is filled correctly, whereas subsequent tables are added to the DataSet as Table01 , Table02 , etc. rather than correctly mapping to the specified tables. Version 2 contains the following two lines of code:

(in VB.NET)

dataAdapter.TableMappings.Add(tableName, tableNames(index))

 tableName = tableName & (index + 1).ToString()

(in C#)

dataAdapter.TableMappings.Add(tableName, tableNames[index]);

 tableName += (index + 1).ToString();

The correction to this is the single line below:

(in VB.NET)

dataAdapter.TableMappings.Add(tableName + CType(IIf(index = _

 0, "", index.ToString()), String), tableNames(index))

(in C#)

dataAdapter.TableMappings.Add(tableName + (index == _

 0 ? "" : index.ToString()), tableNames[index] );

Version 3, as mentioned, has corrected this and a variety of other bugs, and it also contains other enhancements.

The above fix needs to be placed in one of the FillDataSet overloads of the SqlHelper class. Certain of these overloads accept a String array of table names in the DataSet to populate in a specific order. Two points are important here. First of all, the tables must be returned from the query or stored procedure in this same order. Secondly, if a child table is populated before its parent table, a ConstraintException will be thrown. To get around this, either return the results in the correct order from the query or stored procedure or set oDriversAndCarsSchema2.EnforceConstraints = False before the call and then oDriversAndCarsSchema2.EnforceConstraints = True afterwards, as illustrated in Listing One. The former approach simply requires one to have multiple select statements in the query or stored procedure.

Code Generation

We have seen that one of the primary reasons that typed DataSets are used is simply that they create a far more easy to use wrapper around the regular DataSet. The developer is automatically supplied with a property to match each field in the database, as well as a variety of standard methods to add, edit, and delete objects (i.e., rows). In addition, the code generator gives us more advanced properties that allow us to determine, for example, whether a field is null (e.g., IsFirstnameNull) and a variety of more advanced methods. John Papa mentions FindByID, which locates a specific record by its ID; there are also methods to access all the children of a row as an array (e.g., GetOrderDetails; note that the naming of these methods is also improved through the use of Annotations).

All of this code is really quite useful, and these methods definitely make it easier and far quicker to program against the DataSet. However, there are two primary drawbacks against this use of strongly typed DataSets. First of all, the code that is generated is not necessarily the best possible code for your situation. Option Strict, for example, is turned off, and there are a few other lines that could be improved upon. Secondly, if we examine the root of the problem we see that what is required is code generation, and not necessarily DataSets at all. If you simply want a tool to automatically create the skeleton for a business object, you d be far better off actually using a dedicated code generation tool such as CodeSmith. These tools generally have more options for specifying how the code is generated and they also commonly support templates so that one can control the actual code that is outputted.

myDataAdapter.Update

Ultimately, it becomes necessary to propagate the modifications to the DataSet back to the data source. This is most easily accomplished by calling the Update method on the DataAdapter and passing it the entire DataSet as a parameter. It will iterate through the rows in the tables executing the appropriate command for each row (insert, update, or delete). However, this becomes inadequate when one has parent-child relationships for two reasons. First of all, the DataAdapter accepts only one set of CRUD commands and so it will effectively only be able to update one table. In addition, the order of the updates suddenly becomes critical, as we shall now examine.

Deletions. If the rows of a parent table are removed before the child rows, the children are effectively orphaned. Although these children can be located based on their status, they can no longer be linked back to the parent. More importantly, if the EnforceConstraints property is still set to True (the default) then a System.Data.InvalidConstraintException will be thrown. The solution is to delete the children before the parent.

Updates. Updates are the least problematic of the operations because the primary key of a row rarely changes for an update command. Nonetheless, it is still safer to update the parent before the children in this instance because the data still needs to be returned back to the DataRow to ensure that it contains the correct updated data.

Additions. When the parent and child are both new records, the parent s key in the DataSet is almost certainly a temporary one. It is essential that the parent be saved to the data source first so that the true key can be obtained. This will need to be updated to the child rows so that they will be saved correctly with referential integrity maintained both in the system and in the data source. In order for this to occur the insert command for the parent must in some way return the new identity field. One way that this can be accomplished is through the setting of the UpdatedRowSource property of the DataAdapter s Insert command to UpdateRowSource.FirstReturnedRecord, as follows:

myDataAdapter.InsertCommand.UpdatedRowSource = _

 UpdateRowSource.FirstReturnedRecord

This assignment means that the fields for the row will be updated with the first record returned from the update command itself. To do this in SQL Server we need to have the following command at the end of our SQL query or stored procedure:

SELECT [all fields] FROM [table] WHERE [identitycolumn] = @@IDENTITY

This should in fact be done for the Update command, as well. In addition, for the new key to propagate automatically to the children, the Cascade Updates option must be turned on in the schema. Figure 1 shows an example of this. Finally, it is essential that the parent be updated before the children. However, there is another interesting catch. The child rows will enter this method marked as an addition, but the very act of retrieving the final key for the parent and propagating this down to the children marks the children as modified. To get around this we need to re-execute the update command on the children as if it is an insert (which in fact it now is). This is done by setting the actual Update command equal to the Insert command, as follows:

myChildDataAdapter.UpdateCommand = _

 myChildDataAdapter.InsertCommand

In addition, we now need to set the UpdatedRowSource of the Update command as we did previously for the Insert command, as follows:

myChildDataAdapter.UpdateCommand.UpdatedRowSource = _

 UpdateRowSource.FirstReturnedRecord

For the changes to propagate to the children it is also essential that the AcceptReject rule for the relationship in the schema be set to None, as shown in Figure 1. If this is set to Cascade, then when the parent row is updated or inserted and subsequently marked as complete the child rows are also marked as complete. They therefore lose their status of New or Modified and are no longer handled. There are other ways around this problem though, such as hooking into the RowUpdated event of the DataAdapter, but setting AcceptReject in the schema is the simplest solution.

To obtain only the subset of rows we would like to work with from the DataTable we query it for the specific type of changes with which we are dealing. To update the current versions of modified rows we use the following command:

myDataAdapter.Update(myTypedDataSet.[ParentTable].Select _

 ("", "", DataViewRowState.ModifiedCurrent))

and to perform the insertions we use this:

myDataAdapter.Update(myTypedDataSet.[ParentTable].Select _

 ("", "", DataViewRowState.Added))

However, the deletions are a bit more complicated. Certain instances of the DataSet have trouble accessing the deleted rows depending on your transport mechanism. Instead of returning deletions, the query:

myTypedDataSet.[ParentTable].Select("", "", _

 DataViewRowState.Deleted)

 

returns null/Nothing values for the rows. To get around this we can use the GetDeletedRows method provided in the Microsoft Support article Q310350 (http://support.microsoft.com/default.aspx?scid=kb;en-us;310350), as shown in Figure 4.

Public Shared Function GetDeletedRows(ByVal dt As DataTable) _

 As DataRow()

 Dim Rows() As DataRow

 If dt Is Nothing Then Return Rows

 Rows = dt.Select("", "", DataViewRowState.Deleted)

 If Rows.Length = 0 OrElse Not (Rows(0) Is Nothing) Then _

    Return Rows

 '

 ' Workaround:

 ' With a remoted DataSet, Select returns the array

 ' elements filled with Nothing/null, instead of

 ' DataRow objects.

 '

 Dim r As DataRow, I As Integer = 0

 For Each r In dt.Rows

     If r.RowState = DataRowState.Deleted Then

         Rows(I) = r

         I += 1

     End If

 Next

 Return Rows

End Function

Figure 4: The GetDeletedRows method.

And we then process these rows as follows:

myDataAdapter.Update(GetDeletedRows _

 (myTypedDataSet.[ParentTable]))

Listing One illustrates a sample class for populating and updating a typed DataSet, and shows the final code for a sample Update method.

Conclusion

Once more I want to stress that this article neither advocates nor discourages the use of DataSets. It merely presents the reader with more information regarding strongly typed DataSets and their benefits. Some of the issues discussed are still appearing as questions in the forums, and the goal of this article is to discuss some of the common caveats and pitfalls that developers will encounter if they do choose to go this route. The typed DataSet can be useful in certain situations, but you need to be aware of its strengths and weaknesses in order to anticipate and solve the problems you may encounter.

References

The sample code referenced in this article is available for download.

Hilton Giesenow has been developing commercial software and Web sites since 1997 using a variety of Microsoft tools and technologies, including VB6, COM, VB.NET, ASP, ASP.NET, SQL Server, Access, HTML, CSS, JavaScript, DHTML, XHTML, and XML, and has written articles for a variety of trade journals. Contact Hilton at mailto:hiltonwork@giesenow.com.

Errata

Larry Guger, an alert reader from Canada, pointed out that one may encounter difficulties with the code to handle additions. This case will only occur if one does not apply the cascading options recommended (i.e. if cascading for Accept/Reject is set to Cascade). But to catch all possibilities, and as a fail-safe, it may be better to set and reset this cascading option in code. An example of this appears below, where the Inserts code would change from:

' Inserts

daCars.Update(driversAndCarsUpdates.Cars.Select("", "", DataViewRowState.Added))

daDrivers.UpdateCommand = daDrivers.InsertCommand

daDrivers.Update(driversAndCarsUpdates.Drivers.Select("", "", DataViewRowState.Added))

daDrivers.Update(driversAndCarsUpdates.Drivers.Select("", "", DataViewRowState.Added))

to:

' Inserts

Dim arrExistingRule As AcceptRejectRule = driversAndCarsUpdates.Cars.ChildRelations("CarsDrivers").ChildKeyConstraint.AcceptRejectRule

driversAndCarsUpdates.Cars.ChildRelations("CarsDrivers").ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None

daCars.Update(driversAndCarsUpdates.Cars.Select("", "", DataViewRowState.Added))

daDrivers.Update(driversAndCarsUpdates.Drivers.Select("", "", DataViewRowState.Added))

driversAndCarsUpdates.Cars.ChildRelations("CarsDrivers").ChildKeyConstraint.AcceptRejectRule = arrExistingRule

Begin Listing One

Imports System.Data

Imports System.Data.SqlClient

Public Class DriversAndCars

 Private Shared strConnectionString As String = _

System.Configuration.ConfigurationSettings.AppSettings _

("sqlConn.ConnectionString")

 ' Default constructor

 Public Sub New()

 End Sub

 

 ' Don't do this - search and return only the results

 ' you need - this is for demo purposes only!

 Public Function GetAllDriversAndCars() _

  As DriversAndCarsSchema2

   Dim oDriversAndCarsSchema2 As New DriversAndCarsSchema2

   oDriversAndCarsSchema2.EnforceConstraints = False

   SqlHelper.FillDataSet(strConnectionString, _

   "spCarsAndDrivers_View", oDriversAndCarsSchema2, _

    New String() {"Cars", "Drivers"})

   oDriversAndCarsSchema2.EnforceConstraints = True

   Return oDriversAndCarsSchema2

 End Function

 ' This method saves the changes made to a DriverAndCars

 ' typed DataSet back to the data source. It handles

 ' changes to both the parent (Cars) and child (Drivers)

 ' data tables.

 ' e.g.:

 ' Dim oDriversAndCars As New DriversAndCars

 ' Dim strReturn As String

 ' strReturn = _

 ' oDriversAndCars.Update(tdsDriversAndCarsSchema)

 '

 ' Parameters:

 ' driversAndCarsUpdates: a valid DriversAndCarsSchema2

 ' for which HasChanges = True

 ' Returns:

 ' a string containing the result of the updates.

 ' An empty string indicates success.

 Public Function Update(ByVal driversAndCarsUpdates _

  As DriversAndCarsSchema2) As String

   'Ensure that the DataSet passed in actually exists

   If driversAndCarsUpdates Is Nothing Then

     Throw New ArgumentNullException("DriversAndCarsUpdates")

   End If

   ' Create and open database connection

   Dim sqlConn As New SqlConnection(strConnectionString)

   Try

     sqlConn.Open()

   Catch ex As Exception

     sqlConn.Dispose()

     Return "An error occurred connecting to the

      data source"

   End Try

   Dim oTransaction As SqlTransaction = _

    sqlConn.BeginTransaction()

   Dim daCars As New SqlDataAdapter

   Dim daDrivers As New SqlDataAdapter

   Try

     ' Set Commands - these could be moved into a

     ' function and are listed here for illustrative

     ' purposes

     daCars.InsertCommand = SqlHelper.CreateCommand _

      (sqlConn, "spCars_Ins", "CarName")

     daCars.InsertCommand.Transaction = oTransaction

     daCars.UpdateCommand = SqlHelper.CreateCommand _

      (sqlConn, "spCars_Upd", "CarID", "CarName")

     daCars.UpdateCommand.Transaction = oTransaction

     daCars.DeleteCommand = SqlHelper.CreateCommand _

      (sqlConn, "spCars_Del", "CarID")

     daCars.DeleteCommand.Transaction = oTransaction

     daDrivers.InsertCommand = SqlHelper.CreateCommand _

      (sqlConn, "spDrivers_Ins", "DriverName", "CarID")

     daDrivers.InsertCommand.Transaction = oTransaction

     daDrivers.UpdateCommand = SqlHelper.CreateCommand _

      (sqlConn, "spDrivers_Upd", "DriverID", _

      "DriverName", "CarID")

     daDrivers.UpdateCommand.Transaction = oTransaction

     daDrivers.DeleteCommand = SqlHelper.CreateCommand _

      (sqlConn, "spDrivers_Del", "DriverID")

     daDrivers.DeleteCommand.Transaction = oTransaction

 ' Set UpdatedRowSource so that Insert and Edits will

 ' maintain the data correctly (and thus cascade to

 ' the children)

     daCars.InsertCommand.UpdatedRowSource = _

      UpdateRowSource.FirstReturnedRecord

     daCars.UpdateCommand.UpdatedRowSource = _

      UpdateRowSource.FirstReturnedRecord

     daDrivers.InsertCommand.UpdatedRowSource = _

      UpdateRowSource.FirstReturnedRecord

     daDrivers.UpdateCommand.UpdatedRowSource = _

      UpdateRowSource.FirstReturnedRecord

     ' Hook into the RowUpdated event so that the

     ' dataadapter will continue updating on errors.

     ' You can also handle any logging etc. in this method.

     AddHandler daCars.RowUpdated, AddressOf _

      DataAdapter_MyRowUpdated

     AddHandler daDrivers.RowUpdated, AddressOf _

      DataAdapter_MyRowUpdated

     driversAndCarsUpdates.EnforceConstraints = False

     ' Deletions

     daDrivers.Update(GetDeletedRows _

      (driversAndCarsUpdates.Drivers))

     daCars.Update(GetDeletedRows _

      (driversAndCarsUpdates.Cars))

     ' Updates

     daCars.Update(driversAndCarsUpdates.Cars.Select _

      ("", "", DataViewRowState.ModifiedCurrent))

     daDrivers.Update(driversAndCarsUpdates.Drivers.Select _

      ("", "", DataViewRowState.ModifiedCurrent))

   ' Inserts

   daCars.Update(driversAndCarsUpdates.Cars.Select _

    ("", "", DataViewRowState.Added))

   daDrivers.UpdateCommand = daDrivers.InsertCommand

   daDrivers.Update(driversAndCarsUpdates.Drivers.Select _

    ("", "", DataViewRowState.Added))

   daDrivers.Update(driversAndCarsUpdates.Drivers.Select _

    ("", "", DataViewRowState.Added))

   oTransaction.Commit()

   Return String.Empty

   Catch ex As Exception

     oTransaction.Rollback()

     ' Log errors

     Return "An error occurred updating the data source"

 Finally

   'Clean up

   If Not oTransaction Is Nothing Then _

    oTransaction.Dispose()

   If Not daDrivers Is Nothing Then _

     daDrivers.Dispose()

   If Not daCars Is Nothing Then daCars.Dispose()

   If sqlConn.State = ConnectionState.Open Then

       sqlConn.Close()

   End If

   sqlConn.Dispose()

 End Try

 End Function

 Private Sub DataAdapter_MyRowUpdated(ByVal sender _

  As Object, ByVal e As _

  System.Data.SqlClient.SqlRowUpdatedEventArgs)

   e.Status = UpdateStatus.SkipCurrentRow

   ' log or report on any errors, eg.

   If Not e.Errors Is Nothing Then

       '...

   End If

 End Sub

 Private Function GetDeletedRows(ByVal dt As DataTable) _

  As DataRow()

   Dim Rows() As DataRow

   If dt Is Nothing Then Return Rows

   Rows = dt.Select("", "", DataViewRowState.Deleted)

   If Rows.Length = 0 OrElse Not (Rows(0) Is Nothing) _

    Then Return Rows

   '

   ' Workaround:

   ' With a remoted DataSet, Select returns the array

   ' elements filled with Nothing/null, instead of

   ' DataRow objects.

   '

   Dim r As DataRow, I As Integer = 0

   For Each r In dt.Rows

       If r.RowState = DataRowState.Deleted Then

           Rows(I) = r

           I += 1

       End If

 

   Next

   Return Rows

 End Function

End Class

End Listing One