Sql Datareader null values

Question

I am reading from a SQL datareader in C# and passing the values from the columns to a dropdownlist. There are two columns being read. Using IsDbNull, I am able to handle the null values. However, as I have the code written right now, if dr.GetString(0) is null, no values are passed along at all, while as long as only dr.GetString(1) (or neither) is null, all of the values are passed along and the null values are ignored. Here is what I have for while the datareader is reading:

while (dr.Read())
{
     if (!dr.IsDBNull(0))
     {
          machineName.Items.Add(dr.GetString(0).ToString());
     }
     else if (!dr.IsDBNull(1))
     {
          machineName.Items.Add(dr.GetString(1).ToString());
     }
 }

What I need to happen is for the dropdownlist to be populated with whatever values the datareader returns, regardless of which column they are in. I've removed the using and try/catch statements in order to declutter the code. Thanks everyone.

Sorry about that. Clarified the intent of my code.

Geo Ego
– Geo Ego

2009-12-10 18:43:37 +00:00
Commented Dec 10, 2009 at 18:43 — Geo Ego
– Geo Ego, Commented Dec 10, 2009 at 18:43

Jim Schubert · Accepted Answer · 2009-12-10 19:03:25Z

6

My preferred way of handling nulls in a reader is to use named columns and then use Convert.ToString(object)

So, you'd have the following:

while (dr.Read())
{
     string firstItem = Convert.ToString(dr["FIRST_ITEM"]);
     if(!string.IsNullOrEmpty(firstItem))
     {
        machineName.Items.Add(firstItem);
     }
     ... etc.
}

I'm not sure I understand your question, though.
You say

However, as I have the code written right now, if dr.GetString(0) is null, no values are passed along at all, while as long as only dr.GetString(1) (or neither) is null, all of the values are passed along and the null values are ignored.

which sounds like you're using if/else if, but your code shows two if statements.

Edit:

To better address your updated question, my solution would be to have a generic list of ListItems and add each column to that list. Then, bind that list to the dropdown:

List<ListItem> items = new List<ListItem>();

while(dr.Read())
{
    string firstItem = Convert.ToString(dr["FIRST_ITEM"]);
    if(!string.IsNullOrEmpty(firstItem))
    {
        ListItem thisItem = new ListItem(firstItem, firstItem);
        items.Add(thisItem);
    }
}

 machineName.Items = items;

This way, if you have no data, items will be null. If you have one column in one row and the other column in the next row, it'll add them appropriately. Although, your code should work the same if you took out the else.

edited Dec 10, 2009 at 19:03

answered Dec 10, 2009 at 18:31

Jim Schubert

20.4k5 gold badges64 silver badges69 bronze badges

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

8 Comments

Jim Schubert Over a year ago

As a side note: to represent other nullable values, you can use (for existence) Nullable<int> or int?. int? is a shorthand way of writing Nullable<int>, then you can check two see whether it is null using the HasValue property.

ryanulit Over a year ago

And even easier => !string.IsNullOrEmpty(dr["FIRST_ITEM"].ToString())

Jim Schubert Over a year ago

@ryanulit: wont't that fail if the column doesn't exist? I'm pretty sure (but I could be wrong) that the Convert.ToString() method will not throw an exception if the indexer on the column fails.

Geo Ego Over a year ago

My mistake. I left out the "else" when I reformatted. Your methods of checking nulls in a reader are new to me. Would you guys mind telling me why I would want to go with the string check as opposed to IsDbNullable? Thanks.

Jim Schubert Over a year ago

It's just a different way of accessing the column in the datareader. Accessing via index can be a pain when you start adding or removing columns in the database. Accessing via column name is more scalable in that sense. At work, we use a customized version of Spring.Net's NullMappingDataReader which can be found here: src.springframework.org/svn/spring-net/trunk/src/Spring/… I think you'll find this class very useful.

|

Collectives™ on Stack Overflow

Sql Datareader null values

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related