Introduction

Designing a C# ASP .NET web application which utilizes a database is a common and straight-forward task for developers. The web application accesses various tables, stored procedures, executes SQL script, and retrieves records. Often, developers not familiar with design patterns will use a simple design for making database calls, which relies on calling database functions straight from the user interface. While this certainly works (and provided you close and dispose of all connections when you're done with them), you achieve a far greater amount of flexibility with your C# ASP .NET web application be using a well-defined and loosely-coupled database layer.

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:

<configuration>
  <configSections>
    <section name="DatabaseFactoryConfiguration" type="MyStuff.Managers.DatabaseFactorySectionHandler, MyStuff.Managers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
  </configSections>

  <connectionStrings>
    <clear/>
    <add name="MyConnection1" providerName="Oracle.DataAccess.Client" connectionString="Your Connection String Here" />
    <add name="MyConnection2" providerName="MSSQL" connectionString="Your Connection String Here" />
  </connectionStrings>

  <DatabaseFactoryConfiguration Name="MyStuff.Managers.OracleDatabase" ConnectionStringName="MyConnection1" />

</configuration>

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

Conclusion

The database layer is a powerful addition to the C# ASP .NET web application. It provides advanced extensibility, flexibility, and adaptation to change. By implementing the database layer with a database factory design pattern, we can add enhanced power to our architecture and better anticipate changes in database platforms and connections. Separating database code from application level code is a key method for providing better readability and maintenance of code in C# ASP .NET.

About the Author

This article was written by Kory Becker, founder and chief developer of Primary Objects, a software and web application development company. You can contact Primary Objects regarding your software development needs at http://www.primaryobjects.com