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

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?

---

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.

---

Only open the connection when you need it. Do not maintain a connection to the database, that is much more wasteful. Of course your db layer will open the connection, Im not sure why you think the BLL is going to pass a connection to the db. The BLL does not know about the database (at least it shouldnt), it should handle business rules and such. The actual connection is open at the db layer.

Here is a link that shows how the BLL should look:

Validating data in .net

The connection string itself should simply be a private string variable within your db layer class and you should be able to pull connection string information from say a web.config file.

---

It's okay to create and open a new connection every time you enter the database layer, and close the connection as soon as you're finished with it. .Net/Sql Server handles connection pooling well enough to make this work, and it's the accepted way to do it.

You are also right that you don't pass your connection string in from the business layer. That should be a private (but configurable) member of the data layer.

---

Traditionally, a separate "Data Access Layer" provides the database context for retrieving and committing data. There are several well-known patterns for this, such as Repository. ADO.NET implements several others, such as Provider.

Entity Framework and LINQ to SQL are also good options to further encapsulate and simplify the isolation of the data tier.

---

You can create a class (or namespace of classes, depending on the size) to host the database layer. Within your database class, you should just use a connection pool in the database layer. The pool will keep n number of connections open to the database at any given time, so you can just run a query using one of the pooled connections without incurring significant overhead.

With this in place, your database layer should present an "API" of public methods that the business layer can call into. None of these methods should expose a database connection object - those details are internal to the data layer.

Then from your business layer, just call into the database layer's "API" each time you need to run a query.

Does that help?