How to close the connection opened by ExecuteReader

Topics: Data Access Application Block
Aug 3, 2007 at 11:20 PM
I'm learning how to use DAAB of ETL 3.1. If I understand correctly, closing ExecuteReader will close the connection opened by DataReader. However, when I check the connection in SQL Server, I still see the connection after the DataReader is closed. Here is the coding that I tried:

Dim db As Database = DatabaseFactory.CreateDatabase()
Dim dbCommand As Common.DbCommand = db.GetSqlStringCommand("select * from customers")
Dim rdrCust As IDataReader
rdrCust = db.ExecuteReader(dbCommand)
rdrCust.Close()
Aug 6, 2007 at 3:20 PM
Hi,

Is this code executed within a transaction, or are you using connection pooling?
The DAAB will only prepare the call to ExecuteReader, but it's ADO.NET doing the actual work.

Regards,
Fernando
Aug 6, 2007 at 11:33 PM
Hi Fernando,

I did not change anything about connection pooling so I guess it is enabled by default. The coding is not executed within a transaction.


Peter
Aug 7, 2007 at 1:49 AM
Hi Peter,

I'm not sure why you want the connection gone, but you can try disabling connection pooling to make sure that's the reason why you still see the connection (just remember to re-enable it later ;)). Just add ;Pooling=False to the connection string.

Regards,
Fernando
Aug 7, 2007 at 2:42 AM
Hi Fernando,

I'm not really want to disable connection pooling. I thought if I close the DataReader, it will also close the connection to SQL Server. But apparently, closing the DataReader does not close the connection to SQL Server. It only disallocates the connection and puts the connection back to the connection pool. I guess if connection pooling is disabled, closing the DataReader will close the connection to SQL Server.



Peter
Aug 7, 2007 at 5:25 AM
Hi Peter,

The purpose of connection pooling is keeping connections open to avoid the expense of opening new ones. You want to keep connection pooling enabled and also see the connection gone when it's no longer used, and these are contradicting goals.

If you disable connection pooling you'll make sure that is the reason why the connection is not being closed. Once you know that, you can re-enable connection pooling (and your connections will stay open).

Fernando
Aug 7, 2007 at 6:48 PM
Hi Fernando,

When I post this question, I was not aware of the connection pooling feature. The articles that I read about DAAB and DataReader do not mention about the connection pooling effect. For example, I read this article
http://msdn.microsoft.com/msdnmag/issues/05/08/DataPoints/ and it has this statement:
When using a DataReader, the DAAB opens the connection for me, then leaves it open until the DataReader is closed.



Thanks,

Peter



Aug 7, 2007 at 7:57 PM
Hi Peter,

Just to clarify, connection pooling isn't about DataReaders or the DAAB. Closing an ADO.NET connection, no matter where it came from (DAAB or not) or how it's being closed, will just return the connection to the pool if connection pooling is used.
I understand this behavior might make understanding the functionality of the block harder to follow (although the block is really a relatively thin wrapper on top of ADO.NET), so disabling the connection pool could make the learning experience easier.

Regards,
Fernando