Ok guys....here's a question which i've been asking myself for the past 2 months or so...
I've been using asp.net for about 3 yrs now...rather successfully...and since recently i've been questioning the way i code my database related functions. Specifically managing sql connections
Currently i have a DOA class that has something like this
dim dbconn as sqlconnection
public function conect() as sqlconnection
dbconn = new sqlconnection(connstr)
return dbconn
end function
now somewhere in my app..i would reference the connection as follows
private function dosomethingwithdb()
cmd = new sqlcommand("query",connect())
cmd...type and whatever else
da = new sqldataAdapter(cmd)
dset = new dataset()
cmd.connection.open()
da.fill(dset,"dt")
if cmd.connection.state <> ConnectionState.Closed
cmd.connection.close()
end if
End function
Now notice i have one public method which reference when needed. and i left out the [try catch finally] block to save time.
Since recently i had a problem with a max connection pool error. which leads to rebooting the box.
No has anybody had this problem. Also can you all share with me -and TJ - what you believe to be the best way to use/reference a connection..
...And i know what most resource sites are saying and i know the open connection late and close early rhetoric... so u don't have to waste time digging things up on the net...just to answer this post...i want what works for u based on person experience...(large scale/widely used apps)
My observation is that resources on the net will show a code block where they create a new connection object with the string and everything just before they make the database calls..but doesn't seem clean but it actually works for u...please let me know....and explain how it differs from a centrally located connection....
Please post replies...we all wanna learn and get better....