DBNull if statement

Question

I'm trying to execute a stored procedure and then use an if statement to check for null values and I'm coming up short. I'm a VB guy so please bear with me if I'm making a schoolboy syntax error.

objConn = new SqlConnection(strConnection);
objConn.Open();
objCmd = new SqlCommand(strSQL, objConn);
rsData = objCmd.ExecuteReader();
rsData.Read();

if (!(rsData["usr.ursrdaystime"].Equals(System.DBNull.Value)))
        {
            strLevel = rsData["usr.ursrdaystime"].ToString();

        }

Would this allow me to check whether the SQL connection is returning just a value and if so then populating my string?

I'm used to being able to just check the below to see if a value is being returned and not sure I'm doing it correctly with C#

If Not IsDBNull(rsData("usr.ursrdaystime"))

Any help would be appreciated!

I came here because `== null` did not work. Amazed that different types of NULL exist, and that DBNull even has a value... — Roland, Apr 06 '18 at 10:08

score 150 · Accepted Answer · edited May 05 '23 at 22:38

150

This should work.

if (rsData["usr.ursrdaystime"] != System.DBNull.Value)
{
    strLevel = rsData["usr.ursrdaystime"].ToString();
}

also need to add using statement, like bellow:

using (var objConn = new SqlConnection(strConnection))
     {
        objConn.Open();
        using (var objCmd = new SqlCommand(strSQL, objConn))
        {
           using (var rsData = objCmd.ExecuteReader())
           {
              while (rsData.Read())
              {
                 if (rsData["usr.ursrdaystime"] != System.DBNull.Value)
                 {
                    strLevel = rsData["usr.ursrdaystime"].ToString();
                 }
              }
           }
        }
     }

that'll automatically dispose (close) resources outside of block { .. }.

edited May 05 '23 at 22:38

TheHunter Shergill

792
5
24

answered May 03 '12 at 12:57

Kamil Lach

4,519
2
19
20

Does the using statement replace my initial connection strings? I'm assuming it would replace the following objConn = new SqlConnection(strConnection); objConn.Open(); Would I still use the objCmd& rsdata strings? – PipBoy May 03 '12 at 14:40
I've edited code - added proper using statements (which close/dispose) connection. You still need to **open** connection – Kamil Lach May 03 '12 at 14:46
I think in this instance this will best suit my needs - thanks! – PipBoy May 03 '12 at 15:07
remember to remove the extra ) when you are trying this code out. i tried to edit the post but the "suggested edit queue is full" if (rsData["usr.ursrdaystime"] != System.DBNull.Value) { strLevel = rsData["usr.ursrdaystime"].ToString(); } – user3267567 Jun 28 '22 at 06:21

score 14 · Answer 2 · answered May 03 '12 at 12:58

The idiomatic way is to say:

if(rsData["usr.ursrdaystime"] != DBNull.Value) {
    strLevel = rsData["usr.ursrdaystime"].ToString();
}

This:

rsData = objCmd.ExecuteReader();
rsData.Read();

Makes it look like you're reading exactly one value. Use IDbCommand.ExecuteScalar instead.

score 8 · Answer 3 · answered May 03 '12 at 13:03

8

The closest equivalent to your VB would be (see this):

Convert.IsDBNull()

But there are a number of ways to do this, and most are linked from here

answered May 03 '12 at 13:03

Cade Roux

88,164
40
182
265

score 5 · Answer 4 · answered May 03 '12 at 12:58

5

Yes, just a syntax problem. Try this instead:

if (reader["usr.ursrdaystime"] != DBNull.Value)

.Equals() is checking to see if two Object instances are the same.

answered May 03 '12 at 12:58

mgnoonan

7,060
5
24
27

1

That will take him in if condition. Make `==` to `!=` – Nikhil Agrawal May 03 '12 at 12:59

score 2 · Answer 5 · answered May 03 '12 at 12:58

Consider:

if(rsData.Read()) {
  int index = rsData.GetOrdinal("columnName"); // I expect, just "ursrdaystime"
  if(rsData.IsDBNull(index)) {
     // is a null
  } else {
     // access the value via any of the rsData.Get*(index) methods
  }
} else {
  // no row returned
}

Also: you need more using ;p

score 1 · Answer 6 · answered Oct 24 '18 at 12:48

1

Ternary operator should do nicely here: condition ? first_expression : second_expression;

strLevel = !Convert.IsDBNull(rsData["usr.ursrdaystime"]) ? Convert.ToString(rsData["usr.ursrdaystime"]) : null

answered Oct 24 '18 at 12:48

GoranSu

479
4
9

score 1 · Answer 7 · answered Jun 08 '22 at 00:34

I know this is a 10+ year old question, but I happened across this and was recently debugging a casting SQL null issue in a .Net 6 app using "Microsoft.Data.SqlClient" Version="5.0.0-preview2.22096.2", using the DataTableReader

You'll want to use the reader's built-in IsDBNull method to test a value before retrieving it, because the implicit (or explicit) cast from using any of the Get* functions may throw an exception (varies on type)

So for a line-if solution, the following is BAD: DateTime lastSentDate = DBNull.Value.Equals(reader.GetDateTime("LastSentDT")) ? reader.GetDateTime("LastSentDT") //Null can't convert to DateTime, exception thrown

This is good: DateTime lastSentDate = !reader.IsDBNull("LastSentDT") ? reader.GetDateTime("LastSentDT") : DateTime.Parse("1-1-2022");

Hope this helps in 2022 :)

score 0 · Answer 8 · answered May 03 '12 at 13:01

if(!rsData.IsDBNull(rsData.GetOrdinal("usr.ursrdaystime")))
{
  strLevel = rsData.GetString("usr.ursrdaystime"); 
}

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull.aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal.aspx

score 0 · Answer 9 · answered Nov 08 '17 at 18:22

0

I use String.IsNullorEmpty often. It will work her because when DBNull is set to .ToString it returns empty.

if(!(String.IsNullorEmpty(rsData["usr.ursrdaystime"].toString())){
        strLevel = rsData["usr.ursrdaystime"].toString();
    }

answered Nov 08 '17 at 18:22

dstineback

103
2
4

Nur.B · Answer 10 · 2021-09-21T05:39:45.393

0

There is still such an alternative option:

strLevel = rsData.GetValue(0) is DBNull? "" : rsData.GetString(0);

You need to know exactly which column counts.

edited Sep 21 '21 at 05:39

answered Sep 20 '21 at 17:39

Nur.B

319
2
4

score 0 · Answer 11 · answered Mar 01 '22 at 09:44

0

Just simply check

string val= string.IsNullOrEmpty(rsData["usr.ursrdaystime"]?"":rsData["usr.ursrdaystime"].ToString())

answered Mar 01 '22 at 09:44

anil soni

75
1
4

Likurg · Answer 12 · 2012-05-03T13:08:33.737

-2

At first use ExecuteScalar

 objConn = new SqlConnection(strConnection);
 objConn.Open();
 objCmd = new SqlCommand(strSQL, objConn);
 object result = cmd.ExecuteScalar();
 if(result == null)
     strLevel = "";
 else 
     strLevel = result.ToString();

edited May 03 '12 at 13:08

answered May 03 '12 at 13:01

Likurg

2,742
17
22

DBNull if statement

12 Answers12

Linked

Related