CRUD operations on PostgreSQL using C# and Npgsql

PostgreSQL is one of the most popular relevant databases. It has tons of features, and is open source.

In a previous article, we saw how to run a Postgres instance using Docker.

In this article we will learn how to perform CRUD operations in C # using Npgsql.

Presentation of the project

To borrow a Postgres database, I created a simple .NET API application with CRUD operations.

We will operate on one table that stores information for my board game collection. Of course, we will create, read, update and delete items from the DB (otherwise it will not be an article on CRUD operations 😅).

Before we start writing, we need to install NpgsqlNuGet package that acts as a data provider for PostgreSQL.

Open the connection

Once we have created the app, we can create a show and open a connection in front of our database.

private NpgsqlConnection connection;

public NpgsqlBoardGameRepository()
{
    connection = new NpgsqlConnection(CONNECTION_STRING);
    connection.Open();
}

We just create a NpgsqlConnection Object, and we keep reference to it. We will use this reference to query our DB.

Connecting string

The only parameter we can pass as input to NpgsqlConnection A builder is the connecting string.

You need to assemble it by specifying the host address, the port, the name of the database we are connecting to, and the credentials of the user who queries in db.

private const string CONNECTION_STRING = "Host=localhost:5455;" +
    "Username=postgresUser;" +
    "Password=postgresPW;" +
    "Database=postgresDB";

If you run Postgres using Docker following the steps I described in a previous article, most of the connection string configurations we use are compatible with the environment variables we have previously defined.

CRUD operations

Now that everything’s in place, it’s time to analyze our DB!

We work on a table, games, whose name is stored regularly:

private const string TABLE_NAME = "Games";

God Games The table consists of several fields:

This table is mapped to BoardGame status:

public class BoardGame
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int MinPlayers { get; set; }
    public int MaxPlayers { get; set; }
    public int AverageDuration { get; set; }
}

To check the results again, you can use the UI tool to access the database. For example, if you use pgAdminYou can find the list of databases running on a host.

And if you want to see the contents of a particular table, you can select it under Schemes> Public> Tables> Table Name, then choose View> All Rows

to create

First, we need to put some data into our DB.

public async Task Add(BoardGame game)
{
    string commandText = $"INSERT INTO {TABLE_NAME} (id, Name, MinPlayers, MaxPlayers, AverageDuration) VALUES (@id, @name, @minPl, @maxPl, @avgDur)";
    await using (var cmd = new NpgsqlCommand(commandText, connection))
    {
        cmd.Parameters.AddWithValue("id", game.Id);
        cmd.Parameters.AddWithValue("name", game.Name);
        cmd.Parameters.AddWithValue("minPl", game.MinPlayers);
        cmd.Parameters.AddWithValue("maxPl", game.MaxPlayers);
        cmd.Parameters.AddWithValue("avgDur", game.AverageDuration);

        await cmd.ExecuteNonQueryAsync();
    }
}

God commandText A string contains the complete command to be issued. In this case, it’s simple INSERT Disclaimer.

We use commandText A string for creating a NpgsqlCommandObject by specifying the query and the connection in which we will execute the query. Note that the command must be disposed of after using it: wrap it coming using block.

Next, we will add the parameters to the query. AddWithValue Gets two parameters: The first is the key name, with the same name defined in the query, but without @ symbol; In the query, we use @minPlAnd as a parameter, we use minPl.

Never, ever, create the query by stringing the input parameters as a stringTo prevent SQL Injection attacks.

Finally, we can execute the query asynchronously with ExecuteNonQueryAsync.

to read

Now that we have some games stored in our table, we can retrieve these items:

public async Task<BoardGame> Get(int id)
{
    string commandText = $"SELECT * FROM {TABLE_NAME} WHERE ID = @id";
    await using (NpgsqlCommand cmd = new NpgsqlCommand(commandText, connection))
    {
        cmd.Parameters.AddWithValue("id", id);

        await using (NpgsqlDataReader reader = await cmd.ExecuteReaderAsync())
            while (await reader.ReadAsync())
            {
                BoardGame game = ReadBoardGame(reader);
                return game;
            }
    }
    return null;
}

Again, we define the query as text, using it to create a NpgsqlCommandSpecify the parameter values, and then we execute the query.

God ExecuteReaderAsync Return method a NpgsqlDataReader An object that we can use to retrieve the data. We update the location of the stream with reader.ReadAsync()And then we convert the current data with ReadBoardGame(reader) In this way:

private static BoardGame ReadBoardGame(NpgsqlDataReader reader)
{
    int? id = reader["id"] as int?;
    string name = reader["name"] as string;
    short? minPlayers = reader["minplayers"] as Int16?;
    short? maxPlayers = reader["maxplayers"] as Int16?;
    short? averageDuration = reader["averageduration"] as Int16?;

    BoardGame game = new BoardGame
    {
        Id = id.Value,
        Name = name,
        MinPlayers = minPlayers.Value,
        MaxPlayers = maxPlayers.Value,
        AverageDuration = averageDuration.Value
    };
    return game;
}

This method simply reads the data associated with each column (for example, reader["averageduration"]), Then convert them to their data type. Then build and return a BoardGame resist.

updating

Updating items is similar to inserting a new item.

public async Task Update(int id, BoardGame game)
{
    var commandText = $@"UPDATE {TABLE_NAME}
                SET Name = @name, MinPlayers = @minPl, MaxPlayers = @maxPl, AverageDuration = @avgDur
                WHERE id = @id";

    await using (var cmd = new NpgsqlCommand(commandText, connection))
    {
        cmd.Parameters.AddWithValue("id", game.Id);
        cmd.Parameters.AddWithValue("name", game.Name);
        cmd.Parameters.AddWithValue("minPl", game.MinPlayers);
        cmd.Parameters.AddWithValue("maxPl", game.MaxPlayers);
        cmd.Parameters.AddWithValue("avgDur", game.AverageDuration);

        await cmd.ExecuteNonQueryAsync();
    }
}

Of course, the query is different, but the general structure is the same: create the query, create the command, add parameters, execute the query with ExecuteNonQueryAsync.

Clear

Just for the sake of completeness, here’s how to delete an item by specifying its identifier.

public async Task Delete(int id)
{
    string commandText = $"DELETE FROM {TABLE_NAME} WHERE ID=(@p)";
    await using (var cmd = new NpgsqlCommand(commandText, connection))
    {
        cmd.Parameters.AddWithValue("p", id);
        await cmd.ExecuteNonQueryAsync();
    }
}

Always the same story, so I have nothing to add.

ExecuteNonQueryAsync vs. ExecuteReaderAsync

As you have seen, some operations use ExecuteNonQueryAsyncWhile some others use ExecuteReaderAsync. Why?

ExecuteNonQuery and ExecuteNonQueryAsync Execute connection commands. These methods do not return data from the database, but only the number of affected rows. They are used to perform INSERT, UPDATE and delete operations.

However, ExecuteReader and ExecuteReaderAsync Are used to query the database and return a DbDataReader An object, which is a read-only stream of rows retrieved from the data source. They are used in conjunction with SELECT queries.

Bonus 1: Create the table if it does not already exist

Of course, you can also create tables programmatically.

public async Task CreateTableIfNotExists()
{
    var sql = $"CREATE TABLE if not exists {TABLE_NAME}" +
        $"(" +
        $"id serial PRIMARY KEY, " +
        $"Name VARCHAR (200) NOT NULL, " +
        $"MinPlayers SMALLINT NOT NULL, " +
        $"MaxPlayers SMALLINT, " +
        $"AverageDuration SMALLINT" +
        $")";

    using var cmd = new NpgsqlCommand(sql, connection);

    await cmd.ExecuteNonQueryAsync();
}

Again, nothing fancy: create the command text, create a NpgsqlCommand Object, follow the command.

Bonus 2: Check the database version

To check if the database is working, and your credentials are correct (the ones defined in the connection string), you may want to retrieve the DB version.

You can do this in 2 ways.

In the following method, you query the version directly in the database.

public async Task<string> GetVersion()
{
    var sql = "SELECT version()";

    using var cmd = new NpgsqlCommand(sql, connection);

    var versionFromQuery = (await cmd.ExecuteScalarAsync()).ToString();

    return versionFromQuery;
}

This method returns a lot of information that depends directly on the database instance. In my case, I see PostgreSQL 14.1 (Debian 14.1-1.pgdg110 + 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit.

The second way is to use PostgreSqlVersion.

public async Task<string> GetVersion()
{
    var versionFromConnection = connection.PostgreSqlVersion;

    return versionFromConnection;
}

PostgreSqlVersion Returns a Version An object that contains several fields like Major, Minor, RevisionAnd more.

You can call ToString A method of the same object to get a value like “14.1”.

Additional readings

In a previous article, we saw how to download and run a PostgreSQL instance on your local computer using Docker.

How to Run PostgreSQL Locally with Docker | Code4IT

To execute PostgreSQL queries with C #, we used the Npsgql NuGet package. So you may want to read the official documentation.

🔗 Documentation Npgsql | Npsgql

In particular, an important part to consider is the mapping between the C # and SQL data types:

🔗 PostgreSQL mapping to C # | Npsgql

When I talked about parameters to be passed to the query, I mentioned the SQL injection vulnerability. Here you can read more about it.

🔗 SQL Injection | Impreva

Finally, here you can find the repository used for this article.

🔗 Database used for this article GitHub

Finishing

In this article, we learned how to perform simple operations on a PostgreSQL database to retrieve and update the contents of a table.

This is the most basic way to perform these actions. You explicitly write the queries and take them out without much stuff in between.

In future articles, we will look at some other ways to perform the same operations in C #, but using different tools and packages. Maybe an entity framework? Maybe Dapper? Keep following!

Happy coding!

🐧