CODEWITHSUNDEEP
c#sqldatareader
user1033744
user1033744

Reputation: 73

"Invalid attempt to call Read when reader is closed" when using a SqlDataReader

1) I have the following codes:

private static sqlDataReader gCandidateList = null;

public SqlDataReader myCandidateList
{
    set
    {
        gCandidateList = value;
    }
    get
    {
        return gCandidateList;
    }
}

2) In FormA I have:

sqlConn.ConnectionString = mySettings.myConnString;
sqlConn.Open();
SqlCommand cmdAvailableCandidate = new SqlCommand(tempString, sqlConn);
SqlDataReader drAvailableCandidate = cmdAvailableCandidate.ExecuteReader();
mySettings.myCandidateList = drAvailableCandidate;
sqlConn.Close();

3) In FormB I want to reuse the data saved in myCandidatList so I use:

SqlDataReader drCandidate = mySettings.myCandidateList;
drCandidate.Read();

4) I then got the error "Invalide attempt to call Read when reader is closed."

5) I tried mySettings.myCandidateList.Read() in (3) above and again received the same error message.

6) How can I re-open SqlDataReader drCandidate to read data?

7) Would appreciate very much for advise and help, please.

Upvotes: 4

Views: 19779

Answers (5)

Richard
Richard

Reputation: 1692

Just to add to the answers already given, if you're using async/await then it's easy to get caught out with this by not awaiting an operation inside a using block of a SqlConnection. For example, doing the following can give the reported error

public Task GetData()
{
    using(new SqlConnection(connString))
    {
        return SomeAsyncOperation();
    }
}

The problem here is we're not awaiting the operation inside the using, therefore it's being disposed of before we actually execute the underling async operation. Fairly obvious, but has caught me out before.

The correct thing to do being to await inside the using.

public async Task GetData()
{
    using(new SqlConnection(connString))
    {
        await SomeAsyncOperation();
    }
}

Upvotes: 0

Yogesh P
Yogesh P

Reputation: 321

If you want to use the datareader at later stage, you have to specify the same as a parameter to the ExecuteReader Method. Your code in FormA should be changed as below.

sqlConn.ConnectionString = mySettings.myConnString;  
sqlConn.Open();  
SqlCommand cmdAvailableCandidate = new SqlCommand(tempString, sqlConn);  
SqlDataReader drAvailableCandidate = cmdAvailableCandidate.ExecuteReader(CommandBehavior.CloseConnection);  
mySettings.myCandidateList = drAvailableCandidate;  
sqlConn.Close();

Make sure to dispose the datareader once it is used, as the connection to the database will be held open till the datareader is closed. Better change your code in FormB as below.

using (mySettings.myCandidateList)
{
mySettings.myCandidateList.Read();  
}

Upvotes: 1

James Shuttler
James Shuttler

Reputation: 1374

When you call Close on the SqlConnection object (sqlConn.Close();) it closes the connection and your data reader. That is why you are getting the error when you try to read from your SqlDataReader from FormB.

What you need to do is change the definition of your myCandidateList property to instead return a representation of the data that you have extracted from the your drAvailableCandidate reader.

Essentially what you need to do is iterate through the rows in the drAvailableCandidate object, extract the values and cache them in your property for later retrieval.

Upvotes: 0

phoog
phoog

Reputation: 43036

You're closing the connection before you attempt to read from the reader. That won't work.

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94625

You can't read reader once the connection is closed or disposed. If you want to use those rows (fetch result) later in your code you need to create a List or DataTable.

For instance,

System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(drAvailableCandidate);

Upvotes: 6

Related Questions