Why do we have to close and re-open the connection in some cases?

Question

0.00/5 (No votes)

See more:

I am using the classe SqlCommand, SqlConnection, SqlDataAdapter, SqlDataReader, and in some cases, I have to close the connection first and re-open it before executing some code with the connection. I thought that once we open a connection, we can use it for any operations on that connection at any time we want, and the only time the connection should be closed is when the user closes the application.
Could you explain more to me or refer me to some external helpful article about this problem?
Thank you very much!

Posted 23-Oct-11 8:32am

King Boy

Add a Solution

Comments

André Kraak 23-Oct-11 15:20pm

Would you please post a code sample where it is necessary to close the connection?

If you wish to change your question use the Improve Question button.

[no name] 24-Oct-11 3:05am

Thank you, in fact it threw an exception at the command of updating the database like this adapter.Update(datatable) and it said some thing like that the connection should be closed first. At that time, I didn't really need to know why but inserted a pair of closing and re-openning my connection right at before the command of update, and it worked. I'm sorry but the problem may be at somewhere in one of dozens of functions of mine (not in the function the exception thrown) and that's why I can't post any code here. I hope you can imagine (with your experience) what the problem can be in my case.
Thank you!

3 solutions

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)

RaviRanjanKr · Accepted Answer · 2011-10-23T10:46:00

The SqlConnection has a connection pool when you call Open() and Close() you aren't actually opening and closing the physical connection to the server. You are just adding / removing the connection from a pool of available connections. For this reason it is a good and best practice to open the connection as late as possible and close the connection as early as possible after executing your command.
Mainly programmers believe in open late and close early. This is only a problem if the latency for opening and closing the connection each time causes the entire application to slow down.
Reference Link :- Link1-[ When should I open and close a connection to SQL Server ][^]

Further Reference :- MSDN-[ SQL Server Connection Pooling (ADO.NET) ][^]
MSDN-[SqlConnection.Open Method][^]
from Reference :- If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close.

I.explore.code · Accepted Answer · 2011-10-23T11:29:00

Generally, opening and closing connections is something I would do on a need basis. So imagine a method in the data layer like this:

C#

private List<employee> GetEmployees()
{
   //create any local variables and non-db related code
   OpenSQLConnection();
   //populate my data list
   CloseConnection(); // close it as soon as i m done with db related work
   //return the list and do any other non-db bits
}</employee>

So open connections as late as possible and close it ASAP, keeping persistent connections open is not advised especially in case of web apps coz sooner or later the server will hit the max capacity of number of connections and will start rejecting connections at one point. So rapidly opening and closing connections would mean there is a steady availability of connections in the pool.

People also prefer to do a try..catch()..finally{} block and close connections in the finally block, I would do that only if there is no other non-db related code in my try block after i have used the connection. Because otherwise you would be unnecessarily and unwittingly keeping the connection open until you hit the finally block. Just my views.

Cheers.

I.explore.code · Accepted Answer · 2011-10-24T03:19:00

thought to post it in the solution section: if you are calling some code in between that has to work with db connections, then yes there is a possibility of an exception. I remember doing something like that, where I was calling a function from within a function, and both needed to access the database but the called function after it was done closed the connection which resulted in some exception in the calling function due to the connection having been closed. I fixed it by creating another function to reset the connection to a valid state. It was years ago and wasn't probably the best practice coz you shouldn't really have to call any other db related function within the data layer. So, yes there are pros and cons of this approach as well, but 9 out of 10 times this approach works well. So just make sure the connection is not being set/reset by some other code which results in exception, try debugging your code to see where exactly it fails. Keep the connection state on Watch and give it a go...

Mostly an already open DataReader can be the culprit.

hope this helps

Why do we have to close and re-open the connection in some cases?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0