C# Invalid attempt to call Read when reader is closed

Viewed 80539

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;
}
2 Answers
Related