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.
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.
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.
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:
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.
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.
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:
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.
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:
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.
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:
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.
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:
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:
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:
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.
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).
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.
This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.