Uses of "using" in C# has a nice explanation of the utilities of the using feature.
.Net has its garbage collector. How does it handle the lack of a dipose()?
Specifically for DB connections, statements and resultsets, is it required a using() for each of them? What happens if they are left behind with no using(), dispose() and neither close()?
Update: the context is web applications, therefore there may be thousands of simultaneous users, each with his own connection/stmt/rs and the app will never be closed.
Specifically for DB connections, statements and resultsets, is it required a using() for each of them? What happens if they are left behind with no using(), dispose() and neither close()?
Actually the worst consequence of a memory leak is holding some memory until you restart the PC. However in this case probably the worst consequence is leaking memory until you restart the application.
If memory growth increases up to where GC cannot clean any longer, in fact if Gen 2 of Small Object Heap is overflow (Large object heap also can overflow), it will throw out of memory exception and close the application.
.Net has its garbage collector. How does it handle the lack of a dipose()?
All the standard database connection related classes have implement Dispose and Finalize methods properly. Generally there are unmanaged resources in those classes. Unmanaged resources are the resouces (eg: file handlers, database connection handlers and etc) which could cause worse memory leaks that may hold memory until you restart the PC. However, that's where GC's finalization comes handy. If you don't call the Dispose for such Disposable object, garbage collector will execute the Finalize method(if there is a ~destructor) and clear unmanaged resources.
That's the reason why it is required to implement IDispose Pattern properly Dispose and Finalization as required. Finalization is required only if it has Unamanged resources.