C# and PostgreSQL Using Npgsql Tutorial 1 - Database Connection

In this tutorial I will share how to make an application with .NET C# that use PostgreSQL database. I am using Visual Studio 2012 for this tutorial. To use Npgsql you must download the library from here. Then add Npgsql.dll and MonoSecurity.dll to project references. References can be found in solution explorer.

After you add Npgsql.dll and MonoSecurity.dll to project references, the next step is to include Npgsql library to your source code using this code:

using Npgsql;

For this tutorial, I will share how to make connection between the application and the database. I will make a class called DatabaseHelper. This class is responsible for handling database functions such as database connection, INSERT, UPDATE, DELETE, SELECT and so on.

The database that I used for this tutorial is consist of one table called tb_music. This table is consist of three columns (id, title, and artist).

To make a connection you need 5 parameters (server, port, user, password, and database). I made these 5 parameters as a constant variable.

// Database connection settings.
private const String SERVER = "localhost";
private const String PORT = "5432";
private const String USER = "postgres";
private const String PASSWORD = "root";
private const String DATABASE = "db_test_c#";

The next step is to make NpgsqlConnection object called connection.

private NpgsqlConnection connection = null;

The constructor of this class will initialize connection object with 5 paramaters that already set before.

// Constructor.
public DatabaseHelper()
{
    // Create connection object.
    connection = new NpgsqlConnection(
        "Server=" + SERVER + ";" +
        "Port=" + PORT + ";" +
        "User Id=" + USER + ";" +
        "Password=" + PASSWORD + ";" +
        "Database=" + DATABASE + ";"
    );
}

The last thing you need to do is to make 2 method, first is for open the database connection and second is for close the database connection.

// Open database connection.
private void openConnection()
{
    try
    {
        connection.Open();
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }
}

// Close database connection.
private void closeConnection()
{
    try
    {
        connection.Close();
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }
}

In the catch block there is a function called showError(), that function is for showing the error message to MessageBox. This is the showError() function.

// Show error to message box.
private void showError(NpgsqlException ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, 
        MessageBoxIcon.Error);
}