how to close underlying DbConnection without using "Using" statement

Topics: Data Access Application Block
Feb 9, 2011 at 8:29 PM

Here's my  code,  I am using "Data Access Application Block"  in ObjectDataSource's class file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;


namespace cHospital
{
    public class Hospital
    {
        private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>("Conn");
        public SqlDataReader sDR;

        public SqlDataReader GetHospitals()
        {

            RefCountingDataReader dr = (RefCountingDataReader)_db.ExecuteReader("GetHospitals");
            return (SqlDataReader)dr.InnerReader;
        }
}
}


in my code, I didnt dispose the dataReader. will it mess up my connection management ? I want to make sure that DbConnection is properly closed.

I found this from "hands on lab".
"You are not doing any connection management here, but it is very important to Dispose the data reader returned. This is accomplished by the using statement in the code above. When the data reader is disposed, the underlying DbConnection is also closed."

please let me know if what I did is ok, if not how do i close DbConnection properly.

if you are wondering why I dont use "using" statement.

basically, I bind my gridviews using ObjectDataSource, if I am using "using" statement in my code, then DataReader will be disposed, its giving me error message.

I posted that problem here:  http://entlib.codeplex.com/Thread/View.aspx?ThreadId=245208




Feb 10, 2011 at 7:09 AM

Closing the reader immediately after use is highly recommended to make sure connection won't have leaks.

I can't think of any approach of how you'll be able to retain the values of the reader after closing/disposing it other than iterating each value of the result and putting it in a custom object or someting like it while the reader is still open.

Though,  I noticed from your other post that you're actually returning a couple of fields and binding it in your GridView, the question is - is utilizing ExecuteDataSet() won't work for you?

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 10, 2011 at 11:27 AM

Thank you Gino.  Actually, I already tried using ExecuteDataSet, it works.

I did not try DataSet before, cause I thought there might be a solution for me to use DataReader.  After days searching on google,  I gave up.  ExecuteDataSet works.

Feb 10, 2011 at 12:30 PM

another quick question. here's my code.

 

        public DataTable GetHospitals()
        {
            DbCommand dbCmd = _db.GetStoredProcCommand("GetHospitals");
            return _db.ExecuteDataSet(dbCmd).Tables[0];
        }

 

I didnt use "using" statement here, will underlying DbConnection  be closed automatically?

Feb 11, 2011 at 12:12 AM

Yes, the connection will be closed automatically in that case.  The only time you need to explictly do the closing is when returning a datareader and when you explicitly created and opened connection prior to executing a command.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com