Introduction
Wouldn't it be great to drag your database model into Visual Studio designers, add your own code to the generated datasets, bind your business objects to forms just like the built-in ADO.NET DataSets?
Anyone who has tried to create a subclass of a Typed DataSet will have discovered that this is very limited. You can create a subclass of the DataSet itself, but it is impossible to create a usable subclass of the DataTables or DataRows.
If you look at the code that the MSDataSetGenerator generates, you see that most of the interesting stuff is not overridable. Shawn Wildermuth, who calls himself The ADO Guy, created his own DataSetGenerator to solve this problem. You can find a description of the how and why of this great tool on his web site. To be able to understand the rest of this article, I urge you to read his explanation first.
Do use my version of the generator, though, because I made some small changes that make it possible to put the base classes in a different assembly than the sub-class.
Installing the DataSet Generator
When you add a DataSet to your Visual Studio project, you get an .xsd file, with MSDataSetGenerator as the associated Custom Tool. Select the file in the Solution Explorer and look at the Property Grid. To create a Typed DataSet that you can inherit from, you have to replace this with AGDataSetGenerator. But first, you have to tell Visual Studio where it can find this tool. For this, you have to run reg.cmd and VSDataSetGenerators.reg. If you want to put AGDataSetGenerator.dll in a different folder, you may have to change reg.cmd. I did not make a fancy installer, but if anyone cares to build one for me, send it over and I'll add it to this page.
Creating Base DataSets
Creating inheritable DataSets is almost exactly like normal typed DataSets. Just add a DataSet to your project and drag your tables from the Server Explorer onto the design surface. To be able to add annotations, you add xmlns:codegen="urn:schemas-microsoft-com:xml-msprop" inside the <xs:schema> tag. To read about this, search for "annotations with typed DataSet" in the Visual Studio help files.
These are the two lines in the XSD file that I changed to create my base class:
<xs:element name="Orders" codegen:typedName="OrderBase"
codegen:typedPlural="OrdersBase">
<xs:element name="OrderDetails" codegen:typedName="OrderDetailBase"
codegen:typedPlural="OrderDetailsBase">
Creating DataAdapters
DataAdapters need to be hosted somewhere. This can be a Form or a Component. In my applications, I create a separate Component for each set of tables. In the example, I have one component that contains DataAdapters for both Orders and OrderDetails. I use multiple DataAdapters for the same table, if I want to use different selection criteria for filling a DataSet from the database.
I have my own version of optimistic concurrency control. For this, I have to change the stored procedures that are generated by the DataAdapter wizard. After changing the Stored Procedure, I have to re-run the wizard, to update the DataAdapter with the new Stored Procedure.
UPDATE Orders
SET Customer = @Customer, OrderDate = @OrderDate
WHERE (Id = @Original_Id) AND (Customer = @Original_Customer)
AND (OrderDate = @Original_OrderDate) AND (Timestamp = @Original_Timestamp);
... becomes:
UPDATE Orders
SET Customer = @Customer, OrderDate = @OrderDate
WHERE (Id = @Id) AND (Timestamp = @Timestamp);
These are the steps to follow:
- Make
DataAdapters by dragging tables from Server Explorer onto the design surface of a Component. - Choose menu option "Configure Data Adapter".
- Choose "Create new stored procedures".
- Edit generated stored procedures: remove
@Original_... parameters and do optimistic concurrency by means of Timestamp field. - Go to "Configure Data Adapter" again, and refresh the data adapter with the changed stored procedures ("Use existing stored procedures").
Derive from the Base DataSet
Most of the functionality of the DataSet Generator is covered by Shawn Wildermuth. He describes how you have the option to derive from the base DataTable, or use the base class directly. He explains the few extra lines of code you have to put in every subclass to glue it all together.
The things I will explain here are:
- Handling identity fields
- Optimistic concurrency
Identity fields: Both SQL Server and ADO do autonumbering. If they both start at 1 and increase by 1, chances are that you get collisions when updating a DataSet to the database. To prevent this, simply set both the AutoIncrementSeed and AutoIncrementStep to -1 in the XSD designer. This is what you get in the XML:
<xs:element name="Id" msdata:ReadOnly="true" msdata:AutoIncrement="true"
type="xs:int" msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" />
New records in the DataSet get a temporary ID value. When they are INSERTed in the database, the DataAdapter takes care of writing the ID value that the database generated into the DataRow in memory. This all works great, but if you want to separate between the DataSet and the Service that communicates with the server, you need a temporary DataSet to hold only the changes. To merge the changes back into the original DataSet, you can't use the ID field, because they no longer match for INSERTed rows. To circumvent this problem, you have to add a temporary identity field to the DataSet. That's why we have this code in the DataSet:
Protected Overrides Sub InitClass()
MyBase.InitClass()
Dim Column As DataColumn
For Each Table As DataTable In Me.Tables
Column = New DataColumn("ClientSideID", GetType(System.Int32), Nothing,
System.Data.MappingType.Element)
Column.AutoIncrement = True
Column.ColumnMapping = MappingType.Hidden
Table.Columns.Add(Column)
Next
End Sub
In the Update method of the DataSet, where we get back the temporary DataSet from the DataService, the ClientSideID is used to match the original row with the updated row. You'll find this code in OrderSet.vb.
Optimistic concurrency is handled by adding a Timestamp field to every table in the database. Updates for records that have been changed by another user will fail. Provided you have set ContinueUpdateOnError = True on the DataAdapter, the update for other records will succeed. After the update operation, you can inspect the rows that are returned by DataTable.GetErrors.
Ideas for Using the DataSets
The framework I presented here solves some common issues, such as mapping database fields with object members, identity fields, optimistic concurrency. It also separates between the objects and communicating with the database. The next step could be to hide the Service objects containing the DataAdapters, in a web service. I tried this, and it works beautifully.
People fond of design patterns could apply the Factory pattern to build a DataAdapter for a DataSet on-the-fly. You could also create your own DataAdapterGenerator, or your own StoredProcedureGenerator. In my opinion, it's best not to use too many code generators, though. You might end up spending more time on tweaking your generators than building the software you are paid for.
Summary
- Use the built-in designer tools of Visual Studio as much as possible.
- Save on O/R mapping tools - they aren't perfect either.
- Try to limit the number of necessary manual changes (e.g., the stored procedures).
- Make the necessary changes by hand and do not try to reinvent the wheel.
History
- 27th October, 2004: Initial version
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below. A list of licenses authors might use can be found here.
