hsc wrote: which option is better
create one (global) OleDb connection and use it while the program is running
create a new connection for each SQL statement?
It is choice I believe, but my preference is one connection that is opened and closed as required. I will create a connection per database. I have various systems that need to connect to various databases for independent systems and create statistical reports based on joined data.
I have a SetupDictionary (singleton) that keeps all my "global" settings and it has a Collection<strDbName, oConnection> that I can call when needed.
George, South Africa
Connections are pooled (reused) in .net which means that you don't have to treat them as a precious resource. I would create one connection per data access object (DAO) that the application uses. That means that the number of connections depends on how you have designed your data access. My suggestions is that you make your DAO-objects small so that they only handle one type of data (not necessarily one table) and that they only live while the window and the Business Classes (BC) that uses the data are alive. This design will not limit you in future development like going multi threaded or changing data access technology.