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.

What line is throwing the exception?

Matt Rowland
– Matt Rowland

2016-03-14 13:02:56 +00:00
Commented Mar 14, 2016 at 13:02 — Matt Rowland
– Matt Rowland, Commented Mar 14, 2016 at 13:02
while (reader.Read()) this one

opelhatza
– opelhatza

2016-03-14 13:04:57 +00:00
Commented Mar 14, 2016 at 13:04 — opelhatza
– opelhatza, Commented Mar 14, 2016 at 13:04

Zohar Peled · Accepted Answer · 2016-03-14 13:47:50Z

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;
}

And sqlConnection.Close(); deserves to be eliminated as well; put using (SqlConnection sqlConnection = new SqlConnection(...)) insetead, please

Igor · Accepted Answer · 2016-03-14 13:10:56Z

1

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

You check if the reader is open
you iterate over the powerplant list, by the way this is reassigning each powerPlant to the same record in the reader
You close and dispose of the reader
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);
    }
}

edited Mar 14, 2016 at 13:10

answered Mar 14, 2016 at 13:05

Igor

62.5k10 gold badges111 silver badges181 bronze badges

2 Comments

Igor Over a year ago

@ltanderl - glad to do it. Even though you can only mark one answer per question you can upvote multiple answers you found useful. Good luck with the rest of your project/code.

opelhatza Over a year ago

well i need 3 more reputation on this side to upvote stuff. But thanks and i dont give up.

Collectives™ on Stack Overflow

C# SqlDataReader gets closed

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related