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 and click Next. ASP.NET Core Web Application Specify the project name and location to store that project in your system. Optionally, checkmark the checkbox. Place solution and project in the same directory Click . Create In the window, select as the project template. Create a new ASP.NET Core Web Application API Select or as the version. ASP.NET Core 5 later Disable the options (uncheck them). Configure for HTTPS and Enable Docker Support Since we won’t use authentication in this example, specify authentication as No Authentication. Click to finish the process. Create 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.

Keep

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Alternative Overview of the SSMSBoost Add-in for SQL Server Management Studio (SSMS)

A Starter Guide to Middleware in ASP.NET Core

30-Day .NET Challenge - Day 26: Loop Unrolling

How To Implement API Key Authentication Middleware in ASP NET Core

Autofac ComponentRegistryBuilder in ASP.NET Core

IServiceCollection in Console Applications - A How To Guide

Alternative Overview of the SSMSBoost Add-in for SQL Server Management Studio (SSMS)

A Starter Guide to Middleware in ASP.NET Core

30-Day .NET Challenge - Day 26: Loop Unrolling

How To Implement API Key Authentication Middleware in ASP NET Core

Autofac ComponentRegistryBuilder in ASP.NET Core

IServiceCollection in Console Applications - A How To Guide

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps