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
– Salah Akbari, Commented Apr 23, 2020 at 5: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
– Iliar Turdushev, Commented Apr 23, 2020 at 6:02
@IliarTurdushev Yes, if only ONE row, then I want to read the value. — kudlatiger
– kudlatiger, Commented Apr 23, 2020 at 6:04
If you changed if to while then you could keep track of the row count. — mjwills
– mjwills, Commented Apr 23, 2020 at 6:06

Iliar Turdushev · Accepted Answer · 2020-04-23 10:19:52Z

1

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.
    }
}

edited Apr 23, 2020 at 10:19

answered Apr 23, 2020 at 6:03

Iliar Turdushev

5,3331 gold badge12 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lunar_ninja · Accepted Answer · 2020-04-23 05:54:42Z

1

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#

answered Apr 23, 2020 at 5:54

lunar_ninja

686 bronze badges

Collectives™ on Stack Overflow

How to handle multiple rows scenario in SqlDataReader?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related