关于SqlConnection.Close()和Dispose()方法该何时调用

这是一个老生常谈的问题了，但是看到这里经常会有各种各样的回答。有的说用完之后两个方法最好都调用一下，有的说不调用的话就会导致连接池满，……

总之，似乎并没有统一的标准答案。让我们先看看MSDN吧：http://msdn.microsoft.com/en-us/library/ff647768.aspx

这上面提到的关闭数据库连接的两种标准做法：

public void DoSomeWork()

{

  SqlConnection conn = new SqlConnection(connectionString);

  …try

  {

    conn.Open();

    // Do Work

  }

  catch (Exception e)

  {

    // Handle and log error

  }

  finally

  {

    if(null!=conn)

       conn.Close();

  }

}

using (SqlConnection conn = new SqlConnection(connString))

{

  conn.Open();

  . . .

} // Dispose is automatically called on the conn variable here

显而易见，我们并不需要同时调用两个方法，只要调用Close()或者Dispose()中的任意一个就可以了，理由很简单，看看Dispose()的实现就知道了。

/// <summary>

/// Releases all resources used by the <see cref="T:System.ComponentModel.Component" />.

/// </summary>

public void Dispose()

{

    this.Dispose(true);

    GC.SuppressFinalize(this);

}



protected override void Dispose(bool disposing)

{

    if (disposing)

    {

        this._userConnectionOptions = null;

        this._poolGroup = null;

        this.Close();

    }

    this.DisposeMe(disposing);

    base.Dispose(disposing);

}

也就是说，调了Dispose()方法之后，它会自动调用Close()方法的。其实，对于Close()来说，并不是真正地把数据库连结给关闭了，只是放回到的连接池而已；这一点，很多人会误以为调用了Close()就是把连接关闭了。

那么，如果不调用Dispose()或者Close()又会怎样呢？
当你的程序并不需要频繁地操作数据库的时候，就算忘记调用Dispose或者Close也无伤大雅，因为SqlConnection类实现了Finalize方法，而Finalize方法里面又调用了Dispose。所以，最终Finalizer Queue在回收资源的时候也会调用到SqlConnection的Close()方法的。

其实，关于这一切MSDN上面都说得很清楚：
•Using either the Close method or the Dispose method is sufficient. You do not have to call one method after the other. There is no benefit to calling one method after the other.
•Dispose internally calls Close. In addition, Dispose clears the connection string.
•If you do not call Dispose or Close, and if you do not use the using statement, you are reliant upon the finalization of the inner object to free the physical connection.
•Use the using statement, instead of Dispose or Close, when you are working with a single type, and you are coding in Visual C#®. Dispose is automatically called for you when you use the using statement, even when an exception occurs.
•If you do not use the using statement, close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs.
•You do not have to set the SqlConnection reference to null or Nothing because there is no complex object graph. Setting object references to null or to Nothing is usually done to make a graph of objects unreachable.