1

When using ado.net, should I worry about opening and closing too many connections?

For example, say I have an MVC controller method that calls 3 separate repositories to fill all the needed objects.

 public ActionResult()
 {
      var codesRepository = new CodesRepository();
      viewModel.codes = codesRepository.GetCodesByName("D");

      var employeeRepository = new EmployeeRepository();
      viewModel.employees = employeeRepository.GetByName("Thomas");

      var companyRepository = new CompanyRepository();
      viewModel.company = companyRepository.GetById(20);

      //.... rest of code
 }

In each GetBy method above, I am opening and closing a connection.

using(var connection = DbFactory.GetConnection('SQLBase') 
{
    // code to fill the objects.
}

Do I need to worry about this approach being that I am opening and disposing a connection for each method called? It seems to me that the same connection should be used for all 3?

I've read about connection pooling a bit. Is that a database specific feature, or a feature in ado.net?

Chace Fields
  • 857
  • 2
  • 10
  • 20

2 Answers2

1

Yes, you should - mostly because your design is an anti pattern. While the performance impact is low on read, you totally destroy either transactional integrity OR performance on the write / update side.

Point is - in order to make updates across multiple repositoies then you need either DTC (slow), or make it repository by repository, which has no transactional integrity.

Yo also are terribly slow for any select operation that cross references entities. And you ahve a maintenance nightmare.

companyRepository.GetById(20);

Should be

repository.Element<Company>.Where (x => x.id=20);

This approach would allow you to:

  • Use one repository for everything.
  • Use Company flexible, including join operation and subselects
  • Use one repository with one connection for all updates in a transaction without DTC.

Right now your problem is not so much the opening, but the updates and the transaction.

This is an / the approach that is used by Entity Framework etc. - if you want performance, raw, you can use BlToolkit.

TomTom
  • 61,059
  • 10
  • 88
  • 148
-1

ADO.NET uses connection pooling automatically so you don´t have to worry about it.

So if you open up multiple connections to the database it will optimize the connections by pooling them to one connection.

Basically you don´t have to worry about it :)

Viktor
  • 679
  • 6
  • 21
  • -1. While the impact may be low, it is there and there ARE other implications - mostly on the write side, which has a SERIOUS prerforamnce impact. – TomTom Dec 10 '12 at 04:26
  • Im´not sure where you are getting this information from but I found another stackoverflow thread (which you should have found) which indicates that you are wrong. If you have any evidence otherwise please share them. http://stackoverflow.com/questions/9807268/is-it-best-to-pass-an-open-sqlconnection-parameter-or-call-a-new-one-in-each-me – Viktor Dec 10 '12 at 07:32
  • yes connection to the DB are free with connection pooling, just check this article -> https://en.wikipedia.org/wiki/Fallacies_of_Distributed_Computing – David DV Dec 10 '12 at 09:50