Author: niwalker Source: 9cbs
The design idea of the SQLCommandGenerator class is the design idea of the SQLCommandGenerator class is to assemble a Command instance by reflecting the parameters of the method. Namespace references: //SqlCommandGenerator.csusing 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 searated class sqlcommandgenerator {// Private constructor, does not allow parameters to construct an instance private sqlcommandgenerator () {throw new notsupportedException ()} // Static read-only field, defined for return values parameter name public static readonly string ReturnValueParameterName = "RETURN_VALUE"; // static readonly field for storing a procedure with no arguments public static readonly object [] NoValues = new object [] {}; public static SqlCommand GenerateCommand (SqlConnection connection, MethodInfo method, Object [] value) {// If there is no specified method name, get the method name IF (Method == Null) (New StackTrace (). GetFrame (1) .getMethod ()) ; // Gets SQLCommandMethodAttribute // to generate a Command object in order to use this method, requiring this Attribute. 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 while configuring it by the specified Attribute.
SqlCommand command = new SqlCommand (); command.Connection = connection; command.CommandType = commandAttribute.CommandType; // Get command text, if not specified, the name of the stored procedure as a method of using the name if (commandAttribute.CommandText.Length = = 0) {Debug.Assert (commandAttribute.CommandType == CommandType.StoredProcedure); command.CommandText = method.Name;} else {command.CommandText = commandAttribute.CommandText;} // call GeneratorCommandParameters method generates command argument while adding 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, processed by looping one by one. ParameterInfo [] methodParameters = method.GetParameters (); int paramIndex = 0; foreach (ParameterInfo paramInfo in methodParameters) {// parameters are marked as ignored [NonCommandParameter] parameter if (Attribute.IsDefined (paramInfo, typeof (NonCommandParameterAttribute)) ) Continue; // Get the parameters of SQLParameter Attribute, if not specified, create one and its default settings. SqlParameterAttribute paramAttribute = (SqlParameterAttribute) Attribute.GetCustomAttribute (paramInfo, typeof (SqlParameterAttribute)); if (paramAttribute == null) paramAttribute = new SqlParameterAttribute (); // attribute settings to use a configuration parameter object. Use those already defined parameter values. If it is not defined, it will infer its parameter value from the method // parameter.
SqlParameter sqlParameter = new SqlParameter (); if (paramAttribute.IsNameDefined) sqlParameter.ParameterName = paramAttribute.Name; elsesqlParameter.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.Ineput;}} // Detection provides enough parameter object value debug.assert (paramindex Reconstruction AddCustomer New 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; // Need to initialize output parameter // call Command Builder examples generates SqlCommand SqlCommand command = SqlCommandGenerator.GenerateCommand (connection, null, new object [] {customerName, country, province, city, address, telephone, customerId}); connection.Open (); command.ExecuteNonQuery (); connection.Close (); // must return to the value of the output parameter CustomerId = (int) Command.Parameters ["@ CustomerID"]. Value;} The OUT parameter is the OUT parameter, you need to initialize in advance, and after the Command is executed , Pass the parameter value back to it. Benefit from Attribute, let us get rid of the kind of career with a large number of boring code programming. We can even use the SQL stored procedure to write code to generate the entire method. If you do it, you can save your time, the last section and the code shown in this section, you can compile them separately It is a component so that you can continue to use them in your project. From the next section, we will introduce detribute applications, please continue to pay attention. 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 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 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 { [AttributeUSAG (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. The support of Attribute in .NET Framework is a new feature that supports Attribute classes from it. Use this class appropriately in your program, or flexiblely use this class, which will make your program get a difficult ability in past programming. Let's take an example: If you are a member of a project development team, you want to track the information check of the project code, usually you can save the code's check information in the database to query; or write the information to the code Inside, this can read the code while seeing the information checked. We know that the component of the .NET is self-described, can you let the code you describe the information it being checked? This allows us to save information and code together, and you can get information through the self-description of the code. The answer is to use Attribute. The following steps and code tells you how to do: First, we create a custom attribute, and set our Attribute on the elements of the Class to get a class code check information. Using system; using system.reflection; [AttributeUSAGETARGETS.CLASS] / / Remember the content of the last section? public class CodeReviewAttribute: System.Attribute // define a CodeReview of Attribute {private string reviewer; // Code Checker private string date; // check the date private string comment; // check result information parameter // constructor public CodeReviewAttribute (string reviewer, string date) {this.reviewer = reviewer; this.date = date;} public string Reviewer {get {return reviewer;}} public string Date {get {return date;}} public string Comment {get {return comment; } set {comment = value;}}} Our custom codeReViewAttribute does not differ from the ordinary class, which is derived from Attribute, and said that our Attribute can only be applied to class elements through Attributeusage. The second step is to use our codeReviewAttribute. If we have a Jack written type myclass, check the people Niwalker, July 9, 2003, so we applied Attribute as follows: [CodeReView ("Niwalker", "2003-7-7-7-7-7-7 9 ", Comment =" Jack code ")] member definition of public class myclass {// class} When this code is compiled, the compiler will call the CodeReViewAttribute constructor and put" niwalker "and" 2003-7 -9 "As a parameter of the constructor, respectively. Note that there is also an assignment of a Comment property in the parameter table. This is the unique way of Attribute. Here you can set more attribute public properties (if any), you need to point out .NET Framework1.0 Allow PRIVATE The attribute assignment, but in .NET Framework1.1 is not allowed to do so, only the value of the PUBLIC is assigned. The third step is to take out the information we need. This is achieved by the reflection of .NET, the knowledge of reflection is limited to the space, I don't plan to explain here, maybe I will write another article in the future. class test {static void Main (string [] args) {System.Reflection.MemberInfo info = typeof (MyClass); // get the information of MyClass obtained by reflecting // custom Attribute CodeReviewAttribute att exerted on the class MyClass = (CodeReviewAttribute Attribute.getCustomAttribute (INFO, TYPEOF (CodeReviewAttribute)); if (att! = Null) {Console.WriteLine ("Code Check: {0}", att.reviewer; console.writeline ("Check Time: {0 } ", att.date); Console.Writeline (" Note: {0} ", att.comment;}}} In the above example, Attribute played a role that adds additional information to a class, it does not affect Myclass class behavior. With this example, we can roughly know how to write a custom Attribute, and how to use it in the app. In the next section, I will show how to use Attribute to generate the code of ADO.NET's data access class. I often have a friend asking, what is Attribute? what's it for? It seems that this Dongdong program can also run. In actually in .NET, Attribute is a very important part. In order to help you understand and master Attribute, and its usage method, special collection of several Attribute uses, provide you with reference. Before the specific demo, I would like to introduce Attribute first. We know that there is a member of the Property member in the members of the class, both in Chinese are explained in Chinese, then are they not the same thing? From the code, it is obviously different, first of all, their position is different in the code, followed by the different ways (Attribute must be written in a pair of squares). What is Atribute First, we are sure Attribute is a class, below is the description of the MSDN document: When the public language is run, you will add a description of the similar keyword, called Attributes, which labels the elements in the program, such as type, field, method, and attributes. The metadata of Attributes and Microsoft .NET Framework files are saved together, which can be used to describe your code when running, or affect the application's behavior when running. In .NET, Attribute is used to handle multiple problems, such as serialization, security features, to prevent the instant compiler from optimizing the program code to easily debug, etc. Next, let's first look at the use of the properties in .NET, we will return to the head of Attribute later. (The code in the text uses C # written, but it also applies all .NET-based all languages) Attribute as a compiler There is a certain number of compilers instructions in C #, such as: #define debug, #undefine debug, #if, etc. These instructions are specialized to C # and are fixed in quantity. Attribute is used as a compiler instruction without quantity. For example, the three attributes below: Conditional: The role of conditional compilation, only satisfying the condition, allowing the compiler to compile its code. Generally used during program debugging. DLLIMPORT: The function used to mark non-.NET indicates that the method is defined in an external DLL. Obsolete: This property is used to mark the current method has been discarded and no longer used. The following code demonstrates the use of the above three properties: #define debug // Here define conditions Using system; Using system.Runtime.InteropServices; Using system.diagnostics; Namespace AttributeMo { Class Mainprogramclass { [DLLIMPORT ("User32.dll")]]]]] Public Static Extern Int MessageBox (int hparent, string message, string caption, int type); Static void main (string [] args) { DisplayRunningMessage (); DisplayDebugMessage (); Messagebox (0, "Hello", "Message", 0); Console.readline (); } [Conditional ("Debug")]] Private static void displayRunningMessage () { Console.Writeline ("Start running the main subroutine. The current time is" DateTime.now); } [Conditional ("Debug")]] [Obsolete] Private static void displaydebugmessage () { Console.Writeline ("Start Main Subprogram"); } } } If an attribute is declared in front of a program element, then this Attribute is applied to the element, the previous code, [DLLIMPORT] is applied to the MessageBox function, [Conditional] applies to the DisplayRuntimeMessage method, [Obsolete] applied Go to the DisplayDebugMessage method. Based on the three Attribute described above, we can guess the output when the program is running: DLLIMPORT Attribute indicates that MessageBox is a function in user32.dll so that we can call this function as internal method. The important point is that Attribute is a class, so DLLIMPORT is also a class, and the Attribute class is instantiated when compiling, rather than being instantiated as usual classes. When Attribute is instantiated, according to the design of the Attribute class, you can use parameters, or without parameters, such as DLLIMPORT with "user32.dll" parameters. Conditional compiles the code that meets the definition of the parameters. If DEBUG is not defined, then the method will not be compiled. The reader can comment about the result of the #define debug and take a look at the result of the output (Release version, in the debug version Conditional debug Always set up). Obsolete indicates that the DispalyDebugMessage method is outdated, it has a better way to replace it, when our program calls a method of declaring Obsolete, then the compiler will give information, Obsolete has other two overloaded version. You can refer to the description of the ObsoleTeattribute class in MSDN. Attribute class In addition to those Attribute derived classes provided by .NET, we can customize our own Attribute, all custom Attributes must be derived from the Attribute class. Now let's take a look at the details of the Attribute class: protected attribute (): Protected constructor can only be called by Attribute derived class. Three static methods: Static Attribute getCustomAttribute (): This method has 8 overloaded versions, which are used to remove Attribute applied to the specified type on the class member. Static Attribute [] getCustomAttributes (): This method has 16 overload versions to remove the Attribute array that applies the specified type on the class member. Static Bool IsDefined (): From eight overload versions, see whether or not to specify custom Attributes are applied to the members of the class. Example method: Bool isdefaultAttRibute (): If the value of Attribute is the default value, then Return true. Bool match (): Indicates whether this Attribute instance is equal to a specified object. Public property: TYPEID: Get a unique identifier that is used to distinguish between different instances of the same Attribute. We simply introduce the method and properties of the Attribute class, and some are inherited from Object. It is not listed here. The following describes how to customize an attribute: Customize an Attribute does not require special knowledge, in fact, and write a class. Custom attribute must be derived from Attribute directly or indirectly, such as: Public mycustomattribute: attribute {...} Here, it is necessary to point out the naming specification of Attribute, which is your Attribute, the class name "Attribute". When your Attribute is applied to a program, the compiler first finds your Attribute, if not found Then it will find the definition of "Attribute Name" Attribute. If you have not found it, then the compiler is wrong. For a custom Attribute, you can define the type of element applied by Attributeusage at attributeusage. Code form as follows: [AttriubteUsage (parameter)] public custom Attribute:. Attribute {...} is very interesting, is itself a AttributeUsage Attribute, which is specifically applied AttributeUsage naturally derived from the Attribute Attribute Attribute class, It has a parameter constructor that is an enumeration type of AttributeTargets. Here is the definition of AttributeTargets: Public Enum AttributeTargets { ALL = 16383, AskSEMBLY = 1, Module = 2, Class = 4, Struct = 8, Enum = 16, Constructor = 32, Method = 64, Property = 128, Field = 256, Event = 512, Interface = 1024, Parameter = 2048, Delegate = 4096, ReturnValue = 8192 } The value of AttributeTARGES as a parameter allows multiple worth combinations to be permitted via "or", if you do not specify parameters, then the default parameter is all. In addition to inheriting Attribute, AttributeUsage defines the following three properties: ALOWMULTIPLE: Reads or sets this property, indicating whether multiple Attributes can be applied to a program element. Inherited: Read or set this property, indicating whether the Attribute that is applied can be derived or overloaded. Validon: Reads or sets this property, indicating the type of Elements that Attribute can be applied. Use examples of AttributeUSAGE: Using system; Namespace Atttargscs { // This Attribute is only valid for classes. [AttributeUsage (AttributeTargets.class)] Public Class ClassTargettattribute: Attribute: Attribute { } // The Attribute is only valid for the method. [AttributeUSAG (AttributeTargets.method)] Public Class MethodTargetTribute: Attribute: Attribute { } // The Attribute is only valid for the constructor. [AttributeUSAGE (AttributeTargets.constructor)] Public Class ConstructOrgetAtttribute: Attribute { } // The Attribute is only valid for the field. [AttributeUSAGE (AttributeTargets.field)] Public Class FieldTargettattribute: Attribute: Attribute { } // This Attribute is valid (combined) for class or method. [AttributeUSAGE (AttributeTargets.class | attributeTargets.method)] Public Class ClassMethodtargetAtttribute: Attribute { } // The Attribute is valid for all elements. [AttributeUSAGE (AttributeTargets.all)] Public Class AllTargetsAttribute: Attribute { } / / The usage of Attribute is applied to the program element. [ClassTarget] // Application to the class [ClassMethodTarget] // Application to the class [Alltargets] // Application to class Public Class TestClassAttribute { [ConstructOrget] // Application to the constructor [AllTargets] // Application to the constructor TestClassAttribute () { } [MethodTarget] // Application to Method [ClassMethodTarget] // Application to Method [AllTargets] // applied to method Public void method1 () { } [FieldTarget] // Apply to the field [AllTargets] // Application to field Public int myint; Static void main (string [] args) { } } } At this point, we introduced the Attribute class and their code format. You must know how to use Attribute in your app, if it is just the content introduced, it is not enough to explain what is the practical value of Attribute, then we will introduce a few Attribute, which will introduce several Attribute, I believe you will have a new understanding of Attribute. (to be continued)
