Database, Connection and Transaction

Topics: Data Access Application Block, General discussion
Dec 1, 2007 at 2:03 PM
Hi,

How is the relation between the Database objects of DAAB and the connection and transaction ADO.NET oject that i can get from a Database ?

If i ask for Database.CreateConnection() on different Database objects, but with the same provider and connection string, i get different phisic connections to the database and by this a transaction cannot be shared between this connections ?

Also from the quickstart examples of transaction:
db.ExecuteNonQuery(creditCommand, transaction);

in this case the transaction object is obtained from a connection which is from the same database object as db

Wich happens if i call some of this methods with a transaction of other db object

Transaction t = db1.CreateConnection().BeginTransaction();

db2.ExecuteNonQuery(creditCommand, t); ???

If i have a lot of simple methods that:

Call db = DatabaseFactory.CreateDatabase();
db.DoSomething
return

How i can enclose a set of calls to this methods inside a transaction ?

Thanks for the guidance.

Enrique

Dec 4, 2007 at 11:59 AM
Hi,

Answers inline.


How is the relation between the Database objects of DAAB and the connection and transaction ADO.NET oject that i can get from a Database ?

If i ask for Database.CreateConnection() on different Database objects, but with the same provider and connection string, i get different phisic connections to the database and by this a transaction cannot be shared between this connections ?


You will get different DbConnection instances even if you ask for them to the same Database instance.


Also from the quickstart examples of transaction:
db.ExecuteNonQuery(creditCommand, transaction);

in this case the transaction object is obtained from a connection which is from the same database object as db

Wich happens if i call some of this methods with a transaction of other db object

Transaction t = db1.CreateConnection().BeginTransaction();

db2.ExecuteNonQuery(creditCommand, t); ???


AFAICT, you would only get wrong instrumentation figures.


If i have a lot of simple methods that:

Call db = DatabaseFactory.CreateDatabase();
db.DoSomething
return

How i can enclose a set of calls to this methods inside a transaction ?


Depending on what you do in your methods (in particular, if you use methods that don't requiere you to provide a connection), TransactionScopes might be what you're looking for. Look at ms-help://ms.EntLib.2007May/EnterpriseLibrary/html/04-100-Using_the_TransactionScope_Class.htm in the help file.

Hope this helps,
Fernando