1

I am developing a Web Application using ASP.NET 4/VB.NET with web forms and using MS SQL Server 2012 Express Advanced Edition as back end.

My application is huge and there are so many lines of code which contains SqlDataReaders and SqlConnection objects in huge numbers(hundreds of).

I took precautions to use Using statement and Try.. Catch.. Finally statements everywhere so that DataReader or Connection object is closed for sure.

Still, is there a way I can check from thousands of line of code, which DataReader or Connection I forgot to close?

leppie
  • 115,091
  • 17
  • 196
  • 297
Hemal
  • 3,682
  • 1
  • 23
  • 54
  • 1
    You could make your own `DbDataReader` and `DbConnection` classes that would track this for you, but it's a bit annoying (ADO.NET isn't exactly designed for composition). Another option would be to enable tracing and write a simple program that would go through the trace logs to check if there's as many Closes as Opens. – Luaan Dec 17 '15 at 08:29
  • 1
    I have some vague notion of using SQL Server Management Studio to `exec sp_who2` to find whatever is using the database, then refer to [Get Last Running Query Based on SPID](http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/) to see the query. That query could lead you to the part of your code you're trying to find. – Andrew Morton Dec 17 '15 at 09:11
  • @Luaan Can you provide some example with this? – Hemal Dec 17 '15 at 10:48
  • @AndrewMorton Can you also provide me some demo code? – Hemal Dec 17 '15 at 10:48
  • 2
    if you were `using` each SqlDataReader and SqlConnection, then nothing should remain open after use as the end of the `using` statement calls `dispose` on the class, the dispose for both of these classes closes the reader/connection. –  Dec 17 '15 at 11:07
  • @AndrewMorton Your answer and help link was very useful. Thanks. – Hemal Dec 17 '15 at 11:42
  • 1
    It's really simple, it's just a lot of work. Make your own class that derives from `DbCommand`, `DbDataReader`, `DbConnection`, `DbDataAdapter` etc., and have them take the actual command (or whatever) as an argument. Then override all the methods to call the underlying command (or whatever) and add logging when needed (debug-only finalizers can be quite useful for finding unclosed data readers and connections in particular). This works best if you already have some abstractions in place - if you e.g. tend to manually assign connections to commands, it's a bit trickier. – Luaan Dec 17 '15 at 12:37
  • @Luaan Great and thank you so much – Hemal Dec 17 '15 at 13:15

0 Answers0