Get a NotSupportedException when trying to add an object to a List

Question

The table in SQL Server that I am trying to extract data from has the following columns:

name | gender | salary | department

In the code below, all I am trying to do is extract employee information from a table, create an employee object out of that information, and then add that object to a list of employees. Here is the code:

namespace LINQQueriesPart2
{
    class Program
    {
        List<Employee> whatever = EmployeeDB.getEmployees();

        IEnumerable<Employee> query = from y in whatever
                                      where y.name[0] == 'M'
                                      select y;

        foreach (Employee x in query)
        {
            Console.WriteLine(x.name);
        }
    }
}

namespace ConnectingToSql
{
    public static class EmployeeDB
    {
        public static List<Employee> getEmployees()
        {
            List<Employee> returnList = new List<Employee>();

            String connectionString = "server=DESKTOP-T5KDKOP;"
                                    + "database=MCP_Exam_70-483_Prep;"
                                    + "Trusted_Connection=yes";

            if (SQLConnectingTools.CheckConnection(connectionString))
            {
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT * FROM employees", connection);

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    returnList.Add(
                       new Employee
                       {
                           name = reader.GetString(0),
                           gender = reader.GetChar(1),
                           salary = reader.GetInt16(2),
                           department = reader.GetString(3)
                       }
                   );
               }
               reader.Close();

               connection.Close();
           }
           else
           {
               Console.WriteLine("SQL connection is not successful.");
           }
           return returnList;
       }
   }

   public class Employee
   {
       public String name { get; set; }
       public char gender { get; set; }
       public int salary { get; set; }
       public String department { get; set; }

       public Employee() { }
       }
   }

When I run the above code in debug mode (in Visual Studio), the code breaks and the following code is highlighted yellow:

returnList.Add(
    new Employee
    {
        name = reader.GetString(0),
        gender = reader.GetChar(1),
        salary = reader.GetInt16(2),
        department = reader.GetString(3)
    }
);'

Answer 1

First of all you should check if any rows exist in the result. with this code you can check that:

if (reader.HasRows)

You may get System.NotSupportedException or System.InvalidCastException if the design of employee table and your Employee object differ and you try an invalid cast or if there was no data in your result You'll get a System.NotSupportedException.

I made some improvement in your code and this should be working:

using (SqlConnection cn = new SqlConnection(connectionString))
{
    string commandText = "SELECT * FROM employees";
    using (SqlCommand cmd = new SqlCommand(commandText, cn))
    {
        cmd.CommandText = commandText;

        cn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                var employeeColumns = new object[4];
                var colCount = reader.GetSqlValues(employeeColumns);
                returnList.Add(
                    new Employee
                    {
                        name = employeeColumns[0].ToString(),
                        gender = employeeColumns[1].ToString()[0],
                        salary = Convert.ToInt16(employeeColumns[2].ToString()),
                        department = employeeColumns[3].ToString()
                    }
                );
            }
        }
    }
}

Just Copy the code above it your if block (After this line):

if (SQLConnectingTools.CheckConnection(connectionString))