CODEWITHSUNDEEP
c#database
Zignd
Zignd

Reputation: 7025

The usage of the `using` statement in C# when dealing with Database related classes the best practice?

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

So if I going to use this sort of classes should I use a using statement for the objects creation so the resources will be released at operation end?

For example, I need to use a SqlConnection, SqlCommand, SqlDataAdapter and a DataTable for some reason so I write this code below, is this the best way to do so or should I put the Dispose() at the finally clause of a 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...
}

Upvotes: 4

Views: 183

Answers (3)

Mark Hurd
Mark Hurd

Reputation: 10931

Chris's answer is correct, but note that some of the current database implementations don't actually do much in their Dispose calls.

This can mean "poor" but simple code, such as returning a DataTable itself, may not actually be much different to "proper" code resource usage-wise.

But because these classes already implement IDisposable you should always use Using, in case their implementation changes in the future to actually dispose something.

Upvotes: 2

Pragmateek
Pragmateek

Reputation: 13374

To complete Chris answer:

If you go the try/finally way you'll have to check if all the instances are not null:

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

The using statement is precisely made to simplify this kind of use-case.

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

Upvotes: 3

Chris Doggett
Chris Doggett

Reputation: 20757

The way you have it is correct. The Dispose() methods will automatically call Close(). That's not necessarily true for everything that implements IDisposable, but for the DB-related classes, it is.

Upvotes: 6

Related Questions