CODEWITHSUNDEEP
.netlinq-to-sqlsqlconnection
SharpAffair
SharpAffair

Reputation: 5478

Linq to SQL in ASP.NET - keep DB connections open

I know that it's not a good practice to keep a DataContext alive and re-use it, so I wrapped it in a Using statement.

Also, as far as I know, DataContext opens a connection when it's being initialized, and closes the connection when it's disposed.

What I'm looking to accomplish here, is keeping a separate connection open for each currently logged in user (and close the connection manually when the user logs out).

Multithreading is not an issue, because I'm using locks.

I keep SQLConnections and pass them to the DataContext constructor. This sort of works. But the problem is that DataContext automatically closes the connection when it's disposed.

Is it somehow possible to force the DataContext to not close the connection it uses?

Upvotes: 1

Views: 1300

Answers (1)

Robert Harvey
Robert Harvey

Reputation: 180788

In general, your pain point will occur if you try and execute some sort of loop through records, and you create a DataContext (and, by extension, a SQL Connection) each time. As long as you don't do that, you shouldn't have any major performance problems.

The notion of holding a SQL Connection open so that you can pass it to a DataContext is premature optimization, in my opinion. SQL Connections are not that expensive, and neither are DataContext objects.

In general, the lifetime of your DataContext object should be the same as the lifetime of your associated Repository object.

Example

public class CustomerRepository
{
    private MyDatabaseDataContext dataContext;

    public CustomerRepository()
    {
        dataContext = new MyDatabaseDataContext();
    }

    public Customer GetCustomer(int id)
    {
        return dataContext.Customers.FirstOrDefault(x => x.id == id);
    }

    public IQueryable<Customer> CustomersByRep(int repID)
    {
        return dataContext.Customers.Where(x => x.repID == repID);
    }
}

See Also
ASP.NET MVC Tip #34 – Dispose of Your DataContext (or Don’t)

Upvotes: 2

Related Questions