how to check if a datareader is null or empty

Question

I have a datareader that return a lsit of records from a sql server database. I have a field in the database called "Additional". This field is 50% of the time empty or null.

I am trying to write code that checks if this field isnull. The logic behind this is: If the field "Additional" contains text then display the info otherwise hide the field.

I have tried:

if (myReader["Additional"] != null)
{
    ltlAdditional.Text = "contains data";
}
else
{
     ltlAdditional.Text = "is null";
}

The above code gives me this error:

Exception Details: System.IndexOutOfRangeException: Additional

Any help would be greatly appreciated...

1

Thanks for your response Robert, I tried your suggested code however it still gives me "System.IndexOutOfRangeException: Additional" because in this case "Additional" is null – Jason Apr 18 '09 at 04:25
Although this is an older post, in VB "If reader("Additional") <> System.DBNull.Value Then" this code returns the error "Operator '<>' is not defined for types 'Object' and 'System.DBNull' – htm11h May 24 '12 at 13:47
In VB the syntax is "If Not reader("Additional") Is DBNull.Value Then" – George Dec 12 '13 at 09:14

score 16 · Answer 2 · edited Mar 10 '16 at 16:28

16

if (myReader.HasRows) //The key Word is **.HasRows**

{

    ltlAdditional.Text = "Contains data";

}

else

{   

    ltlAdditional.Text = "Is null Or Empty";

}

edited Mar 10 '16 at 16:28

Gabriel GM

6,391
2
31
34

answered Aug 23 '13 at 08:11

Kemal Can ÖZÇELİK

724
7
12

Thanks it helped me but in Windows Phone 8.1 it shuld be myReader.HasRows, without "()". – Icet Aug 20 '15 at 09:38
This answers my question and the question in the title, but the original post suggests that the title is totally wrong. Glad its here for people looking for an answer to the title question. Just sad its so far down... – Hans Goldman Mar 02 '17 at 23:18
I'm not sure this is valid for the circumstance. Unless the query fails to execute, the SqlReader will ALWAYS have at least one empty dataset row. – Jesse Williams Nov 07 '18 at 15:19

score 11 · Answer 3 · answered Oct 20 '10 at 20:39

I haven't used DataReaders for 3+ years, so I wanted to confirm my memory and found this. Anyway, for anyone who happens upon this post like I did and wants a method to test IsDBNull using the column name instead of ordinal number, and you are using VS 2008+ (& .NET 3.5 I think), you can write an extension method so that you can pass the column name in:

public static class DataReaderExtensions
{
    public static bool IsDBNull( this IDataReader dataReader, string columnName )
    {
        return dataReader[columnName] == DBNull.Value;
    }
}

Kevin

score 8 · Answer 4 · edited Nov 15 '11 at 20:16

8

This is the correct and tested solution

if (myReader.Read())
{

    ltlAdditional.Text = "Contains data";
}
else
{   
    ltlAdditional.Text = "Is null";
}

edited Nov 15 '11 at 20:16

dmc

2,596
21
24

answered Mar 08 '11 at 23:54

Ahmed Fahmy

97
1
1

2

what if their if `NULL` in particular column..? – shashwat Feb 17 '13 at 06:47
Calling .Read() reads in the first row, so if DataReader is passed into a method / function with while(DataReader.Read()), would the first row then be skipped? If it does, I suppose we could use a do while. – Ernest May 28 '13 at 20:33
This will only test if the DataReader has rows. The question was how to test for NULL values in specific fields. IDataReader docs: https://msdn.microsoft.com/en-us/library/system.data.idatareader.read(v=vs.110).aspx – Brian Merrell May 14 '15 at 19:24

score 8 · Answer 5 · answered Nov 27 '11 at 17:58

8

I also use OleDbDataReader.IsDBNull()

if ( myReader.IsDBNull(colNum) ) { retrievedValue = ""; }
else { retrievedValue = myReader.GetString(colNum); }

answered Nov 27 '11 at 17:58

catalyst

81
1
1

Shorthand: `retrievedValue = myReader.IsDBNull(colNum) ? string.Empty : myReader.GetString(colNum);` Note that colNum can be swapped for colName. – clamchoda Mar 30 '22 at 19:52

Joe Phillips · Answer 6 · 2009-04-18T04:19:29.080

3

First of all, you probably want to check for a DBNull not a regular Null.

Or you could look at the IsDBNull method

edited Apr 18 '09 at 04:19

answered Apr 18 '09 at 04:12

Joe Phillips

49,743
32
103
159

In this database table there are 15 rows. 14 of them return data however the 15th ("Additional") doesn;t always contain data. How would .HasRows work with the valid rows? – Jason Apr 18 '09 at 04:14

score 1 · Answer 7 · answered Jun 25 '10 at 22:27

1

@Joe Philllips

SQlDataReader.IsDBNull(int index) requires the ordinal number of the column. Is there a way to check for nulls using Column Name, and not it's Ordinal Number?

answered Jun 25 '10 at 22:27

Shiva

20,575
14
82
112

i guess I could use if (readerObject["ColumnName"] != DBNull.Value) if I wanted to use column name instead of the ordinal number. Although I am not sure if there is a performance improvement (or degradation) between this approach and the IsDBNull(columnOrdinalNumber) approach. -Shiva http://mycodetrip.com/ – Shiva Jun 25 '10 at 22:47

score 1 · Answer 8 · answered Feb 13 '19 at 15:36

1

Try this simpler equivalent syntax:

ltlAdditional.Text = (myReader["Additional"] == DBNull.Value) ? "is null" : "contains data";

answered Feb 13 '19 at 15:36

cnom

3,071
4
30
60

score 1 · Answer 9 · answered Apr 18 '09 at 04:22

1

In addition to the suggestions given, you can do this directly from your query like this -

SELECT ISNULL([Additional], -1) AS [Additional]

This way you can write the condition to check whether the field value is < 0 or >= 0.

answered Apr 18 '09 at 04:22

Kirtan

21,295
6
46
61

I usually follow this pattern since NULL represents nothing in a DB. – Saif Khan Apr 18 '09 at 05:07

score 0 · Answer 10 · answered Feb 02 '18 at 13:05

0

This

Example:

objCar.StrDescription = (objSqlDataReader["fieldDescription"].GetType() != typeof(DBNull)) ? (String)objSqlDataReader["fieldDescription"] : "";

answered Feb 02 '18 at 13:05

Rodrigo Medeiros da Maia

1

score 0 · Answer 11 · answered Nov 04 '22 at 18:06

Best thing to get this done in query. However, if query bond to other functions than checking DBNull.Value in while loop would address the issue.

if (reader.HasRows)
{
   while (reader.Read())
   {
      (reader["Additional"] != DBNull.Value) ? "contains data" : "is null";
   }
}

score 0 · Answer 12 · answered Oct 16 '11 at 03:01

I also experiencing this kind of problem but mine, i'm using DbDataReader as my generic reader (for SQL, Oracle, OleDb, etc.). If using DataTable, DataTable has this method:

DataTable dt = new DataTable();
dt.Rows[0].Table.Columns.Contains("SampleColumn");

using this I can determine if that column is existing in the result set that my query has. I'm also looking if DbDataReader has this capability.

score -1 · Answer 13 · answered Apr 18 '09 at 04:35

AMG - Sorry all, was having a blond moment. The field "Additional" was added to the database after I had initially designed the database.

I updated all my code to use this new field, however I forgot to update the actual datareader code that was making the call to select the database fields, therefore it wasn't calling "Additional"

how to check if a datareader is null or empty

See Also:

13 Answers13

Linked