disposing multiple objects that reference each other in c#

Question

Okay, I've searched "similar" topics but still haven't come across any answer to what I'm going to ask here.

I have a function that creates multiple sql objects under the System.Data.SqlClient namespace. I've read that the using statement disposes of an object after the using block but the variables declared are readonly. My function reuses some of these variables, so I can't really declare them within a using statement.

Here is the body of my function for clarity. Should I call Dispose on the other objects (command, transaction, reader, etc) or will using recursively dispose of them through the connection object? How should I dispose these objects?

I'm still new to C# (I come from C/C++ background) so please forgive me if the question sounds very ignorant.

public string SignIn(string userId, string password)
{
     SqlCommand sqlCommand = null;
     SqlTransaction sqlTransaction = null;
     string sessionId = "";

     using(SqlConnection sqlConnection = new SqlConnection Properties.Settings.Default.SessionManagerDBConnectionString))
     {
          try
          {
               sqlConnection.Open();

               sqlCommand = sqlConnection.CreateCommand();
               sqlCommand.CommandText = "GetUserByUserIdPassword";
               sqlCommand.CommandTimeout = 30;
               sqlCommand.CommandType = CommandType.StoredProcedure;
               SqlParameter parameterUserId = sqlCommand.Parameters.Add("@UserId", SqlDbType.NVarChar, 32);
               parameterUserId.Value = userId;
               SqlParameter parameterPassword = sqlCommand.Parameters.Add("@Password", SqlDbType.NChar, 64);
               parameterPassword.Value = this.GetSHA256Hash(password);
               sqlTransaction = sqlConnection.BeginTransaction("SampleTransaction");

               // more database activity, execute command, store results in datareader
               sqlTransaction.Commit();
               sqlConnection.Close();
          }
          catch (SqlException ex)
          {
               if(sqlTransaction != null)
                    sqlTransaction.Rollback();
               MessageBox.Show(ex.Number + ":" + ex.Message, ex.Server + ":" + ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
          }
     }
     return sessionId;
}

---

I tried to search for similar questions again and found some closer answers.

Is SqlCommand.Dispose() required if associated SqlConnection will be disposed?

Does SqlCommand.Dispose close the connection?

I suppose I should add a finally clause to my try-catch and call several Dispose methods there for all the sql objects I've created. I hope that suffices or is there a recommended style of doing this?

finally
{
     if(sqlCommand != null)
          sqlCommand.Dispose();
     if(sqlTransaction != null)
          sqlTransaction.Dispose();
     ...
}

---

I tried putting a using statement within the try-catch block for one of the sqlCommand objects, but if that part of the code aborts when an exception is thrown, the execution jumps down to the catch portion. The using does not dispose that sqlCommand object.

try
{
     ...
     using(sqlCommand = sqlConnection.CreateCommand())
     {
          sqlCommand.CommandText = "GetUserByUserIdPassword2";
          sqlCommand.CommandTimeout = 30;
          sqlCommand.CommandType = CommandType.StoredProcedure;
          SqlParameter parameterUserId = sqlCommand.Parameters.Add("@UserId", SqlDbType.NVarChar, 32);
          parameterUserId.Value = userId;
          SqlParameter parameterPassword = sqlCommand.Parameters.Add("@Password", SqlDbType.NChar, 64);
          parameterPassword.Value = this.GetSHA256Hash(password);

          SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
          // throws exception, no stored procedure "GetUserByUserIdPassword2"
     }
     ...
}
catch() {}

// sqlCommand still accessible at this point because using above was "aborted".

Answer 1

If the object implements IDisposable, then use using. If you need to create a new SqlCommand for some reason then finish one using block and start a new one. Or nest them if you still need access to the first SqlCommand.
You can reuse SqlCommand objects, as long as you haven't got a datareader still open from the command. So, you could create a SqlCommand, set all its properties and execute it, then reset all its properties and execute it again and so on. This does save slightly on the costs of memory allocation, but I think it also reduces the clarity of the code, so it is something I would only do if profiling proved it necessary.

Answer 2

will using recursively dispose of them through the connection object

Of course, using knows nothing about SqlConnections or other ADO.NET objects. All it knows is to call Dispose. Whatever the object being disposed does is what happens.

It happens to be the case that disposing a SqlConnection also disposes of the resources of all readers and commands associated with the connection. You only need to dispose the connection.

I don't know whether this is documented on MSDN but I know it from decompiling the assemblies. For compatibility reasons they can never change this behavior so it is safe to rely on it.

In general, you must call dispose on any object implementing IDisposable. Exception to this rule is when you know for sure that it is safe to not call Dispose. Like in this case.

Answer 3

I think I found the answer!

Even though the sqlCommand object was still accessible in the bottom parts of the code when a nested using statement was skipped by a thrown exception, sqlCommand is still disposed later on. I tested this by actually assigning a function to the disposed event of said sqlCommand object.

The code here is slightly different than above because of the transaction object requirement. But the logic is essentially the same.

try
{
       using(sqlCommand = new SqlCommand("GetUserByUserIdPassword2", sqlConnection, sqlTransaction))
       {
              sqlCommand.CommandTimeout = 15;
              sqlCommand.CommandType = CommandType.StoredProcedure;
              SqlParameter parameterUserId = sqlCommand.Parameters.Add("@UserId", SqlDbType.NVarChar, 32);
              parameterUserId.Value = userId;
              SqlParameter parameterPassword = sqlCommand.Parameters.Add("@Password", SqlDbType.NChar, 64);
              parameterPassword.Value = this.GetSHA256Hash(password);

              sqlCommand.Disposed += new System.EventHandler(this.sqlCommand_Disposed);

              SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
              // exception thrown, no sored proc "GetUserByUserIdPassword2"
              sqlDataReader.Close();
       }
}
catch(...) {}

...

private void sqlCommand_Disposed(object sender, EventArgs e)
{
     MessageBox.Show("sqlCommand has been disposed");
}

So, basically, even if a nested using statement is "aborted" by a thrown exception within the try block, and execution is skipped down to the catch block, the Dispose method is still called for that object after the function exits (or when the variable goes out of scope).

I presume this behavior is the same for any number of nested using statements.

Answer 4

in CSharp, using is a special keyword which have different acts in code..

if you put using to top of the codepage then using acts as c++ ' s Include or VB (and some other script langs') Import

if you write a "using" which follows by a paranthesis then it acts like an alias of Dispose() method of CSharp.. or Destructor tilda of c++ or free() method of plain c..

About SQL Connection.. Best way is first Close() the connection (so with this, connection is still alive -actually just ready to use- but null.. return to wait new order in connection pool.. ) and then dispose if you need..

if you have still problems and suspected that calling the Dispose() method not enough to free the resources

then you can use Garbage Collector's Collect() static method to force the garbage collection But not so much recommended by Microsoft because of face with accidental garbage collection problems (i.e. you collect the form but need some variable from that form )

in example of usage :

public void Dispose (bool ForceToCollect )
{
   if (ForceToCollect) 
               GC.Collect (0, GCCollectionMode.Forced );
}

In Gc.Collect() method you can set which group of data memory freed with first option where is int..if you plan to free resources of the last group - means last created group of items, as an example last created form instance and its all child controls - then int should be 0; the one which is 1 before the last created then should be 1 and so on..

Hope this helps..