Introduction
There's a lot of repetitive work associated with the writing of data classes within the data access layer of an N-tiered architecture. Apart from adding a column to the relevant database table, adding a field to the class involves adding a private
variable to hold the field value, a property to expose it to other classes, and several lines to make sure the field is included in insert, fetch, and update operations. If you're using stored procedures (very silly for CRUD queries) there are several more modifications to make. This is dull work, I'm sure you'll agree. Presented here is a lightweight method for taking away all this tedium, such that you only have to add a getter/setter property in order to add a field to a data class.
The technique involves abstracting SQL query writing and execution, and variable storage in data access classes to a base class. In the implementation presented here, I've named this base class "PersistentDataObject
". The "less than 300 lines of code" referred to in the title of this post are the lines of code in this base class, the database access class it uses, and a couple of exception classes used as markers to add clarity to exception origins. The complete code is included at the end of the post.
Background
I conceived this idea, before my .NET days, while working on a monolithic PHP project. After writing hundreds of simple CRUD queries that were all basically the same, I realized that it would be a simple matter to abstract all that query writing in such a way that I would never have to write such a simple query again. That was over a year ago. Now I'm a fully paid up ASP.NET developer, and I think the code has reached a level of refinement at which it is ready to be presented to the world. I use the code presented here in every system I build and I love it. Maybe you will too.
Using the Code
Here is a simple Person
class created by extending PersistentDataObject
:
using System;
public class Person : PersistentDataObject
{
public Person() : base("Person", "PersonId") { }
public Person(int id) : this() { Load(id); }
public string FirstName
{
get { return Get<string>("FirstName"); }
set { Set("FirstName", value); }
}
public string LastName
{
get { return Get<string>("LastName"); }
set { Set("LastName", value); }
}
public DateTime DateOfBirth
{
get { return Get<DateTime>("DateOfBirth"); }
set { Set("DateOfBirth", value); }
}
public int? LuckyNumber
{
get { return Get<int?>("LuckyNumber"); }
set { Set("LuckyNumber", value); }
}
}
Configuring the Data Object Class
The only configuration values necessary for the class are name of the table from which the data is to be retrieved, and the name of the unique id column within that table (assumed to be an integer column). These are passed to PersistentDataObject
in the constructor.
public Person() : base("Person", "PersonId") { }
Fetching Data from the Database
The Person
class shown above provides a constructor that accepts an integer identifier as an argument. This constructor delegates to the Load
method of PersistentDataObject
to fetch all data from the database for the row with the specified identifier.
public Person(int id) : this() { Load(id); }
An overload of the Load
method is provided that takes a DataRow
as an argument. This allows the object to be populated with data from a DataRow
that has already been fetched from the database.
Exposing Column Values as Properties
Column Values are exposed by delegating to the Get
and Set
methods of PersistentDataObject
. The Get
method takes a string
argument indicating the column from which the value should be retrieved. Similarly, the Set
method takes a string
argument to indicate for which column the value is to be set. This example shows a property exposing a DateTime
column:
public DateTime DateOfBirth
{
get { return Get<DateTime>("DateOfBirth"); }
set { Set("DateOfBirth", value); }
}
Note that the Get
method uses generics to identify the type of the value that should be returned. The type specified should be the appropriate type dependant on the type of the column.
Handling Null Values
Nullable value types come into play in order to handle columns that allow null
values. This example shows an integer column for which null
values are allowed exposed as a property:
public int? LuckyNumber
{
get { return Get<int?>("LuckyNumber"); }
set { Set("LuckyNumber", value); }
}
Note that as string
is a reference type, it does not need to be specified as nullable to accept null
values.
Inserting, Updating, and Deleting
A Save
method is provided that handles both the inserting into, and the updating of objects in the database. If the Save
method is called on an object that does not yet exist in the database, then an insertion is performed. If the object does already exist in the database, an update is performed. This code snippet shows an instance of Person
being created and a record for it being inserted into the database:
Person person = new Person();
person.FirstName = "Nick";
person.LastName = "Higgs";
person.DateOfBirth = new DateTime(1983, 2, 7);
person.Save();
A Delete
method is provided to allow the object to be deleted from the database. This code shows an object for an existing person record being instantiated and then deleted from the database:
Person person = new Person(51);
person.Delete();
Closing Remarks
That completes the explanation. I've found that this technique not only takes most of the repetitive work out of writing data objects, but provides a solid foundation to a data access layer, regardless of the complexity of the system.
History
Modified from an article posted on my (now discontinued) blog
http://developmental-issues.blogspot.com/2007/04/simple-or-mapping-in-less-than-300.html