Using C#'s 'using' statement with a custom object's function, do I Need to implement IDisposable?

Question

I have an sqlConnection manager class like so:

public class SQLConn {
  public string connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

  private SqlConnection sqlConn;

  public SqlConnection Connection()
  {
      sqlConn = new SqlConnection(connStr);

      return sqlConn;
  }

  public void Open()
  {
        sqlConn .Open();
  }
}

If I use a function with the 'using' statement like:

var conn = new SQLConn();

using (conn.Connection()) 
{ 
    String query = "Select * from table";
    objSql = new SqlCommand(query, conn.Connection());      

    conn.Open(); 
    DoSomething(); 
}

Does the using statement dispose of the connection automatically since conn.Connection() returns a SqlConnection object? Or, do I have to implement IDisposable and a custom Dispose method on the SqlConn class?

Is this even a good way at all? I'm working with legacy code and I'm not able to use an ORM yet but is there a way to simplify this existing pattern to manage/create SQL connections?

Answer 1

No you do not, as long as the returned object is IDisposable.

The returned object needs not to implement IDisposable, but then the using block would serve no purpose.

Answer 2

The using statement will look at the final type of the expression - i.e. whatever is returned from .Connection(); if this returns something that is IDisposable, then you're OK.

The compiler will tell you if you get it wrong ;-p (it won't let you use using on something that isn't IDisposable).

You should probably watch out for where you are creating two connections:

using (var c = conn.Connection()) // <==edit
{ 
    String query = "Select * from table";
    objSql = new SqlCommand(query, c); // <==edit

    c.Open(); 
    DoSomething(); 
}

and possibly:

public SqlConnection Connection()
{
  if(sqlConn == null) sqlConn = new SqlConnection(connStr); // <== edit
  return sqlConn;
}

Answer 3

From MSDN:

The object provided to the using statement must implement the IDisposable interface.

You do not have to call Dispose() though, the using statement implicitly does this for you.

Answer 4

It will work but after the using {} you will be left with a sqlConn that internally holds a Disposed SlConnection. Not a really useful situation

Answer 5

Your code is wrong!

shoudl be something like this:

Dim conn as New SQLConn();
Dim sqlConnection New SQLConnection();

sqlConnection = conn.Connection();

using (sqlConnection) 
{ 
    String query = "Select * from table";
    objSql = new SqlCommand(query, sqlConnection);      

    conn.Open(); 
    DoSomething(); 
}

That way the using statement will dispose the connection at the end.

Answer 6

It seems the connection will be closed properly, but this is not recommended:

You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

http://msdn.microsoft.com/en-us/library/yh598w02.aspx

Answer 7

to answer your headline question, you must implement IDisposable in the class whose object you're using with "using". Otherwise, you'll get a compile-time error.

Then, yes, "using" will dispose your SqlConnection at the end of the block. Think of "using" as a "try-finally": there is an implicit call to Dispose() in the "finally" block.

Finally, cleaner code would be:

using( SqlConnection = new SqlConnection( connStr ) {
    // do something
}

At least readers of your code won't have to make the mental effort to realize as Hank Holterman pointed out that your SQLConn object holds a reference to a disposed connection.

Answer 8

Here's a nice reference.