How to handle multiple rows scenario in SqlDataReader?

Question

I have a SQL query which supposed to return only ONE row from the business database. Based on this, I have written following sql script to get the data from the result set.

string query = @"select 
ProdMaster.data_Id Id,
ProdMaster.data_name Name,
ProdMaster.data_countryname CountryName
from RM.Db
order by ProdMaster.data.FromDate desc"

SqlCommand command = new SqlCommand(query, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
 if (reader.Read()) 
   {                            
      countryname = reader["CountryName"].ToString(); 
   }
}

But, there is some data issue in the database, sometimes it returns multiple rows.

How do we check the row count? If rows more than one we want to return a custom exception.

Note:

I do not want to use COUNT(*) in the query.
We don't have control on RM.Db database - it might have data issues (3rd party)

It depends on what you have in your `command`. Can you show that? — Salah Akbari, Apr 23 '20 at 05:24
Do you want to know exact number of rows returned or just want to throw an exception if number of rows is more than 1? — Iliar Turdushev, Apr 23 '20 at 06:02
@IliarTurdushev Yes, if only ONE row, then I want to read the value. — kudlatiger, Apr 23 '20 at 06:04
If you changed `if` to `while` then you could keep track of the row count. — mjwills, Apr 23 '20 at 06:06

score 1 · Answer 1 · answered Apr 23 '20 at 05:54

You can use SqlDataAdapter instead and fill the contents from the table in a dataset. The dataset will have a table inside it you can count the row like this - ds.Tables[0].Rows.Count

There can be problems related to Datareader as it is a stream of data and db can have changes while reading. A more thorough discussion on the same can be found on this thread - How to get number of rows using SqlDataReader in C#

Iliar Turdushev · Accepted Answer · 2020-04-23T10:19:52.000

Don't you consider the next approach to solve your problem:

SqlCommand command = new SqlCommand(query, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.Read()) 
    {                            
        countryname = reader["CountryName"].ToString(); 
    }

    // Try to read the second row.
    if (reader.Read())
    {
        // If we are inside this if-statement then it means that the query has returned more than one row.
        // Here a custom exception must be thrown.
    }
}

How to handle multiple rows scenario in SqlDataReader?

2 Answers2