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
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
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
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"
<xs:element name="OrderDetails" codegen:typedName="OrderDetailBase"
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
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.
SET Customer = @Customer, OrderDate = @OrderDate
WHERE (Id = @Original_Id) AND (Customer = @Original_Customer)
AND (OrderDate = @Original_OrderDate) AND (Timestamp = @Original_Timestamp);
SET Customer = @Customer, OrderDate = @OrderDate
WHERE (Id = @Id) AND (Timestamp = @Timestamp);
These are the steps to follow:
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
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
Protected Overrides Sub InitClass()
Dim Column As DataColumn
For Each Table As DataTable In Me.Tables
Column = New DataColumn("ClientSideID", GetType(System.Int32), Nothing,
Column.AutoIncrement = True
Column.ColumnMapping = MappingType.Hidden
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
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.
- 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.