The Use of Attribute in .NET Programming (Part 4)

Design of the SqlCommandGenerator Class

The design idea of the SqlCommandGenerator class is to use reflection to get the method’s parameters, and use the parameters marked by SqlCommandParameterAttribute to assemble a Command instance.

Namespaces referenced:

//SqlCommandGenerator.cs

using System;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using Debug = System.Diagnostics.Debug;
using StackTrace = System.Diagnostics.StackTrace;

Class code:

namespace DataAccess
{
   public sealed class SqlCommandGenerator
   {
      // Private constructor; not allowed to construct an instance with the parameterless constructor
      private SqlCommandGenerator()
      {
         throw new NotSupportedException();
      }

      // Static read-only field; defines the parameter name used for the return value
      public static readonly string ReturnValueParameterName = "RETURN_VALUE";
      // Static read-only field; used for stored procedures with no parameters
      public static readonly object[] NoValues = new object[] {};

      public static SqlCommand GenerateCommand(SqlConnection connection,
                                  MethodInfo method, object[] values)
      {
         // If no method name is specified, get the method name from the stack frame
         if (method == null)
             method = (MethodInfo) (new StackTrace().GetFrame(1).GetMethod());

         // Get the SqlCommandMethodAttribute passed into the method; this attribute is required to generate a Command object.
         SqlCommandMethodAttribute commandAttribute =
            (SqlCommandMethodAttribute) Attribute.GetCustomAttribute(method, typeof(SqlCommandMethodAttribute));

         Debug.Assert(commandAttribute != null);
         Debug.Assert(commandAttribute.CommandType == CommandType.StoredProcedure ||
       commandAttribute.CommandType == CommandType.Text);

         // Create a SqlCommand object and configure it via the specified attribute.
         SqlCommand command = new SqlCommand();
         command.Connection = connection;
         command.CommandType = commandAttribute.CommandType;

         // Get the command text; if not specified, use the method name as the stored procedure name
         if (commandAttribute.CommandText.Length == 0)
         {
            Debug.Assert(commandAttribute.CommandType == CommandType.StoredProcedure);
            command.CommandText = method.Name;
         }
         else
         {
            command.CommandText = commandAttribute.CommandText;
         }

         // Call GeneratorCommandParameters to generate the command parameters, and add a return-value parameter
         GenerateCommandParameters(command, method, values);
         command.Parameters.Add(ReturnValueParameterName, SqlDbType.Int).Direction
                              =ParameterDirection.ReturnValue;

         return command;
      }

      private static void GenerateCommandParameters(
                           SqlCommand command, MethodInfo method, object[] values)
      {

         // Get all parameters and process them one by one in a loop.

         ParameterInfo[] methodParameters = method.GetParameters();
         int paramIndex = 0;

         foreach (ParameterInfo paramInfo in methodParameters)
         {
            // Ignore parameters marked with [NonCommandParameter]

            if (Attribute.IsDefined(paramInfo, typeof(NonCommandParameterAttribute)))
               continue;

            // Get the parameter's SqlParameter attribute; if not specified, create one with default settings.
            SqlParameterAttribute paramAttribute = (SqlParameterAttribute) Attribute.GetCustomAttribute(
     paramInfo, typeof(SqlParameterAttribute));

      if (paramAttribute == null)
         paramAttribute = new SqlParameterAttribute();

      // Configure a parameter object using the attribute's settings, using the already-defined parameter values. If not defined, infer its value from the method's parameter.
      SqlParameter sqlParameter = new SqlParameter();
      if (paramAttribute.IsNameDefined)
         sqlParameter.ParameterName = paramAttribute.Name;
      else
         sqlParameter.ParameterName = paramInfo.Name;

            if (!sqlParameter.ParameterName.StartsWith("@"))
               sqlParameter.ParameterName = "@" + sqlParameter.ParameterName;

            if (paramAttribute.IsTypeDefined)
               sqlParameter.SqlDbType = paramAttribute.SqlDbType;

            if (paramAttribute.IsSizeDefined)
               sqlParameter.Size = paramAttribute.Size;

            if (paramAttribute.IsScaleDefined)
               sqlParameter.Scale = paramAttribute.Scale;

            if (paramAttribute.IsPrecisionDefined)
               sqlParameter.Precision = paramAttribute.Precision;

            if (paramAttribute.IsDirectionDefined)
            {
               sqlParameter.Direction = paramAttribute.Direction;
            }
            else
            {
               if (paramInfo.ParameterType.IsByRef)
               {
                  sqlParameter.Direction = paramInfo.IsOut ?
                              ParameterDirection.Output :
                              ParameterDirection.InputOutput;
               }
               else
               {
                  sqlParameter.Direction = ParameterDirection.Input;
               }
            }

            // Check whether enough parameter object values are provided
     Debug.Assert(paramIndex < values.Length);

           // Assign the corresponding object value to the parameter.
           sqlParameter.Value = values[paramIndex];
           command.Parameters.Add(sqlParameter);

           paramIndex++;
         }

         // Check whether there are leftover parameter object values
         Debug.Assert(paramIndex == values.Length);
      }
   }
}

The necessary work is finally done. The code in SqlCommandGenerator has comments, so it’s not hard to read. Let’s move to the last step: using the new method to implement the AddCustomer method we showed at the beginning of the previous section.

Refactored AddCustomer code:

[ SqlCommandMethod(CommandType.StoredProcedure) ]
public void AddCustomer( [NonCommandParameter] SqlConnection connection,
                   [SqlParameter(50)] string customerName,
                   [SqlParameter(20)] string country,
                   [SqlParameter(20)] string province,
                   [SqlParameter(20)] string city,
                   [SqlParameter(60)] string address,
                   [SqlParameter(16)] string telephone,
                   out int customerId )
{
   customerId=0; // the out parameter needs to be initialized
  // Call the Command generator to generate the SqlCommand instance
   SqlCommand command = SqlCommandGenerator.GenerateCommand( connection, null, new object[]
{customerName,country,province,city,address,telephone,customerId } );

   connection.Open();
   command.ExecuteNonQuery();
   connection.Close();

   // Must explicitly return the out parameter's value
   customerId=(int)command.Parameters["@CustomerId"].Value;
}

One thing to note in the code is the out parameter, which needs to be initialized in advance, and after the Command executes, the parameter value is passed back to it. Thanks to Attribute, we’re freed from writing that large amount of tedious code. We could even use SQL stored procedures to write code that generates the entire method — if we did that, it would save you a lot of time. The code shown in the previous and this section can be compiled into a separate component, so you can reuse them continuously in your project. Starting from the next section, we’ll introduce Attribute applications at a deeper level. Stay tuned. (To be continued)