它不起作用,因为您在返回阅读器之前先关闭了连接。阅读器仅在连接打开时才起作用:
result = command.ExecuteReader();
connection.Close();
return result; // here the reader is not valid
一般来说,您不应该使读者回到业务层。阅读器应仅在数据访问层中使用。应该先使用它,然后再关闭连接。
您应该返回一个在连接关闭后可以工作的对象,例如一个DataSet
或DataTable
多个DTO的集合。例如:
public List<Distribution> getDistributionAll()
{
List<Distribution> distributionAll = new List<Distribution>();
using (var connection = new sqlConnection(FoodBankDB.GetConnectionString())) // get your connection string from the other class here
{
sqlCommand command = new sqlCommand("SELECT b.addressLineOne FROM dbo.Beneficiaries b INNER JOIN dbo.Distributions d ON d.beneficiary = b.id", connection);
connection.open();
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
string address = dr["addressLineOne"].ToString();
distributionAll.Add(new Distribution(address));
}
}
}
return distributionAll;
}