VB.NET - Desktop App - DB Connections

[Leo J. Hart IV]

Hello,

I come from a mostly web application background, but am currently working on
a desktop app to be designed using VB.NET. The app will have around 15
users; I don't expect that amount to increase significantly.

I'm trying to decide on which approach to use in regards to database
connection handling. Should I open my database connection (to SQL Server)
on application startup and hold it for the user's entire session (meaning
that each user will have 1 dedicated DB connection) or do I go with the
approach I'm more familiar with using on web apps where you open and close
DB connections as you need them.

Most of the guys here are telling me that the first approach is generally
how it's done on client server apps with a small amount of users, but once
again, given my background, I feel a bit uncomfortable with that route.

Any advice from you experience windows programmers out there?

Thanks,
Leo

 

[Cablewizard]

In my opinion it would depend on usage.
If the app is constantly hitting the database, leave the connection open.
If you are just querying out some stuff in the beginning and not going back to
it regularly, I would close it.
Of course if you want to keep the recordset open, then might need to keep it
open.
Connecting and disconnecting can sometimes take a little while (seconds), but if
it has to be re-established often, then this can annoy the users.
Additionally, depending upon your configuration, your server might have a time
out, and after a period of inactivity it might disconnect you anyway, in which
case you might get a run-time error if you think it should be open. Don't see
this too often with SQL, but pops up with Oracle a lot.

I have apps that use both methods.
Opening and keeping open is the "easy" and sometimes lazy way.
But like I said, the "right" choice depends on what you are doing.

Gerald

 

[Leo Hart]

The user will be constantly querying and sending updates to the
database, so it sounds like I SHOULD be using the "1 connection per
user" method, given your response.

It just seems so foreign to me since the general rule of thumb in web
apps is: open the connection as late as possible, close the connection
as quickly as possible.

Another question: wouldn't the time to "open" and "close" connections be
rather minimal if using connection pooling?

Thanks,
Leo

 

[Cablewizard]

In this case, since it is a desktop app and not a web app, then my decision
would be to use the "1 connection per user" method. As long as you have need to
access the connection regularly, keep it open.

This is indeed a different paradigm than web apps.
Since web apps are "stateless" and you have no control over client responses, it
is not safe to attempt to keep connections open. This you know. But with desktop
apps, you have much more control over things, so it is much safer. Just make
sure to close it when you don't need it anymore, and/or when your application
closes.

As far as your question on connection pooling, my needs don't generally deal
with this so I can't provide an experienced reply.

Gerald

 

[Leo Hart]

Thanks for all of your assistance Gerald!

Leo