How do I return a value to a sqldatareader if value is null?

sqldatareader check for null
c# sqldatareader getstring null value
c# handling null values from database
sql getstring null
handle null datetime in datareader c#
reader getdouble null
c# sql reader null string
c# sql check for null

I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below:

theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdinal("Relevance"))))

My problem is that the Truthfulness and Relevance may return a null value and this is causing the function to fall over.

I think I understand why. I am asking for an integer value (getin32) and because null is returned it fails.

How do I accommodate the null value from the database so it does not fall over?

You can check whether or not a given ordinal position is null using .IsDBNull() and then do something - e.g. set your value to -1 or something:

int myOrdinal = myReader.GetOrdinal("Truthfullness");

if(myReader.IsDBNull(myOrdinal))
{
  theArticle.Truthfulness = -1;
}
else
{
  theArticle.Truthfulness = myReader.GetInt32(myOrdinal);
}

As Mike Hofer points out in his answer, you could also wrap all this logic into an extension method:

public static class SqlDataReaderExtensions 
{
    public static int SafeGetInt32(this SqlDataReader reader, 
                                   string columnName, int defaultValue) 
    {
        int ordinal = reader.GetOrdinal(columnName);

        if(!reader.IsDbNull(ordinal))
        {
           return reader.GetInt32(ordinal);
        } 
        else
        {
           return defaultValue;
        }
    }
}

and then just use that "SafeGetInt32" method instead:

  theArticle.Truthfulness = myReader.SafeGetInt32("Truthfullness", -1);

Marc

SQL Data Reader - handling Null column values, 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  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.

Did you check, SqlDataReader.IsDBNull Method? Probably something like:

if(myReader.IsDBNull(myReader.GetOrdinal("Truthfulness"))
theArticle.Truthfulness = string.Empty;
else
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))

SqlDataReader Null Handling – bitScry, When reading values from a SqlDataReader in . but expanded to allow column selection by name and to return null rather than empty values. public static int? ToNullableInt32(this SqlInt32 value) { return value.IsNull ? (int?) null : value.Value; } Then: Field2 = rdr.GetSqlInt32(Field2_Ordinal).ToNullableInt32() (Comment on other answers: there's no need to bring DbNull into this, as SqlInt32 can already represent null values.

You know, I deal with this all the time in Oracle. To clean the code up, I wrote a set of extension methods to simplify the operation:

using System.Data.OracleClient;
public static class OracleDataReaderExtensions 
{
    public static int GetInt32(this OracleDataReader reader, string columnName, int defaultValue) 
    {
        return reader.GetInt32(reader.GetOrdinal(columnName)) != DbNull.Value ? 
               reader.GetInt32(reader.GetOrdinal(columnName)) : 
               defaultValue;
    }
}

Create a separate overload for each type you want to return. I primarily work with string, int, date, and decimal. Remember YAGNI (you don't need to work with every type supported by the reader, only those you actually use.)

An extension class like this for SQL Server is really easy to write, and will VASTLY simplify your work. Trust me on that. Would I lie to you? :)

SqlDataReader.IsDBNull(Int32) Method (System.Data.SqlClient , true if value is of type DBNull; otherwise, false . Examples. The following example uses a SqlDataReader object to retrieve survey data from a database. There are several ways to retrieve values from a DataReader. The first method is by using the index and looping through the fields in order as retrieved within the Select statement. Starting with the 0 index, you may use the GetValue () Function. The following code snippets are in VB. A C# snippet is provided at the bottom.

This generic version may be of use:

    private T ValueOrDefault<T>(System.Data.IDataReader rdr, string columnName)
    {
        T vod = default(T);
        try
        {
            int idx = rdr.GetOrdinal(columnName);
            if (!rdr.IsDBNull(idx))
                return (T)rdr[idx];
        }
        catch (IndexOutOfRangeException) { }

        return vod;
    }

Could be extended to catch InvalidCastException, or use Convert.ChangeType instead of casting?

Convert.IsDBNull(Object) Method (System), Handling Null column values in sqlDataReader In this article I will explain how to handle null value. Value to check sql data reader columns value is null or not, if found null then column value assigned return returnList; }. You know, I deal with this all the time in Oracle. To clean the code up, I wrote a set of extension methods to simplify the operation: using System.Data.OracleClient; public static class OracleDataReaderExtensions { public static int GetInt32(this OracleDataReader reader, string columnName, int defaultValue) { return reader.GetInt32(reader.GetOrdinal(columnName)) != DbNull.Value ?

IsDbNull(int) is usually much slower that using methods like GetSqlInt32 and then comparing to DBNull.Value or using it's own .IsNull Like:

    public static int Int32(this SqlDataReader r, int ord)
    {
        var t = r.GetSqlInt32(ord);
        return t.IsNull ? default(int) : t.Value;
    }

Tried a few template solutions but to no avail so far. The problem is that all Sql-types (SqlInt32 here) types are actually structs and while they all have .Value property C# doesn't have real templates to handle that. Also they have their own INullable interface which has only .IsNull and is not conpatible with Nyllable<>.

I suspect that one would need full set of Sql-types as C# templates or to add ICOnvertible to them in order to be able to have just one or two templated methods.

If someone has maybe an idea with a functional trick or two speak up :-)

Handling Null column values in sqlDataReader, We can't see your code, and we have no idea what "SQLDataReader breaks when it comes to the first row with some null values" actually  I always had to do a check on my datareader values as they came out of the database. if not dr[0] is DBNull.Value then textbox1.text = dr[0].ToString() 'I always have option strict on end if Kind of a pain, which is why I stopped using null unless I had to.

[Solved] Read null values from SQL table with sqldatareader , When you try to access the value, it is null. How can you access the return value? Solution. Use a parameter defined with a ParameterDirection property of  I don't think you can, DataReader objects have to be instantiated by using an ExecuteReader method. "Learner" wrote: Hello, This my a method to call a stored proce and uses a DataReader to read the data in the below method I am trying to assign a null value to my datareader variable Dim datareader As SqlDataReader datareader = null but it doesn

Retrieving Stored Procedure Return Values Using a DataReader , If this cannot be determined, a null value is returned. This name always reflects the most recent renaming of the column in the current view or command text. Gets the value of the specified column in its native format. This method returns DBNull for null database columns. Implements. GetValue(Int32) Remarks.

Retrieving Data Using a C# .NET DataReader, When dealing with value types, null values are not permitted. You can handle the exception and act accordingly, or use the SqlDataReader. Instead, the value returned is an instance of the correct SQL data type (in this case SqlGuid).

Comments
  • or catch the exception and deal with it
  • Yes, you could do that too - but avoiding an exception is better than catching and handling one (in general)
  • @marc_s :Agreed with your comment on my answer. Deleting it. Thanks for making it clear. Your comment was "I don't tiink this will work, since if the db column is NULL, the .GetInt32() call will fail with an exception - you won't get back a NULL value which you can then feed into the "??" operator...."
  • Nowadays you would probably return a nullable int: stackoverflow.com/a/53391141/572644
  • well, string.Empty won't do much good if you're dealing with a int32 property......
  • I don't think this will work, since if the column in the database is NULL, then the call to GetInt32() will cause an exception. You cannot compare that GetInt32() call against DBNull.Value and react on that....
  • The reason it works is because we're using the short-circuiting ?: operator. If it's not null, we return it. Otherwise, we return the default value.
  • actually, at least with SQL Server, it does NOT work - GetInt32() will throw an exception if the underlying db column contains NULL, and you cannot compare the GetInt32() call to DBNull.Value - VS2008 will complain and not even compile it.
  • But I definitely like the idea of wrapping this up in an extension method - makes life a lot easier!
  • I tried to use your approach and compare the output of reader.GetInt32() to DBNull.Value in SQL Server: all I get is this error message: Operator '!=' cannot be applied to operands of type 'int' and 'System.DBNull'
  • I like this option except for the IndexOutOfRangeException catch statement. I would think that you'd want to know as quickly as possible if the schema structure has changed instead of pushing default values to other parts of your system.