尝试将对象添加到列表时获取NotSupportedException
问题描述:
我试图从中提取数据的SQL Server中的表具有以下列:尝试将对象添加到列表时获取NotSupportedException
name |性别|薪水|部门
在下面的代码中,我试图做的是从表中提取员工信息,从该信息中创建一个员工对象,然后将该对象添加到员工列表中。 下面是代码:
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() { }
}
}
当我运行在调试模式下,上面的代码(在Visual Studio),代码休息和下面的代码以黄色高亮显示:
returnList.Add(
new Employee
{
name = reader.GetString(0),
gender = reader.GetChar(1),
salary = reader.GetInt16(2),
department = reader.GetString(3)
}
);'
答
首先你应该检查结果中是否存在任何行。 与此代码,你可以检查:
if (reader.HasRows)
你可能会得到System.NotSupportedException或System.InvalidCastException如果员工表的设计和你的Employee对象不同,你尝试了无效的转换,或者出现在没有数据你的结果你会得到一个System.NotSupportedException。
我在你的代码做了一些改进,这应该工作:
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()
}
);
}
}
}
}
代码只需复制它上面的,如果块(该线后):
if (SQLConnectingTools.CheckConnection(connectionString))
什么是确切的例外消息和内部异常消息,你正在得到?你从SELECT * FROM employees'查询中得到的结果集中的列的类型是什么? – Shyju
您可以更轻松地将员工加载到DataTable中。就像,如果你选择了'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' – Plutonix