Data Access in MVC controller

Question

This is a theoretical question, I would appreciate some feedback from experts for a web application served in IIS 7.5

I have a DAO layer, written in C#. In the MVC controller, when you are expecting heavy load is it better to create an instance of the DAO when the controller is created and then reuse that same instance in every method or is it better to create an instance of the DAO in each method?

The DAO does create a new connection to the database when it is created. Every method in the DAO also checks the status of the connection, if it isn't open, it re-initializes the connection.

Overmachine · Accepted Answer · 2014-03-18 14:34:05Z

1

The DAO does create a new connection to the database when it is created. Every method in the DAO also checks the status of the connection, if it isn't open, it re-initializes the connection.

That's actually the way to go.

If you are using Entity Framework in the Data Access Layer, then EF handles the connection to the database. if not and you are using ADO.NET you can let Connection Pooling handle this for you.

Like you are in a web application environment every request to the controllers is unique so, a new connection to the database is the way to go always create new connections and destroy them. Depending if you are using ADO.NET or EF connections are not really created from scratch, they are fetched from a connection pool. There is no performance penalty. Actually that's the best and correct way to go.

answered Mar 18, 2014 at 14:34

Overmachine

1,7333 gold badges15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erik Funkenbusch Over a year ago

To reinforce this, don't try to optimize connections yourself. ADO.NET already does this very efficiently, and it's handle both in EF and ADO.NET automatically.

edepperson Over a year ago

Thanks for the feedback. I'm using FirebirdSql on this project instead of MsSql. Using FbClient as the ADO object. In my connection string, I'm setting the option Pooling=true; MinPoolSize=0; MaxPoolSize=50;. So providing those options don't conflict with ADO, I guess its good to go. Thanks again.

Erik Funkenbusch · Accepted Answer · 2014-03-18 14:58:38Z

Unless you are doing something very resource intensive when you create your DAL (I think you mean DAL as in Data Access Layer, not DAO, DAO is an obsolete technology called Data Access Objects), then don't try to optimize this before you know it to be a problem. All too often you will create added complexity for no value, and probably create new bugs in the process. You may even make performance worse, or create data corruption issues.

If you're using Entity Framework in your DAL, and it's a multi-user application, such as a web server, then you should NEVER us a single instance, because Entity Framework keeps buffers and caches for each request, and you could very well corrupt those with multiple users overwriting them.

Collectives™ on Stack Overflow

Data Access in MVC controller

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related