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

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

Rate me:
Please Sign up or sign in to vote.
4.82/5 (29 votes)
20 Mar 20025 min read 213.2K   2K   138   32
Persistance to business objects through attributes and reflection

Index

Introduction

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:

C#
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.

Benefits

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:

C#
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)
    {
        UpdateObjects(customer.Dependents);
    }
}

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

C#
public static void Main()
{

    DAL dal = new DAL();

    try
    {

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

        dal.UpdateObject(contact);
        Console.WriteLine(contact);


        Contact joaoCardoso = (Contact)dal.RetrieveObject(1, typeof(Contact));
        joaoCardoso.Age++;
        Console.WriteLine(joaoCardoso);
        Console.WriteLine("");


        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.NumberOfPurchases++;

        dal.UpdateObject(customer);


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

        paul.TotalPurchased += 100M;
        paul.NumberOfPurchases++;
        dal.UpdateObject(paul);

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


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


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

            dal.UpdateCustomerDependents(paul);
        }
        else
        {
            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;
            }

            dal.UpdateCustomerDependents(paul);
        }

    }
    finally
    {
        dal.Dispose();
    }
}

Limitations

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.

Conclusion

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.

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
Architect VisionOne AG
Switzerland Switzerland
XicoLoko is a brazilian developer based in Switzerland.

Comments and Discussions

 
GeneralExcellent Posts Pin
jefferycxl11-Apr-10 17:42
jefferycxl11-Apr-10 17:42 
QuestionInsert Null value ? Pin
voro30-Jan-10 4:34
voro30-Jan-10 4:34 
GeneralLocalization Issue on systems with a language other than englisch Pin
Andy19883-Oct-07 8:12
Andy19883-Oct-07 8:12 
GeneralRe: Localization Issue on systems with a language other than englisch Pin
xicoloko3-Oct-07 20:49
xicoloko3-Oct-07 20:49 
GeneralRe: Localization Issue on systems with a language other than englisch Pin
Andy19884-Oct-07 16:10
Andy19884-Oct-07 16:10 
GeneralParam Order (For SQL 2005) Pin
Dave Rundle10-Sep-07 7:23
Dave Rundle10-Sep-07 7:23 
GeneralNice work - And see also Pin
Maruf Maniruzzaman21-Apr-07 23:40
Maruf Maniruzzaman21-Apr-07 23:40 
QuestionData Acces Layer Pin
Anwar Jamal23-Feb-06 2:10
Anwar Jamal23-Feb-06 2:10 
GeneralParams order Pin
vquillet5-May-04 7:25
vquillet5-May-04 7:25 
GeneralUse of interfaces Pin
joebits5-Feb-04 5:09
sussjoebits5-Feb-04 5:09 
GeneralStored Procedure Pin
Member 14901615-Jan-04 8:56
Member 14901615-Jan-04 8:56 
GeneralVery good!!! Pin
Member 16490313-Nov-03 21:21
Member 16490313-Nov-03 21:21 
GeneralAbout n-tier Pin
31-May-03 6:44
suss31-May-03 6:44 
GeneralAutomatic Transaction Handling in enterprise services Pin
ICE M18-May-03 15:11
ICE M18-May-03 15:11 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini22-May-03 15:50
zucchini22-May-03 15:50 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
ICE M22-May-03 22:30
ICE M22-May-03 22:30 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini23-May-03 3:25
zucchini23-May-03 3:25 
GeneralRe: Automatic Transaction Handling in enterprise services Pin
zucchini27-Jun-03 5:11
zucchini27-Jun-03 5:11 
GeneralTaking it a step farther Pin
Nick Harrison21-Apr-03 8:00
Nick Harrison21-Apr-03 8:00 
GeneralRe: Taking it a step farther Pin
zucchini22-May-03 15:55
zucchini22-May-03 15:55 
GeneralRe: Taking it a step farther Pin
Kevin G13-Dec-03 10:30
sussKevin G13-Dec-03 10:30 
GeneralRe: Taking it a step farther Pin
Mark Churchill11-Sep-07 20:46
Mark Churchill11-Sep-07 20:46 
GeneralObjects referring to objects... Pin
Joe West18-Apr-03 7:30
Joe West18-Apr-03 7:30 
GeneralRelational tables Pin
shlomii29-Jan-03 5:02
shlomii29-Jan-03 5:02 
GeneralRe: Relational tables Pin
zucchini27-Jun-03 5:13
zucchini27-Jun-03 5: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.