0

Could I store a SqlConnection in the HttpContext? I mean, how can I share a single SqlConnection across multiple classes?

In my project every class inherits a base abstract class that open and close connection. This class recognizes if a SqlConnection was opened by another class in that case uses it.

I can store this connection in another way that isn't HttpContext? There is another method to do this, exp. pass the connection between layers?

THX

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2336491
  • 47
  • 1
  • 2
  • 4
  • 8
    Why do you want to do this? Do you share transaction context? Why can't you simply open and close the connection when you need it, and not share it? – Lasse V. Karlsen Aug 06 '13 at 09:31
  • You probably *could* - but really: why would you want to? What benefit to do you expect to get from storing the `SqlConenction` into `HttpContext` ?? I would argue you should always open your `SqlConnection` only when needed - using a `using (SqlConnection conn = new SqlConnection(....)) { ... }` approach. – marc_s Aug 06 '13 at 09:35

2 Answers2

0

I wonder why you need to store a single SqlConnection. It doesn't smell great.

If you really do need to share a single SqlConnection across multiple classes, dependency injection is likely a better option. Have a connection factory instantiate a connection object and pass it around as required.

Otherwise, let the DBMS worry about controlling your connection resources. Create, open and close a connection each time you need one.

Tom Tom
  • 423
  • 4
  • 9
  • OK, store connection isn't great. The solution for you is to pass the connection object between classes, right? – user2336491 Aug 06 '13 at 10:03
0

You are going wrong way. You shouldn't follow this thinking unless you need to share transaction context as mentioned by Lasse V. Karlsen mentioned in his comment. If you are worried about performance and thi is the reason why you want to keep one connection open and shared then it also wrong.

In case of e.g. ADO.NET you have connection pooling. This means that even though you call close on connection it is not closed, it is returned to the pooler. This is a mechanism that keeps track of connections and maximizes efficiency by managing them. If you call Open to get a new connection then under the hood you may get an existing one that was used a few minutes before and that was still kept open by the pooler and returned back for reuse.

So, if the motivating force is efficiency, then it is not a path you should follow. It is already taken care of on a lower level. Refer to http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx .

Tobiasz
  • 1,059
  • 1
  • 12
  • 29
  • I have not explained well. I know exactly how Application Pool works. The problem isn't this. I'd like to use one connection for the performance. If I keep the connection opened the elaboration duration is shorter then in the other case (use a connection from each class) – user2336491 Aug 06 '13 at 09:55