Application of Attribute in .NET Programming (4)
Design of SQLCommandGenerator class
The design idea of the SQLCommandGenerator class is to assemble a Command instance by reflecting the parameters of the method.
Quote Namespace:
//Sqlcommandgenerator.cs
Using 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 Sealed Class SqlcommandGenerator
{
// Private constructor, not allowing parameters to construct an example
Private sqlcommandgenerator ()
{
Throw new NotSupportedException ();
}
// Static read-only field, define the parameter name used to return values
Public static readonly string returnValueParameterName = "Return_Value";
// Static read-only field, used for stored procedures without parameters
Public static readonly object [] novalues = new object [] {};
Public Static SqlCommand GenerateCommand (SqlConnection Connection,
MethodInfo Method, Object [] VALUES
{
// If there is no specified method name, get the method name from the stack frame
IF (Method == Null)
.................................. ..
// Get the SQLCommandMethodAttribute sent by the method
/ / In order to use this method to generate a Command object, this Attribute is required.
SQLCommandMethodAttribute Commandattribute =
(SQLCommandMethodattribute) attribute.getcustomattribute (Method, TypeOf (SqlCommandMethodAttribute);
Debug.assert (Commandattribute! = NULL);
Debug.assert (commandttribute.commandtype == comMMandType.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 the text of the Command, if not specified, the name of the method is used as a stored procedure name
IF (commandttribute.commandtext.length == 0) {
Debug.Assert (Commandattribute.commandType == CommandType.StoredProcedure);
Command.comMandText = method.name;
}
Else
{
Command.commandtext = Commandattribute.commandText;
}
// Call the GeneratorCommandParameters method, generate a Command parameter, and add a return value parameter
GenerateCommandparameters (Command, Method, Values);
Command.Parameters.add (ReturnValueParameterName, SqldbType.It) .direction
= ParameterDirection.ReturnValue;
Return Command;
}
Private static void generateCommandparameters
SQLCommand Command, MethodInfo Method, Object [] VALUES)
{
// Get all parameters, processed by looping.
ParameterInfo [] method.com = method.getParameters ();
INT paramindex = 0;
Foreach (ParameterInfo Paraminfo In Methodparameters)
{
/ / Ignore the parameters to be marked as [NoncommandParameter]
Attribute.Indefined (paraminfo, typeof (noncommandparameterattribute))))
CONTINUE;
// Get the parameters of SQLParameter Attribute, if not specified, then create an default settings using it.
SQLParameterattribute Paramattribute = (Sqlparameterattribute) Attribute.getCustomattribute (
PARAMINFO, TYPEOF (SQLParameterattribute);
IF (Paramattribute == Null)
Paramattribute = New Sqlparameterattribute ();
// Use Attribute settings to configure a parameter object. Use those already defined parameter values. If there is no definition, then it is from way
// The parameters are used to infer its parameter value.
SQLParameter Sqlparameter = New Sqlparameter ();
IF (paramattribute.isnamededefined)
SQLParameter.ParameterName = paramattribute.name;
Else
SQLParameter.ParameterName = paraminfo.name;
IF (! sqlparameter.ParameterName.startSwith ("@")))
SQLParameter.ParameterName = "@" SQLParameter.ParameterName;
IF (paramattribute.istry.iPedefined)
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;
Paramattribute.IndirectionDefined)
{
Sqlparameter.direction = paramattribute.direction;
}
Else
{
IF (paraminfo.ParameterType.isbyref)
{
SQLParameter.direction = paraminfo.isout?
ParameterDirection.output:
ParameterDirection.InputOutput;
}
Else
{
Sqlparameter.direction = parameterdirection.input;
}
}
// Test if a sufficient parameter object provided
Debug.assert (paramindex // Put the corresponding object value to the parameter. Sqlparameter.Value = VALUES [paramindex]; Command.Parameters.Add (Sqlparameter); PARAMINDEX ; } // Test if there are excess parameter object values Debug.Assert (paramindex == VALUES.LENGTH); } } } The necessary work is finally completed. The code in the SQLCommandGenerator has a comment, so it is not difficult to read. Below we entered the final step, that is, using new methods to implement the previous section we first display a method of That AddCustomer. Reconstruct new addCustomer 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 Televhone, Out int custom000 { Customerid = 0; // Need to initialize output parameters // Call the Command Builder Generate SQLCommand Instance Sqlcommand command = sqlcommandgenerator.GenerateCommand (connection, null, new object [] {Customername, Country, Province, City, Address, Telephone, Customerid}); Connection.open (); Command.executenonQuery (); Connection.Close (); / / The value of the output parameter must be explicitly returned Customerid = (int) Command.Parameters ["@ Customerid"]. Value; The code must be noted in the code is the OUT parameter, you need to initialize in advance, and pass the parameter value back to it after the Command is executed. 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. (to be continued)