CODEWITHSUNDEEP
c#sql-server
user6229886
user6229886

Reputation:

How do I handle this NULL data?

I have the following table: 

CREATE TABLE [dbo].[CATEGORIE](
    [bijdrage_id] [int] NOT NULL,
    [categorie_id] [int] NULL,
    [naam] [nvarchar](255) NOT NULL,)

I use this query to retrieve the data:

select c.bijdrage_id, c.categorie_id AS Subcategorievan, c.Naam from CATEGORIE as c

With the following c# code I put all my values into Categorie objects:

public List<Categorie> geefAlleCategorien()
        {
            List<Categorie> categorien = new List<Categorie>();
            string query = "select c.bijdrage_id, c.categorie_id as SubCategorieVan, c.Naam from CATEGORIE as c";
            SqlDataReader reader = db.Select(query);
            while (reader.Read())
            {
                Categorie c = new Categorie();
                c.Id = reader.GetInt32(0);
                c.SubCategorieVan = reader.GetString(1);
                c.Naam = reader.GetString(2);
                categorien.Add(c);
            }
            db.Close();
            return categorien;
        }

My problem is that some of the values in the "categorie_id" column are NULL and the "reader.GetString(1)" method can't handle NULL values.

How can handle these NULL values with my c# code?

Upvotes: 0

Views: 282

Answers (4)

Martin Mulder
Martin Mulder

Reputation: 12934

The problem is, a null in the database does not return a C# null, but a DbNull.Value. If a c# null was returned, the problem would be solved for all reference types (but not all value type). So when a column inside a database is nullable, you have to check for a DbNull.Value.

You have serveral options:

  • Test using SqlDataReader.IsDbNull(...).
  • Test using the as operator in combination with SqlDataReader.GetValue(...). This works only with nullable types.

Examples:

c.SubCategorieVan = reader.IsDbNull(1) ? null : reader.GetString(1);

or

c.SubCategorieVan = reader.GetValue(1) as string;

Or... if you want to give your properties a default value, when a DbNull is returned, you can give a default value in your code:

c.SubCategorieVan = reader.IsDbNull(1) ? "Leeg" : reader.GetString(1);

or

c.SubCategorieVan = reader.GetValue(1) as string ?? "Leeg";

You could create an extension method:

static public T GetValue<T>(this IDataReader reader, int columnIndex, T defaultValue = default(T))
{
    return reader.IsDbNull(columnIndex) ? defaultValue : (T)reader.GetValue(columnIndex)
}

This way, your reading method could become nice and clean:

c.Id = reader.GetValue<int>(0);
c.SubCategorieVan = reader.GetValue<string>(1, "Leeg"); // In case you want to use this default value.
c.Naam = reader.GetValue<string>(2);

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82534

Everything is better with extension methods....
Here is a little gem I've found a long time ago somewhere on the internet:

public static class IDataReaderExtensions
{
    public static T GetValueOrDefault<T>(this IDataReader reader, int index)
    {
        return (Convert.IsDBNull(reader[index])) ? default(T) : (T)reader.GetValue(index);
    }

    public static T GetValueOrDefault<T>(this IDataReader reader, string name)
    {
        return reader.GetValueOrDefault<T>(reader.GetOrdinal(name));
    }
}

And then you use it like this:

c.SubCategorieVan = reader.GetValueOrDefault<int>(1);

Upvotes: 0

Rahul
Rahul

Reputation: 77936

Well you can use COALESCE() function in your SQL and return a default value like

select c.bijdrage_id, 
coalesce(c.categorie_id,0) AS Subcategorievan, 
c.Naam 
from CATEGORIE as c

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460340

You can use IsDBNull:

Categorie c = new Categorie();
c.Id = reader.GetInt32(0);
c.SubCategorieVan = reader.IsDBNull(1) ? null : reader.GetString(1);
c.Naam = reader.GetString(2);
categorien.Add(c);

Upvotes: 3

Related Questions