Proper abstraction of the database tier in a 3 tier system?

Question

I am creating a 3 tier application. Basically it goes

Client -> (through optional server to be a thin-client) -> Business Logic -> Database Layer

And basically making it so that there is never any skipping around. As such, I want for all of the SQL queries and such to be in the Database Layer.

Well, now I'm a bit confused. I made a few static classes to start off the database tier but what should I do for the database connections? Should I just create a new database connection anytime I enter the Database Layer or would that be wasteful? Does Connection.Open() take time whenever you have a ConnectionPool?

To me, it just feels wrong for the Business tier to have to pass in a IdbConnection object to the Database tier. It seems like the Database tier should handle all of that DB-specific code. What do you think? How can I do it the proper way while staying practical?

Answer 1

Because of the ConnectionPool, open a new connection each time you access the db is usually not a problem.

If you can reuse open connection without leaving connections open a long time, and without risking leaving orphaned opened connections, then it doesn't hurt to reuse open connections. (I actually inject a datatool into all my classes that access the db. This is mainly for unit testing purposes, but it also allows me to optionally keep a connection open to be used by multiple calls to the db.)

But again, you should not stress too much about opening/closing a lot of connections. It is more important that your DAL:

• is maintainable, simple, flexible
• performs as well as possible
• (most importantly) always properly disposes of it's connections.