Connections: opened and closed

[viperbyte]

Hello everyone. I have a Visual Studio 2008-SQL Server 2005 app that will be throwing alot at it. It's my testbed learning app. I'm also a student of this craft(43 years young) like another poster posted on another thread. I come from a PICK/BASIC jBase background. Anyway as-is my app has a connection to the database that is opened on form load. I close the connection on form close. It's a one form app currently. Is this wrong? It seems that maybe I should not have a connection opened possibly all day. I'm looking into incorporating Datasets, dataAdapters into my next phase of evolution. I looked at the material and it's tough. For me anyway. But I will get into it. I will fetch the data into a dataset so that I can keep the connection open only for a little while. I was turned on to this "keep the connection just as long as you need it" idea from something I read recently. But what I would like to know from the veterans; is it so wrong to keep a connection open all day or even for minutes at a time. After all it's only ONE connection per user/client application instance.

[sotoasty]

That's a good question. And I will answer like this. It depends. If you have 1 app with 10,20,50 people using it on a nice database server, then I would say probably not. But what happens if now you have 1000 users? Or you have to start opening connections to other databases. I have learned from experience, that you can't always plan in advance what may happen with your app.

There are a few other things to consider. What happens if your app looses it's connection to the DB Server? Do you check the connection state before running queries? How good is the DB Server? Remember, each connection will use up resources on the server. How often will queries be run against the server with the open connection? If the answer is almost continuously then keep the connection open. If the answer is once every 1/2/5 minutes, then I would say close the connection. The time it takes to initiate the connection is minuscule.

Good luck in you DB endeavor.

[Cimperiali]

the net model is a disconnected model: as a general rule (exceptions are possible, of course) you should never keep conection opened. You should look for "optimistic concurrency" to see how to deal with concurrent users modifying same disconnected data.

[viperbyte]

Ok folks. Thanks for the input. Looks like I'll have to take the disconnected approach even though it looks painful. But it seems that anything in programming that is truly useful or correct is of advanced stuff anyway. And advanced is relative i know. Hey Sotoasty since I see your in Florida; guess what? We had frost on our windshields a couple of months ago when that really cold front came through. I'm in Miami. The last time i saw frost on the windshields in Miami was back in '77 when I was a kid. Back then it was common to see frost on the windshields and on the lawn every once in a while on a cold winter's morning. It was a very rare event.

[viperbyte]

Howdy. Now that I’m digging into datasets I'm seeing that when it comes to reading, inserting, updating and deleting; the dataAdapter handles all the select statements on its own when submit changes to database is coded/executed. From what I can tell with this happening my stored procedures that I wrote that do read, insert, update and delete aren't required. I have specs for an app that insists on stored procedures. No ad-hoc SQL allowed. But most importantly is that Stored Procedures are the mandatory means of interacting with the database. But what if I wanted to use datasets so that I can keep a mini-database at the client for the user to work with and submit the changes whenever the user was finished with the task at hand? Can I do that dataset stuff in tandem with Stored Procedures? From what I can tell the answer is 'No'. It looks like the DataAdapter is going to do what it wants to do and can't be coded to use my Stored Procedures. Is that correct? If that's correct then I can give the dataset stuff a rest and or do it on a test program. But if it is possible and correct to do the DataSet stuff with my Stored Procedures then I have much to figure out and need some help with that if I can get it.

[Cimperiali]

You can.
http://www.codeguru.com/vb/gen/vb_database/storedprocedures/article.php/c5155/

[viperbyte]

I read the article. It's helpful and a good read. Thanks.