Click here to Skip to main content
13,247,804 members (42,629 online)
Click here to Skip to main content
Add your own
alternative version


70 bookmarked
Posted 9 Apr 2007

Persistent Object Management in Less than 300 Lines of Code

, 8 Oct 2007
Rate this:
Please Sign up or sign in to vote.
Presenting a lightweight method to cut out most of the repetitive work of creating data classes.


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.


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);

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);

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.


Modified from an article posted on my (now discontinued) blog


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Nick Higgs
Web Developer
United Kingdom United Kingdom
Since graduating from the University of Sheffield with a Masters degree in Software Engineering in 2005 I have been woking as a professional web developer for a Sheffield new media company.
ASP.Net is currently my primary technology, but I also have substantial experience with PHP and Flex and various Java technologies.

You may also be interested in...


Comments and Discussions

QuestionMultiple object Pin
suraj Chhetry9-Oct-07 21:56
membersuraj Chhetry9-Oct-07 21:56 
AnswerRe: Multiple object Pin
Nick Higgs10-Oct-07 3:25
memberNick Higgs10-Oct-07 3:25 
GeneralInitiative way! Pin
M.Khadem7-Jun-07 5:31
memberM.Khadem7-Jun-07 5:31 
QuestionHow to bind the Person object to a view object Pin
Aliasncnu7-May-07 17:43
memberAliasncnu7-May-07 17:43 
AnswerRe: How to bind the Person object to a view object Pin
Nick Higgs9-May-07 13:12
memberNick Higgs9-May-07 13:12 
GeneralQuestions and suggestions Pin
felipedr19-Apr-07 7:16
memberfelipedr19-Apr-07 7:16 
GeneralRe: Questions and suggestions Pin
Nick Higgs19-Apr-07 14:25
memberNick Higgs19-Apr-07 14:25 
GeneralTransactions Pin
probe5319-Apr-07 0:05
memberprobe5319-Apr-07 0:05 
GeneralRe: Transactions Pin
Nick Higgs19-Apr-07 10:30
memberNick Higgs19-Apr-07 10:30 
GeneralExcellent Article Pin
Dewey9-Apr-07 21:02
memberDewey9-Apr-07 21:02 
GeneralRe: Excellent Article Pin
Nick Higgs10-Apr-07 3:07
memberNick Higgs10-Apr-07 3:07 
General2 security flaws Pin
reinux9-Apr-07 18:21
memberreinux9-Apr-07 18:21 
GeneralRe: 2 security flaws Pin
Nick Higgs10-Apr-07 2:53
memberNick Higgs10-Apr-07 2:53 
GeneralRe: 2 security flaws Pin
Nick Higgs12-Apr-07 12:06
memberNick Higgs12-Apr-07 12:06 
GeneralRe: 2 security flaws Pin
reinux12-Apr-07 15:16
memberreinux12-Apr-07 15:16 
Nick Higgs wrote:
Are you suggesting that impressionable beginners who see that I have posted code containing a connection and string may "follow my example" by posting code containing a connection string that could be used to connect to a database via the internet? Well, I guess it could happen, but it's not a flaw in the code its self though is it? Hard coding a connection string is considered bad practice because it doesn't promote portability, that is, you can't swap to another database without recompiling the code. It doesn’t make the application any less secure.

It increases the risk of human error.

You may be more inclined to share your code and your connection string along with it, whereas you'd think twice about sharing a .config file written in plain xml.

This is what I thought you might have done at first, even though it seemed almost too silly, that you just carelessly cut & pasted code from your workplace code and shared it on CP. It does happen.

Is it a flaw in the code to neglect setting good examples for beginners? Arguably not, but it is certainly a flaw in the article.

Nick Higgs wrote:
DatabaseAccessor contains functionality that is likely to be needed by classes other than PersistentDataObject, so it makes complete sense to encapsulate it in a separate class. Even if this wasn't the case, it's good practice for a class not to have disparate responsibilities.

There's a limit.

You don't want to expose anything from your class library that doesn't immediate accomplish what the library is designed for, unless it's a Helper class of some sort that makes some sophisticated manoeveurs or provides validated access to private features. Clearly, DatabaseAccessor doesn't.

Nick Higgs wrote:
I included DatabaseAccessor for completeness, but I would expect that most C# developers will already have a similar class that they use most of the time.

Yet PersistentDataObject is reliant on DatabaseAccessor.

Let's examine the try{} blocks of the methods in this class.
sqlCommand.Connection = SqlConnection;
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))

sc.Connection = SqlConnection;
obj = sc.ExecuteScalar();

sqlCommand.Connection = SqlConnection;
iRowsAffected = sqlCommand.ExecuteNonQuery();

Considering that the calls to DatabaseAccessor are also wrapped in try/catch blocks, you've effectively only isolated maybe 10 lines of code, all of which is very standard stuff. You say that you expect most c# developers would already have similar classes for this (I wouldn't have a class just for this by the way), which would render PersistentDataObject unusable without modification.
GeneralRe: 2 security flaws Pin
Nick Higgs14-Apr-07 3:20
memberNick Higgs14-Apr-07 3:20 
GeneralRe: 2 security flaws Pin
reinux16-Apr-07 13:06
memberreinux16-Apr-07 13:06 
QuestionStored Procedures "silly" for CRUD operations? Pin
chmod7559-Apr-07 11:10
memberchmod7559-Apr-07 11:10 
AnswerRe: Stored Procedures "silly" for CRUD operations? Pin
Nick Higgs9-Apr-07 14:09
memberNick Higgs9-Apr-07 14:09 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
mr_lasseter9-Apr-07 16:44
membermr_lasseter9-Apr-07 16:44 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
reinux9-Apr-07 18:38
memberreinux9-Apr-07 18:38 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
Nick Higgs10-Apr-07 10:25
memberNick Higgs10-Apr-07 10:25 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
reinux10-Apr-07 11:52
memberreinux10-Apr-07 11:52 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
Nick Higgs10-Apr-07 13:43
memberNick Higgs10-Apr-07 13:43 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
reinux11-Apr-07 3:49
memberreinux11-Apr-07 3:49 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
Nick Higgs12-Apr-07 11:57
memberNick Higgs12-Apr-07 11:57 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
reinux12-Apr-07 15:16
memberreinux12-Apr-07 15:16 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
Nick Higgs14-Apr-07 12:13
memberNick Higgs14-Apr-07 12:13 
GeneralRe: Stored Procedures "silly" for CRUD operations? Pin
jarle8017-Apr-07 22:03
memberjarle8017-Apr-07 22:03 
GeneralRe: Stored Procedures &amp;quot;silly&amp;quot; for CRUD operations? [modified] Pin
Nick Higgs19-Apr-07 14:56
memberNick Higgs19-Apr-07 14:56 
AnswerRe: Stored Procedures "silly" for CRUD operations? Pin
Bohicette29-Oct-07 12:03
memberBohicette29-Oct-07 12:03 
Generalsuggestion Pin
timvw9-Apr-07 6:31
membertimvw9-Apr-07 6:31 
GeneralRe: suggestion Pin
Nick Higgs9-Apr-07 6:50
memberNick Higgs9-Apr-07 6:50 
GeneralRe: suggestion Pin
mr_lasseter9-Apr-07 16:49
membermr_lasseter9-Apr-07 16:49 
GeneralRe: suggestion Pin
seesharper9-Apr-07 22:43
memberseesharper9-Apr-07 22:43 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 9 Oct 2007
Article Copyright 2007 by Nick Higgs
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid