Sqlconnection SQLcommand SQLdatareader Idisposable

SqlConnection SqlCommand SqlDataReader IDisposable

This is 100% the correct way. If a class leverages IDisposable it should be wrapped in a using statement to ensure that the Dispose() method is called. Further, communicating with an outside technology -unmanaged at that -like SQL Server shouldn't be taken lightly. The SqlCommand object implements IDisposable for a very good reason. The code below is the Dispose() method for the SqlCommand object:

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._cachedMetaData = null;
    }
    base.Dispose(disposing);
}

and as you can see, it's releasing a reference to the _cachedMetaData object so that it too can get cleaned up.

do we need using for the SqlCommand or is it enough just for the SqlConnection and SqlDataReader

You need a using for every object you create that implements IDisposable. That includes the SqlCommand and the SqlConnection.

---

There are very few exceptions to this rule. The main exception is WCF client proxies. Due to a design flaw, their Dispose method can sometimes throw an exception. If you used the proxy in a using statement, this second exception would cause you to lose the original exception.

Do I need to close and dispose the SQLConnection explicitly?

You should Dispose every temporary IDisposable instance you create manually, i.e. wrap them into using:

   // Connecton is IDisposable; we create it 
   //   1. manually - new ...
   //   2. for temporary usage (just for the query)
   using (SqlConnection con = new SqlConnection(objUtilityDAL.ConnectionString)) {
     // Check is redundant here: new instance will be closed 
     con.Open();

     // Command is IDisposable
     using (SqlCommand cmd = con.CreateCommand()) {
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.Add(Parameter);
       cmd.CommandText = _query;

       // Finally, Reader - yes - is IDisposable 
       using (SqlDataReader rdr = cmd.ExecuteReader()) {
         // while (rdr.Read()) {...}
       }
     }   
   }

Please, notice that

   try {
     ...
   }
   catch (Exception ex) {
     throw ex;
  } 

is at least redundant (it does nothing but rethrow the exception, while loosing stack trace) and that's why can be dropped out

Is it necessary to manually close and dispose of SqlDataReader?

Try to avoid using readers like this:

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
      while (reader.Read())
      {
              //do something
      }
}
reader.Close(); // <- too easy to forget
reader.Dispose(); // <- too easy to forget
connection.Close(); // <- too easy to forget

Instead, wrap them in using statements:

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } // reader closed and disposed up here

    } // command disposed here

} //connection closed and disposed here

The using statement will ensure correct disposal of the object and freeing of resources.

If you forget then you are leaving the cleaning up to the garbage collector, which could take a while.

Is closing/disposing an SqlDataReader needed if you are already closing the SqlConnection?

In my opinion, there are two rules to follow here:

1. Classes that implement IDisposable should be wrapped in a using block.
2. You should not rely on a class's implementation of IDisposable to ignore rule 1.

That is, even if you know that disposing the connection object took care of disposing its associated command object, you should not rely on this behavior.

By the way, it's possible to nest using blocks in a cleaner fashion:

using (SqlConnection conn = new SqlConnection(conStr))
using (SqlCommand command = new SqlCommand())
{
    // dostuff
}

and I would use

SqlCommand command = conn.CreateCommand();

instead of creating a new SqlCommand and then associating it with the connection.

Is it necessary to dispose SqlConnection as well as SqlCommand?

Yes, you will have to dispose (of) the connection separately. You can also see this in the source code: SqlCommand.Dispose() does not close or dispose the connection it uses (by the way, that would be bad anyway as you could not execute multiple commands with the same connection).

Proper use of SqlDataReader

the second has less code.

Not really.

Which one is to prefer?

The first one, by far. But just for esthetic reasons.

In the second sample, the Reader will be closed when it's (owning) connection is Disposed but it is far better to do so explicitly in the code.

Is it correct to not dispose of SqlCommand like the example in Microsoft documentation?

See my comments in your code first

private static void ReadOrderData(string connectionString)
{
    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
            }
        } // -- disposes reader and closes connection
    } // -- disposes connection and closes it 
}

command was declared in the scope of connection using, i.e. try/catch/finally
Command is not necessarily holding any resources outside of connection. And you disposing connection, which already closed when reader is disposed. (see CommandBerhavior). So, in the end, command is the object that holds references to disposed resources.

To know better, use some reflection tool and look inside.

protected override void Dispose(bool disposing)
{
    if (disposing)
        this._cachedMetaData = (_SqlMetaDataSet) null;
    base.Dispose(disposing);
}

So, what is this metadata? Probably some Parameter info. But if you don't add parameters then you might have nothing to dispose. Microsoft knows this and found unnecessary to include dispose on command. This is to answer, why Microsoft did not include using for command.

Look at the constructor

public SqlCommand()
{
    GC.SuppressFinalize((object) this);
}

Looks like there is nothing to dispose in the first place. And all those "cached items" will be garbage collected normally. I did not find there any IO or other unmanaged resources anywhere there.

One thing however, to remember, is that implementation might change from version to version and you don't want to change the code. So, add another layer to command

private static void ReadOrderData(string connectionString)
{
    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(queryString, connection))
    { 
       . . . . . . 
    }   
}

This will potentially save some hustle.

Fun fact: When working with MySql provider which implements same interfaces and base classes as SqlClient, there was a bug (still is probably) in the MySqlCommand.Dispose which closed the reader always. And this same code with using would work with SqlClient and Oracle ODP but not MySql. I had to wrap MySqlCommand and use my custom class (think of Decorator pattern) in the provider-agnostic code to overwrite the behavior.

---

Related Topics