C# SqlDataReader gets closed

Question

I have no clue why I get an exception. It says that the reader got closed before I try to access it. Why is that so?

Here is the code:

//Load Projects from SQL Server (nothing else)
public SPpowerPlantList loadProjectsFromServer(DateTime timestamp, string note, string sqlServer, string database)
{
    SqlConnection sqlConnection = new SqlConnection(String.Format(@"Integrated Security=SSPI;server={0};Initial Catalog={1};", sqlServer, database));
    sqlConnection.Open();

    string selectstring = "SELECT * FROM [dbo].[tblSPpowerPlant]"; //(WHERE note {0} = " + note + " AND timestamp {1} = " + timestamp;
        SqlCommand sqlSelectCommand = new SqlCommand(selectstring, sqlConnection);
    sqlSelectCommand.CommandType = CommandType.Text;
    sqlSelectCommand.CommandText = selectstring;

    SqlDataReader reader;

    SPpowerPlantList powerPlantList = new SPpowerPlantList();

    reader = sqlSelectCommand.ExecuteReader();

    //this line trowhs the exeption
    while (reader.Read())
    {
        foreach (SPpowerPlant powerPlant in powerPlantList)
        {
            powerPlant.ID = reader.GetInt32(0);
            powerPlant.projectName = reader.GetString(1).ToString();
            powerPlant.location = reader.GetString(2); 
            powerPlant.shortName = reader.GetString(3); 
            powerPlant.numberOfWtgs = reader.GetInt32(4); 
            powerPlant.mwWtg = reader.GetDouble(5); 
            powerPlant.mwTotal = reader.GetDouble(6); 
            powerPlant.projectShareWeb = reader.GetDouble(7); 
            powerPlant.mwWeb = reader.GetDouble(8); 
            powerPlant.phase = reader.GetString(9); 
            powerPlant.phaseNumber = reader.GetString(10);
            powerPlant.phaseDescription = reader.GetString(11); 
            powerPlant.projectProgress = reader.GetDouble(12); 
            powerPlant.mwDeveloped = reader.GetDouble(13); 
            powerPlant.projectManager = reader.GetString(14); 
            powerPlant.spaceUrl = reader.GetString(15); 
            powerPlant.country = reader.GetString(16); 
            powerPlant.technology = reader.GetString(17);
            powerPlant.state = reader.GetString(18);
            powerPlant.allPermits = reader.GetDateTime(19);
            powerPlant.cod = reader.GetDateTime(20);
            powerPlant.stateSince = reader.GetDateTime(21);
            powerPlant.spID = reader.GetInt32(22);
            powerPlant.currency = reader.GetString(23);
            powerPlant.possibleWtgTypes = reader.GetString(24);
            powerPlant.hubHeight = reader.GetString(25);
            powerPlant.visibility = reader.GetString(26);
            powerPlant.templateName = reader.GetString(27);

            powerPlantList.Add(powerPlant);
        }

        reader.Dispose();
        sqlConnection.Close(); 
    }

    return powerPlantList;
}

Here is the exception (google translate):

Invalid attempt to Read access because the data reader has been closed.

I tried it with google but had no luck. So any help would be great. Thanks for your time.

BTW Sorry for my english not my native language but I work on it.

Answer 1

the code lines

reader.Dispose();
sqlConnection.Close(); 

are inside the while(reader.read()) loop.

Also, you better use using instead of calling Dispose() yourself.

public SPpowerPlantList loadProjectsFromServer(DateTime timestamp, string note, string sqlServer, string database)
{
    using(var sqlConnection = new SqlConnection(String.Format(@"Integrated Security=SSPI;server={0};Initial Catalog={1};", sqlServer, database)))
    {
        sqlConnection.Open();

        var selectstring = "SELECT * FROM [dbo].[tblSPpowerPlant]"; //(WHERE note {0} = " + note + " AND timestamp {1} = " + timestamp;
        var sqlSelectCommand = new SqlCommand(selectstring, sqlConnection);
        sqlSelectCommand.CommandType = CommandType.Text;
        sqlSelectCommand.CommandText = selectstring;

        SPpowerPlantList powerPlantList = new SPpowerPlantList();

        using(var reader = sqlSelectCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                foreach (SPpowerPlant powerPlant in powerPlantList)
                {
                    powerPlant.ID = reader.GetInt32(0);
                    powerPlant.projectName = reader.GetString(1).ToString();
                    powerPlant.location = reader.GetString(2); 
                    powerPlant.shortName = reader.GetString(3); 
                    powerPlant.numberOfWtgs = reader.GetInt32(4); 
                    powerPlant.mwWtg = reader.GetDouble(5); 
                    powerPlant.mwTotal = reader.GetDouble(6); 
                    powerPlant.projectShareWeb = reader.GetDouble(7); 
                    powerPlant.mwWeb = reader.GetDouble(8); 
                    powerPlant.phase = reader.GetString(9); 
                    powerPlant.phaseNumber = reader.GetString(10);
                    powerPlant.phaseDescription = reader.GetString(11); 
                    powerPlant.projectProgress = reader.GetDouble(12); 
                    powerPlant.mwDeveloped = reader.GetDouble(13); 
                    powerPlant.projectManager = reader.GetString(14); 
                    powerPlant.spaceUrl = reader.GetString(15); 
                    powerPlant.country = reader.GetString(16); 
                    powerPlant.technology = reader.GetString(17);
                    powerPlant.state = reader.GetString(18);
                    powerPlant.allPermits = reader.GetDateTime(19);
                    powerPlant.cod = reader.GetDateTime(20);
                    powerPlant.stateSince = reader.GetDateTime(21);
                    powerPlant.spID = reader.GetInt32(22);
                    powerPlant.currency = reader.GetString(23);
                    powerPlant.possibleWtgTypes = reader.GetString(24);
                    powerPlant.hubHeight = reader.GetString(25);
                    powerPlant.visibility = reader.GetString(26);
                    powerPlant.templateName = reader.GetString(27);

                    powerPlantList.Add(powerPlant);
                }
            }
        }           

    }
    return powerPlantList;
}

Answer 2

If you remove that foreach part you can see the problem.

1. You check if the reader is open
2. you iterate over the powerplant list, by the way this is reassigning each powerPlant to the same record in the reader
3. You close and dispose of the reader
4. Now you check if it is open again at the top of the while which throws the exception

I believe you want to create a new list of SPpowerPlant objects from your reader. You should change your code to the following which does that and releases the reader. Note that you should wrap all your Disposable objects in using statements like with the reader below.

using(var reader = sqlSelectCommand.ExecuteReader()) // closes and disposes reader once it is out of scope
{    
    while (reader.Read()) 
    {
        var powerPlant = new SPpowerPlant();
        powerPlant.templateName = reader.GetString(27);
        //// rest of calls to populate your item
        SPpowerPlantList.Add(powerPlant);
    }
}