Pluggable data access components - Part 2

• Download source and demo - 118 Kb

Introduction

This article explains how to build a better pluggable system for data access. The other article describing this subject I wrote was less flexible. I hard coded all functionality into the data access components. While this works fine for small applications, it�s a complete nightmare for larger applications that change a lot.

The goal of the components

The goal of the data access components should be communication with the database. It doesn�t really matter how you build your business logic, I think the data access layer should provide you with the tools to do your work properly. So it has several things that need to be implemented:

1. Filling datasets
2. Updating datasets
3. Executing commands

General design

The general design is simple yet very flexible. There�s a single interface for all kinds of providers and a factory to produce the providers. To be able to pass a single type of command to the data access, I created a wrapper. This wrapper is just a class with the properties and doesn�t do much on its own. Once I pass the command to the provider it is translated into a specific command for that type of data access provider. The class diagram looks like this:

Using the code

To use the code in your application you will first need a reference to the assembly. If you use Visual Studio .NET, it shouldn�t be much of a problem. After that you can start working right away.

Beginning a session with the database

To begin a new database session, you will need a piece of code that looks like this:

DatabaseSessionFactory factory = new DatabaseSessionFactory();
factory.DriverType = "SQL";
factory.ConnectionString =
"Server=(local');Database=Store;Integrated Security=SSPI";

DatabaseSession session = factory.BeginSession();

Executing commands

After you create a new session, you can start filling or updating datasets, execute commands, etc. A sample is displayed below:

DatabaseCommand cmd = new 
  DatabaseCommand("SELECT * FROM Employees WHERE Name = @Name");

cmd.Parameters.Add(new CommandParameter("@Name","Some name", 
                                       ParameterType.Input);

IDataReader reader = session.ExecuteQuery(cmd);

All parameters (it doesn�t matter which provider) should have the @<ParamName> format. Although some providers require ?<ParamName> or even no name, the provider will resolve these issues internally.

Points of Interest

When writing this code I discovered that OleDB is a rather weird provider. Especially for Access databases. Parameters are used by index rather than by name. This means I got a problem with this provider, because I work by name. I solved this issue by repairing the index of the command parameters when translating the command to the specific OleDbCommand.

//Place all the parameters in the right order

Regex regex = new Regex(@"@(\S+)");
MatchCollection matches = regex.Matches(cmd.CommandText);

foreach(Match m in matches) 
{
    foreach(CommandParameter param in command.Parameters) 
    {
        if(param.Name == m.Value) 
        {
            //Remove the @ sign at the beginning 

            //of the parametername

            //when adding it to the command

            OleDbParameter parameter = new OleDbParameter(
                param.Name.Substring(1),param.Value);

            parameter.Direction = param.Type;

            cmd.Parameters.Add(parameter);
            cmd.CommandText = cmd.CommandText.Replace(m.Value, �?�);
        }
    }
}

The MySQL provider that is included with the assembly also had some issues. The parameters are by name, but the prefix is different from the @ sign I use in my interface. This is fixed rather simple, I replaced the prefix with a question mark, which is the prefix of MySQL parameters.

Points of improvement

I am not extremely happy with the FillDataSet methods, they tend to add tables like Table1, Table2, Table �, etc. This isn�t what I want to happen. So I added the method FillDataSet with a table parameter. This is an ugly solution, since I think it can be solved in a more elegant way. Ideas on this one are welcome!

The other thing that can be improved is the settings of the factory class. It should be possible to build a connection string out of parameters configured in the factory class. This should happen in the DatabaseSession implementation classes. Maybe I will solve this in the next version.

The documentation isn�t always in agreement with the actual implementation. While this is only with some examples, this should be solved in the next release.

Conclusion

This version of the pluggable data access components is way better than the old implementation I wrote. It�s more scalable and it is easier to work with than the old one. I hope that the people who read this article will agree with me.

History

• 5/23/2005 � Initial release.