Application of Attribute in. NET Programming (3)

Attribute used for parameters

When writing multi-tier applications, do you feel boring when writing a large amount of similar data access code? For example, you need to write the code that calls the stored procedure, or write the T_ SQL Code. These codes often need to pass various parameters. Some of them have a large number of parameters, so it is easy to write errors accidentally. Is there a permanent way? Of course, you can use MS Data Access Application Block or self-compiled Block. Here we provide you with an alternative method, that is, using Attribute.

The following code is a general method for 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, adds the stored procedure parameters, calls the ExecuteMonQuery method to insert data, and returns CustomerId. From the code, we can see that adding parameters is repetitive and monotonous. If a project has more than 100 or even hundreds of stored procedures, do you want to be lazy as a developer? (I will do it :-)).

The following code starts to automatically generate a project:

The purpose is to automatically generate a Command object instance based on the method parameters and method names. The first step is to create a SqlParameterAttribute. The Code is as follows:

SqlCommandParameterAttribute. csusing System; using System. data; using Debug = System. diagnostics. debug; namespace DataAccess {// SqlParemeterAttribute is applied to the stored procedure parameter [AttributeUsage (AttributeTargets. parameter)] public class SqlParameterAttribute: Attribute {private string name; // Parameter name private bool paramTypeDefined; // whether the Parameter type has been defined private SqlDbType paramType; // parameter type private int size; // parameter size private byte Precision; // parameter precision private byte scale; // parameter range: private bool directionDefined; // whether the parameter direction is private ParameterDirection ction; // 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 (diredefindefined); return direction;} set {direction = value; directionDefined = tru E ;}} public 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 fields and corresponding attributes of SqlParameterAttribute are defined above. To facilitate Attribute usage, We need to overload several constructors. different constructors are used for unused parameters:

... // Reload constructor. If the parameter names of stored procedures in the methods are different, we use it to set the name of the stored procedure // other constructors for a similar purpose as 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 parameters that are not stored procedure parameters in methods, such as SqlConnection, we also need to define an Attribute that is not stored procedure parameters:

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

We have completed the definition of the SQL parameter Attribute. Before creating the Command object generator, let's consider the fact that if the data access layer calls not a stored procedure, that is to say, the CommandType of Command is not a stored procedure, but an SQL statement with parameters. We want our methods to be the same for this situation, and we can still use Attribute, define an Attribute for the method to indicate whether the CommandType of the Command generated in the method is stored procedure or SQL text. The code for the newly defined Attribute is as follows:

//SqlCommandMethodAttribute.csusing 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;         }      }   }}