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

N

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.Items["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.Items["currentConnection"]).C
lose();

((SqlConnection)System.Web.HttpContext.Current.Items["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.Items["currentConnection"]).B
eginTransaction();

try {
ClientObject.Save();

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


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

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

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


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

Nayt Grochowski
 
K

Kathleen Dollard

Nayt,

What problem are you trying to solve? Connections are cached so creating
them is generally not an issue.

--
Kathleen (MVP-VB)



Nayt Grochowski said:
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.Items["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.Items["currentConnection"]).C
lose();
Click to expand...
((SqlConnection)System.Web.HttpContext.Current.Items["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.Items["currentConnection"]).B
eginTransaction();

try {
ClientObject.Save();

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


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

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

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


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

Nayt Grochowski
Click to expand...
 
N

Nayt Grochowski

Its an issue of simplifying how a connection and transaction is used in an
entire system.

Its a pain to create use and close a connection in every object that might
use it on a page, when I could have them all use a common one - since they
are all using the same context and are not threaded...
 
K

Kathleen Dollard

So if they are on the same page, why not have a simple class level variable,
and potentially a property that returns the connection/transaction. I don't
seen the value of putting it in the HTTPcontext. I think that risks a
transaction being carried beyond a logical context for it. I don't see
technical problems with it.Making it global this way would seem to have all
the problems it would have if you provided it as a global variable, either
through a shared method of a class, or via a VB module. If that's what you
want, just use a shared property.
 
Top