What is the best way to use an IDisposable object, such as SqlConnection, with several methods in the class?

Question

Sometimes I have several methods, all of which connect to SQL Server. This means that all methods contain local variables of type IDisposable, such as SqlConnection.

What is the best way to reuse a single sqlconnection object? Will it pass it as a reference and use it as a class level variable? Also, if I use it in all methods, do I need to pass it through ref and should the implementation of the idisposable class remove the variable?

thanks

Answer 1

When it comes to SqlConnection , pooling will come into play, so in this case the answer is no.

In general, if a one-time item is not intended to be shared, I would not share it. Some cleaning may be required before it can be reused without consequences.

Answer 2

I agree with Oded - best practice for using SqlConnection does not share it.

If you need to share another IDisposable between several ways than I would suggest you implement IDisposable in your class.

Answer 3

The single answer is brief and correct; You should not pass SqlConnections between objects.

A more complete answer is that if you want to centralize the management of SqlConnections, you must encapsulate them in a repository object; centralized database access object. This has the double advantage of putting code that deals with SqlConnections in one place and hiding this implementation detail from the rest of your code, so if you upgrade to Oracle or MySql or SqLite, you won’t have to reorganize everything in its system. Do some research on repository models; there should be an example of one or two that will “peer” into the model used to process their own database calls.

Answer 4

Actually, when you divide the material into several methods, a “general” connection (inside, for example, a class) can occur. For this, a closure-based pattern works very well:

 public void Do() { WithConnection(o => { o.Foo(); o.Bar(); }); } private void WithConnection(Action<ThisClass> action) { try { con.Open(); action(this); } finally { con.Dispose() } } 

I agree that in this special case, the union can be your friend, but if you want to have an available resource using several methods (follow several steps that usually trigger an event, but you only want one event, etc. etc. ), closing, as shown, may be your friend.

Answer 5

I would not share SqlConnection , as Oded says, but I think it would be nice to split the DataContext between the methods, if you have one. The way I do this is to allow my own data access class to be IDisposable , and in the Dispose method for my data access class, I would select my DataContext .