Database Connection and Session State

Discussion in 'ASP .Net' started by =?Utf-8?B?R3JpZ3M=?=, Aug 15, 2005.

  1. Hello,

    I have a web service that reads its web.config file to connect to an Oracle
    database. There are a number of methods in this socalled BACKBONE that
    either send inforomation to or from the database. That works great.

    I have the INTERFACE which is what the user sees. When the page is first
    navigated to, it creates an object of the BACKBONE and calls certain Web
    Methods to get data so I may populate dropdown lists and such.

    That works great too. :)

    However, my DBA wanted me to set an action for each "program" that connects
    to his database. I can create this "action" no problem. The only issue is
    that it happens to create 11 entries/connection to the database. Bascially 1
    for each of the 11 seperate calls I make to the BACKBONE.

    I thought I could just create the connection in the BACKBONE's Global.asax
    file as a session variable and use it when I need to connect to the DB but
    that had no difference. Still 11 connections. I understand that the web
    service simple is being called 11 seperate times.

    My question is, is there any way to keep that web-service open? Or some way
    of at least storing the connection in a session variable that will stay? I
    have to believe I am not the only person creating a Web Service to do all of
    the DB grunt work. Are we all wasting connections and the overhead of
    connections connecting? Am I missing something here?

    Please help,
    Thanks in advance
    --
    Thanx,
    Grigs
     
    =?Utf-8?B?R3JpZ3M=?=, Aug 15, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?R3JpZ3M=?=

    Lucas Tam Guest

    "=?Utf-8?B?R3JpZ3M=?=" <> wrote in
    news::

    > I thought I could just create the connection in the BACKBONE's
    > Global.asax file as a session variable and use it when I need to
    > connect to the DB but that had no difference. Still 11 connections.
    > I understand that the web service simple is being called 11 seperate
    > times.


    Are you storing the db connection as an application variable or session
    variable?

    If you want to have 1 connection that is shared across all instances,
    place your connection in an application variable.


    > Are we all wasting
    > connections and the overhead of connections connecting? Am I missing
    > something here?


    BTW, you're not wasting connections... When you share 1 connection, you
    might end up with queued request (you may have to build the queuing
    mechanism yourself). A connection can only process 1 request at a time,
    so if you have 5 users hitting your web service, users 2-5 might have to
    wait until the connection is freed by user 1 before they can submit a
    query.

    If you'll pulling a subset of data frequency, consider caching the data
    locally either in a shared dataset or in a local database.

    --
    Lucas Tam ()
    Please delete "REMOVE" from the e-mail address when replying.
    http://members.ebay.com/aboutme/coolspot18/
     
    Lucas Tam, Aug 15, 2005
    #2
    1. Advertising

  3. > > I thought I could just create the connection in the BACKBONE's
    > > Global.asax file as a session variable and use it when I need to
    > > connect to the DB but that had no difference. Still 11 connections.
    > > I understand that the web service simple is being called 11 seperate
    > > times.

    >
    > Are you storing the db connection as an application variable or session variable?


    Right now it is a SESSION variable created in the Session_Start. To test
    it, I had it send me an email each time a "session" was started. I received
    11 emails, one for each of the tasks I asked for. Even though they really
    came from the same single object on the INTERFACE side.

    > If you want to have 1 connection that is shared across all instances,
    > place your connection in an application variable.


    Not sure how good a single connection for EVERYONE would be. Is there a way
    to have it per SESSION? Some switch, attribute, variable that I am missing?

    I tried SQL Server State and that failed because of serialization.
    I tried creating the object in the INTERFACE and passing it back and that
    was a serialization issue as well.

    Is it difficult to write a serialize/deserialize method/class/object? I
    have seen some code on this but am not 100% sure about it.


    >
    > > Are we all wasting
    > > connections and the overhead of connections connecting? Am I missing
    > > something here?

    >
    > BTW, you're not wasting connections... When you share 1 connection, you
    > might end up with queued request (you may have to build the queuing
    > mechanism yourself). A connection can only process 1 request at a time,
    > so if you have 5 users hitting your web service, users 2-5 might have to
    > wait until the connection is freed by user 1 before they can submit a
    > query.
    >
    > If you'll pulling a subset of data frequency, consider caching the data
    > locally either in a shared dataset or in a local database.


    Thank you for your help.

    >
    > --
    > Lucas Tam ()
    > Please delete "REMOVE" from the e-mail address when replying.
    > http://members.ebay.com/aboutme/coolspot18/
    >
     
    =?Utf-8?B?R3JpZ3M=?=, Aug 15, 2005
    #3
  4. Never, never do this

    It is extremely bad practice and could lead to serious problems
    with performance of your application when you put it into
    production. This applys to static objects, application objects,
    and/or session objects.

    Best practice says you open a connection, perform your task,
    and close your connection as soon as possible. Check with
    your DBA to see if some sort of connection pooling is available
    with the Oracle .NET Providers (the sql server provider offers
    this but I'm not certain whether Oracle implements it or not).

    --
    Robbe Morris - 2004/2005 Microsoft MVP C#

    Earn money answering .NET Framework
    messageboard posts at EggHeadCafe.com.
    http://www.eggheadcafe.com/forums/merit.asp



    "Grigs" <> wrote in message
    news:...
    >> > I thought I could just create the connection in the BACKBONE's
    >> > Global.asax file as a session variable and use it when I need to
    >> > connect to the DB but that had no difference. Still 11 connections.
    >> > I understand that the web service simple is being called 11 seperate
    >> > times.

    >>
    >> Are you storing the db connection as an application variable or session
    >> variable?

    >
    > Right now it is a SESSION variable created in the Session_Start. To test
    > it, I had it send me an email each time a "session" was started. I
    > received
    > 11 emails, one for each of the tasks I asked for. Even though they really
    > came from the same single object on the INTERFACE side.
    >
    >> If you want to have 1 connection that is shared across all instances,
    >> place your connection in an application variable.

    >
    > Not sure how good a single connection for EVERYONE would be. Is there a
    > way
    > to have it per SESSION? Some switch, attribute, variable that I am
    > missing?
    >
    > I tried SQL Server State and that failed because of serialization.
    > I tried creating the object in the INTERFACE and passing it back and that
    > was a serialization issue as well.
    >
    > Is it difficult to write a serialize/deserialize method/class/object? I
    > have seen some code on this but am not 100% sure about it.
    >
    >
    >>
    >> > Are we all wasting
    >> > connections and the overhead of connections connecting? Am I missing
    >> > something here?

    >>
    >> BTW, you're not wasting connections... When you share 1 connection, you
    >> might end up with queued request (you may have to build the queuing
    >> mechanism yourself). A connection can only process 1 request at a time,
    >> so if you have 5 users hitting your web service, users 2-5 might have to
    >> wait until the connection is freed by user 1 before they can submit a
    >> query.
    >>
    >> If you'll pulling a subset of data frequency, consider caching the data
    >> locally either in a shared dataset or in a local database.

    >
    > Thank you for your help.
    >
    >>
    >> --
    >> Lucas Tam ()
    >> Please delete "REMOVE" from the e-mail address when replying.
    >> http://members.ebay.com/aboutme/coolspot18/
    >>
     
    Robbe Morris [C# MVP], Aug 16, 2005
    #4
    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. shamanthakamani
    Replies:
    1
    Views:
    3,535
    Natty Gur
    Nov 20, 2003
  2. Not Liking Dot Net Today
    Replies:
    0
    Views:
    664
    Not Liking Dot Net Today
    Apr 21, 2004
  3. jnickfl1
    Replies:
    0
    Views:
    621
    jnickfl1
    Sep 18, 2006
  4. archana
    Replies:
    0
    Views:
    363
    archana
    Mar 13, 2007
  5. Grigs

    Database Connection and Session State

    Grigs, Aug 15, 2005, in forum: ASP .Net Web Services
    Replies:
    2
    Views:
    146
    oraclevsmicrosoft
    Aug 30, 2005
Loading...

Share This Page