CODEWITHSUNDEEP
c#sqldatabase
kudlatiger
kudlatiger

Reputation: 3278

How to handle multiple rows scenario in SqlDataReader?

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:

Upvotes: 1

Views: 124

Answers (2)

Iliar Turdushev
Iliar Turdushev

Reputation: 5213

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

Upvotes: 1

lunar_ninja
lunar_ninja

Reputation: 68

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#

Upvotes: 1

Related Questions