Implement a Singleton DBManager in ASP.NET Core and C#

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.

publicvoid 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.