.NET Question: Any problems with loading a SqlConnection into the current HttpContext?

[Nayt Grochowski]

Does anyone see any problem with the loading a SqlConnection into the
System.Web.HttpContextCurrent.Items collection in a Page's Constructor. Then
Closing and Disposing of it the OnUnload method?

Connection would not be "Opened" until it was actually used (this is handled
by a common "Helper" class - similar to Microsoft's SqlHelper Application
Block), Ie:

public class MyPage : Page {
public MyPage() {
System.Web.HttpContext.Current.Items["currentConnection"] = new
SqlConnection();

((SqlConnection)System.Web.HttpContext.Current.Ite ms["currentConnection"]).C
onnectionString = "connection_string_here";
}

... Object calls here to SELECT/INSERT/UPDATE data using the common
connection

override protected void OnUnload(EventArgs args) {

((SqlConnection)System.Web.HttpContext.Current.Ite ms["currentConnection"]).C
lose();

((SqlConnection)System.Web.HttpContext.Current.Ite ms["currentConnection"]).D
ispose();

base.OnUnload(args);
}
}

This could allow other objects to use this connection and would greatly
simplify connection usage.


Could also use the same exact concept for a Transaction that is used in
multiple objects that know nothing about each other, ie:
(each Save method below uses the Connection and Transaction objects stored
in System.Web.HttpContext.Current.Items)

public SaveData() {
System.Web.HttpContext.Current.Items["currentTransaction"]
=
((SqlConnection)System.Web.HttpContext.Current.Ite ms["currentConnection"]).B
eginTransaction();

try {
ClientObject.Save();

AddressObject.Save(ClientObject.ID);
PhoneObject.Save(ClientObject.ID);


((SqlTransaction)System.Web.HttpContext.Current.It ems["currentTransaction"].
Transaction).Commit();
} catch (Exception exp) {

((SqlTransaction)System.Web.HttpContext.Current.It ems["currentTransaction"].
Transaction).Rollback();
throw(exp);
} finally {

((SqlTransaction)System.Web.HttpContext.Current.It ems["currentTransaction"].
Transaction).Dispose();
}
}


Does anyone see any major performance issues or other potential problems
with this? Thanks for any input!

Nayt Grochowski

[Nayt Grochowski]

a Shared (or Static in C#) property is not the way to go - I don't a single
connection shared between everyone, that will create a bottle-neck.
And if I share a Transaction that will blow everything up too. Only way
around it is to have the object availble to the single thread that is
currently running.

And your not actually putting the object in the Context - I belive its just
a memory pointer that is stored there... not a big deal I would think. But
that is what I am curious to find out from the community.

And its not always a matter of create and closing again and again - its also
sharing a Transaction between all of the objects that may potentially use
it.

Thank you for the thought - but I need something more...


"Mamcx" <thismail@notexist.com> wrote in message
news:%23glBfe5VDHA.1984@TK2MSFTNGP11.phx.gbl...

> Is bad design put heavy objects inside any thing that act like a sesion in

a

> web server..if you want to share the conexion exist a easy way, create a
> empty module or class and put a shared property:
>
> Public Shared
>
> Public Shared Property or function sqlCon:SqlConnection
> get
>
> In this way, you can use a clear code:
>
> THelper.sqlCon......
>
> get the idea?
>
> Anyway, creating and closing a sql connection not cause a notorio
> degradation in performance...in some ways can improve that
>
>