Implementing a Database Factory Pattern in C# ASP .NET

Why Do I Need a Database Layer Anyway?

When creating software for clients, even the beginner developer recognizes the frequency of change. Requirements change, user interface pieces change, platforms change, and databases changes. Determining ways to minimize re-work in your web applications becomes a very important task and by adding a distinct database layer to your C# ASP .NET application, you can greatly increase its flexibility and adaptability to change.

In addition to adaptability, a database layer can also provide an on-the-fly means of changing connections strings and even the database type itself. By changing just a few lines in your C# ASP .NET web.config file, you can effectively change the type of database accessed by your application from MSSQL to Oracle to MySQL - all without a single change to the application code.

The Simple Method

Many developers begin by coding the user interface of a web application, as this helps move the project visually. Because of this, it's not surprising that database code for populating the fields is often mixed directly in. In a C# ASP .NET application, this is similar to adding calls to SQLConnection in your Page_Load() function, as shown below.

using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection MyConnection = new SqlConnection(MyConnectionString))
{
using (SqlCommand MyCommand = new SqlCommand("SELECT * FROM Flowers", MyConnection))
{
using (SqlDataReader MyReader = MyCommand.ExecuteReader())
{
// read flowers and process ...
}
}
}

}

Notice in the above code, we're referencing System.Data.SqlClient directly in the user interface code. We're also making calls to the database. A program will run just fine with this method. However, managing this code becomes a problem.

The core problem with the above code is not only that business logic may be mixed in with the user interface code (business logic would appear inside the "read database information" section), but that including database accessibility logic complicates the readability of the program. Furthermore, what happens when you need to change from using the SqlClient to using ODBC? You could change the "using" statement and rename the lines from SqlConnection to OdbcConnection throughout your code, but this may waste time and is also prone to errors.

A much more clear picture of this application could be made by leaving only user interface code inside the Page_Load() function. Defer the decision on what type of database to use and instead concentrate on user interface code. Any calls to database routines would be handled by a call to a separate layer, as follows:

using MyStuff.Managers;

protected void Page_Load(object sender, EventArgs e)
{
Flowers = CommonManager.GetFlowers();
}

Notice in the above code, the database functionality has been minimized to just a single line. The details of the database logic are abstracted away in a database layer. This greatly simplifies reading and managing the code. Now that you can see the initial benefits to utilizing a database layer in your C# ASP .NET web application, let's move on to implementing it.

Putting the Web.Config to Good Use

Before defining the actual database factory classes, we can use the C# ASP .NET web application's web.config file to hold some important data about how our database factory will be used. We will be storing information such as the type of database provider (MSSQL, Oracle, MySQL), a list of possible connection strings, as well as which connection string to use. By storing this information in the web.config file, we provide the flexibility to change the database provider or connection string in real-time, without changing code in the application.

Our web.config file will look similar to the following:

In the above web.config file, we have two connection strings defined. One is for an Oracle database and another for an MSSQL database. You could have any number of connection strings. Our database factory design uses its own web.config section, defined in DatabaseFactoryConfiguration. This allows us to tell the database factory which provider class to instantiate (Oracle, MSSQL, etc) and which connection string to use. You can see the flexibility this adds, by allowing us to change any of these parts right in the web.config file.

The DatabaseFactory Web.Config Handler

Since we've added our own custom section to the web.config file for the database factory, we need to create a web.config custom section handler. The section handler is defined as follows:

using System.Configuration;

public sealed class DatabaseFactorySectionHandler : ConfigurationSection
{
[ConfigurationProperty("Name")]
public string Name
{
get { return (string)base["Name"]; }
}

[ConfigurationProperty("ConnectionStringName")]
public string ConnectionStringName
{
get { return (string)base["ConnectionStringName"]; }
}

public string ConnectionString
{
get
{
try
{
return ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
}
catch (Exception excep)
{
throw new Exception("Connection string " + ConnectionStringName + " was not found in web.config. " + excep.Message);
}
}
}
}

The only important part to note about the section handler is that it derives from the ConfigurationSection base class. When this class is instantiated, it will automatically read the line from the web.config file and populate with the values. Since we include a ConnectionString property, it will also fetch the correct connection string, so that we never have to access the ConfigurationManager.ConnectionStrings property ourselves.

It All Starts With a Database

The first part to creating a database factory design pattern is to implement your generic Database object. This is the object you will reference when calling database routines. Since this object is generic, you don't have to decide what type of physical database to actually use with it. This gives you the power to change database providers without changing any of your code. The database object is designed as follows:

using System.Data;

public abstract class Database
{
public string connectionString;

#region Abstract Functions

public abstract IDbConnection CreateConnection();
public abstract IDbCommand CreateCommand();
public abstract IDbConnection CreateOpenConnection();
public abstract IDbCommand CreateCommand(string commandText, IDbConnection connection);
public abstract IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection);
public abstract IDataParameter CreateParameter(string parameterName, object parameterValue);

#endregion
}

It's important to note that we're using .NET's own abstract database factory interfaces. The interfaces can be used in exactly the same way as the concrete database classes (SqlConnection, OdbcCommand, etc), without forcing you to bind to one. This is the core piece to providing flexibility to your database layer.

The Powerful Database Factory

With the details out of the way, we can move on to the power behind the database factory design pattern, which of course, is the factory class itself. The database factory is the class we will use to instantiate the concrete provider for our generic Database class. Remember, we defined an abstract Database class which we can use without deciding on a concrete database provider. Instead, we specify the concrete provider in the web.config and let the database factory design pattern instantiate it. Note, the database factory is able to instantiate any type of concrete database by using C# .NET reflection.

The database factory is defined as follows:

using System.Reflection;
using System.Configuration;

public sealed class DatabaseFactory
{
public static DatabaseFactorySectionHandler sectionHandler = (DatabaseFactorySectionHandler)ConfigurationManager.GetSection("DatabaseFactoryConfiguration");

private DatabaseFactory() { }

public static Database CreateDatabase()
{
// Verify a DatabaseFactoryConfiguration line exists in the web.config.
if (sectionHandler.Name.Length == 0)
{
throw new Exception("Database name not defined in DatabaseFactoryConfiguration section of web.config.");
}

try
{
// Find the class
Type database = Type.GetType(sectionHandler.Name);

// Get it's constructor
ConstructorInfo constructor = database.GetConstructor(new Type[] { });

// Invoke it's constructor, which returns an instance.
Database createdObject = (Database)constructor.Invoke(null);

// Initialize the connection string property for the database.
createdObject.connectionString = sectionHandler.ConnectionString;

// Pass back the instance as a Database
return createdObject;
}
catch (Exception excep)
{
throw new Exception("Error instantiating database " + sectionHandler.Name + ". " + excep.Message);
}
}
}

It's important to note the use of .NET Reflection in the database factory. While this adds the extensibility and power for our database layer, it also adds a bit of overhead to processing. This overhead can be minimized, as you'll see below, by utilizing a static variable so that the number of times objects are instantiated by the factory is minimized.

Putting the Database Factory to Use

We've now implemented the C# ASP .NET database factory design pattern, using the built-in .NET generic database interfaces. To actually use the database factory, we'll create a data worker class to take care of handling how the database factory and generic database are used.

Our data worker class holds a static instance of the abstract Database class. In its constructor, we instantiate the concrete database (defined in the C# ASP .NET web applicaion's web.config file) by using our database factory design pattern. The data worker is defined as follows:

public class DataWorker
{
private static Database _database = null;

static DataWorker()
{
try
{
_database = DatabaseFactory.CreateDatabase();
}
catch (Exception excep)
{
throw excep;
}
}

public static Database database
{
get { return _database; }
}
}

Notice how, in the above code, there are no references to concrete database providers. Nowhere do we reference MSSQL, Oracle, or other concrete types. Everything is kept generic by using the abstract Database class and leaving the details of the concrete type inside the web.config file for the factory to access.

Now, whenever we create a manager class to perform business logic related to the database, we simply inherit from DataWorker. This gives the class instant access to the database routines. For example, we can now create a manager class as follows:

class MyDatabaseLogic : DataWorker
{
public void LoadFlowers()
{
using (IDbConnection connection = database.CreateOpenConnection())
{
using (IDbCommand command = database.CreateCommand("SELECT * FROM FLOWERS", connection))
{
using (IDataReader reader = command.ExecuteReader())
{
// read flowers and process ...
}
}
}
}
}

How About a Concrete Database Class?

Up until this point, we've created abstract and generic classes which defer the decision on which database type to actually use. Now, it's time to create a concrete implementation of the abstract Database object. You will create a concrete Database class for the type of database you plan on using (or may use in the future). If you end up needing to change the database provider, you can change the web.config to use a different concrete implementation.

For this example, we'll define an Oracle Database class as follows:

using Oracle.DataAccess.Client;

public class OracleDatabase : Database
{
public override IDbConnection CreateConnection()
{
return new OracleConnection(connectionString);
}

public override IDbCommand CreateCommand()
{
return new OracleCommand();
}

public override IDbConnection CreateOpenConnection()
{
OracleConnection connection = (OracleConnection)CreateConnection();
connection.Open();

return connection;
}

public override IDbCommand CreateCommand(string commandText, IDbConnection connection)
{
OracleCommand command = (OracleCommand)CreateCommand();

command.CommandText = commandText;
command.Connection = (OracleConnection)connection;
command.CommandType = CommandType.Text;

return command;
}

public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection)
{
OracleCommand command = (OracleCommand)CreateCommand();

command.CommandText = procName;
command.Connection = (OracleConnection)connection;
command.CommandType = CommandType.StoredProcedure;

return command;
}

public override IDataParameter CreateParameter(string parameterName, object parameterValue)
{
return new OracleParameter(parameterName, parameterValue);
}

As you can see in the above code, we fill out the body for each abstract function defined in the Database class. You can customize the abstract and concrete classes further to perform more functionality.

Putting It All Together

Now that our C# ASP .NET database layer is complete, we can use the layer to refactor our original "Simple" database access code listed above. An example is shown below which replaces our Page_Load() database code with usage of our Manager class (which, in turn, uses the Database and factory classes).

class FlowerManager : DataWorker
{
public static void GetFlowers()
{
using (IDbConnection connection = database.CreateOpenConnection())
{
using (IDbCommand command = database.CreateCommand("SELECT * FROM FLOWERS", connection))
{
using (IDataReader reader = command.ExecuteReader())
{
// ...
}
}
}
}
}

protected void Page_Load(object sender, EventArgs e)
{
Flowers = FlowerManager.GetFlowers();

// Populate the flowers in a C# ASP .NET droplist control.
MyDropListControl.DataSource = Flowers;
MyDropListControl.DataBind();
}

What Are Design Patterns and Do I Need Them?

Software professionals may be familiar with the term "Design Patterns," but many have no idea of where they come from and what they truly are. Consequently, some do not see the value and benefits design patterns bring to the software development process, especially in the areas of maintenance and code reuse. This article will bridge this gap by defining design patterns from a historical perspective. It will also summarize the salient features of a typical design pattern and arrive at a working definition so that you will know what they are and what to expect when you incorporate them into your designs. Finally, it will explicitly summarize the benefits design patterns bring to software development and why you should incorporate them into your work. Subsequent articles will present more detailed descriptions of some of the more common design patterns, and how they can be applied to software development on the .NET platform.

What Are Design Patterns and Where Do They Come From?

Design patterns are commonly defined as time-tested solutions to recurring design problems. The term refers to both the description of a solution that you can read, and an instance of that solution as used to solve a particular problem. (I like the analogy of comparing design patterns to a class and an object instance of the class. Each is a different way to represent a thing.) Design patterns have their roots in the work of Christopher Alexander, a civil engineer who wrote about his experience in solving design issues as they related to buildings and towns. It occurred to Alexander that certain design constructs, when used time and time again, lead to the desired effect. He documented and published the wisdom and experience he gained so that others could benefit. About 15 years ago, software professionals began to incorporate Alexander's principles into the creation of early design pattern documentation as a guide to novice developers. This early work led others to also write about design patterns and culminated in the publication of Design Patterns: Elements of Reusable Object-Oriented Software in 1995 by Eric Gamma, Richard Helm, Ralph Johnson, and John Vlissides. This book is considered to be the "coming out" of design patterns to the software community at large and has been influential in the evolution of design patterns since. Design Patterns described 23 patterns that were based on the experience of the authors at that time. These patterns were selected because they represented solutions to common problems in software development. Many more patterns have been documented and cataloged since the publishing of Design Patterns. However, these 23 are probably the best known and certainly the most popular.

Design patterns are represented as relationships between classes and objects with defined responsibilities that act in concert to carry out the solution. To illustrate a design pattern, consider the Adapter pattern, one of the original 23 patterns described in Design Patterns. Adapter provides a solution to the scenario in which a client and server need to interact with one another, but cannot because their interfaces are incompatible. To implement an Adapter, you create a custom class that honors the interface provided by the server and defines the server operations in terms the client expects. This is a much better solution than altering the client to match the interface of the server.

The design pattern community is growing both in membership and coverage. The pattern literature describes new patterns that solve emerging issues related to technical advancements. As a software professional, you are the beneficiary of this body of knowledge. To use these patterns, you will need to learn them and become familiar with them so you will know which pattern to pull from your toolbox when a design issue arises. Many patterns have been documented over the years. They have been classified in different ways by different authors. Take the time to learn different ways to classify design patterns because you will gain greater insight into them. As you learn more and more patterns, it would be a good idea to develop your own classification system; one reflecting the way you utilize them.

Structure of a Design Pattern

Design pattern documentation is highly structured. The patterns are documented from a template that identifies the information needed to understand the software problem and the solution in terms of the relationships between the classes and objects necessary to implement the solution. There is no uniform agreement within the design pattern community on how to describe a pattern template. Different authors prefer different styles for their pattern templates. Some authors prefer to be more expressive and less structured, while others prefer their pattern templates to be more precise and high grain in structure. We will use the template first described by the authors of Design Patterns to illustrate a template.

Term	Description
Pattern Name	Describes the essence of the pattern in a short, but expressive, name
Intent	Describes what the pattern does
Also Known As	List any synonyms for the pattern
Motivation	Provides an example of a problem and how the pattern solves that problem
Applicability	Lists the situations where the pattern is applicable
Structure	Set of diagrams of the classes and objects that depict the pattern
Participants	Describes the classes and objects that participate in the design pattern and their responsibilities
Collaborations	Describes how the participants collaborate to carry out their responsibilities
Consequences	Describes the forces that exist with the pattern and the benefits, trade-offs, and the variable that is isolated by the pattern

This template captures the essential information required to understand the essence of the problem and the structure of the solution. Many pattern templates have less structure than this, but basically cover the same content.

Benefits of Design Patterns

Design patterns have two major benefits. First, they provide you with a way to solve issues related to software development using a proven solution. The solution facilitates the development of highly cohesive modules with minimal coupling. They isolate the variability that may exist in the system requirements, making the overall system easier to understand and maintain. Second, design patterns make communication between designers more efficient. Software professionals can immediately picture the high-level design in their heads when they refer the name of the pattern used to solve a particular issue when discussing system design.