Visual Basic General
I open and close the connection on each read/write. Your main overhead is in the initial connection so when you first connect (I asume to sql2005) it may take a few seconds but after that its lightning fast. Also you may want to think about wrapping all the database functionality into a wrapper to simplify your code elsewhere. You can also then make this wrapper threadsafe therefore allowing you to multithread your app safely.
Hope this helps
Mark
It depends - how often will you be accessing the Database
If you are constantly accessing it, then there's little point in closing the connection after each write (that would be like going to the store, buying bread, going home; back to store, buy milk, go home; buy eggs, go to store, go home....).
If it's every few minutes, then you may want to think about closing it (I'd keep the connection open but that's because of the nature of my applications that run 24-7).
Some database managers get their knickers in a twist if you maintain a connection ad-infinitum, however.
(I've only had connection/timeout issues with MS databases when I've held the connection open for more than several hours without doing anything with it - other databases seem to be a bit less tolerant).
A good compromise will be to have your application have its own timeout, so that if no transactions have ocurred in a certain time period (e.g. 1 minute, 10 minutes, 1 hour, whatever is appropriate), disconnect nicely. This is easy to do if you encapsulate your database functionality. Then when a transaction is required, reconnect nicely.
Which ever way you go - correct error handling will make your life much easier.