Click here to Skip to main content
Click here to Skip to main content

Inherit from ADO.NET DataSet to create your own business objects

, 27 Oct 2004
Rate this:
Please Sign up or sign in to vote.
Extend generated Strong Typed Datasets, add your own business-specific functionality to a real DataSet object. Binding to forms and updating to a database is all done by the .NET framework. This is built on Shawn Wildermuth's a.k.a. ADO Guy's DataSetGenerator.

Sample Image - InheritFromDataSet.jpg

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:

  1. Make DataAdapters by dragging tables from Server Explorer onto the design surface of a Component.
  2. Choose menu option "Configure Data Adapter".
  3. Choose "Create new stored procedures".
  4. Edit generated stored procedures: remove @Original_... parameters and do optimistic concurrency by means of Timestamp field.
  5. 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.

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

About the Author

Pinx
Software Developer
Netherlands Netherlands
My first encounters with computers where Sinclair, TI99-4A, C64, Apple II. Have programmed ever since. Worked for a few logistic companies.
I have been a VB.NET programmer for quite some years. I work with a small firm, that specializes in supply chain planning for bulk products. We have our own application, Delivery+, that helps vehicle planners with their daily job.
I am married and I have two children. I am building an eco-friendly house and try to live green.

Comments and Discussions

 
General.Net FrameWork2.0 Register Pinmemberxuxiwei12-Sep-06 15:47 
GeneralRe: .Net FrameWork2.0 Register PinmemberPinx12-Sep-06 21:48 
I have not switched over to 2.0 (VS 2005), so I can not test it myself, but I think a few changes to reg.cmd and VSDataSetGenerators.reg will suffice. Look for the version numbers in the path names and change them to the correct numbers for your system.
If you succeed, it would be nice if you could post the required changes here.
Thanks in advance.
GeneralRe: .Net FrameWork2.0 Register Pinmemberxuxiwei13-Sep-06 17:49 
GeneralRe: .Net FrameWork2.0 Register Pinmemberwmcsd10-Jan-07 0:23 
GeneralHelp!!!!!!! PinmemberBrotherFCH8-Nov-05 16:36 
GeneralExtension: provide typesafe access to column names Pinmembersixeyed26-Aug-05 5:03 
GeneralRe: Extension: provide typesafe access to column names PinmemberPinx28-Aug-05 0:39 
GeneralRe: Extension: provide typesafe access to column names Pinmembersixeyed30-Aug-05 22:37 
QuestionRe: Extension: provide typesafe access to column names PinmemberPinx29-Sep-05 22:51 
QuestionMicrosoft code? PinmemberDavid Gasaway29-Apr-05 7:00 
AnswerRe: Microsoft code? PinmemberPinx29-Apr-05 9:19 
AnswerRe: Microsoft code? Pinmemberwmcsd9-Jan-07 21:16 
GeneralMissing chapter PinmemberShinz4-Mar-05 13:08 
GeneralRe: Missing chapter PinmemberPinx6-Mar-05 20:26 
GeneralPath to install it PinmemberANORTON15-Feb-05 7:32 
GeneralRe: Path to install it PinmemberPinx15-Feb-05 20:38 
GeneralRe: Path to install it PinsussNeil Mosafi15-Apr-05 0:49 
Generalgreat work PinmemberManuel Permuy27-Oct-04 10:22 
GeneralThanks! PinmemberRajesh_K27-Oct-04 5:54 
GeneralGreat PinmemberOskar Austegard27-Oct-04 4:47 
GeneralInheritance vs composition PinmemberSyd Wright24-Oct-04 18:09 
GeneralRe: Inheritance vs composition PinmemberPinx27-Oct-04 2:25 
GeneralThat is not a TRUE business object per se PinmemberRussel Harvey17-Oct-04 19:56 
GeneralRe: That is not a TRUE business object per se PinmemberPinx17-Oct-04 21:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 27 Oct 2004
Article Copyright 2004 by Pinx
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid