C# and PostgreSQL Using Npgsql Tutorial 2 - INSERT, UPDATE, and DELETE Command

In this tutorial, I will share to you how to make functions for performing basic SQL command such as INSERT, UPDATE, and DELETE. For this tutorial I will add 3 more functions that needed to perform SQL basic commands (INSERT, UPDATE, and DELETE). This is the function for INSERT new record to tb_music table.

// Insert new record to tb_music.
public void insertRecord(Int32 id, String title, String artist)
{
    openConnection();

    try
    {
        // Create insert command.
        NpgsqlCommand command = new NpgsqlCommand("INSERT INTO " +
            "tb_music(id, title, artist) VALUES(:id, :title, " +
            ":artist)", connection);

        // Add paramaters.
        command.Parameters.Add(new NpgsqlParameter("id",
            NpgsqlTypes.NpgsqlDbType.Integer));
        command.Parameters.Add(new NpgsqlParameter("title",
            NpgsqlTypes.NpgsqlDbType.Varchar));
        command.Parameters.Add(new NpgsqlParameter("artist",
            NpgsqlTypes.NpgsqlDbType.Varchar));

        // Prepare the command.
        command.Prepare();

        // Add value to the paramater.
        command.Parameters[0].Value = id;
        command.Parameters[1].Value = title;
        command.Parameters[2].Value = artist;

        // Execute SQL command.
        int recordAffected = command.ExecuteNonQuery();
        if (Convert.ToBoolean(recordAffected))
        {
            showInformation("Data successfully saved!");
        }
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }

    closeConnection();
}

This function has 3 input parameters that corresponding to the record of tb_music table. The second function is updateRecord(). This function is needed for updating the record from the tb_music table based on id. This is the updateRecord() function:

// Update existing record in tb_music by id.
public void updateRecord(Int32 id, String title, String artist)
{
    openConnection();

    try
    {
        // Create update command.
        NpgsqlCommand command = new NpgsqlCommand("UPDATE tb_music " +
            "SET title = :title, artist = :artist WHERE id = :id",
            connection);

        // Add paramaters.
        command.Parameters.Add(new NpgsqlParameter("title",
            NpgsqlTypes.NpgsqlDbType.Varchar));
        command.Parameters.Add(new NpgsqlParameter("artist",
            NpgsqlTypes.NpgsqlDbType.Varchar));
        command.Parameters.Add(new NpgsqlParameter("id",
            NpgsqlTypes.NpgsqlDbType.Integer));

        // Prepare the command.
        command.Prepare();

        // Add value to the paramater.
        command.Parameters[0].Value = title;
        command.Parameters[1].Value = artist;
        command.Parameters[2].Value = id;

        // Execute SQL command.
        int recordAffected = command.ExecuteNonQuery();
        if (Convert.ToBoolean(recordAffected))
        {
            showInformation("Data successfully updated!");
        }
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }

    closeConnection();
}

The last function is deleteRecord(). This function is needed for deleting the record from the tb_music table based on id. This is the deleteRecord() function:

// Delete existing record in tb_music by id.
public void deleteRecord(Int32 id)
{
    openConnection();

    try
    {
        // Create update command.
        NpgsqlCommand command = new NpgsqlCommand("DELETE FROM " +
            "tb_music WHERE id = :id", connection);

        // Add paramaters.
        command.Parameters.Add(new NpgsqlParameter("id",
            NpgsqlTypes.NpgsqlDbType.Integer));

        // Prepare the command.
        command.Prepare();

        // Add value to the paramater.
        command.Parameters[0].Value = id;

        // Execute SQL command.
        int recordAffected = command.ExecuteNonQuery();
        if (Convert.ToBoolean(recordAffected))
        {
            showInformation("Data successfully deleted!");
        }
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }

    closeConnection();
}

Every execution of the SQL command there is a return value that has integer type. This return value is stored in a variable called recordAffected. If the value of this variable is not zero means that the SQL command is successfully executed. Otherwise if the value is zero means that the SQL command is not successfully executed. I used showInformation() function for the notification if the SQL command is successfully executed. This is the showInformation() function:

// Show information to message box.
private void showInformation(String message)
{
    MessageBox.Show(message, "Information", MessageBoxButtons.OK,
        MessageBoxIcon.Information);
}