OracleDataReader reader.Read() starts to read rows from the second record row, skipping the first row record

Question

I have a SQL command and it returns 8 rows from the database.

When I use reader.Read() it starts at the second line, so I loose the fist row result. I don't know why it's skipping the first row record.

command.CommandText = "SELECT ATTRIBUTE_DESCRIPTION, SUBSTR(ATTRIBUTE_DATATYPE, 2, 6) FROM " + proj.PID_Schema + "PIDD.ATTRIBUTES@" + proj.PID_Database + " WHERE " +
                "attribute_name LIKE 'Controller' " +
                "OR attribute_name LIKE 'Initials' " +
                "OR attribute_name LIKE 'IOType' " +
                "OR attribute_name LIKE 'NetworkType' " +
                "OR attribute_name LIKE 'SignalOutput' " +
                "OR attribute_name LIKE 'SignalInput' " +
                "OR attribute_name LIKE 'SPIInstrumentType' " +
                "OR attribute_name LIKE 'Substation' ORDER BY 1";

            try
            {
                reader = command.ExecuteReader();
                reader.Read();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        codelistsNumbersDict.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
                    }
                }
            }
            catch (Exception)
            {
            }

            connection.Close();

            return codelistsNumbersDict;
        }

**Use proper parameterization, do NOT concatenate data into queries.** See https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements and https://bobby-tables.com. Also, you can use a verbatim string for your query `@"SELECT...` then you can use newlines in the string. Furthermore, don't swallow exceptions with an empty `catch` block, and always dispose connection, command, and reader with `using` blocks — Charlieface, Jul 21 '21 at 20:49

dani herrera · Accepted Answer · 2022-05-18T08:34:58.107

Do you have two reads:

reader = command.ExecuteReader();
reader.Read(); // <--- FIRST READ (skipe first row)

if (reader.HasRows)
{
    while (reader.Read())  // <---- SECOND READ
    {

Because of this, you starting reading on second row.

If you take a look to Retrieve data using a DataReader sample, it only has one read:

SqlDataReader reader = command.ExecuteReader();
                   // <-- No read here on sample!
if (reader.HasRows)
{
    while (reader.Read()) // <-- Just ONE READ
    {

Doc screenshot:

score 0 · Answer 2 · answered Jul 21 '21 at 20:43

As mentioned in @dani-herrera's answer, you are calling reader.Read() twice.

Also, you don't need the if (reader.HasRows), since if there are no rows the code inside the do loop won't execute. If you need to know the rows processed, you can always add a counter.

            try
            {
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    codelistsNumbersDict.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
                }
            }

OracleDataReader reader.Read() starts to read rows from the second record row, skipping the first row record

2 Answers2