C＃无效试图调用时，请阅读阅读器关闭 [英] C# Invalid attempt to call Read when reader is closed
问题描述
我有无效试图调用阅读时读者是封闭的错误，当我做在C＃语言的3层项目。
我所试图做的是通过在下拉列表将两个表连接在一起，并显示检索地址的数据列。
这里是我的数据访问层：
公开名单＆LT;分销及GT; getDistributionAll（）
{
清单＆LT;分销及GT; distributionAll =新的List＆LT;分销及GT;（）;
字符串的地址;
SqlDataReader的博士= FoodBankDB.executeReader（选择b.addressLineOne FROM dbo.Beneficiaries b INNER JOIN dbo.Distributions d在d.beneficiary = b.id）; 而（dr.Read（））
{
。地址=博士[addressLineOne]的ToString（）;
distributionAll.Add（新分配（地址））;
} 返回distributionAll;
}
这是我FoodBankDB类：
公共类FoodBankDB
{
公共静态字符串的connectionString = Properties.Settings.Default.connectionString;
公共静态SqlDataReader的的ExecuteReader（查询字符串）
{
SqlDataReader的结果= NULL;
System.Diagnostics.Debug.WriteLine（FoodBankDB的ExecuteReader：+查询）;
SqlConnection的连接=新的SqlConnection（的connectionString）;
的SqlCommand命令=新的SqlCommand（查询，连接）;
connection.Open（）;
结果= Command.ExecuteReader却（）;
connection.close（）时;
返回结果;
}
我分开这两大类，这样每当我的连接字符串改变了，我可以很容易地通过改变FoodBankDB类修改整个项目。
这是我的商业逻辑层：
公开名单＆LT;分销及GT; getAllScheduledDistribution（）
{
清单＆LT;分销及GT; allDistribution =新的List＆LT;分销及GT;（）;
分布distributionDAL =新发行（）;
allDistribution = distributionDAL.getDistributionAll（）;
返回allDistribution;
}
和最后但并非最不重要的，我的presentation层：
列表＆LT;分销及GT; scheduledList =新的List＆LT;分销及GT;（）;
scheduledList = packBLL.getAllScheduledDistribution（）;
ddlScheduleList.DataSource = scheduledList;
ddlScheduleList.DataTextField =地址;
ddlScheduleList.DataValueField =地址;
ddlScheduleList.DataBind（）;
这是工作好，如果我没拆的数据访问层和连接字符串类。有谁知道如何解决这个问题？
先谢谢了。
更新部分的
公共静态字符串GetConnectionString（）
{
返回的connectionString;
}
它不工作，因为你关闭返回在读者面前的连接。读者仅当连接打开：
结果= Command.ExecuteReader却（）;
connection.close（）时;返回结果; //这里的读者是无效的
一般来说，你不应该阅读器返回到业务层。读者应该只在数据访问层中使用。它应该使用，然后将其和连接应关闭。
您应该相当返回一个对象的连接被关闭后能正常工作，例如一个的DataSet 或数据表或可选择地DTO的集合。例如：
公开名单＆LT;分销及GT; getDistributionAll（）
{
清单＆LT;分销及GT; distributionAll =新的List＆LT;分销及GT;（）; 使用（VAR连接=新的SqlConnection（FoodBankDB.GetConnectionString（）））//从其他班级的连接字符串在这里
{
的SqlCommand命令=新的SqlCommand（选择b.addressLineOne FROM dbo.Beneficiaries b INNER JOIN dbo.Distributions d在d.beneficiary = b.id，连接）;
connection.Open（）;
使用（VAR博士= Command.ExecuteReader却（））
{
而（dr.Read（））
{
。字符串地址=博士[addressLineOne]的ToString（）; distributionAll.Add（新分配（地址））;
}
}
} 返回distributionAll;
}
I am having Invalid attempt to call Read when reader is closed error when I am doing 3 tier project in C# language. What I am trying to do is retrieve address data column by joining two tables together and display in a drop down list. Here is my data access layer:
public List<Distribution> getDistributionAll()
{
List<Distribution> distributionAll = new List<Distribution>();
string address;
SqlDataReader dr = FoodBankDB.executeReader("SELECT b.addressLineOne FROM dbo.Beneficiaries b INNER JOIN dbo.Distributions d ON d.beneficiary = b.id");
while (dr.Read())
{
address = dr["addressLineOne"].ToString();
distributionAll.Add(new Distribution(address));
}
return distributionAll;
}
And this is my FoodBankDB class:
public class FoodBankDB
{
public static string connectionString = Properties.Settings.Default.connectionString;
public static SqlDataReader executeReader(string query)
{
SqlDataReader result = null;
System.Diagnostics.Debug.WriteLine("FoodBankDB executeReader: " + query);
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
result = command.ExecuteReader();
connection.Close();
return result;
}
I separated the these into two class so that whenever my connection string is changed, I can amend the whole project easily by changing the FoodBankDB class.
And this is my business logic layer:
public List<Distribution> getAllScheduledDistribution()
{
List<Distribution> allDistribution = new List<Distribution>();
Distribution distributionDAL = new Distribution();
allDistribution = distributionDAL.getDistributionAll();
return allDistribution;
}
And last but not least, my presentation layer:
List<Distribution> scheduledList = new List<Distribution>();
scheduledList = packBLL.getAllScheduledDistribution();
ddlScheduleList.DataSource = scheduledList;
ddlScheduleList.DataTextField = "address";
ddlScheduleList.DataValueField = "address";
ddlScheduleList.DataBind();
It was working well if I didn't split the data access layer and connection string class. Does anybody know how to solve this error?
Thanks in advance.
Updated portion
public static string GetConnectionString()
{
return connectionString;
}
It doesn't work because you close the connection before returning the reader. Reader works only when the connection is open:
result = command.ExecuteReader();
connection.Close();
return result; // here the reader is not valid
Generally speaking, you should not be returning a reader to a business layer. Reader should be used only in the data access layer. It should be used and then it and the connection should be closed.
You should rather return an object that can work after the connection is closed, e.g. a DataSet or DataTable or alternatively a collection of DTO's. For example:
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;
}
这篇关于C＃无效试图调用时，请阅读阅读器关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
