How best to share an IDisposable object like SqlConnection with several methods in a class?

Sometimes I have several methods all of which connect to SQL Server. This means that all methods contain local variables of IDisposable types like SqlConnection.

What is the best way to reuse one sqlconnection object? Would it be to pass it in as a reference and have it as a cl开发者_如何学Cass-level variable? Also, if I use it throughout methods, does it need to be passed in by ref and should the class implement idisposable to dispose of the variable?

Thanks

When it comes to SqlConnection, connection pooling will come into play, so in this case the answer is - don't share.

In general, unless a disposable object is designed for sharing, I wouldn't try to share it. There might be some cleanup required before it can be reused without consequence.

I agree with Oded - best practice for using SqlConnection is not sharing it.

If you need to share other IDisposable object between several methods than I would suggest you to implement IDisposable in your class.

Oded's answer is concise and correct; you shouldn't share SqlConnections between using objects.

A more complete answer is that if you want to centralize control of SqlConnections, you should encapsulate them in a Repository object; a centralized database-access object. It has the dual advantage of putting the code that deals with SqlConnections in one place, and hiding that implementation detail from the rest of your code, so if you change to Oracle or MySql or SqLite, you don't have to refactor everything in your system. Do some research on Repository patterns; there should be an example of one or two that will "drop in" to a model that used to handle its own DB calls.

Actually, when you segregate stuff into several methods, a 'shared' connection (inside e.g. a class) may occur. For this, a pattern based on a closure works pretty 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, pooling may be your friend, but if you want to have a resource available throughout several methods (do several things that would usually fire an event, but you only want the event once, etc. etc.), a closure as shown can be your friend.

I wouldn't share an SqlConnection as Oded says, but I think it might be fine to share a DataContext between methods if you have one. The way I would do that is to let my own data access class implement IDisposable, and in the Dispose method for my data access class I would dispose my DataContext.