connection pooling vs session state

  • Thread starter Thread starter roger
  • Start date Start date
R

roger

Using System.Data.OracleClient in .NET 1.1 framework...

If I'm reading the OracleConnection docs right, it indicates
that if I do this:

using (OracleConnection con = new OracleConnection(dsnstring)) {
...
}

then although it appears that I'm destroying the connection
each time, internally it actually implements connection pooling
and maintains a single connection that is established just one time.

Further, that a subsequently created connection will have
in tact all session state that was previously set when the
connection was last disposed.

In other words, the following code uses Oracle's
SYS_CONTEXT machinery to set/retrive session values.
As can be seen, the session values persist from one
instance of a connection to the next:


string dsn = "Data Source=S20;User ID=roger;Password=xxx";

using (OracleConnection con = new OracleConnection(dsn)) {
con.Open();
OracleCommand cmd;
cmd = new OracleCommand("begin context.set_context_val('foo', 1);
end;", con);
cmd.ExecuteNonQuery();
}

using (OracleConnection con = new OracleConnection(dsn)) {
con.Open();
OracleCommand cmd =
new OracleCommand("select sys_context('ctx', 'foo')
from dual", con);
object val = cmd.ExecuteScalar();
Console.WriteLine("value = " + val.ToString());
}


When I do this, I see that the second connection instance still
sees the previously established "foo" context value.

So, my question is this:

Is there any mechanism for managing this sort of thing?

Note that for my application, all connections use the same
User ID in the DSN string. Doing this means that I am able
to keep connections down to a minimal number, owing to the
built in connection pooling. This is nice, since my application
manages potentially hundreds of users, each of which needs
the database connection only occasionally for brief periods.
The pool keeps my total number of connections down to a
relatively small fraction of the total number of users the
application is serving at any point in time.
This is good.

The down side apparently being the loss of ability to maintain
independent session state among the various connections.

If I were to use a distinct User ID for each connection,
then the session state problem would go away, but I'd be
in the less desireable position of having a connection sitting
open for each distinct application user.


I'm curious as to how other people approach this problem.

Thanks.
 
roger said:
Using System.Data.OracleClient in .NET 1.1 framework...

If I'm reading the OracleConnection docs right, it indicates
that if I do this:
Click to expand...
.. . .
The pool keeps my total number of connections down to a
relatively small fraction of the total number of users the
application is serving at any point in time.
This is good.

The down side apparently being the loss of ability to maintain
independent session state among the various connections.

If I were to use a distinct User ID for each connection,
then the session state problem would go away, but I'd be
in the less desireable position of having a connection sitting
open for each distinct application user.


I'm curious as to how other people approach this problem.
Click to expand...

You've got a good understanding of the problem, and there's really not much
of a solution.

Oracle has a bunch of great features (package variables, global temporary
tables, authentication) that support users with long-running sessions. But
in a web environment, it's hard to make much use of them.

For an intranet application, you could just cache the open connection in the
user's ASP.NET session. But I wouldn't try this if you plan on supporting
more than a few hundred simultaneous sessions.

David
 
Back
Top