1

Today I got an exception when I run my application from the below code

dbContext.ManageTasks.Where(x => x.IsDeleted == false).ToList();

Error is

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I got a solution from this discussion : How can I solve a connection pool problem between ASP.NET and SQL Server?

Solution is : if I close current connection string object, then this error will be gone. 

SqlConnection myConnection = new SqlConnection(ConnectionString);
myConnection.Open();
// some code
myConnection.Close();

The above code for ADO.NET, not Entity Framework.

How can I close the connection string object in Entity Framework?

Improve this question
4
  • 2
    with a 'using' block msdn.microsoft.com/en-us/data/jj729737.aspx Commented Apr 21, 2015 at 10:03
  • 2
    You are not "closing the connection string". You are closing the connection to the database. At least that's what you should be doing. Commented Apr 21, 2015 at 10:06
  • XY problem. OP has mistakenly used a long-lived global data context instead of using many short-lived contexts. Don't fix this specific problem. You need to change your architecture so that the context is not a long-lived global. Commented Apr 21, 2015 at 10:16
  • stackoverflow.com/questions/888185/… Commented Apr 21, 2015 at 10:21

2 Answers 2

Reset to default
6

I'm a bit unsure as to why you would need the use of "SqlConnection". DbContext is a disposable class. I found the safest way to clean up is by using the "using" tag. E.g.

using(DbContext _context = new MyContext())
{
     \\Your Work here
}

This will automatically call _context.Dispose() when it reaches the closing curly bracket which in effect closes your connection.

The alternative would be:

DbContext _context = new MyContext();
_context.ManageTasks.Where(x => x.IsDeleted == false).ToList();
_context.Dispose();

This does mean it's possible to miss the dispose call if an exception occurs.

Hope this helps.

Useful Link

In order to handle transaction scope and to help dispose your objects, you may want to look at the repository pattern and the unitOfWork pattern. Or both.

Further to this, I know use DependencyInjection to handle a lot of connections. I just ensure they are Scoped (per web request) :)

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

10 Comments

You are missing the try ... finally part of using.
it's not good for me, because i have created the object globally for context class TimeSheetEntity dbContext = new TimeSheetEntity()
A "try" is not linked in any way to the using statement, granted it can be used but that's a developer decision based on what code is being run inside the using statement.
Ramesh, use the alternative. You can call .Dispose() at any time you like. Then I would re-instantiate dbContext in the constructor.
@RameshRajendran That's your mistake. The context class should not be long-lived. You need to redesign your app so that the context is non-global. Create a context per logical database operation. Don't use a global context.
|
4

You are probably keeping a global variable dbContext somewhere in your class. You should not do that!

You could use the IDisposable interface that DbContext implements and use using statements. You initialize a new DbContext whenever you need one:

using (YourDbContext dbContext = ...)
{
    // do some actions
}

// context will be closed

The benefit of using using is that it will close and dispose, even if the code inside will throw an exception.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.