Click here to Skip to main content
15,860,943 members
Articles / Programming Languages / C#
Article

PersistedObject - an ADO.NET mapping mechanism

Rate me:
Please Sign up or sign in to vote.
4.29/5 (6 votes)
24 May 20028 min read 113.3K   1.1K   52   14
An alternative to Microsoft's typesafe DataSet

Introduction

Originally, I needed to store an object to the database in the .NET environment. For this purpose, Microsoft serves the ADO.NET component which is in-memory database consisting of tables and columns, including data reference relationships. You can map those tables in your class manually, which is time-consuming and error prone.

Microsoft suggests the use of the typesafe datasets. The main disadvantage I found is the resulting code, which is generated based on the visual designer you must use. After the resulted code review, I declined to go this way.

Instead, I created a simple PersistedObject abstract class with ADO.NET mapping capabilities, using inheritance for code reuse. My idea was to build an framework which will provide the mapping mechanism for me. There is list of requirements for the solution I wanted to deliver:

  • it must be simple to use, so creating new persisted class should be an 1-2-3 step process
  • it must be extensible to employ a different approaches of object mapping onto a database tables
  • it must solve the problem of the object mapping only
  • there must be a set of well defined methods, which must be used to achieve desired functionality, or they could be redefined to extend a default behavior

Framework architecture

The framework is built around one single class, PersistedObject, which understands two attributes, DataColumnAttribute and DataTableAttribute. Those attributes are used to redefine the default behavior of the PersistedObject class, specifically naming conventions which are as follows:

  • ADO's DataTable shoud have the same name as the persisted class has
  • DataColumns in DataTable sould have the same names as the persisted class has
  • Identity column has name ID

As an add-on, I have built the PersistedObjectCollection, which is nothing more than the collection of persisted objects. It's main task is to wrap a collection behavior around the ADO.NET DataSet, including IEnumerable interface. This collection class is designed as abstract to be used as base class for it's successors, which can redefine the public methods to create a typesafe collection. The framework could be depicted as follows:

Image 1

In the diagram, public attributes are used to express properties. Operations with the same name as the class describes constructors.

PersistedObject brief description

The primary goal was not to hide the database technology, but rather to identify a common scenarios used for object persistence and retrieval mechanism.

For example, there are four constructors for PersistedObject abstract class for different object creation mechanisms. The default constructor is intended to create new instance which will be saved afterwards into database, while the others are intended to create an object which represents already existing instance, using several different data sources.

The default constructor does nothing in the base class and should be redefined to initialize a brand new instance to a stable state, so it could be saved immediately after the successful creation process has been completed. The rest three constructors provide object identifying mechanism within the system:

  • PersistedObject(int id) loads the object instance using system's standard load mechanism, typically load from the database
  • PersistedObject(DataRow row) and PersistedObject(DataSet ds) are used for loading data from the particular DataSet object, or DataRow object respectively, bypassing the standard object load mechanism

There are also the services intended for work with the DataSet in it's native format. For this purpose, there are several mapping methods and properties defined in the class. Their names suggests what they are intended to do:

  • string mappedTableName gives you a name of the table the object is stored in
  • string mappedIdentityDataColumnName gives you a name of the column in table which stores the identifier for the object
  • string mappedDataColumnname(string fieldName) gives you a name of the particular object field
  • void ToDataRow(DataRow row) fills the row with object's fields values
  • DataSet ToDataSet() generates the dataset filled with the data

The last sort of methods are predefined abstractions of common handing tasks when working with persisted objects:

  • save() saves the object into database
  • delete() deletes object from database
  • refresh() refreshes the object with data loaded from database

Note there is no load() method in the public interface of the PersistedObject class. It is due to the object lifetime semantic implied by the framework, which assumes you are creating either valid new object, or the valid instance of the object persisted in database, or in DataSet object. The object does not need explicit load, it is loaded by default during the creation process using the appropriate constructor.

It's time to an example. So let's see how the PersistedObject should be used to create new persisted class:

C#
public class Employee:PersistedObject{
  // data to be stored in DataSet must have the 'dm' prefix as follows:
  private string dmName;
  private string dmEmail;
  // ... etc
  
  // creates new object, the employee with a particular name and email
  public Employee(string name, string email){
    dmName = name;
    dmEmail = email;
  }
  
  // creates an object instance based on it's identifier, 
  // the data are loaded from database
  public Employee(int id):base(id){
  }
  
  // creates an object instance based on the data provided in DataRow
  public Employee(DataRow dr):base(dr){
  } 
  
  // define a method for loading object from database
  protected override DataSet loadDataSet(){
    // Loading the data from a database is  beyond this article,
    // which serves a solution for mapping objects onto ADO.NET 
    // and vice versa
    // Typically, I'm using a special data loader class, 
    // so my code would look like this
    DataLoader loader = new DataLoader();
    return loader.loadSingleEmployee(ID);
  }
  
  // And finally, let's build the data accessors 
  public string name{
    get { return dmName; }
    set { dmName = value;
  }
  public string email{
    get { return dmEmail; }
    set { dmEmail = value; }
  }
}
The accompanying database table for the Employee class defined above should look like the following script suggests:
SQL
CREATE TABLE Employee (
  ID int NOT NULL CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED,
  name varchar(50) NOT NULL ,
  email varchar(50) NOT NULL
) 
GO

You know already the purpose of the methods defined in class's public interface, we have reviewed the sample code creating a sample class Employee, along with the class database representation, the Employee database table. The Employee class contains exactly the same fields as it's database representation. Except of the "dm" prefix for data members declared in the class, the names of the fields/columns are the same. The class name and the name of the table are the same as well. The same table structure as shown by the SQL script above will be generated when calling ToDataSet() method, as well as the object load method will understand the only structure shown above. Please note the DataSet does not operate upon a datatypes compatible with database, so the table DataTable will use columns typed as string and int.

As an example for use of the Employee class, we could write the following sample code:

C#
// creating brand new object with immediate save into database
Employee employee = new Employee("Vasek", "vprokop@hotmail.com");
employee.save();

// change the employee name
employee.name = "Vaclav Prokop";
employee.save();

// delete employee from the system
employee.delete();

The code above could be described using a sequence diagram as depicted below:

Image 2

As I stated already, the purpose of the framework is to collaborate with ADO.NET and to map object fields onto ADO.NET tables (DataTable) and to load the object from those tables. The PersistedObject framework makes the following assumptions:

  • Data are persisted by calling an independent agent's save(DataSet data) method which understands the database structure. Such agent can provide automatic mapping mechanism for generating SQL statements for insert, update or delete respectively. The details of the agent are not discussed in this article.
  • The DataSet received from the loadDataSet() method should contain the single row of data only in the table.
  • When creating new instance, the DataSet will be created with new row populated by fields with "dm" prefix, marked as Added (see the RowState property for DataRow object).
  • When deleting the instance, the row from the dataset will be deleted using a Delete method, so marking a row as Deleted
  • On every save() method call, the DataSet is populated with new values from the class fields, forwarding the whole dataset to the appropriate database agent class responsible for data storing mechanism.
  • For new objects, the framework does not generate object identifier (the ID property). This identifier must be assigned by database agent class, or by the database respectively.

PersistedObject internals

There are several protected and private methods to fulfill desired behavior. The most important methods and properties are:
  • dataSet property returns valid DataSet object containing the data the object has been built from. Use it whenever you need a direct access to the underlying DataSet object.
  • load() method which is used for loading object from the database, it is the high-level function which wraps a single rule for loading the object form the database.
  • loadDataSet() abstract method should return a valid DataSet with appropriate data for the proper object load. Every descendant of PersistedObject must implement this method to load the data from the appropriate data source using the appropriate method.
  • saveDataSet() saves the DataSet to the database using an independent agent class. In the source code I use agent named ObjectSaver which uses the DataSet as the source for database update operation. The agent could take advantage of the fact the names used in DataTable describes the actual table in RDBMS to build the appropriate SQL statement automatically.
  • createDataSetSchema(DataSet ds) creates the database schema into the valid DataSet object passed by argument.
  • fillDataMembers(DataRow dr) fills-up the class fields prefixed with "dm".
  • fillDataRow(DataRow dr) populates given DataRow by values of the class fields prefixed with "dm".
  • fillDataSet(DataSet ds) fills the DataSet with new instance data.

For further comments see the source code.

Persisting the descendants

The framework does not solve the problem of persisting the further descendants. In fact, there is no single solution to this problem. Always you have to trade-off between the two possible options:

  • One large table with null values
  • Several small tables with 1:1 relationship

The first approach does not require to redefine the mapping mechanism. However, the second approach requires some further modifications. You can redefine the object storing mechanism to use more than one table when reading from and/or writing to the DataSet object. In that case, the important methods are:

  • createDataSetSchema to extent the schema by the creation of the next table
  • fillDataMembers to read from the ancestor's table, than to read from the specific successor table
  • fillDataSet to fill properly the extended table schema in DataSet
  • loadDataSet to ensure more than one table will be returned, typically by choosing another data source

Conclusion

Using PersistedObject framework you can easily map your persisted objects to the ADO.NET DataSet which is the main data handling subsystem used in .NET framework. Given framework works great when dealing with the objects directly inherited from PersistedObject, the further descendants should implement their own mapping mechanism. Mapping rules could be redefined using attributes, the DataColumnAttribute for change the name of the column the field will be mapped to, and the DataTableAttribute for the class/table and the object identifier mapping.

The framework is also suitable for creating the objects directly from the DataSet object that can be built from the other source than the database.

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


Written By
Web Developer
Czech Republic Czech Republic
Started programming on 8-bit computers using BASIC, then I moved to Turbo Pascal along with TurboVision. Professional carrer: 2 years of programming for workgroup software ALL-IN-1 running on VAX/VMS machines made by Digital Co. More than 2 years of programming in PowerBuilder environment, backend databases used: Oracle, Informix. Since then using Microsoft technology: VB6, C#, MS DNA, MS.NET. More than 2 years Microsoft Certified Professional. Since 2003 Freelance Software Developer

Comments and Discussions

 
GeneralObjectSaver Pin
umpa19-Jul-06 3:58
umpa19-Jul-06 3:58 
GeneralObjectSaver please Pin
Madmaximus6-Jun-06 4:57
Madmaximus6-Jun-06 4:57 
Hi, Can I please get a copy of the ObjectSaver code.
Here my email: terryken@erols.com Wink | ;)

Thanks
Ken
Questionplease ObjectSaver ? Pin
Member 21883482-Sep-05 12:39
Member 21883482-Sep-05 12:39 
GeneralobjectSaver Pin
younesnr15-Apr-04 5:19
younesnr15-Apr-04 5:19 
GeneralRe: objectSaver Pin
lhblhx17-Apr-04 14:32
lhblhx17-Apr-04 14:32 
GeneralRe: objectSaver Pin
Vasek17-May-04 22:01
Vasek17-May-04 22:01 
GeneralRe: objectSaver Pin
bigals26-Jun-05 23:20
bigals26-Jun-05 23:20 
GeneralObjectSaver Pin
AndreLyra28-Feb-04 4:56
AndreLyra28-Feb-04 4:56 
GeneralRe: ObjectSaver Pin
Vasek29-Feb-04 3:07
Vasek29-Feb-04 3:07 
GeneralRe: ObjectSaver Pin
klenne19-Apr-04 3:44
klenne19-Apr-04 3:44 
GeneralAdvantages of mapping to ADP.NET Pin
kevinimnotspacey26-Feb-03 9:54
kevinimnotspacey26-Feb-03 9:54 
GeneralRe: Advantages of mapping to ADP.NET Pin
Vasek2-Mar-03 21:54
Vasek2-Mar-03 21:54 
GeneralEnumerator bug Pin
nahankid20-Aug-02 21:13
nahankid20-Aug-02 21:13 
GeneralRe: Enumerator bug Pin
Vasek20-Aug-02 22:51
Vasek20-Aug-02 22:51 

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.