keeping sql connection open

[lokee]

hello all....
i have one query....
can somebody please tell me advantages and disadvantages of making connection to sql server open at the start of application and closing it at the end of application (in windows application or web application) ?
what will be consequences ?
is it a good practice ?
please comment.

[Shuja Ali]

When you keep a SQL Connection open throughout your Application, you are actually locking a resource on your Database Server.
For example you have Database Server with 5 user license and your application is being used by 10 users, then only 5 users can use application, others will have to wait till someone closes your application.

To overcome this problem, .NET comes with disconnected Database access.. Now there are certain limitations to it, but you could certainly create a work-around for that..

So IMO you should stick to the basics of .NET of using Disconnected Database access..

[jhammer]

I think vb_the_best is right on his answer.
I also think that the answer depends on what you want to do in your application. If you need to access the database frequently, using lots of commands and stored procedures, and so on (lets say you are reaching the database once per second), then in this case keeping the database connection open makes sense. If you need to access the database once per five minutes, then you can close the connection after every command, and reopen it before the next access to the database.

[lokee]

hi...
thanx for the reply,
actually me and my friend is having argument on this...
he says, dot net have disconnected architecture.... so u dont need to every time (in ur functions or subroutine where u executes different sql commands) open or close the connection.
well, what is a good practice ?
what exactly is the meaning of disconnected architecture ?
please comment.

[jhammer]

A disconnected architecture means that you have a copy of the database (or part of it) in the memory - this is stored in the DataSet class. Each time you edit the data - the memory copy only will be changed. henever you want you can update the datasource and commit the changes.
This gives a huge advantage: You can now change the data without storing it to the actual database, and cancel those changes easily (unlike Access forms, or Recordsets, which whenever you changed something in the Recordset, the changes were commit exactly at that instance). It also let you change the data in an order of your choosing (which is not allowed in a database).

What does all this have to do with DataConnection objects? Absolutely nothing. The DataConnection objects is the pipe connecting between the database and the DataAdapter objects. You can open a connection, fill a dataset and close the connection. Then work on the DataSet (in a disconnected fashion), and when you wish to save the changes, open the connection again, update through the dataAdapter, and then close the connection again.

[lokee]

hi...
i'm still waiting for more replies...
i'm not yet satisfied....
please help.

[Shuja Ali]

hi...
i'm still waiting for more replies...
i'm not yet satisfied....
please help.
The name itself explains a lot. Jhammers post is a very good one. Read it once again.

I am gonna add to what jhammer has written.
Disconnected ArchitecturePrior to .NET, developers(although not all of them) used to develop applications that used to be connected to the database/server through out the life time of the application. But now as the number of users using the applications has grown manifolds, .NET came up with an architecture to tackle these problems. So basically what it means is that You don't keep your connection to the database open all the time. You only open it when there is a need for it. This reduces the roundtrips to the server and also increases the scalability of the applications (web/desktop based). This is by default done in .NET.

ADO.NET uses Datasets as an in-memory presentation of the data. You actually modify datasets when you do any operation using ADO.NET. This is then updated to the database when you call Update method on the data-adapter. This has been more clearly stated here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetarchitecture.asp