Application of Attribute in .NET Programming (3)
ATTRIBUTE for parameters
When writing multi-storey applications, are you boring for every time you have to write a lot of similar data access code? For example, we need to write code that calls the stored procedure, or write T_SQL code, which often needs to pass various parameters, and some of the number of parameters is more, and it is easy to write wrong. Is there a way for one for all? Of course, you can use the MS Data Access Application Block, you can also use your own BLOCK. Here you provide you an alternative method, that is to use Attribute.
The following code is a general way to call the AddCustomer stored procedure:
Public Int AddCustom (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 = Televhone;
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 above code, create a Command instance, then add the parameters of the stored procedure, then call the ExecuteMonQuery method to perform the insertion operation of the data, and finally return CustomerID. From the code, you can see the addition of parameters is a duplicate work. If a project has more than 100 or hundreds of stored procedures, will you be lazy as a developer? (Anyway, I will :-)).
Let's start our code automatically generate project:
Our purpose is to automatically generate a Command object instance based on the name of the method and the name of the method. The first step we have to do is to create a SQLParameterattribute, the code is as follows: SQLCommandparameterattribute.cs
Using system;
Using system.data;
USING Debug = system.diagnostics.debug;
Namespace DataAccess
{
// SqlParemeterattribute applies to stored procedure parameters
[AttributeUSAGE (AttributeTargets.Parameter)]
Public Class SqlparameterTribute: Attribute: Attribute
{
Private string name; // parameter name
Private bool paramtypedefined; // The type of parameter has been defined
Private sqldbtype paramtype; // parameter type
Private int size; // parameter size
Private Byte Precision; // Parameter Accuracy
Private byte scale; // parameter range
PRIVATE BOOL DIRECTIONDEFINED; / / Whether to define parameter directions
Private parameterDirection direction; // parameter direction
Public Sqlparameterattribute ()
{
}
Public String Name
{
Get {return name == null? string.empty: name;
Set {_name = value;}
}
Public int size
{
Get {returnide;}
SET {size = value;}
}
Public Byte Precision
{
Get {returnizon;
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 isnamedefinede
{
Get {return name! = null& name.length! = 0;
}
Public Bool Issizedefinede
{
Get {returnide! = 0;
}
Public Bool IStypedefinede
{
Get {return paramtyped;
}
Public bool isdirectionDefined
{
Get {returnide DirectionDefined;}
}
Public Bool IsscaleDefinede
{
Get {return _scale! = 0;}
}
Public bool isprecisionDefined
{
Get {return_precision! = 0;
}
...
The fields and corresponding properties of SqlParameterattribute are defined above. To make it easy for the use of Attribute, we overload several constructors, different overload constructors are used for unused parameters:
...
// Reload constructor, if the method corresponding to the stored procedure parameter name is different, we use it to set the name of the stored procedure.
// Other constructor's purpose is similar
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;
}
}
}
For those parameters that are not stored in the method, 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 completed the definition of the SQL parameter attribute. Before creating a Command object generator, let us consider a fact that if our data access layer is not a stored procedure, that is, Command's CommandType is not a stored procedure. But with the SQL statement of parameters, we want our way to suit this situation, and we can still use Attribute to define Attribute for the method to indicate that the generated Command's CommandType in the method is a stored procedure Still SQL text, below is 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 PUBLIC
{
get
{
Return CommandType;
}
set
{
CommandType = value;
}
}
}
}
Our Attribute's definition has been completed, the next step is to create a class to generate a COMMAND object. (to be continued)
