Typed connection questions

Discussion in 'ASP .Net' started by pmclinn, Oct 20, 2007.

  1. pmclinn

    pmclinn Guest

    When I'm connecting to Oracle or SQL I always wonder what is the best
    way to close/dispose of a connection. I've been toying around with
    creating a public shared class that has a connection string and then
    calling on the shared class when needed. (See raw examples below.)
    If two of my users open up two different pages at the same time will
    this still work. And if so should I pre-test to see if the connection
    state is already open using a myconnection.state = open then or if
    myconnection.state = closed then.... In oracle I have seen that with
    the old 1.1 framework we had connection leaks and this drove me
    crazy. So what is the best way to handle multiple pages requesting
    different sql commands at the same time?

    EG:
    Public Class clsData
    Public Shared MyConnection As New
    SqlConnection("server=MyServer;uid=;pwd=;database=;pooling=true")
    End Class

    And then referencing the class when needed:
    such as:

    MyConnection.Open()
    Response.Write(MyConnection.State.ToString)
    MyConnection.Close()
    MyConnection.Open()
    Response.Write(MyConnection.State.ToString & "2")
    MyConnection.Close()
    or
    try
    MyConnection.Open()
    Response.Write(MyConnection.State.ToString)
    catch
    'err handling
    finally
    MyConnection.Close()
    end try
    pmclinn, Oct 20, 2007
    #1
    1. Advertising

  2. Using a single connection is not thread safe. Create a static method
    that creates a new connection each time it's called. With connection
    pooling you won't see a degredation in performance and it'll be thread
    safe.

    Also make sure you always close the connection within a finally block
    to prevent connection leaks (c# has a "using" statement for this, VB
    2005 has something equivalent but I'm not sure if the keyword is the
    same or not).

    HTH,

    Sam

    ------------------------------------------------------------
    We're hiring! B-Line Medical is seeking .NET
    Developers for exciting positions in medical product
    development in MD/DC. Work with a variety of technologies
    in a relaxed team environment. See ads on Dice.com.


    On Sat, 20 Oct 2007 01:10:34 -0000, pmclinn <> wrote:

    >When I'm connecting to Oracle or SQL I always wonder what is the best
    >way to close/dispose of a connection. I've been toying around with
    >creating a public shared class that has a connection string and then
    >calling on the shared class when needed. (See raw examples below.)
    >If two of my users open up two different pages at the same time will
    >this still work. And if so should I pre-test to see if the connection
    >state is already open using a myconnection.state = open then or if
    >myconnection.state = closed then.... In oracle I have seen that with
    >the old 1.1 framework we had connection leaks and this drove me
    >crazy. So what is the best way to handle multiple pages requesting
    >different sql commands at the same time?
    >
    >EG:
    >Public Class clsData
    > Public Shared MyConnection As New
    >SqlConnection("server=MyServer;uid=;pwd=;database=;pooling=true")
    >End Class
    >
    >And then referencing the class when needed:
    >such as:
    >
    > MyConnection.Open()
    > Response.Write(MyConnection.State.ToString)
    > MyConnection.Close()
    > MyConnection.Open()
    > Response.Write(MyConnection.State.ToString & "2")
    > MyConnection.Close()
    >or
    >try
    > MyConnection.Open()
    > Response.Write(MyConnection.State.ToString)
    >catch
    >'err handling
    >finally
    > MyConnection.Close()
    >end try
    Samuel R. Neff, Oct 20, 2007
    #2
    1. Advertising

  3. pmclinn

    pmclinn Guest

    Interesting enough, this was original code implementation, but I'm
    still having simultaneous connection errors using this format (in 2.0
    framework) with this format. 'system.data.oracleclient' must still be
    buggy. I saw a hot fix for 1.1 but nothing for 2.0. Maybe I'll change
    over to oracle's library and see if this fixes the issue. Thanks.
    pmclinn, Oct 20, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ersin Gençtürk
    Replies:
    1
    Views:
    3,480
    Ersin Gençtürk
    Oct 6, 2004
  2. =?Utf-8?B?VGVuRG90?=
    Replies:
    1
    Views:
    4,663
    =?Utf-8?B?VGVuRG90?=
    May 15, 2006
  3. Replies:
    0
    Views:
    844
  4. Max2006
    Replies:
    4
    Views:
    457
    Max2006
    Jul 17, 2007
  5. Yingjie Lan
    Replies:
    4
    Views:
    300
    John Nagle
    Jan 29, 2010
Loading...

Share This Page