C# sql instancing, is it possible to list all sql connections not disposed of within the context of an object instance?

I'm getting some really daft errors relating to a block of code that i know for absolute certain cleans up after itself.

essentially i have an N-tier application framework that makes a call in to a "data provider" that in turn talks to a SQl database.

I want to say to the data provider "show me all connections that have been used and not yet disposed of" so i can if need be "force disposal" of any connections that should not be open.

If i can't do this in C# is there some alternative on the Sql server that lets me get some insight in to whats going on?

EDIT :

Okin this instance the code was being called from any location in an application based on a business object instance that calls a single location in an API layer. The API layer exposes an interface that it calls methods on.

I was hoping to have either the object that implements the interface or the API layer look at the call stack and decide weather to wait, throw an exception or make the call (open a new connection).

The problem i found was that I had a business object like say "person" that had a property called "vehicle" and that was an object of type vehicle which contained a property called "owner" this of course referred back to the parent object.

Now because of bad coding on my part the system went in to a sort of loop creating nested instance after nested instance of the 2 objects.

So the fix was simple enough ... lazy load at at least 1 of the 2 levels or remove the circular reference altogether.

However I would still like to look at my call stack at runtime and ask the question ... is this going to cause a problem if i try to create an instance of this business object?

This then led me on to thinking about the caching (to be implemented in the API layer later) ... if this was in place that problem of too many connections go开发者_StackOverflowes away but is replaced with a much bigger "im gonna munch all your ram" problem.

So I got thinking ... why cant i analyse the call stack?

The answer ...

I shouldn't have to if i write good code ... but i still want to :)

Don't know about C# but in Sql Server you can use that queries for monitoring opened connections:

1.Get list of databases and number of connections

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

2.Get all connections for user - sp_who 'userName'

3.Get info for connection DBCC INPUTBUFFER(1)

Regardless of this being possible, you are trying to solve the problem in a wrong place - if you do not mind me saying this. In a multi-threaded environment, how would you know if a the work of the connection has finished and it can be safely closed?

Whoever owns a resource is responsible to release it. So I suggest focusing on releasing the resource in the class that owns it.

However, to answer your question, ADO.NET does not provide a central registry so that you could look at the open connections - also perhaps for a good reason since it could drive bad design. There could be some API that can be called at the connection pooling level but it could be database specific.

If you really have to do it, nothing stops you to manually register a connection with a central registry when it gets open and de-register when it is closed. This could be implemented as singleton pattern - but as I said, I advise against it.

Check for circular references to prevent crazy errors :)

Don't ask stupid questions that expect microsoft to allow for bad coding to be "checked at runtime".