Using `using` statements in C # when working with database related classes is best practice?

Question

The using statement automatically executes the Dispose method contained in IDisposable, the database related classes (for example, SqlConnection, SqlCommand, etc.) implement this interface.

So, if I am going to use such classes, should I use the using statement to create the objects so that the resources are freed when the operation completes?

For example, for some reason I need to use SqlConnection, SqlCommand, SqlDataAdapter and DataTable, so I write this code below, is this the best way to do this, or should I put Dispose () in the finally clause try ... catch ... finally?

 using (SqlConnection con = new SqlConnection(Properties.Settings.Default.ConnectionString)) using (SqlCommand cmd = new SqlCommand()) using (SqlDataAdapter da = new SqlDataAdapter()) using (DataTable dt = new DataTable()) { // Do something... } 

Answer 1

You have it right. Dispose () methods will automatically call Close (). This is not necessarily true for everything that implements IDisposable, but for classes related to DB, it is.

Answer 2

To answer Chris:

If you go to try / finally , you will need to check if all instances are all:

 try { SqlConnection con = new SqlConnection(Properties.Settings.Default.ConnectionString); ... } finally { if (con != null) con.Dispose(); if (cmd != null) cmd... } 

The using statement is intended to simplify this type of use.

By the way, he can do much more interesting things: http://pragmateek.com/c-scope-your-global-state-changes-with-idisposable-and-the-using-statement/

Answer 3

Chris's answer is correct, but note that some of the existing database implementations don't actually make many of their Dispose calls.

This may mean “bad”, but simple code, for example, returning the DataTable itself, actually cannot differ much from the “correct” use of the code resource.

But since these classes already implement IDisposable , you should always use Using in case their implementation changes anything in the future.