DAAB same connection object

Topics: Data Access Application Block
Mar 27, 2008 at 10:24 AM
Per ADO.NET guidelines, keeping a connection open is strongly discouraged. Enterprise Library DAAB uses the connection pool effectively.
But there are some situations where we may need to execute queries with the same conenction object.
Consider the following scenario where there are three queries to be executed with the same connection object WITHOUT USING transaction object:
1. create a temp table(#table)
2. using sqlbulkcopy class to insert bulk rows to the temp table (from .NET)
3. manipulate something with the temp table and other permanent tables

I could make some changes in the DAAB classes and make it work. Before doing that, I just wanted to confirm that the above mentioned scenario cannot be handled with DAAB.

Mar 27, 2008 at 2:32 PM
Edited Mar 27, 2008 at 6:54 PM
I don't think you need to make code changes to DAAB to achieve this.

DbConnection conn = Database.CreateConnection();
DbTransaction trans = conn.BeginTransaction();

Use this DbTransaction with all your execute methods, to use the same connection.


I havent really tried it, but it was my understanding, that, exactly for this purpose DAAB api have execute methods, that takes in a DbTransaction argument ( even though, the prescribed way of doing transaction is using transaction scope).

Ofcourse, remember to take care of your DbConnection in the end.
Mar 28, 2008 at 6:39 AM
You are right, DAAB provides DBTransaction object as a parameter along with the DBCommand object.
While executing an INSERT, UPDATE, or DELETE statement under transaction, respective tables will be locked. This is not what I exactly wanted.
I need to retain the same connection object for executing the above 3 steps without using any transaction and there by not locking the tables.
Mar 28, 2008 at 3:04 PM
Edited Mar 28, 2008 at 3:12 PM
Ahhh, sry i missed "Without using transaction" part in your initial post. I guess, you do need code change, if you are not planing on using transactions.

Mar 29, 2008 at 10:49 PM
I agree with vijaypvb with deconnected constraint

Please take a look there