SqlDataReader Called From a Component Leave Conn Open?

Question

I was reading an article found here (http://www.c-sharpcorner.com/Code/2003/July/DAC4SQLServerInCSharp.asp) about creating a generic data access component. About 2/3's the way down the page the author returns a SqlDataReader from a function. When this function is called and the SqlDataReader is returned wouldn't it leave a connection open? I'm envisioning the usage being DataGrid.DataSource = the SqlDataReader returning function. I can't seem to find a solid answer out there with the necessary info. I have read that the command behaviour will close it for you but in order to trigger the behaviour wouldn't the SqlDataReader.Close() still have to be called? Your guidance in appreciated Many thanks in advance Adam

jshepler · Answer

Yes it would leave the connection open - which is desired. A SqlDataReader object does not keep the data in memory. In fact it hasn't even accessed the database yet. It acts as a pointer - think cursor - to the current row. It doesn't actually get a row of data until you call SqlDataReader.Read(). Then you can work with the row it just read. SqlDataReader r = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection); while(r.Read()) { ... // do something with the data read ... } r.Close(); When you use the SqlDataReader object as a datasource the DataBind() method loops through the SqlDataReader in the same way - but leaves the connection open so you have to close it yourself. So if the function returned a SqlDataReader object with the connection closed it would be useless - you wouldn't be able to use the reader for anything.

classy_catlady · Answer

Hi Adam If you plan on having alot of connections I recomend that you do a sqldatareader.close() and if you used a con.open() to open your sql connection I recommend that you do a con.close() and a con.dispose() also you can do a sqldatareader = nothing after you close it. The reason being is if you have to many connections open you will start to get time out errors. Margaret