C# and PostgreSQL Using Npgsql Tutorial 3 - SELECT Command

In this tutorial, I will share to you about SQL SELECT command. For me there is two technique that commonly used for read the result of SELECT command. The first technique is use NpgsqlDataReader and the second is use NpgsqlDataAdapter. This is the example code when using NpgsqlDataReader:

// Select all records form tb_music using NpgsqlDataReader.
public List<Music> selectAllRecordsUsingList()
{
    List<Music> music = new List<Music>();

    openConnection();

    try
    {
        // Create select command.
        NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM " + 
            "tb_music ORDER BY id ASC", connection);

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

        // Execute SQL command.
        NpgsqlDataReader dr = command.ExecuteReader();

        // Fill results to music list.
        while (dr.Read())
        {
            music.Add(new Music(dr.GetInt32(0), dr.GetString(1), 
                dr.GetString(2)));
        }
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }

    closeConnection();

    return music;
}

This technique is suitable if you want to take the record data by column. This function has a return value type of collection objects, in this case I'm using List that store Music objects. The Music class is made in separated file called Music. This class is consists of variable that needed for storing the record data from tb_music table.

class Music
{    
    // Music record field.
    private Int32 id;
    private String title;
    private String artist;

    // Constructor.
    public Music(Int32 id, String title, String artist)
    {
        this.id = id;
        this.title = title;
        this.artist = artist;
    }

    // Getter and setter.
    public Int32 Id
    {
        get
        {
            return this.id;
        }
        set
        {
            this.id = value;
        }
    }

    public String Title
    {
        get
        {
            return this.title;
        }
        set
        {
            this.title = value;
        }
    }

    public String Artist
    {
        get
        {
            return this.artist;
        }
        set
        {
            this.artist = value;
        }
    }
}

Another technique is use NpgsqlDataAdapter. This technique is suitable for selecting record and display them in DataGridView. This function has a return type of DataSet.

// Select all records form tb_music using NpgsqlDataAdapter.
public DataSet selectAllRecordsUsingDataSet()
{
    DataSet ds = new DataSet();

    openConnection();

    try
    {
        // Create select command using data adapter.
        NpgsqlDataAdapter da = new NpgsqlDataAdapter("SELECT * FROM " + 
            "tb_music ORDER BY id ASC", connection);

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

        // Fill results into data set.
        da.Fill(ds);
    }
    catch (NpgsqlException ex)
    {
        showError(ex);
    }

    closeConnection();

    return ds;
}