best way for open database connection - discussion

Question

hi

what is the best way for open connection to database ?

1. open in form1 - and close and open again in form2 ?

2. open in form1 as static - and use it for all form's (stay open all the time) ?

3. after I clossing the connection is it good to do - Connection.dispose() ?

I work on C#, and connect to sqlCE, Windows mobile 2005

thank's in addvance

Answer 1

Do you have any connection pooling capability?

The idea being that opening a database connection is expensive compared to a single query. Hence if you can keep the connection open it may pay off. So each "form" or other piece of code goes

pool give me a connection
work with connection
pool I am done with that connection now

and the pool is repsonsible for actually open and closing (and all otehr housekeeping)

Some environments have database drivers which hide all this pooling, the app thinks its just opening and closing connections, but under the covers the pool is working. Always prefer to use such facilities if they exist. Writing your own connection pooling is more work than it appears - a lot more work!

Answer 2

Yeah creating your own pooling is quite difficult unless you are an experienced framework developer.

It is important to call the dispose() method, or use the 'using' statement (does the same thing).

If you leave a connection open, then next time it will not be available until it times out.