If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] Why would SQLDataReader close itself? Interesting.

Hi all,
Iam baffled at this - so basically, I want to get a set of records based on a query and then populate a combobox (with a field form the query) and then set the entire query result as a datagridview's datasource which displays all of its fields. Its to give the users 2 different views of their search to make it easier for them. here's the code:

So, when I set the SqlDataReader object to the gridview, it works fine. If I use it to read it , then it says its already closed, even though I have not. Can you find out where the error is or Iam missing something? Please help. Thank you.

Also, it would be very useful to you and me, if you can check out the site below. Thank you very much.

Technically, that did not make sense anyway. Iam not surprised that you are an MVP?? and a Mod?? Ok, that goes against the basic principles of any OO lang like C#. Local Objects do NOT go out of scope until the function exits, or in this case the try block. So, the using keyword does NOT make any difference here.

I believe the way SQLDataReader is implemented in C# that makes all the difference. Once its loaded onto a DataTable, it does NOT go out of scope but is set to NULL or closed by the .NET runtime. I need to check on that, and get back.

Re: Why would SQLDataReader close itself? Interesting.

Originally Posted by CuteAssistant

Technically, that did not make sense anyway. Iam not surprised that you are an MVP?? and a Mod?? !

Ok, that goes against the basic principles of any OO lang like C#. Local Objects do NOT go out of scope until the function exits, or in this case the try block. So, the using keyword does NOT make any difference here.

I believe the way SQLDataReader is implemented in C# that makes all the difference. Once its loaded onto a DataTable, it does NOT go out of scope but is set to NULL or closed by the .NET runtime. I need to check on that, and get back.

I take it you didn't read my followup post that since a SqlDataReader is forward only and read-only, it isn't going to do what you wanted to anyway. Btw, even MVP's and Mod's don't get everything correct the first time, but I guess you understand that since you're the one asking for help.

As far as the using block, sure it won't help the problem, but it sure cleans up the code. Using the using block just prevents you from having to call close and dispose (not that you need to call both anyway).

It really doesn't make any difference in your case since the SqlDataReader can't be reset to the beginning of the records.

Re: Why would SQLDataReader close itself? Interesting.

Re: Why would SQLDataReader close itself? Interesting.

@CuteAssistant - I'm going to cut out all the rhetoric and see if I can lay it out simply. There's a number of factors at work here. When you use ExecuteReader to get a reader that is using a connection that hasn't been opened yet, it will open the connection automatically. Which is fine, and expected - and a bonus - when the reader is then done with it's work, it will AUTOMATICALLY close the connection for you. So, when you use the reader to fill your datatable, it is opening the connection, filling the datatable, then closing the connection. This is why you are getting the closed connection error. Even though you did not explicitly close it, because you let it automatically open, it closed on it's own. Think of it like the automatic doors at a store.

Now... to solve your problem, I see two solutions... 1) Re-execute the ExecuteReader... then attempt your loop through it. Unfortunately there is no rewind method, even if there was, the connection is closed, so it wouldn't matter. 2) Since you have the data in the datatable, loop through that to get your data. Option 1 means a second trip to the data base for data you already have. Option 2 saves you from that extra trip. In addition, you could probably use a LINQ query to get the data and then just simply pass that to the combobox.

Re: Why would SQLDataReader close itself? Interesting.

Originally Posted by Arjay

I take it you didn't read my followup post that since a SqlDataReader is forward only and read-only, it isn't going to do what you wanted to anyway. Btw, even MVP's and Mod's don't get everything correct the first time, but I guess you understand that since you're the one asking for help.

As far as the using block, sure it won't help the problem, but it sure cleans up the code. Using the using block just prevents you from having to call close and dispose (not that you need to call both anyway).

It really doesn't make any difference in your case since the SqlDataReader can't be reset to the beginning of the records.