Home > c# > Checking for Null in SqlDataReader object

Checking for Null in SqlDataReader object

November 20Hits:11
Advertisement

I'm using a SQLdatareader to build POCOs from a database. The code works except when it encounters a null value in the database. For example, if the FirstName column in the database contains a null value, an exception is thrown.

employee.FirstName = sqlreader.GetString(indexFirstName); 

What is the best way to handle null values in this situation?

Answers

You need to check for IsDBNull:

if(!SqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

That's your only reliable way to detect and handle this situation.

I wrapped those things into extension methods and tend to return a default value if the column is indeed NULL:

public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
   if(!reader.IsDBNull(colIndex))
       return reader.GetString(colIndex);
   else
       return string.Empty;
}

Now you can call it like this:

employee.FirstName = SqlReader.SafeGetString(indexFirstName);

and you'll never have to worry about an exception or a NULL value again.

You should use the as operator combined with the ?? operator for default values. Value types will need to be read as nullable and given a default.

employee.FirstName = sqlreader[indexFirstName] as string;
employee.Age = sqlreader[indexAge] as int? ?? default(int);

The as operator handles the casting including the check for DBNull.

I don't think there's a NULL column value, when rows are returned within a datareader using the column name.

If you do datareader["columnName"].ToString(); it will always give you a value that can be a empty string (String.Empty if you need to compare).

I would use the following and wouldn't worry too much:

employee.FirstName = sqlreader["columnNameForFirstName"].ToString();

IsDbNull(int) is usually much slower than using methods like GetSqlDateTime and then comparing to DBNull.Value. Try these extension methods for SqlDataReader.

public static T Def<T>(this SqlDataReader r, int ord)
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return default(T);
    return ((INullable)t).IsNull ? default(T) : (T)t;
}

public static T? Val<T>(this SqlDataReader r, int ord) where T:struct
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? (T?)null : (T)t;
}

public static T Ref<T>(this SqlDataReader r, int ord) where T : class
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? null : (T)t;
}

Use them like this:

var dd = r.Val<DateTime>(ords[4]);
var ii = r.Def<int>(ords[0]);
int nn = r.Def<int>(ords[0]);

One way to do it is to check for db nulls:

employee.FirstName = (sqlreader.IsDBNull(indexFirstName)
    ? ""
    : sqlreader.GetString(indexFirstName));

For a string you can simply cast the object version (accessed using the array operator) and wind up with a null string for nulls:

employee.FirstName = (string)sqlreader[indexFirstName];

or

employee.FirstName = sqlreader[indexFirstName] as string;

For integers, if you cast to a nullable int, you can use GetValueOrDefault()

employee.Age = (sqlreader[indexAge] as int?).GetValueOrDefault();

or the null-coalescing operator (??).

employee.Age = (sqlreader[indexAge] as int?) ?? 0;

Check sqlreader.IsDBNull(indexFirstName) before you try to read it.

This Solution is less Vendor independent and works with an SQL, OleDB, MySQL Reader

public static string GetStringSafe(this IDataReader reader, int colIndex)
{
    return GetStringSafe(reader, colIndex, string.Empty);
}

public static string GetStringSafe(this IDataReader reader, int colIndex, string defaultValue)
{
    if (!reader.IsDBNull(colIndex))
        return reader.GetString(colIndex);
    else
        return defaultValue;
}

public static string GetStringSafe(this IDataReader reader, string indexName)
{
    return GetStringSafe(reader, reader.GetOrdinal(indexName));
}

public static string GetStringSafe(this IDataReader reader, string indexName, string defaultValue)
{
    return GetStringSafe(reader, reader.GetOrdinal(indexName), defaultValue);
}

What I tend to do is replace the null values in the SELECT statement with something appropriate.

SELECT ISNULL(firstname, '') FROM people

Here I replace every null with a blank string. Your code won't throw in error in that case.

I think you would want to use:

SqlReader.IsDBNull(indexFirstName)

We use a series of static methods to pull all of the values out of our data readers. So in this case we'd be calling DBUtils.GetString(sqlreader(indexFirstName)) The benefit of creating static/shared methods is that you don't have to do the same checks over and over and over...

The static method(s) would contain code to check for nulls (see other answers on this page).

I am using the code listed below to handle null cells in an Excel sheet that is read in to a datatable.

if (!reader.IsDBNull(2))
{
   row["Oracle"] = (string)reader[2];
}

private static void Render(IList<ListData> list, IDataReader reader)
        {
            while (reader.Read())
            {

                listData.DownUrl = (reader.GetSchemaTable().Columns["DownUrl"] != null) ? Convert.ToString(reader["DownUrl"]) : null;
                //没有这一列时,让其等于null
                list.Add(listData);
            }
            reader.Close();
        }

and / or use ternary operator with assignment:

employee.FirstName = rdr.IsDBNull(indexFirstName))?
                     String.Empty: rdr.GetString(indexFirstName);

replace the default (when null) value as appropriate for each property type...

This method is dependent on indexFirstName which should be the zero-based column ordinal.

if(!sqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

If you don't know the column index but wan't to check a name you can use this extension method instead:

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

And use the method like this:

if(sqlReader.HasColumn("FirstName"))
{
  employee.FirstName = sqlreader["FirstName"];
}

you can ever check for this as well

if(null !=x && x.HasRows)
{ ....}

Related Articles

  • Checking for Null in SqlDataReader object November 20

    I'm using a SQLdatareader to build POCOs from a database. The code works except when it encounters a null value in the database. For example, if the FirstName column in the database contains a null value, an exception is thrown. employee.FirstName =

  • Entity Framework code first null reference exception after check for null or empty string

    Entity Framework code first null reference exception after check for null or empty string January 11

    I have some code and when it executes, it throws a NullReferenceException, saying: Object reference not set to an instance of an object. What does this mean, and what can I do about it? --------------Solutions------------- What is the cause? Bottom L

  • Check for null dictionary January 22

    I am populating a Dictionary<string, string> through a SqlDataReader how can I check if the Dictionary<string, string> is null before continuing? Dictionary<string, string> dic; SqlDataReader rdr; cmd = new SqlCommand(sql.ToString(), cnn

  • Should one check for null if he does not expect null?May 6

    Last week, we had a heated argument about handling nulls in our application's service layer. The question is in the .NET context, but it will be the same in Java and many other technologies. The question was: should you always check for nulls and mak

  • Is this is good way to check for null?November 26

    I ran into the code below today, and my gut instinct tells me this is an expensive way to do a null check. The point the author was making was that if you changed the name of the object then you don't need to change the value of the string being thro

  • Elegantly check for null before method callMay 9

    How do I elegantly check for null before calling a method on an object? This is how I do it right now: var title = document.querySelector('title'); title = title ? title.text : ''; Null Object pattern would be nice in this case but I don't own the do

  • Java check for null practiseJanuary 26

    Usual practice I have seen so far is to check like this: if (object != null) {...} But today I have encountered the following situation: if (object) {...} Are these two lines COMPLETELY equivalent? The latter seems little bit strange to me, since the

  • How do I concisely check for nulls in a long method chain in Scala?January 27

    I'm in the situation that I receive a tree data structure from outside, which can be of several different shapes. I need to do a sorta switch case depending on what kind of tree I get. The code I'm writing is beginning to look like this: val first =

  • Checking for Null before adding into ListJuly 5

    Is there any way to refactor this? public IEnumerable<Option> Options { get { { List<Option> ListOption = new List<Option>(); if (!String.IsNullOrEmpty(Option1)) { ListOption.Add(new Option() {Name=Option1 }); } if (!String.IsNullOrEmpty

  • How to check for NULL or empty value in fql (fast search)?September 23

    I want to check for null or empty field in fql query , I want to add condition to retrieve result when is column not equal empty or null, I've tried these ways : <Context> <QueryText language='en-US' type='FQL'>and(string("Text"),not

  • Check- in & check-out files using client object modelDecember 4

    As the title says, how can I Check- in & check-out files using client object model? --------------Solutions------------- To check-out: //get the connection ClientContext ctx = new ClientContext("http://sitename"); //get the home page File ho

  • Is checking for null redundant while using Contract Annotator from JetBrains?June 19

    I'm curious whether checking for null is redundant in this concrete example. public LoginJob(@NotNull String login, @NotNull String password) { super(new Params(Priority.NORMAL).requireNetwork().persist()); Preconditions.checkNotNull(login, "login is

  • What Criteria to use in Process Builder to check for NULL reference?

    What Criteria to use in Process Builder to check for NULL reference?January 11

    I have a lookup field on account - for other accounts - Name: Account Parent Tiered OBJ How do I check in Process Builder that it's actually empty? Using the Global Constant NULL or: IS Null true both returned the wrong answer [there always seem to b

  • JSON contains array of nulls with three objects - expected only three objectsJanuary 19

    Using a standalone Google Apps Script and a Google Spreadsheet. I have this script which returns as JSON an array of nulls and three objects, but I expected only to get three objects. Its a search, and when a zipcode is searched, the script is to ret

  • Check if ArrayList contains an object with a property that equals a specific valueJanuary 15

    So I have an application in VB.net that is pulling data from a table and inserting it into an arraylist to be used later. What I want to do is before adding the object to the arraylist, I want to check that arraylist to see if the object exists, but

  • Check For Null String In JavaScriptJanuary 22

    I've read this. But for a null string, if (field.value ==="") { // Issue alert to fill in field. } this will not generate the desired alert. People at the end of that thread suggested that recent browser versions might not accept such a statemen

  • Have knockout observable check for nullFebruary 1

    I was wondering if there was a way to have knockout check to see if data is null before it tries to put it into an observable? Right now I do this: if (!data.Filename) { this.FileName = ko.observable(""); } else { this.FileName = ko.observable(d

  • JS check if the value of object exists

    JS check if the value of object existsFebruary 5

    So, I have following js setup: var NAMES = []; function INFO(id,first,middle,last){ var newMap = {}; newMap[id] = [first, middle, last]; return newMap ; } Then, for (var j = 0; j < NUMBER.length; j++) { //let say it there are three values var my_name

  • C# linq-sql checking for null stringFebruary 11

    In my database table I have 54 rows, of those 53 are NULL in the Code column which is defined as a varchar(100). (I confirmed that those fields are null by performing queries directly on the database using my Microsoft SQL Management Studio) I have t

  • Sharepoint View Filter (how to check for nulls in Scheduling Start/End Date)

    Sharepoint View Filter (how to check for nulls in Scheduling Start/End Date)May 9

    I am trying to use the built in sharepoint U.I to setup a filter for Scheduling Start Date & Scheduling End Date. However I noticed that if you setup something with a "Scheduling Start Date" as "Immediately" it will put nothing in

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.762 s.