Implement Singleton DBManager In ASP.NET Core And C#

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.