Click here to Skip to main content
14,923,486 members
Articles / Web Development / ASP.NET
Posted 17 Oct 2004


83 bookmarked

Inherit from ADO.NET DataSet to Create Your Own Business Objects

Rate me:
Please Sign up or sign in to vote.
4.67/5 (12 votes)
27 Oct 20046 min read
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


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" 
<xs:element name="OrderDetails" codegen:typedName="OrderDetailBase" 

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.


SET Customer = @Customer, OrderDate = @OrderDate

WHERE (Id = @Original_Id) AND (Customer = @Original_Customer) 
AND (OrderDate = @Original_OrderDate) AND (Timestamp = @Original_Timestamp);

... becomes:


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()
 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
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.


  • 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.


  • 27th October, 2004: Initial version


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

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 Pin
xuxiwei12-Sep-06 15:47
Memberxuxiwei12-Sep-06 15:47 
GeneralRe: .Net FrameWork2.0 Register Pin
pinx12-Sep-06 21:48
Memberpinx12-Sep-06 21:48 
GeneralRe: .Net FrameWork2.0 Register Pin
xuxiwei13-Sep-06 17:49
Memberxuxiwei13-Sep-06 17:49 
GeneralRe: .Net FrameWork2.0 Register Pin
wmerifield10-Jan-07 0:23
professionalwmerifield10-Jan-07 0:23 
GeneralHelp!!!!!!! Pin
BrotherFCH8-Nov-05 16:36
MemberBrotherFCH8-Nov-05 16:36 
GeneralExtension: provide typesafe access to column names Pin
sixeyed26-Aug-05 5:03
Membersixeyed26-Aug-05 5:03 
GeneralRe: Extension: provide typesafe access to column names Pin
pinx28-Aug-05 0:39
Memberpinx28-Aug-05 0:39 
GeneralRe: Extension: provide typesafe access to column names Pin
sixeyed30-Aug-05 22:37
Membersixeyed30-Aug-05 22:37 
QuestionRe: Extension: provide typesafe access to column names Pin
pinx29-Sep-05 22:51
Memberpinx29-Sep-05 22:51 
QuestionMicrosoft code? Pin
Member 132696329-Apr-05 7:00
MemberMember 132696329-Apr-05 7:00 
AnswerRe: Microsoft code? Pin
pinx29-Apr-05 9:19
Memberpinx29-Apr-05 9:19 
AnswerRe: Microsoft code? Pin
wmerifield9-Jan-07 21:16
professionalwmerifield9-Jan-07 21:16 
GeneralMissing chapter Pin
Shinz4-Mar-05 13:08
MemberShinz4-Mar-05 13:08 
GeneralRe: Missing chapter Pin
pinx6-Mar-05 20:26
Memberpinx6-Mar-05 20:26 
GeneralPath to install it Pin
ANORTON15-Feb-05 7:32
MemberANORTON15-Feb-05 7:32 
GeneralRe: Path to install it Pin
pinx15-Feb-05 20:38
Memberpinx15-Feb-05 20:38 
GeneralRe: Path to install it Pin
nmosafi15-Apr-05 0:49
Membernmosafi15-Apr-05 0:49 
Generalgreat work Pin
Manuel Permuy27-Oct-04 10:22
MemberManuel Permuy27-Oct-04 10:22 
GeneralThanks! Pin
Rajesh_K27-Oct-04 5:54
MemberRajesh_K27-Oct-04 5:54 
GeneralGreat Pin
Oskar Austegard27-Oct-04 4:47
MemberOskar Austegard27-Oct-04 4:47 
GeneralInheritance vs composition Pin
Syd Wright24-Oct-04 18:09
MemberSyd Wright24-Oct-04 18:09 
GeneralRe: Inheritance vs composition Pin
pinx27-Oct-04 2:25
Memberpinx27-Oct-04 2:25 
GeneralThat is not a TRUE business object per se Pin
Russel Harvey17-Oct-04 19:56
MemberRussel Harvey17-Oct-04 19:56 
GeneralRe: That is not a TRUE business object per se Pin
pinx17-Oct-04 21:21
Memberpinx17-Oct-04 21:21 

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

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