Holding open connection

[CNemo7539]

I need to execute more then one query using AdoTemplate and do not want to open connection every time. How I can do this? Is wrapping in transaction sufficient?

[Mark Pollack]

Hi,

The underlying standard ADO.NET provider will generally offer connection pooling features so that in reality under the covers only a few socket connections are actually opened to the database and they remain opened during the life of the appliation even if you make what looks like seperate open/close calls in the code. Check your database provider docs for information on connection pooling. You should but together multiple data access calls in the same transaction when that is what your requirements dictate, not as a result of connection management concerns.

Cheers,
Mark

[CNemo7539]

Sorry, I didn't get your last sentence ;-(

In my understanding getting connection from pool (even the same one) is not same as executing commands over the single connection, because pool must clean connection on return. And AdoTemplate open and close connection for each execute. Am I wrong?

Wrapping in transaction will hold connection open. I wonder what is overhead and is there any other approach?

[Mark Pollack]

Hi,

The pool keeps the socket connections open and on close returns to the pool, with the socket still open. The act of opening and closing the socket over and over again is the first order performance concern as is external to the executing process. Any decent database provider is going to avoid that.

When you start a transaction using AdoPlatformTransactionManager, the connection that was opened (in the ado.net api sence, not the socket sence) is bound to the thread. Also a transaction object is created (ado.net api) and also bound to the thread. Binding to the thread is not an expensive operation, certainly in comparison to the cost of going out of process to do any database work.

The other approach is to pass the current connection/transaction object pair you created in your first dao operation (or somewhere before calling your first dao) as method parameters to all your dao operations. This is invasive and generally speaking leads to bad oo design since infrastructure concerns are leaking into your api. The thread local approach is a better way to achieve the same goal. This and other infrastructure related issues is what Spring's ADO.NET framework is designed to address. When AdoTemplate's execute method starts (which all the other methods funnel into) it will first check thread local storage for a connection/transaction pair. If one if found, they will be used, otherwise a new connection will be created/opened and a new transaction object created. When AdoTemplate's execute method ends it will not close the connection/transaction if they are found in thread local storage, otherwise it will close/end tx. So unless you either start a transaction via AdoPlatformTransactionManager or fill the thread local storage via some other means, yes, you right, you will open and close a connection for each execute, just like the plain ado.net api if you do nothing else to pass these objects around.

If you want to get some concrete numbers of thread local performance use the class LogicalThreadContext and call the SetData/GetData/FreeDataSlot in a loop and time the results.

Hope this helps.

Cheers,
Mark

[CNemo7539]

Ok!
Thanks for clarification!