Repository Connection Pooling

Question

I'm in a hoo-ha with my boss as I can't shift to using newer technologies until I have proof of some outstanding issues. One of the main concerns is how repositories deal with connections. One of the supposedly largest overheads is connecting and disconnecting to/from the database. If I have a repository where I do the following:

public ContractsControlRepository()
  : base(ConfigurationManager.ConnectionStrings["AccountsConnectionString"].ToString()) { }

with the class like so:

public class ContractsControlRepository : DataContext, IContractsControlRepository

with functions like:

public IEnumerable<COContractCostCentre> ListContractCostCentres(int contractID)
{
    string query = "SELECT C.ContractID, C.CCCode, MAC.CostCentre, C.Percentage FROM tblCC_Contract_CC C JOIN tblMA_CostCentre MAC ON MAC.CCCode = C.CCCode WHERE C.ContractID = {0}";
    return this.ExecuteQuery<COContractCostCentre>(query, contractID);
}

Now if in my controller action called _contractsControlRepository.ListContractCostCentres(2) followed immediately by another call to the repository, does it use the same connection? When does the connection open in the controller? When is it closed?

Cheers

EDIT

I'm using hand-written LINQ as suggested by Steve Sanderson in his ASP.NET MVC book.

EDIT EDIT

To clarify, I'm using LINQ as my ORM, but I'm using raw SQL queries (as shown in the extract above) for querying. For example, here's a controller action:

public ActionResult EditBusiness(string id)
{
   Business business = _contractsControlRepository.FetchBusinessByID(id);
   return View(business);
}

I'm not opening/closing connections.

Here's a larger, more complete extract of my repo:

public class ContractsControlRepository : DataContext, IContractsControlRepository
  {
    public ContractsControlRepository()
      : base(ConfigurationManager.ConnectionStrings["AccountsConnectionString"].ToString()) { }


public IEnumerable<COContractCostCentre> ListContractCostCentres(int contractID)
{
  string query = "SELECT C.ContractID, C.CCCode, MAC.CostCentre, C.Percentage FROM tblCC_Contract_CC C JOIN tblMA_CostCentre MAC ON MAC.CCCode = C.CCCode WHERE C.ContractID = {0}";
  return this.ExecuteQuery<COContractCostCentre>(query, contractID);
}

Then ContractsControlRepository is instantiated in my controller and used like _contractsControlRepository.ListContractCostCentres(2). Connections aren't opened manually, DataContext deals with that for me.

Answer 1

Without knowing the details of your ORM and how it connects the SQL database drivers will connection pool. When a connection is closed it is released back to the pool and kept open for X number of seconds (where X is configurable). If another connection is opened and all the parameters match (the server name, the application name, the database name, the authentication details etc.) then any free, but open connections in the pool will get reused instead of opening a brand new connection.

Having not read the book in question I don't know what "manual linq" actually is. If it's manual means you're getting the tables back youself then obviously you're doing the connection open/close. Linq to SQL will use a new connection object when a statement is finally executed at which point connection pooling comes into play - which means a new connection object may not be an actual new connection.