"Invalid attempt to cause" Read when the reader is closed "when using SqlDataReader

Question

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 stored in myCandidatList, so I use:

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

4) Then I got the error message "Invalide tried to call Read when the reader is closed."

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

6) How can I reopen SqlDataReader drCandidate to read data?

7) Would thank for advice and help, please.

Answer 1

You cannot read the reader when the connection is closed or disposed . If you want to use these lines (the result of the selection) 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); 

Answer 2

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

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

Be sure to remove the datareader after using it, as the database connection will be open until the datareader is closed. Better change your code in FormB as shown below.

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

Answer 3

You close the connection before trying to read it. This will not work.

Answer 4

When you call Close on the SqlConnection object ( sqlConn.Close(); ), it closes the connection and your data reader. This is why you get an error message when you try to read from SqlDataReader from FormB.

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

Essentially, you need to iterate through the lines in the drAvailableCandidate object, extract the values ​​and cache them in the property for later retrieval.

Answer 5

To add to the answers already received, if you use async / await, then it is easy to get this without waiting for the operation inside the SqlConnection use block. For example, doing the following may give an error message

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

The problem is that we do not expect an operation inside use, so it is deleted before we perform the async underling operation. Extremely obvious, but caught up with me before.

The right thing to do to wait inside use.

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