Correct database level abstraction in a three-tier system?

Question

I am creating a three-tier application. Basically it goes

Client → (through an additional server to be a thin client) → Business logic → Database level

And basically it makes it so that it never skips. Thus, I want all SQL queries and such to be in the database layer.

OK, now I'm a little confused. I made some static classes to start the database tier, but what should I do to connect to the database? Should I just create a new database connection anytime I enter the database level or will it be wasteful? Does Connection.Open () set the time when you have ConnectionPool?

It just seems to me that the business layer should go through the IdbConnection object at the database level. It seems that the database level should handle all this database-specific code. What do you think? How can I do it right while staying practical?

Answer 1

Due to ConnectionPool, opening a new connection every time you access db is usually not a problem.

If you can reuse an open connection without opening for a long time, and not risking leaving open open connections, then reusing open connections will not hurt. (I actually embed datatool in all of my classes that access db. This is mainly for unit testing purposes, but also allows me to maintain an open connection for use by multiple calls in db.)

But then again, you shouldn't stress too much opening / closing a lot of connections. It is more important that your DAL:

• is convenient, simple, flexible
• performs as much as possible
• (most important) always uses its connections correctly.

Answer 2

Open the connection only when you need it. Do not maintain a database connection, which is much more wasteful. Of course, your db layer will open the connection, I'm not sure why you think the BLL is going to transfer the connection to db. BLL does not know about the database (at least it should not), it must process business rules, etc. The actual connection is open at db level.

Here is a link that shows what the BLL should look like:

Validating data in .net

The connection string itself should simply be a private string variable in your db class class, and you should be able to extract information from the connection string from the web.config file.

Answer 3

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

In doing so, your database tier should be an “API” of public methods that the business tier can invoke. None of these methods should show the database connection object — this data is internal to the data layer.

Then, from your business layer, just go to the “API” database layer every time you need to complete a query.

Does it help?

Answer 4

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

You are also right that you are not passing the connection string from the business layer. This must be a private (but custom) data layer element.

Answer 5

Traditionally, a separate “data access layer” provides a database context for retrieving and fixing data. There are several well-known templates for this, such as a repository. ADO.NET implements several others, such as a provider.

Entity Framework and LINQ to SQL are also good options for further encapsulating and simplifying data layer isolation.