Database Connection and Session State

[Guest]

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

 

[Lucas Tam]

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.

 

[Guest]

I thought I could just create the connection in the BACKBONE's

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.

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.

 

[Robbe Morris [C# MVP]]

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