Best Practice on initializing a Database Connection

Question

I am currently working on a MVC 4 project and we decided to use plain old SQL and to not rely on EntityFramework.

My question now is what is the best practice to initialize a database connection. I thought about using a Singleton that opens the connection (the connection-string is stored in the Web.config) and then use the established connection throughout the application life-cycle but I'm not sure if this is a good approach!

There is no best approach, only the best for your needs and situation, and you have to figure out that yourself. This is very opinionated and doesn't fit here I'm afraid.. — walther, Feb 20 '13 at 09:22
you mean using the same connection instance throughout the application life cycle? — Hassan Mokdad, Feb 20 '13 at 09:22

Habib · Accepted Answer · 2013-02-20T10:27:14.333

4

For database connection, Open as late as possible and close as early as possible.

Instead of having a singleton connection, you should create connection object when ever it is necessary, and better if you use using block, since Connection implements IDisposable and you should get dispose the connection when you are done with it.

edited Feb 20 '13 at 10:27

answered Feb 20 '13 at 09:23

Habib

219,104
29
407
436

-1 : For the database, opening a connection is a very expensive operation. Allocating PGA, and stuff. You don't want to open 50 connections per second to do a simple select, you'd rather use some pooling. – Plouf Feb 20 '13 at 10:21
3

@Plouf, Connection pooling is there in .Net to take care of that, its about disposing connection object when not required, Also if I need 50 reads in a single module I would do it with a single connection, but will make sure to dispose the connection once I am done with it. – Habib Feb 20 '13 at 10:24

score 0 · Answer 2 · answered Feb 20 '13 at 09:24

ODP.NET supports connection pooling so there is no reason (Unless proven otherwise in your specific case) to keep a Singleton of your OracleConnection object.

The best practice here is imo to use a connection (.NET connection object, not a physical connection) for each statement.

using(OracleConnection connection = ...)
    {
        ...Do work here
    }

score 0 · Answer 3 · edited May 23 '17 at 12:12

If you want to know more about a similar approach to what you've just described, look here: https://stackoverflow.com/a/10153406/1289283 . I've already answered that kind of dilemma. Be sure you won't do this on per-application scale, but rather on per-request, otherwise you get into troubles. Per-application is fine for single-user-desktop apps, NOT for webapps! And do not use singleton for this also...

Best Practice on initializing a Database Connection

3 Answers3

Linked