Results 1 to 6 of 6

Thread: ADO.Net and optimising database connections

  1. #1
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default ADO.Net and optimising database connections

    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....
    Last edited by codecarnage; Jan 26, 2007 at 04:36 PM.

  2. #2
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    theres areally complicated method/class i've been working on... i stopped... might just continue it and post the code. the code itself will be... advanced but the abstraction it will create should free you from maintaining the connection and try/catch/finally block and error messages etc.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  3. #3
    Join Date
    Sep 2004
    Posts
    681
    Rep Power
    0

    Default

    Quote Originally Posted by icymint3 View Post
    ... the abstraction it will create should free you from maintaining the connection and try/catch/finally block and error messages etc.
    Sounds like ground that might have been covered by the Data Access Block portion of Microsoft’s Enterprise Library – there are flavours for both .NET v1.1 and v2 (http://msdn.microsoft.com/practices/...pe/AppBlocks/). It is part of Microsoft’s larger patterns and practices push (http://msdn.microsoft.com/practices/).

  4. #4
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    true... i should just recommend that then.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  5. #5
    Join Date
    May 2003
    Posts
    108
    Rep Power
    0

    Default

    I think you are not closing the connection properly

    Do this instead:


    public function DBConnect(strSQL as String, Connstr as String) as dataset
    dbconn = new sqlconnection(connstr)

    cmd = new sqlcommand(strSQL,dbconn)
    cmd...type and whatever else

    da = new sqldataAdapter(cmd)
    dset = new dataset()
    cmd.connection.open()
    da.fill(dset,"dt")
    dbconn.Close()
    return dset
    end function


    in your code
    'ConnectionString:Get connection string from Web.Config
    Dim dtTable as DataSet = DBConnect(strSQLStatement, ConnectionString)

    This way you dont have to worry about closing your DB connection.
    Your connections will always be closed when you use the DBConnect function.

  6. #6
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    Code:
    public function DBConnect(strSQL as String, Connstr as String) as DataSet
    try
    	dbconn = new sqlconnection(connstr)
    	
    	cmd = new sqlcommand(strSQL,dbconn)
    	cmd...type and whatever else
    	
    	da = new sqldataAdapter(cmd)
    	dset = new dataset()
    
    	cmd.connection.open()
    	da.fill(dset,"dt")
    	
    	return dset
    finally
    if not dbconn is nothing then dbconn.Close()
    end try
    end function
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •