Wednesday, September 23, 2009

Cascading using directive

Disclaimer:
This post is no great trick but a tiny tip for some of you, who probably didn't yet know this C# syntax feature. ;-)

As you know, .NET requires to implement the "IDisposable" interface for classes working with unmanaged resources. This interface provides the single method "Dispose()" which can be called to explicitly free the resources. If you don't call the method explicitly, the garbage collector (GC) calls the method automatically when the instance of the class becomes collected. Nevertheless, as usual you should care about those types and free them in your code.

One ensure the call of the Dispose() method within your code is a try-catch-finally block. Since this approach requires quite a bunch of code for a simple "Dispose()" method, .NET provides the "using" statement which implicit calls the "Dispose()" method as soon as you leave the scope of this statement.
using System;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
   class Programm
   {
      static void Main(string[] args)
      {
         String cnStr = "Server=.\\Sql2k8;" +
                        "Database=Sandbox;" +
                        "Trusted_Connection=true;";

         // using block for connection
         using (SqlConnection cn = new SqlConnection(cnStr))
         {
            cn.Open();
         }
      }
   }
}

In this sample the instance of a "SqlConnection" becomes disposed as soon as you leave the "using" statement.

Unfortunately this becomes a little bit ugly if you have to work with more than one types which require to be disposed. In ADO.NET most times you need a "SqlConnection", a "SqlCommand" and a "SqlDataReader" or a "SqlDataAdapter". All these classes implement the "IDisposable" interface, work with unmanaged resources and have to be disposed. Let's have a look at a simple sample to select one column of a table and write all rows to a text file.

using System;
using System.Data.SqlClient;
using System.IO;

namespace ConsoleApplication1
{
   class Programm
   {
      static void Main(string[] args)
      {
         String cnStr = "Server=.\\Sql2k8;" +
                        "Database=Sandbox;" +
                        "Trusted_Connection=true;";
         String sql = "SELECT TOP(1000) Num FROM Numbers";
         String exportFile = @"D:\Temp\Test\test.txt";

         // using block for connection
         using (SqlConnection cn = new SqlConnection(cnStr))
         {
            cn.Open();

            // using block for command
            using (SqlCommand cmd = new SqlCommand(sql, cn))
            {
               // using block for reader
               using (SqlDataReader reader = cmd.ExecuteReader())
               {
                  // using block for stream writer
                  using (StreamWriter writer = new StreamWriter(exportFile))
                  {
                     // write first column of all rows to a file
                     while (reader.Read())
                     {
                        String row = reader.GetValue(0).ToString();
                        writer.WriteLine(row);
                     }

                     writer.Close();
                  }
               }
            }
         }
      }
   }
}

As you can see we need four "using" statements to ensure a disposing of all classes.


Alternatively to this cascading "using" blocks C# offers the possibility to work with several "using" statements in one scope.

using System;
using System.Data.SqlClient;
using System.IO;

namespace ConsoleApplication1
{
   class Programm
   {
      static void Main(string[] args)
      {
         String cnStr = "Server=.\\Sql2k8;" +
                        "Database=Sandbox;" +
                        "Trusted_Connection=true;";
         String sql = "SELECT TOP(1000) Num FROM Numbers";
         String exportFile = @"D:\Temp\Test\test.txt";

         // using block for connection
         using (SqlConnection cn = new SqlConnection(cnStr))
         {
            cn.Open();

            // using scope for all other instances
            using (SqlCommand cmd = new SqlCommand(sql, cn))
            using (SqlDataReader reader = cmd.ExecuteReader())
            using (StreamWriter writer = new StreamWriter(exportFile))
            {
               // write first column of all rows to a file
               while (reader.Read())
               {
                  String row = reader.GetValue(0).ToString();
                  writer.WriteLine(row);
               }

               writer.Close();
            }
         }
      }
   }
}

As you can see you can use one single "using" scope for all instances, except the "SqlConnection". The connection needs requires to be handled in an own block since the call of "SqlCommand.ExecuteReader()" requires an open connection.

If you have to work with database connections frequently, you can use a simple trick by extending the connection class with an additional method. (Extension methods are a feature of .NET 3.0 and future versions). Let's define a new method which opens the connection and returns this instance of the connection to be used within the "using" statement.

static class SqlConnectionExtensions
{
   public static SqlConnection AutoOpen(this SqlConnection connection)
   {
      connection.Open();
      return connection;
   }
}
>

Now you are able to call this method directly from your connection constructor to open the connection and get the instance into the using-scope reference.

using System;
using System.Data.SqlClient;
using System.IO;

namespace ConsoleApplication1
{
   class Programm
   {
      static void Main(string[] args)
      {
         String cnStr = "Server=.\\Sql2k8;" +
                        "Database=Sandbox;" +
                        "Trusted_Connection=true;";
         String sql = "SELECT TOP(1000) Num FROM Numbers";
         String exportFile = @"D:\Temp\Test\test.txt";

         // one using scope for all disposable instances
         using (SqlConnection cn = new SqlConnection(cnStr).AutoOpen())
         using (SqlCommand cmd = new SqlCommand(sql, cn))
         using (SqlDataReader reader = cmd.ExecuteReader())
         using (StreamWriter writer = new StreamWriter(exportFile))
         {
            // write first column of all rows to a file
            while (reader.Read())
            {
               String row = reader.GetValue(0).ToString();
               writer.WriteLine(row);
            }

            writer.Close();
         }
      }
   }
}

As I told you at the begin of this post, the possibility to use more than one instance of an unmanaged class is not a great trick, but a quiet neat feature of C#.

No comments:

Post a Comment