Unless you are working with legacy code or systems, most application development is done in a multiple tier or layered environment. This may include a database layer along with the business rules and presentation layers. A key part of every n-tiered environment is the database tier. In this column, we examine how the database layer can be used in your .NET applications.
Whether you are building a Web, Windows, Web service, or any other type of application, you are certain to incorporate database CRUD (Create, Read, Update, and Delete) operations. Many developers often make database calls directly from an application resource like a Web page, but this results in maintenance or code change nightmares—especially, if and when database access changes are necessary.
A current industry trend is to separate the data access code from the rest of the code. With this approach, you can use the necessary database calls via the data access code. This allows you to make database access or code changes without touching the rest of an application. In addition, you may completely remove the data access code to its own class and reuse it across applications.
As with any application development endeavor, there is more than one way to tackle it. You may adopt a strategy to make the data access code database independent. This makes it easy to switch the backend database without affecting other application layers. On the other hand, your environment may have a standardized backend database, so that level of transparency may not be required.
Furthermore, you may choose to generalize the data access code to work across applications or tailor it to a specific application. Like most topics, application developers will argue one way is better than another, but in the end, it depends on your environment, project, and time constraints. Let's explore using a data access layer in a .NET application.
Building a data access layer
Basically, a data access layer is just code we write to interact with the data source; it can be as complex or as simple as you want or need. As an example, we'll create a data access class to handle interactions with the ever-popular SQL Server Northwind database. Our application needs to perform the following database operations:
- Add new customer to customer table
- Delete customer from customer table
- View customer information contained in customer table
- Run stored procedures in the Northwind database.
We begin with the creation of the data access class. Methods are included for each of the items in the previous list. The class includes the following methods to handle these functions:
- The GetDataSet method accepts a lone string parameter containing the name of the stored procedure to run. The procedure is executed with the results stored in a DataSet object that is returned by the method.
- The GetCustomers method is a variation of the GetDataSet method, but it is tailored to interact with the Northwind Customer table and return all columns in a DataSet object.
- The AddCustomer method accepts the necessary data and adds the record to the database.
- The DeleteCustomer method accepts the records id as an integer. It is used to delete the appropriate record from the database.
- The RunProcedure method accepts a string value containing the stored procedure name, which is run in the backend database.
- The GetDataReader method accepts a stored procedure name in a string variable. It is run in the backend database with the results returned by the method as a SqlDataReader object.
- The WriteToEventLog method is used by all methods to record any exceptions in the Windows Event Log.
The database connection string is stored in the Web.config file (this is used in a Web application), but an app.config file could be used as well. The class' ConnectionString property retrieves the value from the configuration file. Also, all methods are static, so they may be used without instantiating an instance of the class. See Listing A.
With this class in place, you can easily use it in an application. For example, a DataGrid can take advantage of the DataAccess class to interact with the database. It could utilize the DataSet object returned by the GetCustomers. Likewise, other methods may be utilized to manipulate backend data. The equivalent VB.NET code for the data access layer class is in Listing B.
Making it transparent
The example presented in this article is tailored to work with a specific database and database platform, but you could design it so the backend platform can easily be changed—this often happens when moving from development to testing to staging and finally into production.
The key to programming a provider-independent data access class is using the interfaces provided by the System.Data namespace, and these interfaces are implemented by all providers. This includes the following interfaces: IDbConnection, IDbDataAdapter, IDataParameter, IDataReader, and IDbTransaction. Now, putting this into action is beyond the scope of this article, but I wanted to mention what is involved.
Separate and centralize
The separation of application code into layers is (most often) logical, plus it makes an application much easier to maintain, enhance, and debug by centralizing core application functions into their own chunks of code. One major application layer is data access. While there are debates about how a data layer (and other layers) may be put into practice, it does provide a way to design an application with a clean separation of code into their functional areas within an application. The code presented in this column is by no means the consummate solution, but it offers an overview of the approach. Let us know how or when you utilize data access layer components in your projects.
Miss a column?
Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.
Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.
Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a production environment on a daily basis.