How do you manage database connections and transactions across inserts in an Object Oriented way, using VB.net or C#?

Question

I'm to the point now in my programming development that I'm trying to take advantage of Object Oriented Design instead of the basic behind the button procedural stuff I have been doing.

In a current project I'm taking repeated, copy and paste sql server insert code and migrating it to classes with properties, methods, etc...

One issue I'm getting stuck at is how to keep connections to the database low, while doing inserts across multiple objects, and therefore multiple tables.

In a procedural way I've been using an ADO.net transaction to write to one table, then based on the inserted record's ID, write additional information to another table -- without closing the connection until the second job is done. I use the transaction so if the later insert fails, the first insert can be undone, and using try, catch, finally to close the connection whether it succeeds or fails.

How do you handle this in the object oriented world?

EDIT: Thanks to everyone's answers. I get it. Here's another question that also helped to clarify it for me: http://stackoverflow.com/questions/334729/asp-net-ado-net-handling-many-database-connections-inside-a-net-object

Answer 1

As far as managing connections, don't. .Net will keep a pool of them for you, and recycle them efficiently. Trying to recycle them yourself will actually hurt performance.

Other than that, it sounds like you are doing it right, insert, get ID, insert again using same connection in that scope.

Answer 2

Hmm, not much different.

Your 'objects' (I presume you mean that you're using classes; and instances of those classes represent 'items' in your business domain), should not care about those Transactions. That is, they should not know about transactions, and they should not be responsible for handling transactions.

Transactions should be initiated and handled by another object. Some kind of 'Manager'. This class starts, commits and rollbacks transactions.
You will use this manager in your Application, for instance, in your form.
To me, this is the only correct way of handling transactions, since your application knows the 'context'. Your application knows how many roundtrips you'll have to do to the DB, the application knows how long the transaction should last. Your objects do not know this.

Answer 3

Google DAL and ORM thay contain the functionality you are talking about i think :)