This article talks about how you can build a reusable data access layer using dotConnect for Oracle, dotConnect for PostgreSQL and C#. It can connect to multiple databases such as Oracle, PostgreSQL, etc for performing CRUD operations. We’ll learn how to use the abstract factory and factory design patterns to select the right data access layer for your application at runtime.
Pre-requisites
You’ll need the following tools to deal with code examples:
- Visual Studio 2019 Community Edition (download)
- Oracle Express Edition (download)
- dotConnect for Oracle (download)
- dotConnect for PostgreSQL (download)
What is a Reusable Data Access Layer?
Broadly speaking, an application comprises the following three layers:
- A Presentation Layer
- A Business Logic Layer
- A Data Access Layer
While the presentation layer is concerned with interacting with the end user, accept and display data in the user interface, the business logic layer contains the business rules and logic of the application. The Data Access Layer comprises a collection of types (classes, interfaces, and their methods) that are responsible for performing CRUD (Create, Read, Update and Delete) operations against the database.
Create a new ASP.NET Core Web API Project
Earlier, we mentioned the necessary tools to proceed to the practical scenarios. The time has come to use those tools.
First, we need to create a new ASP.NET Core Web API project:
- Open Visual Studio 2019.
- Click Create a new project.
- Select ASP.NET Core Web Application and click Next.
- Specify the project name and location to store that project in your system. Optionally, checkmark the Place solution and project in the same directory checkbox.
- Click Create.
- In the Create a new ASP.NET Core Web Application window, select API as the project template.
- Select ASP.NET Core 5 or later as the version.
- Disable the Configure for HTTPS and Enable Docker Support options (uncheck them).
- Since we won’t use authentication in this example, specify authentication as No Authentication.
- Click Create to finish the process.
We’ll use this project in this article.
Implement a Reusable Data Access layer in ASP.NET Core
In this section we’ll implement a simple, reusable data access layer using C#. In this example, we’ll build a reusable data access layer that can be used to work with Oracle and PostgreSql databases. You can make a few minor changes in the code to make it work with other databases such as SQL Server, etc.
Install NuGet Packages
Before you get started building the framework, you should install the dotConnect for Oracle and dotConnect for PostgreSql packages in your project. You can install them either from the NuGet Package Manager tool inside Visual Studio or, from the NuGet Package Manager console using the following commands:
If the installation is successful, you’re all set to get started building your reusable data access layer.
Create the DataProviderType Enum
First off, create an enum named DataProviderType in a file having the same name and write the following code in there:
Create the DBManager Class
Next, create a class named DBManager in a file having the same name and replace the default generated code with the following code:
Now create the following three properties in the DBManager class:
The DBProvider property is used to specify the database type in use when using the DBManager class. The ConnectionString property as the name implies, is used to specify the connection string to connect to the database. Note that both these properties are public since they have to be accessed from outside the class. The other property is DbConnection that refers to the database connection instance.
The GetConnection method checks the database provider type in use and returns an instance of the database connection appropriately. The following code snippet illustrates how the GetConnection method looks like:
The OpenConnection methods takes advantage of the GetConnection method to retrieve a database connection instance. Then it opens the connection if the connection is not already open. The OpenConnection method is as follows:
The CloseConnection method closes an open connection. The following code snippet shows the CloseConnection method:
A command object is required to execute queries against a database. The GetCommand method given below returns a command object based on the database provider in use.
ADO.NET provides support for both connected and disconnected modes of operation. In other words, you can use ADO.NET to connect to a database and stay connected to it while a CRUD operation is in progress. To work with disconnected mode, i.e., when the database connection is not needed, you would typically use a Data Adapters, DataSets and DataTables. A data adapter is used to connect to a database and populate a dataset or a datatable with the required data. The following code snippet shows the GetDataAdapter method that returns a data adapter instance based on the data provider type in use.
The ExecuteScalar method returns an object (single value and hence the name) from a database table based on a query. The following code snippet illustrates the ExecuteScalar method:
The ExecuteNonQuery method is used to execute insert, update or delete statements (i.e., DML statements) against the database. The following code snippet shows the ExecuteNonQuery method of the DBManager class:
The complete source code is given below for your reference:
Using the Reusable Data Access Layer
To use the reusable data access framework we just created, you can write the following piece of code that retrieves a single value from a database table in PostgreSql.
Remember to include the following namespaces in your program:
Summary
In this article, we implemented a simple, reusable data access layer that can connect to Oracle and PostgreSql databases. You can feel free to update the code to make it compliant with other databases such as SQL Server, MySQL, SQLite, IBM’s DB2, etc. You can also add more methods as desired.
