How to test if a DataSet is empty?

Question

I'm modifying someone else's code where a query is performed using the following:

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConn);
da.Fill(ds);

How can I tell if the DataSet is empty (i.e. no results were returned)?

Winger

Answer 1

If I understand correctly, this should work for you

if (ds.Tables[0].Rows.Count == 0)
{
    //
}

Gabbro

I suppose that if the query used multiple tables, this wouldn't work properly. – Winger

Ah, but a simple loop over the count of tables grabbing the count of rows from each would be simple to code. I say toss that check in as well. – Multiparous

What about ds.Tables.Count? – Perverted

It is not a proper answer as it will give error. Cannot find table 0. – Perverted

@captainsac, if you don't have any tables, then yes, your DataSet is empty. It sounds like in your case, you'll need to add a Count check. – Gabbro

@rosscj2533, The question is about to know if DataSet is Empty. If there is Table in it, may be with 0 rows then it is not said as empty as it has one whole table. – Perverted

This won't work always. I just tried same but in my case I received "Table[0] can not be found!". Then i figured out if my query didn't return empty result set , but instead returned error , above method won't work. Instead simple but easier way is to use check for ds.Tables.Count condition. It is easy and works in all scenerios. – Stopcock

I believe this answer should explain that it may throw and error, and the more resilient ds.Tables.Count method should be added. – Bashuk

Throws an exception if dataset is null – Hybris

You could check for (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) that way the first condition fulfilled will prevent an error. – Oaxaca

Answer 2

You don't have to test the dataset.

The Fill() method returns the # of rows added.

See DbDataAdapter.Fill Method (DataSet)

Inappetence

Wish I could use this, but the code which fills the dataset is part of a method which returns the filled dataset. The code I'm writing calls the method like this DataSet ds = GetResults(sql, conn). – Winger

Looping through tables and counting rows will definitely work, but check out the ExtendedProperties property of DataSet, which would allow you to set this kind of custom information from within GetResults() and use it upon return. Might not be applicable here but maybe in the future. – Inappetence

Fill() actually only returns the # of rows for the first table in the DataSet. If there are multiple tables in the DataSet, it doesn't return the actual total # of rows. – Fanjet

Tom - I noticed that property too and am currently using it to return the row count (note I only have one table in my dataset): ds.Tables["TableNameAssignedInFillMethodHere"].ExtendedProperties.Count; – Criminate

Answer 3

It is not a valid answer as it gives following error

Cannot find table 0.

Use the following statement instead

if (ds.Tables.Count == 0)
{
     //DataSet is empty
}

Perverted

Answer 4

You should loop through all tables and test if table.Rows.Count is 0

bool IsEmpty(DataSet dataSet)
{
    foreach(DataTable table in dataSet.Tables)
        if (table.Rows.Count != 0) return false;

    return true;
}

Update: Since a DataTable could contain deleted rows RowState = Deleted, depending on what you want to achive, it could be a good idea to check the DefaultView instead (which does not contain deleted rows).

bool IsEmpty(DataSet dataSet)
{
    return !dataSet.Tables.Cast<DataTable>().Any(x => x.DefaultView.Count > 0);
}

Ambulance

Answer 5

We can check total three ways.

1. if(ds != null)
2. if(ds.Tables.Count > 0 )
3. if(ds.Tables[0].Rows.Count > 0)

Flutterboard

it may be safe to do all three checks. third statement should be ds.Tables[0].Rows.Count > 0 – Loiretcher

3rd one (ds.Tables[0].Rows.Count > 0) throws the following error: Cannot find table 0. – Beautiful

Answer 6

To check dataset is empty or not You have to check null and tables count.

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConn);
da.Fill(ds);
if(ds != null && ds.Tables.Count > 0)
{
 // your code
}

Hybris

Answer 7

You can use something like this

if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
   //Code 
}

Adams

Answer 8

This code will show an error like Table[0] can not be found! because there will not be any table in position 0.

if (ds.Tables[0].Rows.Count == 0)
{
    //
}

Rotogravure

Answer 9

 MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 adap.Fill(ds);
 if (ds.Tables[0].Rows.Count == 0)
 {
      MessageBox.Show("No result found");
 }

query will receive the data in data set and then we will check the data set that is it empty or have some data in it. for that we do ds.tables[0].Rows.Count == o this will count the number of rows that are in data set. If the above condition is true then the data set ie ds is empty.

Pelota

answer is in the dataset that it receive the row or not. just check it is the number of rows are zero or not. – Pelota

Answer 10

Don't forget to set table name da.Fill(ds,"tablename");

So you return data using table name instead of 0

if (ds.Tables["tablename"].Rows.Count == 0)
 {
  MessageBox.Show("No result found");
 }

Nodarse

Answer 11

When returning results from SQL query I find that tables[0] exists, but it has zero rows. So in my situation this worked:

if (ds.Tables[0].Rows.Count == 0) //empty

This did not work:

if (ds.Tables.Count == 0)

Cymophane

Answer 12

Fill is command always return how many records inserted into dataset.

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConn);
var count = da.Fill(ds);
if(count > 0)
{
   Console.Write("It is not Empty");
}

Fortuneteller

Answer 13

A much better way is to use the following:

ds.Tables.Count == 0

As the ds.Tables[0].Rows.Count == 0

can give the error:

System.IndexOutOfRangeException: 'Cannot find table 0.'

Overhasty

Answer 14

This should work

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sqlString, sqlConn);
da.Fill(ds);

if(ds.Tables.Count > 0)
{
  // enter code here
}

Blastogenesis

Answer 15

If (ds != null)

Should do the work for you!

Keystroke