Best way to manage connection object used to access to a database in a windows application project developing by c#?

Question

Hi

What is the best way to manage Connection to access to a database while developing a windows application using c#? I mean having a project-wide connection object variable that opens by application start and closes and disposes by application end OR within every procedure (for example select, insert, update,...) that uses a connection to db, we declare a connection object, open it, use it and by the end of the procedure, we close and dispose it?

In summary, how to manage connection object within our applications?

Answer 1

Project-wide connection works fine for a Windows application. Some databases (MySQL specifically) limit idle connection time, so you might need to handle server-side disconnection on timeout.

With connection pooling in place, the "one-connection-per-request" model will work, too, with rather small client-side overhead and no server-side overhead. But that's utterly pointless. This model was invented for high-concurrency environment of a Web server; in a Windows app, there's no concurrency to speak of.

EDIT: in a threaded application, one connection per thread will suffice. Sharing connections between threads is a bad idea, and in general does not work.

Answer 2

I think if its for a smaller number of users probably best to open the connection at start of application, and close it at the end of the application.

The connection pool has very little if any bearing on a Windows Forms application. The pool is created on the individual clients--not on the server.