PersistedObject - an ADO.NET mapping mechanism

• Download source files - 10 Kb

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:

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:

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:

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:

// 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:

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.