C# and PostgreSQL Introduction

PostgreSQL is a database that can be used in conjunction with C# to create desktop, web and console based applications. It can be used to store data, as well as configuration information. C#, together with SQL, can be used to query the data stored within a PostgreSQL database, as well as insert, update and delete the data.

Connecting to a PostgreSQL Database

In order to access data within a PostgreSQL database using C#, a connection to the database must first be established. To facilitate this, the package 'Npgsql' needs to be added to the project. This can be done in a number of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. It also has an integrated package manager console, where this package can be added using the following command.

Install-Package Npgsql

For IDEs that don't have a built in package manager, Powershell can be used. The following command can be used to install the above mentioned package. Before running this command it is necessary to navigate to the folder where the project resides.

dotnet add package Npgsql

Once added, a ‘using‘ statement for the ‘Npgsql’ namespace needs to be included.

// Database variables.
string host = "localhost";
string database = "Demo";
string username = "DemoUN";
string password = "DemoPW";

// Database connection variable.
NpgsqlConnection connect = new NpgsqlConnection(
    "Server=" + host + ";" +
    "Database=" + database + ";" +
    "User Id=" + username + ";" +
    "Password=" + password);

try
{

    // Connect to database.
    connect.Open();

    // Message confirming successful database connection.
    Console.WriteLine("Database connection successful.");

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.
    System.Environment.Exit(1);

}

In this example, a ‘try-catch’ block is used to catch any exceptions that may arise in connecting to the database. A ‘try-catch’ block can be used to handle any exceptions in a user friendly manner. The ‘System.Environment.Exit(1)’ statement, after the confirmation of an unsuccessful database connection, stops execution of the program completely, so no further statements are executed.

It should be noted that it isn’t necessary to have the server and database information in separate variables before using it in the database connection. It much depends on personal preference and whether the variables are going to be re-used elsewhere in the program. The database connection variable can be re-written as follows.

// Database connection variable.
NpgsqlConnection connect = new NpgsqlConnection(
    "Server=localhost;" +
    "Database=Demo;" +
    "User Id=DemoUN;" +
    "Password=DemoPW");

Further Resources