Niwalker (original) SQLCommandgenerator class design SQLCommandGenerator class design idea is to assemble a Command instance by reflecting the parameters of the method.
.
Quote Namespace:
//Sqlcommandgenerator.cs
Using system; 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, does not allow parameters to construct an instance private sqlcommandgenerator () {throw new notsupportedException ();
// Static read-only field, define parameter names for return values PUBLIC STATIC READONLY STRING RETURNVALUEPARTERETERNAME = "Return_Value"; // Static read-only field, stored procedure for non-parameters Public Static Readonly Object [] Novalues = new Object [] {}; Public static sqlcommand generateCommand (SqlConnection Connection, MethodInfo Method, Object [] VALUES) {// If there is no method name, the method name IF is obtained from the stack frame (Method == null) method = (MethodInfo) (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.It) .direction = parameterDirection.ReturnValue;
Return Command;
Private static void generatecommandparameters (Sqlcommand Command, MethodInfo Method, Object [] VALUES) {
// Get all parameters, processed by looping. 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; // acquiring parameters SqlParameter attribute, if not specified, Just create a default settings using it. 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; else sqlParameter.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.Input;}} // sufficient parameter whether the object of detection values Debug.Assert (paramIndex Now we start to 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 telephone, out int customerId) {customerId = 0; // initialize output parameters need // call Command generator generates SqlCommand instance SqlCommand command = SqlCommandGenerator .GenerateCommand (connection, null, new object [] {customerName, country, province, city, address, telephone, customerId}); connection.Open (); command.ExecuteNonQuery (); connection.Close (); // be clear Returns the value of the output parameter 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. Benefiting from Attribute, let us get rid of that Write a lot of boring code programming career. 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 code shown in the previous section and this section, you can compile them into a component so you can keep them in your project. From the next section, we will be deeper Level Introduction Attribute Application, please continue to pay attention.
