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

Attribute for Parameters

When writing multi-tier applications, do you feel bored writing lots of similar data-access code every time? For example, we need to write code to call stored procedures, or write T-SQL code, which often needs to pass various parameters, and some methods have many parameters, so it’s easy to make mistakes. Is there a once-and-for-all method? Of course, you can use MS’s Data Access Application Block, or write your own Block. Here I offer you an alternative method — using Attribute.

The following code is a conventional method calling the AddCustomer stored procedure:

public int AddCustomer(SqlConnection connection,
		string customerName,
		string country,
		string province,
		string city,
		string address,
		string telephone)
{
   SqlCommand command=new SqlCommand("AddCustomer", connection);
   command.CommandType=CommandType.StoredProcedure;

   command.Parameters.Add("@CustomerName",SqlDbType.NVarChar,50).Value=customerName;
   command.Parameters.Add("@country",SqlDbType.NVarChar,20).Value=country;
   command.Parameters.Add("@Province",SqlDbType.NVarChar,20).Value=province;
   command.Parameters.Add("@City",SqlDbType.NVarChar,20).Value=city;
   command.Parameters.Add("@Address",SqlDbType.NVarChar,60).Value=address;
   command.Parameters.Add("@Telephone",SqlDbType.NvarChar,16).Value=telephone;
   command.Parameters.Add("@CustomerId",SqlDbType.Int,4).Direction=ParameterDirection.Output;

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

   int custId=(int)command.Parameters["@CustomerId"].Value;
   return custId;
}

The code above creates a Command instance, then adds the stored procedure’s parameters, then calls ExecuteNonQuery to perform the data insertion, and finally returns CustomerId. As you can see, adding parameters is repetitive and monotonous work. If a project has over a hundred or even hundreds of stored procedures, as a developer wouldn’t you want to find a way to be lazy? (I would, anyway :-)).

Now let’s start our code auto-generation project:

Our goal is to automatically generate a Command object instance based on the method’s parameters and the method’s name. The first step is to create a SqlParameterAttribute, with the code below:

// SqlParemeterAttribute.cs

using System;
using System.Data;
using Debug=System.Diagnostics.Debug;

namespace DataAccess
{
   // SqlParemeterAttribute applied to stored-procedure parameters
   [ AttributeUsage(AttributeTargets.Parameter) ]
   public class SqlParameterAttribute : Attribute
   {
      private string name;                  // parameter name
      private bool paramTypeDefined;       // whether the parameter type is defined
      private SqlDbType paramType;          // parameter type
      private int size;                     // parameter size
      private byte precision;               // parameter precision
      private byte scale;                   // parameter scale
      private bool directionDefined;       // whether the parameter direction is defined
      private ParameterDirection direction; // parameter direction

      public SqlParameterAttribute()
      {
      }

      public string Name
      {
         get { return name == null ? string.Empty : name; }
         set { _name = value; }
      }

      public int Size
      {
         get { return size; }
         set { size = value; }
      }

      public byte Precision
      {
         get { return precision; }
         set { precision = value; }
      }

      public byte Scale
      {
         get { return scale; }
         set { scale = value; }
      }

      public ParameterDirection Direction
      {
         get
         {
            Debug.Assert(directionDefined);
            return direction;
         }
         set
         {
            direction = value;
		    directionDefined = true;
		 }
      }

      public SqlDbType SqlDbType
      {
         get
         {
            Debug.Assert(paramTypeDefined);
            return paramType;
         }
         set
         {
            paramType = value;
            paramTypeDefined = true;
         }
      }

      public bool IsNameDefined
      {
         get { return name != null && name.Length != 0; }
      }

      public bool IsSizeDefined
      {
         get { return size != 0; }
      }

      public bool IsTypeDefined
      {
         get { return paramTypeDefined; }
      }

      public bool IsDirectionDefined
      {
         get { return directionDefined; }
      }

      public bool IsScaleDefined
      {
         get { return _scale != 0; }
      }

      public bool IsPrecisionDefined
      {
         get { return _precision != 0; }
      }

      ...
   }
}

The above defines the fields and corresponding properties of SqlParameterAttribute. To make it easier to use, we overload several constructors; different overloaded constructors are used for different parameters:

      // Overloaded constructor: if the method's parameter name differs from the stored procedure's, use it to set the stored procedure's name. Other constructors serve a similar purpose.
      public SqlParameterAttribute(string name)
      {
         Name=name;
      }

      public SqlParameterAttribute(int size)
      {
         Size=size;
      }

      public SqlParameterAttribute(SqlDbType paramType)
      {
         SqlDbType=paramType;
      }

      public SqlParameterAttribute(string name, SqlDbType paramType)
      {
         Name = name;
         SqlDbType = paramType;
      }

      public SqlParameterAttribute(SqlDbType paramType, int size)
      {
         SqlDbType = paramType;
         Size = size;
      }

      public SqlParameterAttribute(string name, int size)
      {
         Name = name;
         Size = size;
      }

      public SqlParameterAttribute(string name, SqlDbType paramType, int size)
      {
         Name = name;
         SqlDbType = paramType;
         Size = size;
      }
   }
}

To distinguish the parameters in a method that are not stored-procedure parameters — such as SqlConnection — we also need to define an Attribute for non-stored-procedure parameters:

// NonCommandParameterAttribute.cs

using System;
namespace DataAccess
{
   [ AttributeUsage(AttributeTargets.Parameter) ]
   public sealed class NonCommandParameterAttribute : Attribute
   {
   }
}

We have finished defining the SQL parameter Attribute. Before creating the Command object generator, let’s consider this fact: if our data-access layer calls something other than a stored procedure — that is, the Command’s CommandType is not a stored procedure but a parameterized SQL statement — we want our method to also fit this situation. Again we can use Attribute: define an Attribute for methods to indicate whether the generated Command’s CommandType is a stored procedure or SQL text. Below is the code for the newly defined Attribute:

// SqlCommandMethodAttribute.cs

using System;
using System.Data;

namespace Emisonline.DataAccess
{
   [AttributeUsage(AttributeTargets.Method)]
   public sealed class SqlCommandMethodAttribute : Attribute
   {
      private string commandText;
      private CommandType commandType;

      public SqlCommandMethodAttribute( CommandType commandType, string commandText)
      {
         commandType=commandType;
         commandText=commandText;
      }

      public SqlCommandMethodAttribute(CommandType commandType) : this(commandType, null){}

      public string CommandText
      {
         get
         {
            return commandText==null ? string.Empty : commandText;
         }
         set
         {
            commandText=value;
         }
      }

      public CommandType CommandType
      {
         get
         {
             return commandType;
         }
         set
         {
            commandType=value;
         }
      }
   }
}

We have finished defining our Attributes. The next step is to create a class that generates Command objects. (To be continued)