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

Question

Hi im trying to make a notification system. Basically when i add a new row, a notification will be shown on my notificationspage realtime (i use signalR with razor pages asp.net). But for some reason when i get on that page, i get these errors: Unable to cast object of type 'System.DBNull' to type 'System.DateTime'. at myWebApp.Controllers.SpeedListener.GetAlarmList() in \myWebApp\Controllers\SpeedListener.cs:line 83 at myWebApp.Controllers.SpeedListener.ListenForAlarmNotifications() in \myWebApp\Controllers\SpeedListener.cs:line 43

So apparently theres a problem at the controller. Here is the code of the controller

namespace myWebApp.Controllers
{
    public class SpeedListener :Controller
    {
        private IHubContext<speedalarmhub> _hubContext;
        private IMemoryCache _cache;
        public SpeedListener(IHubContext<speedalarmhub> hubContext,IMemoryCache cache)
        {
            _hubContext = hubContext;
            _cache = cache; 
        }
        public static string  cs = Database.Database.Connector();
        public void ListenForAlarmNotifications()
        {
            NpgsqlConnection conn = new NpgsqlConnection(cs);
            conn.StateChange += conn_StateChange;
            conn.Open();
            var listenCommand = conn.CreateCommand();
            listenCommand.CommandText = $"listen notifytickets;";
            listenCommand.ExecuteNonQuery();
            conn.Notification += PostgresNotificationReceived;
            _hubContext.Clients.All.SendAsync(this.GetAlarmList());
            while (true)
            {
                conn.Wait();
            }
        }
        private void PostgresNotificationReceived(object sender, NpgsqlNotificationEventArgs e)
        {

            string actionName = e.Payload.ToString();
            string actionType = "";
            if (actionName.Contains("DELETE"))
            {
                actionType = "Delete";
            }
            if (actionName.Contains("UPDATE"))
            {
                actionType = "Update";
            }
            if (actionName.Contains("INSERT"))
            {
                actionType = "Insert";
            }
            _hubContext.Clients.All.SendAsync("ReceiveMessage", this.GetAlarmList());
        }
        public string GetAlarmList()
        {
            List<NotificationModel> not = new List<NotificationModel>();
            using var con = new NpgsqlConnection(cs);
            {
                string query = "Select datumnu, bericht FROM notification";
                using NpgsqlCommand cmd = new NpgsqlCommand(query, con);
                {
                    cmd.Connection = con;
                    con.Open();
                    using (NpgsqlDataReader dr = cmd.ExecuteReader())
                    {
                        
                        while (dr.Read())
                        {
                            not.Add(new NotificationModel { Datenow = ((DateTime) dr["datumnu"]).ToString("yyyy/MM/dd"), Bericht = dr["bericht"].ToString() });
                        }
                    }
                    
                    con.Close();
                }
            }
            _cache.Set("notification", SerializeObjectToJson(not));
            return _cache.Get("notification").ToString();
        }
        public String SerializeObjectToJson(Object notification)
        {
            try
            {
                
                return  Newtonsoft.Json.JsonConvert.SerializeObject(notification);
            }
            catch (Exception) { return null; }
        }
        private void conn_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {

            _hubContext.Clients.All.SendAsync("Current State: " + e.CurrentState.ToString() + " Original State: " + e.OriginalState.ToString(), "connection state changed");
        }
    }
}

If needed here is my hub

namespace myWebApp.Hubs
{
     
    public class speedalarmhub : Hub
    {
        private IMemoryCache _cache;
        private IHubContext<speedalarmhub> _hubContext;
         public speedalarmhub(IMemoryCache cache, IHubContext<speedalarmhub> hubContext)
        {
            _cache = cache;
            _hubContext = hubContext; 
        }

        public async Task SendMessage()
        {
            if (!_cache.TryGetValue("notification", out string response))
            {
                SpeedListener speedlist = new SpeedListener(_hubContext,_cache);
                speedlist.ListenForAlarmNotifications();
                string jsonspeedalarm = speedlist.GetAlarmList();
                _cache.Set("notification", jsonspeedalarm);
                await Clients.All.SendAsync("ReceiveMessage", _cache.Get("notification").ToString());
            }
            else
            {
                await Clients.All.SendAsync("ReceiveMessage", _cache.Get("notification").ToString());
            }
        }

    }
}

the table name in postgresql is called 'notification', i have two column called 'bericht' with type varchar and 'datumnu' with type date.

Answer 1

Edit suggested by mjwills DateTime don't accept null value. Check if the value is null and assigne a default value

while (dr.Read())
{
    not.Add(new NotificationModel { Datenow = ((DateTime) dr["datumnu"]).ToString("yyyy/MM/dd"), Bericht = dr["bericht"].ToString() });
}

Become

while (dr.Read())
{
    DateTime defaultDateTime = DateTime.Now;
    if(dr.IsNull("datumnu")){
        defaultDateTime = (DateTime)dr["datumnu"];
    }

    not.Add(new NotificationModel { Datenow = defaultDateTime, Bericht = dr["bericht"].ToString() });
}

in single line

while (dr.Read())
{
    not.Add(new NotificationModel { Datenow = (dr.IsNull("datumnu") ? DateTime.Now : (DateTime)dr["datumnu"]), Bericht = dr["bericht"].ToString() });
}