ASP.NET A static object to hold connection with a DB. Is it a good idea?

Question

I'm wondering if it is a good approach in the ASP.NET project if I set a field which "holds" a connection to a DB as a static field (Entity Framework)

public class DBConnector
{
    public static AdServiceDB db;
    ....
}

That means it'll be only one object for entire application to communicate with a DB. I'm also wondering about if that object will be refreshing data changes from DB tables, or maybe it shouldn't be static and I shoud create a connection dyniamically. What do You think ?

Answer 1

Since none of the answers have been marked as an answer and I don't believe any have really addressed question or issue thereof...

In ASP.NET, you have Global or HttpApplication. The way this works is that IIS will cache instances of your "application" (that is an instance of your Global class). Normally (default settings in IIS) you could have up to 10 instances of Global and IIS will pick any one of these instances in order to satisfy a request.

Further, keep in mind that, there could be multiple requests at any given moment in time. Which means multiple instances of your Global class will be used. These instances could be ones that were previously instantiated and cached or new instances (depending on the load your IIS server is seeing).

IIS also has a notion of App Pools and worker processes. A Worker process will host your application and all the instances of your Global classes (as discussed earlier). So this translates to an App Domain (in .NET terms).

Just to re-cap before moving on…

Multiple instances of your Global class will exist in the Worker process for your application (in IIS). Each one waiting to be called upon by IIS to satisfy a request. IIS will pick any one of these instances. They are effectively threads that have been cached by IIS and each thread has an instance of your Global class. When a request comes in, one of these threads is called upon to handle the request-response cycle. If multiple requests arrive simultaneously, then multiple threads (each contains an instance of your Global class) will be called upon to satisfy each of those requests.

Moving on…

Since there will be only one instance of a static class per App Domain you'll effectively have one instances of your class shared across all (up to 10) instances of Global. This is a bad idea because when multiple simultaneous requests hit your server they'll either be blocked (if your class’s methods use locks) or threads will be stepping on each other’s toes. In other words, this approach is not inherently thread-safe and if you make it thread safe using thread synchronization primitives then you’re unnecessarily blocking threads, negatively impacting performance and scalability of your web application, with no gain whatsoever.

The real solution (and I use this in all my ASP.NET apps) is to have an instance of your BLL or DAL (as the case may be) per instance of Global. This will ensure the following: 1. Multiple threads are not an issue since IIS guarantees one request-response per instance of Global) at any given moment in time. So you’re code is inherently threads-safe. 2. You only have up to 10 instances of your BLL/DAL up and running at any given moment in time ensuring that you're not constantly creating and disposing instances of (typically) large objects to satisfy each request, which on busy sites is huge 3. You get really good performance well due to #2 above.

You do have to ensure that your BLL/DAL is truly stateless or that you reset any state at the start of each Request-Response cycle. You can use the BeginRequest event in Global to do that is you need to.

If you go down this route, be sure to read my blog post on this

Instantiating Business Layers – ASP.NET

Answer 2

This is why you should not use a singleton design pattern with your database connection

Hope it helps

Is using a singleton for the connection a good idea in ASP.NET website

Answer 3

A singleton connection to a database that is used across multiple web page requests from multiple users presents a large risk of cross-contamination of personal information across users. It doesn't matter what the performance impact is, this is a huge security risk.

If you don't have users or personal information, perhaps this doesn't apply to your project right now, but always keep it in mind. Databases and the information they contain tend to evolve in the direction of more specifics and more details over time.

Answer 4

I think the recommendation is to "refresh often."

Answer 5

Bad idea. Besides the potential mistakes you could make by not closing connections properly and so forth, accessing a static object makes it very difficult to unit test your code. I'd suggest using a class that implements an interface, and then use dependency injection to get an instance of that class wherever you need it. If you determine that you want it to be a singleton, that can be determined in your DI bindings, not as a foundational point of your architecture.

Answer 6

With connection pooling in .NET, generally creating a new connection for each request is acceptable. I'd evaluate the performance of creating a new one each time, and if it isn't a bottleneck, then avoid using the static approach. I have tried it before, and while I haven't run into any issues, it doesn't seem to help much.

Answer 7

I would say no.

A database connection should be created when needed to run a query and cleaned up after that query is done and the results are fetched.

If you use a single static instance to control all access to the DB, you may lose out on the automatic Connection Pooling that .NET provides (which could impact performance).