How to check for null values in a SqlDataReader without using more than one reader

Question

I have a table that has a few columns that sometimes will have a null value. How can I check if a value is null, and if so set the value to an empty string or something that I can use later?

private DateTime dtDate_Ordered;
private DateTime dtDate_Required;
private DateTime dtDate_Received;

var stringSql = "select * from po where po_num=" + stringPO_NUM;

var Class_Connection = new SQL_Connection();

Class_Connection.cnn.Close();
Class_Connection.cnn.Open();

try
{
    var cmd = new SqlCommand(stringSql, Class_Connection.cnn);
    SqlDataReader sdr = cmd.ExecuteReader();

    while (sdr.Read())
    {
        dtDate_Ordered = (DateTime)sdr["dateordered"];
        dtDate_Required = (DateTime)sdr["daterequired"];
        dtDate_Received = (DateTime)sdr["daterecv"];
        stringComments = (string)sdr["comments"];
    }
}
catch (Exception Ex)
{
    Class_Connection.cnn.Close();
    throw Ex;
}

Class_Connection.cnn.Close();

RDI_Date_Ordered.SelectedDate = dtDate_Ordered;
RDI_Date_Required.SelectedDate = dtDate_Required;
RDI_Date_Received.SelectedDate = dtDate_Received;

I would like to be able to handle the null value, before the exception handler catches it.

why not do something like stringComments = (sdr["comments"] = DbNull.Value ? string.Empty : dr.GetString(dr.GetOrdinal("comments"))); — Rowland Shaw
– Rowland Shaw, Commented Jul 16, 2014 at 20:28
@RowlandShaw I tried that. I got and error that I couldn't resolve the symbols for Db.Null and dr — nate
– nate, Commented Jul 16, 2014 at 20:35
@RowlandShaw I think I got that to work with a string, how would I do it with a datetime type? — nate
– nate, Commented Jul 16, 2014 at 21:05

Sperick · Accepted Answer · 2014-07-16 21:33:17Z

1

Redefine your Datetimes at the top to be nullable, e.g.

private DateTime? dtDate_Ordered;

then when you're assigning them from the reader null won't be a problem.

if(sdr["dateordered"]==DbNull.Value)
    dtDate_Ordered=null
else
    dtDate_Ordered=sdr.GetDateTime(sdr.GetOrdinal("dateordered"))

answered Jul 16, 2014 at 21:33

Sperick

2,7516 gold badges33 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

takendarkk · Accepted Answer · 2014-07-16 22:30:37Z

1

Before type casting the values check each to make sure they're not null.

if( sdr["dateordered"] != null)
    dtDate_Ordered = (DateTime)sdr["dateordered"];

if( sdr["daterequired"] != null)
    dtDate_Required = (DateTime)sdr["daterequired"];

if( sdr["daterecv"] != null)
    dtDate_Received = (DateTime)sdr["daterecv"];

stringComments = ( sdr["comments"] == null)? "" : (string)sdr["comments"];

edited Jul 16, 2014 at 22:30

takendarkk

3,4568 gold badges27 silver badges38 bronze badges

answered Jul 16, 2014 at 22:25

brits1998

112 bronze badges

Collectives™ on Stack Overflow

How to check for null values in a SqlDataReader without using more than one reader

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related