How to guarantee closing SqlConnection on app exit

i have desktop winforms app keeps sqlconnection open while it's running.  sqlconnection member of global singleton object.  rest of app accesses global connection through object.  tried close() connection in destructor of global object, fails.  tried adding idisposable class, , added close dispose method, dispose not called automatically.  keep app modular , have global object take care of closing connection when it's released, don't know of way.

even if violate modularity , have app explicitly call dispose on object, isn't clear how accomplish this.  added handler applicationexit in main() startup code, event raised upon calling application.run open main form.  dispose method of main form won't work either, main form won't opened if there startup problems.

can suggest strategy guaranteeing connection closed, while still preserving code modularity?  maybe winforms question, not ado -- if please advise.

hello,

if want have persistent connection, may use property pooling = false ( avoids problem of opening connection expensive operation in terms of ressources ).but have careful fact connection must closed ( sqlconnection.close() ) before end of program or have orphaned connections can dropped t_sql kill or when sql server instance restarted.

i suggest use sqlconnectionstringbuilder class, jewel little-known unluckily create connection string, able use intellisense set properties of sqlconnectionstringbuilder.

http://msdn.microsoft.com/en-us/library/dce36088(v=vs.90).aspx

in examples in vc#/vb, see 2 ways of use of properties :

- 1 password/asynchronousprocessing properties ( can use intellisense )

- bad 1 server/connect timeout using name of fields of connection string ( must avoid errors in these names , these errors detected when open() method  of sqlconnection called )

to create p_sqlconn sqlconnection object p_scsb sqlconnectionstringbuilder object :

vc# : p_sqlconn = new sqlconnection(p_scsb.connectionstring);

the big problem know put close() method.i put in method treating form_closing event of 1st form of application, sure close() never forgotten.

in vc#, using barbarious method : using main() method of application ( in vb, more difficult vb masking main() method )

       static void main(string[] args)       {          if ( !commoncls.appinit("en-us") )          {            messagebox.show(commoncls.errormessage,commoncls.shortmessage);            goto endofprogram;          }          form mainfrm = new mainform();       endofprogram:          if ( !commoncls.appclose() )          {            messagebox.show(commoncls.errormessage, commoncls.shortmessage);          }       }    

i create connection in appinit() method of commoncls class ( static method private reasons ) , close connection in appclose() ( check static sqlconnection object valid object , if true call close() method of sqlconnection ). in 5 years, have problem twice ( crash on computer when connected sql server instance installed on computer vista home , second time , killed ( error ) the process managing sqlconnection ).so twice, got orphaned sql server process must kill.or wait night shutting down computers every night. 

my way working , of course, not one.

remark : methods ( forms treatement ) including try/catch block .in try block, put code. have 2 or more catch blocks , 1 each type of exception may encounter, call method specific type exception, (generic) system.exception, sqlexception,smoexception,....

this post start thread , , in way, compulsory guide.so replyers disagree my own way welcome.

don't hesitate post again more or explanations

have nice day

 

---

mark post helpful if provides help.otherwise,leave is.

SQL Server  >  SQL Server Data Access