One long connection or multiple short connections to the database?

I'm currently writing a Windows Service that will run on a Windows Server 2008. My colleague and I 开发者_如何学编程were discussing on one point particular. The connection to the database.

We both have a different way in mind and we would like to know your opinions on it. Basicly the service starts a thread that sends a query to the database to check on rows that have a certain status (for example ST005). All rows with that status will be returned. The data we receive will be processed and the rows will be updated in the end.

So basicly we once execute a query and then for each row we execute an update. Multiple threads can be running at the same time. There is no problem with the coding, but it's the structure we don't really seem to agree on.

The classes we have are a controller, a DAO and a database class.

Way #1:

The controller creates a DAO class to process a query. That DAO class builds the sql statement with its parameters and then creates a database class which opens the connection, executes the query, returns the result set and then closes the connection.

This way there will be a new connection each time a query or update is requested.

Way #2:

The controller creates a database class (the database class is now provided with two new methods, connect() and disconnect(). Then the controller calls upon the connect() statement and creates a DAO class and provides the database class as parameter for the constructor of the DB class. The DAO class builds the sql statement with its parameters and then processes the data.

This way there is only one database class during the whole lifetime of the thread. During the whole thread the same connection is opened and only closed near the end of the threads lifetime.

What way is the best to use here? It seems like having multiple connections seems to be a bad practise, or are we wrong here? Any insight on this will be appreciated.

Regards, Floris

Use a connection pool, as almost certainly is provided by your DBMS vendor, and let it figure out the best strategy. Ends the discussion.