ADO.NET Connections, Commands, Parameters

I wanted to write this because I’ve seen some code written in the last few years and while not all bad, some even working as intended but lacking a certain clean look and occasionally a bit inefficient.

As an example to help me explain it I wrote a little console application, you can see the code at the end of this post.

The example is using a localdb version of the MS-SQL Server where a Test database was created.
Table1 is a table on Test with two fields: Id – an autoincremented primary key and Color – an nchar(10) field.

The example inserts two records in the table, one by calling Method1() and the other one by calling Method2().

Method1() is the method that contains the code that I mentioned above is not so clean and so on.
Method2() is the cleaner alternative which I hope you’ll adopt from now on.

The comments in the code should be sufficient to explain why the Method2() code can be written that way while keeping the functionality of the code identical with the one in Method1() and looking much cleaner.

I will however summarize the improvements the new code brings:

  • Instances of classes like SqlConnection and SqlCommand are disposable (implement IDisposable) so we can use the using block/scope which upon the exit of the block automatically calls Dispose() on the variables defined at the using declaration line. Of course what the implementation of the Dispose() method is doing in a particular class is different from one to another. That is where the MSDN page for the respective class can shed some light and if that’s not enough than you will have to use a tool like Reflector or JustDecompile (from Telerik) to take a peek at how Dispose is implemented for a particular class. For SqlConnection the Dispose() method will close the connection among other things so you don’t have to bother anymore with that if you tied your SqlConnection instance in a using block.
    • Above I said “the variables defined at the using declaration line” and it is not a mistake. The using syntax allows more than one variable tied to the using statement (separated by commas) as long as they are of the same type. So basically you can declare and instantiate 2 different connections with one using. However, when declaring multiple variables we have to specify the SqlConnection type explicitly as implicit-typed variables have to be declared individually (e.g.: var m =1, n=2 is not allowed)
  • There are a few cases when you can avoid having to declare SqlParameter variables just because you need to reference them later. The Add() and AddWithValue() methods return the SqlParameter they are creating so at least one more property can be set. Often, that’s enough to set the Value when Add() was used or set the SqlDbType when AddWithValue() was used.
  • It is a good idea to always specify the SqlDbType for a parameter because if there’s a type mismatch with the value set for parameter it may be caught as an InvalidCastException before the SQL statement is executed.
  • The same goes for the size of the parameter which can be specified as the third argument when calling the Add() method. It is important because if we would use Method1() to insert a string longer than 10 characters in the [Color] field SQL will raise a truncation error that comes back to our application as a SqlException. However, if we use Method2 which specifies the size of the field (10), the .Net code will make sure the value of the parameter is truncated before being sent to the SQL Server thus ensuring the success of the execution of that SQL command.
  • You can always use the name of a parameter as an index value to reference the corresponding SqlParameter instance in the SqlCommand.Parameters collection; it may be a good way of avoiding to have to declare a new variable just to store a reference that will be reused once or twice.
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            Method1();
            Method2();
        }
        private static void Method1()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=(localdb)\ProjectsV12;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;");
            SqlCommand comm = new SqlCommand(@"INSERT INTO Table1 (color) VALUES (@color)", conn);
            try
            {
                SqlParameter pColor = comm.Parameters.Add("@color", SqlDbType.NChar);
                pColor.Value = "Red";

                // Sometimes we see AddWithValue being used but without specifying
                // the SqlDbType so they can avoid having to declare a new SqlParameter 
                // comm.Parameters.AddWithValue("@color", "Red");
                
                // other times we created a SqlParameter variable just because needed to be referenced later
                Console.WriteLine("Param: {0} = {1}", pColor.ParameterName, pColor.Value);

                conn.Open();

                if (conn.State == ConnectionState.Open)
                {
                    comm.ExecuteNonQuery();
                }
                // Don't need to check if connection is open or any other state, 
                // Close() can be called many times, no exception will be generated
                conn.Close(); 
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                comm.Dispose();
                //conn.Dispose(); Close and Dispose on a connection are functionally equivalent
            }
        }
        private static void Method2()
        {
            try
            {
                using (var conn = new SqlConnection(@"Data Source=(localdb)\ProjectsV12;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30"))
                {
                    conn.Open();
                    if (conn.State != ConnectionState.Open) return;

                    using (var comm = new SqlCommand(@"INSERT INTO Table1 (color) VALUES (@color)", conn))
                    {
                        // both Add() or AddWithValue return the SqlParameter so we can set at least one other property in the same line
                        comm.Parameters.Add("@color", SqlDbType.NChar, 10).Value = "Blue";
                            
                        // we don't have to create a variable to reference a Parameter, we can do it by its index
                        Console.WriteLine("Param: {0} = {1}", comm.Parameters["@color"].ParameterName, comm.Parameters["@color"].Value);

                        comm.ExecuteNonQuery();
                    } // here the SqlCommand instance will be disposed automatically
                }// here the connection will be Disposed/Closed automatically
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
    }
}