What are proper sql connection techniques

[chris86t]

I am writing a program to keep track of inventory. I am using vb.net express with mysql. Every place I need to query the DB I setup a new connection, fill a new dataset, do what I need to do with the data, and close the connection. I know I should probably put the connection into it's own function(right now I repeat the same lines about 5 times throughout theh program) Do I need to open and close the connection for every query, or should I just connect at program startup, and close at exit?

 

[chris86t]

I did a search but didn't find the information. Then after submitting the post, I see a post 2 below this one that kinda answers it. Any other suggestions would be helpful.

 

[Foada]

There is not an exact answer to this question as it depends on a number of things such as the number of users, the concurrency of the data, etc. I am not sure what you have searched for but a search for disconnected data sets vs connected data sets may answer some more questions. You may also want to search for typed and untyped datasets. There are pros and cons to keeping a connection open vs opening and closing it. The business needs of the application are going to determine the best approach. I realize that this is a vague answer but the truth is that it depends on your apps needs.

If you choose to battle wits with the witless be prepared to lose.