Use system.reflection.emit to generate call storage procedures

/ ************************************************** **************** * * Use system.reflection.emit to generate the realization of call storage procedures! * * By http://lostinet.com * * Copyrights: not-reverse * / ****************************************** ******************************* /

// Use example namespace lostinet.sample {using system; using system.data; using system.data.sqlclient; use system.windows.form;

/ / Define an interface to define the stored procedure

Interface inorthWindStoredProcedProcedures {// Define the method of the stored procedure

DataSet CustOrd (String Customerid);

// If the stored procedure name and method name are different, you should use SQLAccessAttribute to explain [SQLACCESS ("Employee Sales By Country)] DataTable EmployeesaSbyCountry (DateTime Beginning_date, DateTime Ending_date);

// ... more ...

// more ideas ..

/ / Direct SQL statement directly? //[Sqlaccess (sqlaccesstype.sqlQuery, "select * from employees where urgeyeeid = @ Empid ")] // DataTable selectemPloyee (int EmpID);}

class ConsoleApplication {[STAThread] static void Main (string [] args) {using (SqlConnection conn = new SqlConnection ( "server = (local); trusted_connection = true; database = northwind")) {// implementation creates sentence put ! // Need to be like SqlConnection and SqlTransaction // SqlTransaction can be null

// This is nice, can be obtained as long as SqlConnection / SqlTransaction able to use this method, so compatible Lostinet.Data.SqlScope INorthwindStoredProcedures nsp = (INorthwindStoredProcedures) StoredProcedure.CreateStoredProcedureInterface (typeof (INorthwindStoredProcedures), conn, null);

// Call the storage process and display

Showdata ("CustOrhist Alfki", Nsp.CustOrhist ("Alfki")); showdata ("Employee Sales By Country, Nsp.employeesaSbyCountry (New DateTime (1998, 1, 1), New DateTime (1999, 1, 1))) );

}

Static void showdata (string title, object data) {form f = new form (); f.Width = 600; f.height = 480; f.Text = title;

DataGrid Grid = New DataGrid (); Grid.dock = DockStyle.Fill; Grid.DataSource = DATA;

F.Controls.Add (grid); f.Showdialog ();

}

#REGON / / Implementation method (incomplete) Namespace Lostinet.sample {using system; using system.collections; useing system.reflection; using system.data; using system.data.sqlclient;

// This class is the base class for implementation, // is the purpose of providing some methods of saving SQLConnection / SqlTransAction and public use must be public, otherwise the developer does not explicitly access this class public Class SpinterfaceBase : Idisposable {public spinterfacebase () {}

Public void dispose () {}

// CreateStoredProcedureInterface will save the associated value SQLCONNECTION / SQLTRRANSACTION here public SqlConnection Connection; Public SqlTransaction Transaction;

// Create a SqlCommand public SqlCommand CreateCommand (string spname) {SqlCommand cmd = new SqlCommand (spname, connection, transaction); cmd.CommandType = CommandType.StoredProcedure; // TODO: //cmd.Parameters.Add("@ReturnValue " , ... Return CMD;

// Established SqldbType by Type, unfinished SQLDBTYPE GETSQLDBTYPE (Type Type) {///todo:switch (Type )..Return Sqldbtype.nvarchar;}

// define parameters public void DefineParameter (SqlCommand cmd, string name, Type type, ParameterDirection direction) {SqlParameter param = new SqlParameter ( "@" name, GetSqlDbType (type)); param.Direction = direction; cmd.Parameters.Add (Param);

// Set the parameter value PUBLIC VOID SetParameter (sqlcommand cmd, string name, object value) {cmd.parameters ["@" name] .value = (value == null);} // After the SQLCommand is executed, the parameter value public object getParameter (SQLCOMMAND CMD, String Name) {return cmd.parameters [@ " name] .value;}

/ / Perform different operations according to different return values

public SqlDataReader ExecuteDataReader (SqlCommand cmd) {return cmd.ExecuteReader ();} public object ExecuteScalar (SqlCommand cmd) {return cmd.ExecuteScalar ();} public void ExecuteNonQuery (SqlCommand cmd) {cmd.ExecuteNonQuery ();} public DataSet ExecuteDataSet (SqlCommand cmd) {DataSet ds = new DataSet (); using (SqlDataAdapter sda = new SqlDataAdapter (cmd)) {sda.Fill (ds);} return ds;} public DataTable ExecuteDataTable (SqlCommand cmd) {DataTable table = new DataTable (); Using (SqlDataAdapter SDA = New SqldataAdapter (CMD)) {sda.fill (Table);} Return Table; DataRow ExecuteTarow (SQLCOMMAND CMD) {DataTable Table = ExecuteTedATABLE (CMD); if (Table.Rows.count == 0) Return Null; Return Table.Rows [0];}}

public class StoredProcedure {static public object CreateStoredProcedureInterface (Type interfaceType, SqlConnection connection, SqlTransaction transaction) {// check parameters if (interfaceType == null) throw (new ArgumentNullException ( "interfaceType")); (! interfaceType.IsInterface) if throw ( new ArgumentException ( "argument is not interface", "interfaceType")); if (connection == null) throw (new ArgumentNullException ( "connection"));!! if (transaction = null) {if (transaction.Connection = connection "New ArgumentException (" Transaction.Connection! = Connection "," Transaction "));} // Create StoredProcedure

STOREDPROCEDURE SPEMIT = New StoredProcedure (); SPEMIT.ITERFACITYPE = InterfaceType; SPEMIT.CONNECTION = Connection; SPEMIT.TRANSACTION = Transaction;

// Create Return SPEMIT.CREATEINSTANCE ();

// Used to store the created type Static HashTable EmittedTypes = new hashtable (); type interface; sqlConnection connection; sqltransaction

Private storedprocedure () {}

Object createInstance () {lock (interface (interface (interface (interface "{// If there is no specific implementation, create it

IF (EmittedType == Null) {EmittedType = (Type) EmittedTypes [InterfacePe];

IF (EmitType == NULL) {createType (); // Store created type EmittedTypes [interfaceType] = EmittedType;}}}

// Create a specific instance spinterfacebase spi = (spinterfacebase) activator.createinstance (EmittedType);

// Set sqlconnection / sqltransaction spi.connection = connection; spi.transaction = transaction;

Return SPI;

TYPE EmittedType;

Typebuilder Typebuilder;

// Create the type of void CreateType () {// create Assembly //AssemblyBuilderAccess.Run- indicates that only used to run, not on the disk to save AssemblyName an = new AssemblyName (); an.Name = "Assembly." InterfaceType.FullName " . IMPLEMENTATION "; askMBLYBUILDER Asmbuilder = Appdomain.currentDomain.defineDynamicassembly (an, assemblybuilderccess.run);

// Create Modui ModuleBuilder MDLBuilder = asmbuilder.defineDynamicModule ("Module." InterfacePe.FullName ". Implementation");

// Create Type, inherits spinterfacebase type typebuilder = mdlbuilder.definetype (InterfaType.FullName ". Implementation", typettributes.class, typeof (spinterfacebase);

// Implement all interface methods EMITINTERFACE (InterfaType);

// If InterfaType is based on other interfaces (Type Subinterface ") {// idisposable does not need to be implemented, the spinterfacebase implements if (Subinterface == TypeOf (iDisposable)) Continue;

EmitInterface;} EmittedType = Typebuilder.createType ();

Void EmitInterface (Type Type) {// Implement the interface typebuilder.addinterfaceImplement (Type);

// List the member of the interface for Foreach (MemberInflags.instance | BindingFlags.Public)) {// Convention - Members must be a method, no properties, IFs such as events (Member.Membertype! = MEMBERTYPES.METHOD) THROW (New Exception MEMBER.MEMBERTYPE "Automatically!");

// Get the method defined in the interface MethodInfo Method = (MethodInfo) MEMBER;

// Calculate new methods properties, properties that are replicated in the method over and not Public / Abstract, plus Private MethodAttributes methodattrs = method.Attributes; methodattrs & = ~ (MethodAttributes.Public | MethodAttributes.Abstract); methodattrs | = MethodAttributes .PriVate;

Parameterinfo [] paraminfos = method.getParameters (); int paramlength = paraminfos.length;

// Number of types of parameters Type [] paramtypes = new type [paramth]; for (int i = 0; i

// Establish a new method on TypeBuilder, the parameter type and the return type are consistent with the method of the interface Methodbuilder Mthbuilder = typebuilder.defineMethod (Method.Name, Methodattrs, method.callingconvention, method.Returntype, paramtypes);

/ / Copy the name and attribute for the parameters on the new method for (int i = 0; i

/ / Define a field on the type, this field is used to store the SQLCommand FieldBuilder Field_cmd = typebuild.definefield ("_ cmd _" method.name, typeof (sqlcommand), Fieldattributes.private;

// ilgenerator is an object ILGENERATOR ILG = mthbuilder.getilGenerator () for generating an object that implements code.

// Define a temporary variable localbuilder local_res = ilg.declareLocal (TypeOf (Object));

/ / Define a label label label_cmd_ready = ilg.defineLabel () for jump.

//this._cmd_methodname ilg.emit (opcodes.ldarg_0); // this ilg.emit (opcodes.ldfld, field_cmd); //._ cmd_methodname

//if (THIS._CMD_METHODNAME !=NULL) jumps to label_cmd_ready ilg.emit (opcodes.brtrue, label_cmd_ready);

// If this._cmd_methodname is NULL, run the following code to create SQLCommand

//this._cmd_methodname=this.createCommand ("methodname "); Ilg.emit (opcodes.ldarg_0);

//this.createCommand Ilg.emit (opcodes.ldarg_0); // Parameter 0 ILG.Emit (opcodes.ldstr, sqlaccessattribute.getspname (Method)); // Parameter 1 // Call ilg.emit (Opcodes.callvirt, Typeof (Spinterfacebase) .getMethod ("CreateCommand", BindingFlags.instance | BindingFlags.public);

ilg.Emit (OpCodes.Stfld, field_cmd); // ._cmd_MethodName = // this.DefineParameter (...) if (! paramlength = 0) {// obtain DefineParameter REFERENCE MethodInfo method_DefineParameter = typeof (SPInterfaceBase) .GetMethod ( "DefineParameter", bindingflags.instance | bindingflags.public); for (int i = 0; i

//this.defineParameter(th._cmd_methodname, "parameeeeeeee "", ParameterDirection.xxx);

// Parameter 0 - this ilg.emit (opcodes.ldarg_0);

// Parameter 1 - this._cmd_methodname ilg.emit (opcodes.ldarg_0); Ilg.emit (Opcodes.ldfld, Field_CMD);

// Parameter 2 - "ParameterName" ilg.emit (opcodes.ldstr, pi.name);

// Parameter 3 - TypeOf (ParameterType) ilg.emit (opcodes.ldtoken, pi.parameterType);

// Parameter 4 - ParameterDirection.xxx if (pi.ParameterType.isByref) {ilg.emit (Opcodes.ldc_i4, (int) parameterdirection.inputoutput);} else if (pi.isout) {ipg.emit (opcodes.ldc_i4, (int) parameterdirection.output;} else {ilg.emit (opcodes.ldc_i4, (int) parameterDirection.input);} // call defineParameter ilg.emit (opcodes.callvirt, method_defineparameter);}} // Cmd_commandname has OK.

// Set label_cmd_ready refers to ilg.marklabel (label_cmd_ready); // cmd! = Null now.

IF (paramlength! = 0) {// Now set the value of the parameters of the method to SQLParameter

MethodInfo method_SetParameter = typeof (SPInterfaceBase) .GetMethod ( "SetParameter", BindingFlags.Instance | BindingFlags.Public); for (int i = 0; i

// If the parameter is OUT, you do not need to set if (! Pi.parametertype.isbyref && pi.isout) Continue;

//this.SetParameter (THIS._CMD_METHODNAME, "Parametername ";ilg.emit (opcodes.ldarg_0); ilg.emit (opcodes.ldarg_0); Ilg.emit (Opcodes.ldfld, Field_CMD);

Ilg.emit (opcodes.ldstr, pi.name);

// Get the parameter value, if the parameter is ValueType, then Box to Object Ilg.emit (OpCodes.ldarg, i 1); if (Pi.ParameterType.isValueType) ilg.emit (opcodes.box, pi.parametertype);

Ilg.emit (opcodes.callvirt, method_setparameter);}}

/ / Now to perform a storage process (executive sqlcommand)

// This here, based on the return value type, how to perform SQLCommand

TYPE RTURNTYPE = Method.Returntype;

// If it is void, you do not need to return a value Bool Nores = RetURNTYPE == TypeOf (Void);

MethodInfo method_execute = NULL;

if (nores) {// no return value method_Execute = typeof (SPInterfaceBase) .GetMethod ( "ExecuteNonQuery", BindingFlags.Instance | BindingFlags.Public);} else if (returnType == typeof (object)) {// return object method_Execute = typeof (SPInterfaceBase) .GetMethod ( "ExecuteScalar", BindingFlags.Instance | BindingFlags.Public);} else if (returnType == typeof (DataSet)) {// return DataSet method_Execute = typeof (SPInterfaceBase) .GetMethod ( "ExecuteDataSet ", Bindingflags.instance | bindingflags.public);} else if (returnty == typeof (data)) {// Return DataTable method_execute = typeof (Spin terfaceBase) .GetMethod ( "ExecuteDataTable", BindingFlags.Instance | BindingFlags.Public);} else if (returnType == typeof (DataRow)) {// return DataRow method_Execute = typeof (SPInterfaceBase) .GetMethod ( "ExecuteDataRow", BindingFlags. Instance | bindingflags.public);

} Else {// return other types foreach (Type retInterface in returnType.GetInterfaces ()) {// if it is returned IDataReader if (retInterface == typeof (IDataReader)) {// only supports SqlDataReader if (! ReturnType.IsAssignableFrom (typeof (SqlDataReader))) throw (new Exception ( "SqlDataReader could not convert to" returnType.FullName)); method_Execute = typeof (SPInterfaceBase) .GetMethod ( "ExecuteDataReader", BindingFlags.Instance | BindingFlags.Public); break;}} }

/ / If the appropriate policy is not found, if (Method_execute == Null) {// Todo: Of course, there should be returning Int32, string, ..., but too lazy to write.

// Throw an exception, the prompt does not support the return type, the author is changed :) throw ("Notsupport ReturntYpe:" Returntype.Fullname);}

//this.ExecuteXXX(this._cmd_MethodName) ilg.Emit (OpCodes.Ldarg_0); ilg.Emit (OpCodes.Ldarg_0); ilg.Emit (OpCodes.Ldfld, field_cmd); ilg.Emit (OpCodes.Callvirt, method_Execute);

// If there is a return value, it is //local_res=this.executexxxx(this_cmd_methodname) ife (! Nores) {if (returntype.isvalueType) ilg.emit (opcodes.box, returnty); Ilg.emit (opcodes .Stloc, local_res);!} if (paramlength = 0) {// here treated ref / out parameters MethodInfo method_GetParameter = typeof (SPInterfaceBase) .GetMethod ( "GetParameter", BindingFlags.Instance | BindingFlags.Public); for (int I = 0; i

// If it is not REF / OUT, skip the IF (! Pi.parametertype.isbyref &&! Pi.isout) Continue;

//Parametername =tHis.getParameter (this._cmd_methodname ") ilg.emit (opcodes.ldarg_0); ilg.emit (opcodes.ldarg_0); ilg.emit (opcodes.ldfld, field_cmd);

Ilg.emit (opcodes.ldstr, pi.name);

Ilg.emit (opcodes.callvirt, method_getparameter);

// If the type is the value type, you need UNBOX if (Pi.ParameterType.isValueType) ilg.emit (opcodes.unbox, pi.parametertype);

Ilg.emit (Opcodes.Starg, i 1);}}

// If it is void, then directly return; // is return local_res, if the return value type is ValueType, you need UNBOX if (! NORES) {ilg.emit (opcodes.ldloc, local_res); if (Returntype.IsValueType ) ilg.Emit (OpCodes.Unbox, returnType);} ilg.Emit (OpCodes.Ret); // // throw (new NotImplementedException ()); // ilg.Emit (OpCodes.Newobj, typeof (NotImplementedException) .GetConstructor (New Type [0]))); // ilg.emit (opcodes.throw);

}}}

Public Enum SQLACCESSTYPE {StoredProcedure // Todo: //, SQLQuery}

[AttributeUSAGE (AttributeTargets.method)] public class sqlaccessattribute: attribute {string _sp;

Public SQLACCESSATTRIBUTE (STRING SPNAME) {_SP = SPNAME;

Public string storeprocedure {get {return _sp;}}

Static Public String GetSpname (Method Info Method) {if (Method == Null) Throw ("Method");

Object [] attrs = method.getcustomattributes (TypeOf (SqlaccessAttribute), false; if (attrs == null || attrs.length == 0) Return Method.Name;

Return (SQLACCESSATTRIBUTE) Attrs [0]). storeprocedure;}

// Todo: // public sqlaccessattribute (SQLACCESSTYPE TYPE, STRING TEXT) / / {////}}} #ENDREGON