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)?

score 85 · Accepted Answer · answered Jun 04 '10 at 17:44

85

If I understand correctly, this should work for you

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

answered Jun 04 '10 at 17:44

rosscj2533

9,195
7
39
56

I suppose that if the query used multiple tables, this wouldn't work properly. – MCS Jun 04 '10 at 17:57
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. – jcolebrand Jun 04 '10 at 18:40
4

What about ds.Tables.Count? – captainsac Aug 12 '14 at 10:32
6

It is not a proper answer as it will give error. Cannot find table 0. – captainsac Aug 12 '14 at 10:34
@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. – rosscj2533 Aug 12 '14 at 12:55
@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. – captainsac Aug 12 '14 at 13:24
8

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. – Ubaid Ashraf Aug 15 '14 at 16:51
I believe this answer should explain that it may throw and error, and the more resilient `ds.Tables.Count` method should be added. – seebiscuit Jun 16 '15 at 14:56
Throws an exception if dataset is null – Munavvar Sep 03 '16 at 11:14
9

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. – David Oct 06 '16 at 19:45

captainsac · Answer 2 · 2015-05-19T12:01:45.853

21

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
}

edited May 19 '15 at 12:01

answered Aug 12 '14 at 10:38

captainsac

2,484
3
27
48

score 21 · Answer 3 · edited Nov 05 '14 at 15:38

21

You don't have to test the dataset.

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

See DbDataAdapter.Fill Method (DataSet)

edited Nov 05 '14 at 15:38

Malachi

3,205
4
29
46

answered Jun 04 '10 at 17:43

Tom

1,269
8
12

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). – MCS Jun 04 '10 at 18:00
2

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. – Tom Jun 04 '10 at 18:16
`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. – Doug S Sep 30 '17 at 05:32
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; – Doreen Oct 30 '18 at 18:43

Jürgen Steinblock · Answer 4 · 2015-07-27T05:50:26.140

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);
}

score 11 · Answer 5 · edited Mar 01 '19 at 16:49

11

We can check total three ways.

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

edited Mar 01 '19 at 16:49

full-stack

553
5
20

answered Mar 25 '15 at 11:44

prabhakar changala

121
1
3

2

it may be safe to do all three checks. third statement should be ds.Tables[0].Rows.Count > 0 – Samuel Nov 30 '15 at 18:57
1

3rd one (ds.Tables[0].Rows.Count > 0) throws the following error: Cannot find table 0. – Jebastin J Dec 28 '15 at 12:27

score 3 · Answer 6 · answered Sep 02 '16 at 07:10

3

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
}

answered Sep 02 '16 at 07:10

Munavvar

802
1
11
33

score 2 · Answer 7 · answered Mar 08 '21 at 07:35

2

You can use something like this

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

answered Mar 08 '21 at 07:35

Mohsin Khan

175
11

score 1 · Answer 8 · edited Nov 11 '14 at 13:47

1

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)
{
    //
}

edited Nov 11 '14 at 13:47

StormeHawke

5,987
5
45
73

answered Oct 18 '13 at 18:49

Subhash PM

11
1

NomanJaved · Answer 9 · 2015-01-15T18:00:05.897

0

 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.

edited Jan 15 '15 at 18:00

answered Jan 15 '15 at 14:00

NomanJaved

1,324
17
32

answer is in the dataset that it receive the row or not. just check it is the number of rows are zero or not. – NomanJaved Jan 15 '15 at 18:09

score 0 · Answer 10 · answered Jun 23 '16 at 10:20

0

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");
 }

answered Jun 23 '16 at 10:20

Salem Ahmed

1,067
2
12
18

score 0 · Answer 11 · answered Aug 12 '16 at 13:44

0

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)

answered Aug 12 '16 at 13:44

James Grigley

83
1
8

score 0 · Answer 12 · answered Jan 10 '17 at 06:16

0

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");
}

answered Jan 10 '17 at 06:16

Natarajan Ganapathi

551
1
7
19

score 0 · Answer 13 · answered Dec 25 '21 at 07:49

0

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

answered Dec 25 '21 at 07:49

Waqas Ahmed

21
4

score -1 · Answer 14 · answered Jul 25 '19 at 13:31

-1

This should work

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

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

answered Jul 25 '19 at 13:31

sergio abasto

1

score -4 · Answer 15 · edited Mar 10 '14 at 15:01

-4

If (ds != null)

Should do the work for you!

edited Mar 10 '14 at 15:01

Carl0s1z

4,683
7
32
47

answered Mar 10 '14 at 14:37

Sandesjøl

1
1

How to test if a DataSet is empty?

15 Answers15

Linked

Related