Niwalker is used to write a multi-layer application when writing multi-storey applications, is you boring 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 writes
T_SQL code, these codes often need to pass various parameters, 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 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 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;} Top of the code, create a Command instance, then add the parameters of the stored procedure, then call the executemonquery method to perform data insertion operation, final 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 method 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 debug = system.diagnostics.debug;
namespace DataAccess {// SqlParemeterAttribute applied to a stored procedure parameter [AttributeUsage (AttributeTargets.Parameter)] public class SqlParameterAttribute: Attribute {private string name; // parameter name private bool paramTypeDefined; // whether the type of the parameter defined private SqlDbType paramType; / / parameter type private int size; // size parameter private byte precision; // parameter precision private byte scale; // parameters private bool directionDefined; // direction parameter defines whether private ParameterDirection direction; // orientation parameters public SqlParameterAttribute ( ) {} Public string name == null? String.empty: name;} set {_name = value;}} public int size {get {rary size;} set {size = value;}} public byte Precision {get {return;} 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 .Ssert (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 bo} _precision! = 0;}} ... These define the fields and corresponding properties of SQLParameterattribute, in order to facilitate the use of Attribute, we overload several constructors, different overload constructors Unused parameters: ...
// Heavy load 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 constructed purposes Similar to 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 SQL parameters Attribute, before creating the Command object generator, let us consider the fact that, That is if our data access layer is called
Not a stored procedure, that is, Command's CommandType is not a stored procedure, but a SQL statement with parameters. We want to make our way to suit this situation, the same
They can still use Attribute to define an attribute for the method to indicate that the generated command's commandType is a stored procedure or SQL text.
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 SQLCommandMethodttribute (CommandType CommandType, String Commandtext) {CommandType = CommandType; CommandText = CommandText;}
Public SQLCommandMethodAttribute (CommandType CommandType): this (commandtype, null) {}
Public string commandtext {get {return commandtxt == null? string.empty: commandtext;} set {commandtext = value;}}
Public CommandType CommandType {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.
