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.

Possible duplicate of [Unable to cast object of type 'System.DBNull' to type 'System.String\`](https://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string) — SᴇM, May 23 '19 at 08:13
So you are casting to string and then converting to DateTime... Aside from this arguable flow, what DateTime do you want to obtain from a null value? Do you want it to be treated as DateTime.Now instead? — StackLloyd, May 23 '19 at 08:23
From the progamm pesrpective the simplest solution is to make `incheckdatum`, and `uitcheckdatum` columns `not null` in the DB. If it's also ok from a business perspective, just do it. — Serg, May 23 '19 at 08:54

score 2 · Answer 1 · answered May 23 '19 at 08:20

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"];

answered May 23 '19 at 08:20

jason.kaisersmith

8,712
3
29
51

The first option wont work because in another form i have to get date values out of the db instead of null values, where do i have to place the if statement? – Remco Bisschops May 23 '19 at 08:30
1

You need to adapt your code to the solution for which there are many options. One is to pass a DBReader to your Class (maybe in the constructor) from which it can read all the values and set as required. Or you could use the Builder pattern or maybe Factory pattern. You also should not case a DateTime to a string when you need a DateTime in your class. – jason.kaisersmith May 23 '19 at 08:36

score 0 · Answer 2 · answered May 23 '19 at 09:16

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.

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

2 Answers2