how can i convert a null value from sql to datetime or string in C#?

Question

I am trying to get some datetime values that are null in SQL to my C# application but i get some errors. One of the errors is:

'Unable to cast object of type 'System.DBNull' to type 'System.String'

Please, can someone tell me how to set a null DateTime value from SQL to my c# application?

I have already tried casting my C# variables to datetime value and string but both dont work. I've searched in stackoverflow but didn't found a solution for me. I've also tried another solution but then i retrieved the date: '01/01/0001' as value instead of 'null'

        public static List<Kamer> GetOpenstaandeBoekingen()
        {
            var result = new List<Kamer>();
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                const string query = "select b.boekingid, k.naam, bk.incheckdatum, bk.uitcheckdatum, b.hotelid, b.aantal_gasten, bk.kamerid from boeking b join klant k on k.klantid = b.boekingid join boekingkamer bk on b.boekingid = bk.boekingid where bk.incheckdatum is null and bk.uitcheckdatum is null";
                SqlCommand selectKamers = new SqlCommand(query, conn);
                SqlDataReader reader = selectKamers.ExecuteReader();
                while (reader.Read())
                {
                    Kamer kamer = new Kamer((int)reader["boekingid"], (string)reader["naam"], (string)reader["incheckdatum"], (string)reader["uitcheckdatum"], (int)reader["hotelid"], (int)reader["aantal_gasten"], (int)reader["kamerid"]);
                    result.Add(kamer);
                }
                reader.Close();
            }
            return result;
        }

And here is my class with the constructor:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FontysHotel
{
    public class Kamer
    {
        // instantie variabelen
        private int id;
        private string naam;
        private DateTime incheck_datum;
        private DateTime uitcheck_datum;
        private int hotel;
        private int aantal_personen;
        private int kamernr;

        // properties
        public int Id
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }

        public string Naam
        {
            get
            {
                return naam;
            }
            set
            {
                naam = value;
            }
        }
        public string Incheck_datum
        {
            get
            {
                return incheck_datum.ToShortDateString();
            }
            set
            {
                incheck_datum = Convert.ToDateTime(value);
            }
        }
        public string Uitcheck_datum
        {
            get
            {
                return uitcheck_datum.ToShortDateString();
            }
            set
            {
                uitcheck_datum = Convert.ToDateTime(value);
            }
        }
        public int Hotel
        {
            get
            {
                return hotel;
            }
            set
            {
                hotel = value;
            }
        }
        public int Aantal_personen
        {
            get
            {
                return aantal_personen;
            }
            set
            {
                aantal_personen = value;
            }
        }
        public int Kamernr
        {
            get
            {
                return kamernr;
            }
            set
            {
                kamernr = value;
            }
        }

        public Kamer(int id, string naam, string incheck_datum, string uitcheck_datum, int hotel, int aantal_personen, int kamernr)
        {
            Id = id;
            Naam = naam;
            Incheck_datum = incheck_datum;
            Uitcheck_datum = uitcheck_datum;
            Hotel = hotel;
            Aantal_personen = aantal_personen;
            Kamernr = kamernr;
        }
    }
}

Uitcheckdatum and incheckdatum are the date values.

So i want, when i run the query is shows everything where are dates with null, it is for a hotel system and i want to show what bookings haven't checked in or out yet.

Answer 1

I would avoid direct initialization of an object without any previous check. If you want to treat a DBNull value from the database as a null DateTime, there's no other option than declaring your two fields in the Kamer class using the nullable version DateTime? instead, since DateTime alone is a struct, a value type, which cannot be null. With that, you could do:

set
{
    uitcheck_datum = string.IsNullOrEmpty(value) ? null : Convert.ToDateTime(value);
}

And in the loop:

while (reader.Read())
{
    string incheckdatum = reader["incheckdatum"] as string;    
    string uitcheckdatum = reader["uitcheckdatum"] as string;

    Kamer kamer = new Kamer((int)reader["boekingid"], (string)reader["naam"], 
              incheckdatum, uitcheckdatum, (int)reader["hotelid"], 
              (int)reader["aantal_gasten"], (int)reader["kamerid"]);

    result.Add(kamer);
}

as saves you from possible casting exceptions. The indexer returns an instance of object. If it can't be cast to string, then null is returned.

In case you don't want to declare those fields as DateTime?, then just replace null in the set with a dummy date of your choice, e.g. DateTime.Now.

Also, make sure the string you receive from the database is a convertable string, or Convert will throw an exception. Maybe you'd want to add a try-catch to handle it.

Answer 2

One way is to declare your DateTime variables as being a Nullable type, this is done by using the ? sign at the end such as this.

private DateTime? incheck_datum;
private DateTime? uitcheck_datum;

But it might be a better approach to look for, trap, and handle DB Nulls and then set default or min values like this

if (IsDBNullreader.IsDBNull(indexOfUitCheckDatum))
    uitcheckdatum = DateTime.Minvalue;
else
    uitcheckdatum = reader["uitcheckdatum"];