//
You're reading...
C#, Tips/Tricks, VB.Net

Best practice to check for DBNull using .Net:

When we retrieve data from database it is very important for to check for NULL values if writing a robust application. Here are following ways on how to accomplish this need:

1) Using System.Convert:

There is a direct method available to Check for DBNull. Here is what you have to do:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”); 
If(rdr != null && rdr.HasRows)
{
          rdr.Read();
          if(!System.Convert.IsDBNull(rdr[“Addr2”]))  // can also be check for other cases
              customer[i].SecondaryAddress = rdr[“Addr2”];
}

For VB.Net sample you can simply convert this code through this converter

2) Using EQUALS Method:

Here is how you can use Equals() method to check for NULL values:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”);                
If(rdr != null && rdr.HasRows)
{
     rdr.Read();
     if(!rdr[“Addr2”].Equals(DBNull.Value))   // can also be check for other cases
         customer[i].SecondaryAddress = rdr[“Addr2”];
}

For VB.Net sample you can simply convert this code through this converter

3) Using Extension Method:

The above two procedures are very common and in-use. I would also suggest to have look at the methodology I am going to discuss. The above two ways are fine if we are going to read few columns. But what if you have a table with 30+ columns and every column need to validate before use ?

I would suggest to extend this functionality and shrink your code.

Using extension method you can extend object datatype to have IsDBNull method. Here is a quick guide for how to use it:

    public static class MyExtensionForDBNull
    {
        public static bool IsDBNull(this object obj)
        {
            return (obj.Equals(DBNull.Value));
        }

        public static bool IsDBNullOrEmpty(this object obj)
        {
            return (obj.Equals(DBNull.Value) ||
                   (string.IsNullOrEmpty(obj.ToString())));
        }
   }

Add this to above code to your class where SqlDataReader is going to consume. Then use it like this:

// Assuming that a DataReader is returned when query executed
SqlDataReader rdr= SqlHelper.RetreiveDataReader(“select [ID], [Name], [Addr], [Addr2] from customer”);  

If(rdr != null && rdr.HasRows)
{
   rdr.Read();

   if(!rdr[“Addr”].IsDBNull())       // Here check for DBNull Only
      customer[i].SecondaryAddress = rdr[“Addr”];

   if(!rdr[“Addr2”].IsDBNullOrEmpty())      // Here you can also check for DBNull or Empty string
       customer[i].SecondaryAddress = rdr[“Addr2”];
}

In case of VB.NET, it is bit different to extension methods in a same way as you do in C#. If you will try to extend [Object] class as you can see in “MyExtensionForDBNull” you will not find any extended method in object datatype instead it will be visible in all other types like Integer, String etc.

So in order to use extension method in VB.NET the above implementation will look like:

Either create a module or in existing module paste this code:

    <System.Runtime.CompilerServices.Extension()> _
    Public Function IsDBNullOrEmpty(ByVal obj As [Object]) As Boolean
        Return (obj.Equals(DBNull.Value) Or (Not obj.Equals(DBNull.Value) AndAlso String.IsNullOrEmpty(obj)))
    End Function

and then use it like:

        
        Dim rdr As SqlDataReader = SQLHelper.RetrieveDataReader()  ' Expected that a DataReader will be returned

        If (rdr IsNot Nothing) Then
            If (rdr.HasRows) Then
                rdr.Read()
                If (rdr.GetString(rdr.GetOrdinal("Addr2")).IsDBNullOrEmpty()) Then
                    Console.WriteLine("Empty Or Null")
                Else
                    Console.WriteLine("Has some value")
                End If
            End If
        End If

        If (rdr IsNot Nothing) Then
            rdr.Close() 
            rdr = Nothing
        End If

I hope it is more easier and convenient to go with extension methods. You can also extend “MyExtensionForDBNull” class for some other functionalities like IsFieldPopulated (you can check for whether field contains data, can be use when you are expecting this lookup as a trigger for some operation) Or IsNotNullAndInteger (here you can check if the value is not null and contains a integer value (Use int.TryParse()) , can be use when sql query has COUNT(*) function used) etc.

Conclusion:

Using the first two method you do not have the functionalities as you can implement via extension methods. So I believe, this can provide more ease in writing code than the first two methods.

I hope you will find this post helpful.

Await your feedback.

Regards,

Shahan

About these ads

About Shahan Ayyub

Shahan is a Microsoft Certified Professional for .Net framework 2.0. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: