Geek Answers Handbook

ASP.net Connection Management

How do you manage database connections in your ASP.Net application?

My understanding tells me that the "best" way is to open a connection, make a request, close the connection - and do it several times because pooling makes the cost careless.

The problem arises when I have a DAL where each method searches for its own connection. For example.

  User x = DAL.GetUserDetails ();
 Customer y = DAL.GetCustomer ();

That's fine until we talk about TransactionScope.

  using (TransactionScope t ....
 {
 DAL.UpdateCustomer (y);
 DAL.UpdateUser (x);
 t.Complete ();
 }

ASP.Net wants to use DTC because (I assume) there are several connections.

Someone is going to say "Cache the connection somwhere", but I need to explicitly destroy the connection due to the way I manage security (I execute as / revert), and I do not want you to make a call for each to do this, because that someone will forget to make a call. I could also pass the connection to each method, but this is not ideal, because the page should control the connection.

Do I make sense or have I missed something fundamental here?

+3
source share
2 answers

I read somewhere, and I don’t remember exactly where this Microsoft is going to solve this problem, when you have two connections to the same database that they will not grow into DTC, because of which this problem will disappear.

Until we decide to develop our TransactionScope, our DALs will then ask the TS about the new connection, and when we place the TS, it will close the connection.

Links were stored in a LogicalCallContext, although I would look at using an HTTP context. I left the company before the application went live, but from what I heard, they had no problems.

so you have

using (CustomTS.New()) { CustomerDal.Update() userDal.Update() }

CustomTS has a static method that will receive the current transaction and connection.

+1
source

Embed it in a similar idea on how TransactionContext works.

I started writing how to do this, but I think the example makes it clearer:

 public class MyConnectionManager : IDisposable { [ThreadStatic] // static per thread private static SqlConnection con; public static SqlConnection Connection { get { if (con == null) { con = new SqlConnection(); con.Open(); } return con; } } public void Dispose() { if (con != null) { con.Close(); } } } public class Program { public void Run() { using(new MyConnectionManager()) { MakeCall(); MakeCall(); } // Disposal happens here } public void MakeCall() { // The property can be accessed from anywhere SqlCommand cmd = new SqlCommand("SELECT 1", MyConnectionManager.Connection); } }

This will cause any calls within the scope of the using statement to use the same connection if all of them are in the same thread. You can also use a transaction context.

Warning. In an ASP.NET WebPages script, the flow may change between the connection and page_load. See this article for more details . Also the stream is reused. However, if you are developing a DAL that is shared between applications, you may not be able to use the HttpContext as indicated, as it is not there.

0
source

More articles:


All Articles