Click here to Skip to main content
13,349,032 members (68,224 online)
Click here to Skip to main content
Add your own
alternative version


139 bookmarked
Posted 20 Mar 2002

A Data Access Layer to persist business objects using attributes and reflection - Part III

, 20 Mar 2002
Rate this:
Please Sign up or sign in to vote.
Persistance to business objects through attributes and reflection



This is the last article of this series. In the first article we've seen how to give descriptive information about a class using attributes. In the second article we've seen how to extract that information using the System.Reflection namespace. Now we'll create a DAL library that is capable of persisting any object that has a descriptive information about itself.

Designing the DAL library

During the creation of this DAL library I wanted to create it so it could be used either with a SQL Server data provider or an OleDb data provider. It could also be extended to other data providers. We can divide this library in the following parts:

Utility classes

class DALQueryBuilder

As mentioned in the first article this class helps in the construction of SQL statements to update an object in the database

class DALParameter

A generic parameter class to be used in stored procedures

class DALException

It's a System.Exception derived class. It's thrown whenever an exception occurs in our database operations. It provides more information about the error that happened.

Attribute classes

All the classes that are derived from System.Attribute that were mention in the first article.

DAL itself

class DALEngine

The abstract base class that does database operations, making the database programming a lot easier. It has virtual and abstract methods were each data provider has a different implementation. More than methods to return DataSets, DataReaders from a database it has methods that handle objects marked with the DAL attributes.

class DALSqlEngine

A SQL Server implementation of DALEngine

class DALOleDbEngine

An OleDb implementation of DALEngine

A Closer Look at the DALEngine class

This is the declaration of the DALEngine class:

public abstract class DALEngine : IDisposable
    // private data members
    IDbConnection conn       = null;
    string connectionString  = "";
    ArrayList parameters = new ArrayList();
    bool canClose = true;

    // constructor
    public DALEngine(string connectionString);

    public bool CanClose;
    public string ConnectionString;

    protected IDbConnection Connection;
    protected ArrayList Parameters;

    public void Close();
    public void Dispose();

    // methods that must be override with a specific data provider 
    // implementation please see the implementation of DALSqlEngine 
    // or DALOleDbEngine<BR>    // 
    protected abstract IDbConnection GetConnection();
    protected abstract IDbCommand CreateCommand(string spName);
    public abstract void ExecSP_DataSet(string spName, DataSet dataSet, 
                                       string tableName);
    public abstract void ExecQuery_DataSet(string query, DataSet dataSet, 
                                           string tableName);

    // related to stored procedure parameters
    public DALParameter GetParameter(string name);
    void UpdateOutputParameters(IDbCommand cmd);
    public void AddParameter(DALParameter param);
    public void ClearParameters();

    // for those that use stored procedures
    public IDataReader ExecSP_DataReader(string spName);
    public IDataReader ExecSP_DataReader(string spName, <BR>                                         CommandBehavior behavior);
    public object ExecSP_Scalar(string spName);
    public int ExecSP_NonQuery(string spName);

    // methods for those that use plain SQL statements
    public IDataReader ExecQuery_DataReader(string query, <BR>                                            CommandBehavior behavior);
    public IDataReader ExecQuery_DataReader(string query);
    public object ExecQuery_Scalar(string query);
    public int ExecQuery_NonQuery(string query);

    // Business objects methods
    public static object CreateFromReader(IDataReader reader, Type objType);
    public object RetrieveObject(object keyValue, Type objType);
    public int RetrieveChildObjects(object foreignKeyValue, ArrayList objects,
                                    Type childType);
    void UpdateObjectSql(object o, DataTableAttribute dataTable);
    void UpdateObjectStoredProcedure(object o, DataTableAttribute dataTable);
    public void UpdateObject(object o);
    public void UpdateObjects(IEnumerable enumObjects);

As you may be thinking we will only that a look at the last 6 methods. The other ones are easy to understand since there are a lot of similar implementations out there.

The CreateFromReader method returns an instance of the objType type. Since we use DataReaders to get data from the database, that's the method that's used to return business object instances. It's static since it doesn't need a database connection, only a DataReader that can be read.

The method RetrieveObject returns one instance of the objType type. This instance will have it's property values according to que unique table row that has the key value equals to the keyValue parameter. Under the hood this method creates a SELECT statement with a WHERE clause. The RetrieveChildObjects create object instances of type childType where the foreign key is equal to foreignKeyValue parameter value. Those instances created will be added to the objects parameter.

The methods UpdateObject and UpdateObjects make an update in the database. This update operation can happen using a stored procedure (in case the class has the DataTable attribute with an UpdateStoreProcedure property set) or a plain SQL statement. The private methods UpdateObjectSql and UpdateObjectStoredProcedure are in charge of doing this updates.


First of all using the DAL itself will make your database programming a lot easier. Even if you don't intend to do database programming as I do, I strongly recommend using a DAL library that already have tedious and repetitive tasks implemented. You don't have to use this one, but at least use one.

But if you do use business objects to work with the database this library will save you a lot of time and effort. There's only a piece of source code that needs to be reviewed when a change in the database happens. And it can be done using a tool of your own, doing the opposite of the tool we created in the second part of this article.

To show you how easy it is to get/update objects from the database I included a sample application along with the full DAL library code. There's a code fragment of this sample application below so you can have a clue of it's strength.

For sure the DAL has a lot of bugs, and that's one of the reasons I posted this code here. Maybe you could have better solutions and code to accomplish the library goals. Please feel free to send me questions and contributions as I'm really interested in improving this library.

To develop the sample application I created my own DAL class deriving from DALSqlEngine, since I would be using SQL Server. That's the code of my DAL class:

public class DAL : DALSqlEngine
    const string CONN_STRING = "server=localhost;uid=sa;pwd=;database=pubs";

    public DAL() : base(CONN_STRING)


    public ArrayList GetCustomerDependents(Customer customer)
        ArrayList result = new ArrayList();

        RetrieveChildObjects(customer.Id, result, typeof(CustomerDependent));

        return result;

    public void UpdateCustomerDependents(Customer customer)

Simple, isn't it? Now the application code, it's just inserting/updating contact/customers and customer dependents.

public static void Main()

    DAL dal = new DAL();


        Contact contact = new Contact();
        contact.Name = "Joao Cardoso";
        contact.Age  = 23;
        contact.Address = "Av. Rio Branco, 202/121";
        contact.Address2 = "Centro";
        contact.PostalCode = "09029-901";
        contact.City = "Sao Paulo";
        contact.State =  "SP";
        contact.Country = "Brazil";


        Contact joaoCardoso = (Contact)dal.RetrieveObject(1, typeof(Contact));

        Customer customer = new Customer();
        customer.Name = "Paul Noyter";
        customer.Age  = 34;
        customer.Address = "All St, 2202/2121";
        customer.Address2 = "Downville";
        customer.PostalCode = "90931";
        customer.City = "Los Angeles";
        customer.State =  "CA";
        customer.Country = "United States";
        customer.TotalPurchased += 1900.87M;


        Customer paul = (Customer)dal.RetrieveObject(1, typeof(Customer));

        paul.TotalPurchased += 100M;

        if (paul.Dependents.Count == 0)
            CustomerDependent dependent = paul.NewDependent();
            dependent.Name = "Marie Noyter";
            dependent.Age = 31;

            dependent = paul.NewDependent();
            dependent.Name = "Mark Noyter";
            dependent.Age = 10;

            dependent = paul.NewDependent();
            dependent.Name = "Claudia Snorg";
            dependent.Age = 32;
            dependent.Relationship = CustomerRelationship.Friend;

            Console.WriteLine("Dependents of {0}", paul.Name);

            foreach(CustomerDependent dependent in paul.Dependents)
                Console.WriteLine("<Dependent>{0} - {1} [{2}]", dependent.Id, 
                                  dependent.Name, dependent.Relationship);
                dependent.Relationship = CustomerRelationship.Family;




Of course it has limitations. Some can be solved by extending the DAL or the attributes. If you don't use an auto number key column the only way you can update your object is using stored procedures. The problem is that DAL can't figure out if an object should be inserted or updated in the database. You could extend the DAL so that it checks for a known attribute or interface that provides the information needed; Currently it doesn't support tables with more than one key column; Performance isn't fast as pushing SqlParameters down the SqlCommand, but you will have more time to do your business classes instead of database programming; It doesn't enable the use of stored procedures to retrieve objects from the database. But hey, it's easy to be done; There must be others, but those are the one I remember by the time I wrote this.


There a lot of ways to do database programming in the .NET world. Visual Studio .NET enables you to use Typed DataSet, which saves you a lot of time since it generates code. There's nothing wrong with DataSets, but I prefer using business objects. When I started developing such classes I started to notice how long it took me to write each class. With the library introduced here you could see how productive it can be to develop database applications using business objects.


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

Architect VisionOne AG
Switzerland Switzerland
XicoLoko is a brazilian developer based in Switzerland.

You may also be interested in...

Comments and Discussions

GeneralExcellent Posts Pin
jefferycxl11-Apr-10 18:42
memberjefferycxl11-Apr-10 18:42 
QuestionInsert Null value ? Pin
voro30-Jan-10 5:34
membervoro30-Jan-10 5:34 
GeneralLocalization Issue on systems with a language other than englisch Pin
Andy19883-Oct-07 9:12
memberAndy19883-Oct-07 9:12 
GeneralRe: Localization Issue on systems with a language other than englisch Pin
xicoloko3-Oct-07 21:49
memberxicoloko3-Oct-07 21:49 
GeneralRe: Localization Issue on systems with a language other than englisch Pin
Andy19884-Oct-07 17:10
memberAndy19884-Oct-07 17:10 
GeneralParam Order (For SQL 2005) Pin
Dave Rundle10-Sep-07 8:23
memberDave Rundle10-Sep-07 8:23 
GeneralNice work - And see also Pin
Maruf Maniruzzaman22-Apr-07 0:40
memberMaruf Maniruzzaman22-Apr-07 0:40 
QuestionData Acces Layer Pin
Anwar Jamal23-Feb-06 3:10
memberAnwar Jamal23-Feb-06 3:10 
GeneralParams order Pin
vquillet5-May-04 8:25
membervquillet5-May-04 8:25 
First I have to thank you for this interresting work.

I have tried to test every combinaison and I got the following problem :

when using OLEDBEngine and stored procedures, an error is thrown when executing the command. It seems to be the order of parameters that doesn't match those in the procedure. I have noticed that the properties of derived classes like customer are put at the begining of the parameters array.
When I change the order in the procedure it works perfect !

Here is the change :

CREATE PROCEDURE [sp_UpdateCustomer]
@totalPurchased [money],
@numberOfPurchases [int],
@dateRegistered [datetime],
@status [int],
@phone [varchar](20),
@email [varchar](80),
@address [varchar](80),
@address2 [varchar](80),
@city [varchar](50),
@postalCode [varchar](20),
@state [varchar](4),
@id [int] OUTPUT,
@name [varchar](50),
@age [int]


Any comment ? OLEDb doesn't handle parameters orders ?

Thank you

Big Grin | :-D

GeneralUse of interfaces Pin
joebits5-Feb-04 6:09
sussjoebits5-Feb-04 6:09 
GeneralStored Procedure Pin
Roel Ang15-Jan-04 9:56
memberRoel Ang15-Jan-04 9:56 
GeneralVery good!!! Pin
ltphu200113-Nov-03 22:21
memberltphu200113-Nov-03 22:21 
GeneralAbout n-tier Pin
pierrenavarra31-May-03 7:44
memberpierrenavarra31-May-03 7:44 
GeneralAutomatic Transaction Handling in enterprise services Pin
ICE M18-May-03 16:11
memberICE M18-May-03 16:11 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini22-May-03 16:50
memberzucchini22-May-03 16:50 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
ICE M22-May-03 23:30
memberICE M22-May-03 23:30 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini23-May-03 4:25
memberzucchini23-May-03 4:25 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini27-Jun-03 6:11
memberzucchini27-Jun-03 6:11 
GeneralTaking it a step farther Pin
Nick Harrison21-Apr-03 9:00
memberNick Harrison21-Apr-03 9:00 
GeneralRe: Taking it a step farther Pin
zucchini22-May-03 16:55
memberzucchini22-May-03 16:55 
GeneralRe: Taking it a step farther Pin
Kevin G13-Dec-03 11:30
sussKevin G13-Dec-03 11:30 
GeneralRe: Taking it a step farther Pin
Mark Churchill11-Sep-07 21:46
memberMark Churchill11-Sep-07 21:46 
GeneralObjects referring to objects... Pin
Joe West18-Apr-03 8:30
sussJoe West18-Apr-03 8:30 
GeneralRelational tables Pin
Shlo29-Jan-03 6:02
sussShlo29-Jan-03 6:02 
GeneralRe: Relational tables Pin
zucchini27-Jun-03 6:13
memberzucchini27-Jun-03 6:13 

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
Web01 | 2.8.180111.1 | Last Updated 21 Mar 2002
Article Copyright 2002 by xicoloko
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid