Unable to cast object of type 'System.DBNull' to type 'System.Int32' exception in ADO.NET

22 Views Asked by At

I was trying to practice some simple ADO.NET app that would just get some data from Northwind DB and display it but it throws "Unable to cast object of type 'System.DBNull' to type 'System.Int32'." exception.I had googled it and I saw that I need to handle null values but It did not work (still getting same error)

const string connectionString =
    "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True";


var employees=GetEmployees();

foreach (Employee employee in employees)
{
    Console.WriteLine( $"Name: {employee.FirstName} Last Name: {employee.LastName}");

}



static List<Employee> GetEmployees()
{

    const string queryString =
        "SELECT * FROM Employees; ";
    var employees = new List<Employee>();

    using (SqlConnection connection =
        new(connectionString))
    {

        SqlCommand command = new(queryString, connection);

        try
        {

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {

                Employee employee = new();
                employee.EmployeeID = (int)reader["EmployeeID"];
                employee.LastName = (string)reader["LastName"];
                employee.FirstName = (string)reader["FirstName"];
                employee.Title = (string)reader["Title"];
                employee.TitleOfCourtesy = (string)reader["TitleOfCourtesy"];
                employee.BirthDate = (DateTime)reader["BirthDate"];
                employee.HireDate = (DateTime)reader["HireDate"];
                employee.Address = (string)reader["Address"];
                employee.City = (string)reader["City"];
                employee.Region = Convert.IsDBNull((string)reader["Region"]) ? String.Empty : (string)reader["Region"];
                employee.PostalCode = (string)reader["PostalCode"];
                employee.Country = (string)reader["Country"];
                employee.HomePhone = (string)reader["HomePhone"];
                employee.Extension = (string)reader["Extension"];
                employee.Notes = (string)reader["Notes"];
                employee.ReportsTo = Convert.IsDBNull((int)reader["ReportsTo"]) ? 0 : (int)reader["ReportsTo"];
                employee.PhotoPath = (string)reader["PhotoPath"];

                employees.Add(employee);

            }
            reader.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        return employees;
    }

Employee.cs

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

namespace Ado.NET
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Title { get; set; }
       public string TitleOfCourtesy { get; set; }
      public DateTime BirthDate { get; set; }
        public DateTime HireDate { get; set; }
        public string Address {  get; set; }
      public string City {  get; set; }
      public string? Region {  get; set; }
      public string PostalCode { get; set; }
      public string Country {  get; set; }
        public string HomePhone {  get; set; }
      public string Extension {  get; set; }
      //[Photo]
      public string Notes {  get; set; }
      public int? ReportsTo {  get; set; }
      public string PhotoPath {  get; set; }


    }
}

Only ReportsTo and Region have null in DB

Console Output Unable to cast object of type 'System.DBNull' to type 'System.Int32'. Name: Nancy Last Name: Davolio

Googling the problem first and it still did not solve it

0

There are 0 best solutions below