Introduction
This article talks about the Singleton Design Pattern, why it is important and then demonstrates how to build a singleton DBManager (a class that wraps all calls to the database) using dotConnect for PostgreSQL and C#. It also discusses the pros and cons of this approach.
Pre-requisites
You’ll need the following tools to deal with code examples:
- Visual Studio 2019 Community Edition (download)
- dotConnect for PostgreSQL (download)
Introduction to Design Patterns
Design patterns can be used to solve recurring problems or complexities in software development. They are divided into three categories: structural, behavioral, and creational. Creational patterns can be used to create instances of classes and to manage them. However, structural patterns define the types and their relationships and help to understand the relationships between the entities. Behavioral design patterns emphasize on how objects collaborate and how responsibilities are delegated between them.
What is a Singleton Design Pattern?
The Singleton Design Pattern, as the name suggests, restricts the instantiation of a class to one object only. In other words, a class that follows the Singleton Design Pattern will not allow more than one instance of it to be created.
Use Cases
The typical use cases of the Singleton Design Pattern are:
- LogManager
- DbManager
- ServiceRegistry
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 Singleton DBManager in ASP.NET Core and C#
In this section we’ll implement a simple DBManager class that follows the Singleton Design Pattern.
Create a Database Table
Create a new database table using the following script:
CREATE TABLE books(
book_id serial PRIMARY KEY,
book_title VARCHAR (255) UNIQUE NOT NULL,
book_pages INT NOT NULL
);
We’ll use this database table throughout this article to store and retrieve data.
Install NuGet Packages
To get started you should install the dotConnect for PostgreSql package in your project. You can install it either from the NuGet Package Manager tool inside Visual Studio or, from the NuGet Package Manager console using the following command:
PM> Install-Package Devart.Data.PostgreSql
If the installation is successful, you're all set to get started using dotConnect for PostgreSQL in your application.
Configuring the Application
You should specify the database connection string in a config file, i.e., appsettings.json and then read the connection string in your application. Replace the default generated code of the appsettings.json with the following code:
{
"PostgreSqlConnectionString": {
"DefaultConnection": "UserId = postgres; Password =
mypass;host=localhost;database=Test;"
},
"AllowedHosts": "*"
}
You should also add the IConfiguration instance to the services container so that you can access it from anywhere in the application.
public void ConfigureServices(IServiceCollection services)
{
services.AddSingleton(Configuration);
services.AddControllers();
}
Create the DBManager Class
To keep things simple, we'll build a DBManager with minimal features. Now, create a class called DBmanager inside a fie having the same primary name with a .cs extension and write the following code in there:
public class DBManager
{
public int ExecuteNonQuery(string commandText)
{
return 1;
}
public int ExecuteReader(string commandText)
{
return 1;
}
}
Read Data from the Database
To read data from the database using dotConnect for PostgreSQL, we can create a PgSqlDataAdapter instance and then use it to fill a data table and return it. The ExecuteReader method shown in the following code snippet illustrates how this can be achieved:
public DataTable ExecuteReader(string commandText)
{
DataTable dataTable = new DataTable();
using (PgSqlConnection pgSqlConnection = new
PgSqlConnection(ConnectionString))
{
using (PgSqlCommand pgSqlCommand = new PgSqlCommand())
{
pgSqlCommand.CommandText = commandText;
pgSqlCommand.Connection = pgSqlConnection;
if (pgSqlConnection.State != ConnectionState.Open)
pgSqlConnection.Open();
PgSqlDataAdapter pgSqlDataAdapter = new
PgSqlDataAdapter(pgSqlCommand);
pgSqlDataAdapter.Fill(dataTable);
if(dataTable.Rows.Count > 0)
return dataTable;
return null;
}
}
}
The ExecuteReader method of the DBManager class returns an instance of a DataTable. Now, add the following property in the DBManager class to store the database connection string:
public string ConnectionString
{
get;set;
}
Return a List from the ExecuteReader Method
You can also return a List from the ExecuteReader method. Create a class named Book with the following content in there:
public class Book
{
public int book_id { get; set; }
public string book_title { get; set; }
public int book_pages { get; set; }
}
Replace the source code of the ExecuteReader method of the DBManager class with the following code:
public List<Book> ExecuteReader(string commandText)
{
DataTable dataTable = new DataTable();
using (PgSqlConnection pgSqlConnection = new
PgSqlConnection(ConnectionString))
{
using (PgSqlCommand pgSqlCommand = new PgSqlCommand())
{
pgSqlCommand.CommandText = commandText;
pgSqlCommand.Connection = pgSqlConnection;
if (pgSqlConnection.State != ConnectionState.Open)
pgSqlConnection.Open();
PgSqlDataAdapter pgSqlDataAdapter = new
PgSqlDataAdapter(pgSqlCommand);
pgSqlDataAdapter.Fill(dataTable);
return dataTable.ToList<Book>();
}
}
}
Note that the return type has been changed from DataTable to List<Book> and how an instance of List<Book> is created using an extension method called ToList<T>().
The following code listing illustrates a class named Extensions that contains the ToList extension method.
public static class Extensions
{
public static List<T> ToList<T>(this DataTable dataTable) where T : new()
{
List<T> data = new List<T>();
foreach (DataRow row in dataTable.Rows)
{
T item = GetItemFromDataRow<T>(row);
data.Add(item);
}
return data;
}
public static T GetItemFromDataRow<T>(DataRow dataRow)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();
foreach (DataColumn column in dataRow.Table.Columns)
{
foreach (PropertyInfo propertyInfo in temp.GetProperties())
{
if (propertyInfo.Name == column.ColumnName)
propertyInfo.SetValue(obj, dataRow[column.ColumnName], null);
}
}
return obj;
}
}
Note how an instance of a DataTable is converted to an instance of List<T>. The GetItemFromDataRow method returns an instance of the Book class.
Insert Data to the Database
To insert data to the PostgreSQL database using dotConnect for PostgreSQL, you can use the following method:
public int ExecuteNonQuery(string commandText)
{
using (PgSqlConnection pgSqlConnection = new
PgSqlConnection(ConnectionString))
{
using (PgSqlCommand pgSqlCommand = new PgSqlCommand())
{
pgSqlCommand.CommandText = commandText;
pgSqlCommand.Connection = pgSqlConnection;
if (pgSqlConnection.State != ConnectionState.Open)
pgSqlConnection.Open();
return pgSqlCommand.ExecuteNonQuery();
}
}
}
The Complete Source Code
The complete source code of the DBManager class is given below:
public class DBManager
{
public string ConnectionString
{
get;set;
}
public List<Book> ExecuteReader(string commandText)
{
DataTable dataTable = new DataTable();
using (PgSqlConnection pgSqlConnection = new
PgSqlConnection(ConnectionString))
{
using (PgSqlCommand pgSqlCommand = new PgSqlCommand())
{
pgSqlCommand.CommandText = commandText;
pgSqlCommand.Connection = pgSqlConnection;
if (pgSqlConnection.State != ConnectionState.Open)
pgSqlConnection.Open();
PgSqlDataAdapter pgSqlDataAdapter = new
PgSqlDataAdapter(pgSqlCommand);
pgSqlDataAdapter.Fill(dataTable);
return dataTable.ToList<Book>();
}
}
}
public int ExecuteNonQuery(string commandText)
{
using (PgSqlConnection pgSqlConnection = new
PgSqlConnection(ConnectionString))
{
using (PgSqlCommand pgSqlCommand = new PgSqlCommand())
{
pgSqlCommand.CommandText = commandText;
pgSqlCommand.Connection = pgSqlConnection;
if (pgSqlConnection.State != ConnectionState.Open)
pgSqlConnection.Open();
return pgSqlCommand.ExecuteNonQuery();
}
}
}
}
The Singleton DBManager Class
To make the DBManager class a singleton class, you should have a private constructor. A private constructor would prevent the class from being extended or instantiated. Since we don't want more than one instance of this class, a private constructor is needed.
You should then create an instance of the DBManager inside a static property as shown in the code snippet given below:
public class DBManager
{
private static DBManager instance;
private DBManager() { }
public static DBManager Instance
{
get
{
if (instance == null)
{
instance = new DBManager();
}
return instance;
}
}
//Other methods removed for brevity
}
Since the DBManager instance is static, you would have only one instance throughout the lifetime of the application. You can access the DBManager class, and its methods as shown in the following code snippet:
DBManager.Instance.ConnectionString = connectionString;
return DBManager.Instance.ExecuteReader("select * from public.books");
Make the DBManager Class ThreadSafe
But what if you would like to make it thread safe so that no two threads can have access to the critical section? To make this class thread safe, you can take advantage of the lock keyword as illustrated in the following code snippet:
public class DBManager
{
private static object lockObj = new object();
private static DBManager instance;
private DBManager() { }
public static DBManager Instance
{
get
{
lock(lockObj)
{
if (instance == null)
{
instance = new DBManager();
}
}
return instance;
}
}
//Other methods removed for brevity
}
Use the DBManager Class in Controller Classes
You can now use the DBManager instance in your controller class like this:
[Route("api/[controller]")]
[ApiController]
public class BooksController : ControllerBase
{
readonly IConfiguration _configuration;
readonly string connectionString;
public BooksController(IConfiguration configuration)
{
_configuration = configuration;
connectionString =
_configuration["PostgreSqlConnectionString:DefaultConnection"];
}
[HttpGet]
public List<Book> Get()
{
DBManager.Instance.ConnectionString = connectionString;
return DBManager.Instance.ExecuteReader("select * from public.books");
}
}
Summary
A DBManager class serves as a helper to connect to databases and perform required CRUD operations. Ideally it should be a singleton since you don’t need multiple instances of it in your application. This article has demonstrated how we can build a thread-safe DBManager class in C# and use it in an ASP.NET Core application.