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

Question

Hi, 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

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

Hope it helps

http://stackoverflow.com/questions/1557592/is-using-a-singleton-for-the-connection-a-good-idea-in-asp-net-website

Answer 2

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).

Answer 3

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 4

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 5

I think the recommendation is to "refresh often."

Answer 6

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.