Get query result from NpgsqlDataReader

Question

This is my function:

public int gtCountCertificatByExercice()
{
    DataTable resDataTable = new DataTable();
    DBConnection dbConnection = new DBConnection();
    string query = "SELECT COUNT(id) AS nb_cert " +
                   "FROM crs_certificat " +
                   "WHERE id_exercice = " + IdExercice + " ";
    NpgsqlConnection conn = dbConnection.Conn;
            
    NpgsqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = query;
    Int32 nbCertByExercice = 0;
    try
    {
        conn.Open();
        NpgsqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            nbCertByExercice = reader.GetInt32(0);
        }
        MessageBox.Show("" + nbCertByExercice);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.StackTrace);
        MessageBox.Show(ex.Message);
    }
    conn.Close();
    return nbCertByExercice;
}

and I always get this error: "Specified cast is not valid" !!
but when I use this:

while (reader.Read())
{
    nbCertByExercice = Int32.Parse(reader["nb_cert"].ToString());
}

It works fine !!
I also have the same problem with dateTime type!!
What should I do do get directly the type of the field ?

Answer 1

Why don't you use ExecuteScalar for your query?

replace

 NpgsqlDataReader reader = cmd.ExecuteReader();

 while (reader.Read())
 {
     nbCertByExercice = reader.GetInt32(0);
 }

with

nbCertByExercice  = (Int32) cmd.ExecuteScalar();

you should use reader when you have rows to control, executeScalar to take the first value of the first column of the query, executenonquery for operations like Insert, delete

UPDATE

Looking at the documentation you can see that the return type of COUNT is int and not INt32. When you perform

reader.GetInt32(0);

you get InvalidCastException because it doesn't do explicit conversion of type (refer here).

The exception that is thrown for invalid casting or explicit conversion.

otherwise when you perform Int32.Parse it always try to convert and if it fails trows an exception.

In your case the value is always eligible to be converted to an int32 but reader.GetInt32 can't know because of differents types; on the other side Int32.Parse makes an attempt and it is successful.