What Happen If I Do Not Close Sql Conection In Asp.Net C#?

Question

3.50/5 (2 votes)

See more:

C#

ASP.NET

SQL-Server

what happen if i do not close sql connection in asp.net c#? but still data transaction are working successfully.?

Posted 30-Dec-13 8:45am

utm

Add a Solution

4 solutions

Solution 2

If you open the connection and don't close it, then it would decrease the connection pools and limits available for connecting to database again.

It is always recommended to close the connection and data reader objects explicitly when you use them.

Posted 30-Dec-13 17:52pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Solution 3

If the number of concurrent users increase then perhaps you will run out of connections. I have spent many night fixing this problem in old legacy(inherited code).

Perhaps this article will help you understand the problem and provide some best practices for connection handling: ASP.NET - How To Use(Open/Close) Connections Correctly[^]

Posted 30-Dec-13 18:59pm

Rahul Rajat Singh

Solution 4

You may get an error like this after some executions

"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."

This may have occurred because all pooled connections were in use and max pool size was reached.
(default pool size is 100)

so u must close connections as soon as possible like,

SqlCommand cmd = new SqlCommand(str, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

Posted 31-Dec-13 20:35pm

Praveen_P

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-12-30T09:12:00

Solution 1

Depends. You could run out of connections, you could have difficulty creating a new DataReader - it depends on how the SQL server is configured, and how your code works.

Certainly, if an object implements IDisposable, you should always Dispose the object when you are finished with it - and that applies to SqlConnections, SQLCommands and so forth.

In the case of SqlConnection objects, Dispose calls Close foe you, so it's the easiest way.

Posted 30-Dec-13 9:12am

OriginalGriff

Comments

adriancs 31-Dec-13 3:05am

If I understand correctly, after an ASP.NET page life cycle ends, IIS will release/dispose every object called within the thread, and this subsequently will close and dispose SqlConnection too. But I agree that, we should close the connection as soon as possible.