When llbl gen is used as an ORM framework to access the database, data read/write is no longer directly related to SQL. Read sales ticketCodeIt looks like this.
PublicSalesorderentity getsalesorder (system. String refno, iprefetchpath2 prefetchpath, excludemo-defieldslist fieldlist) {salesorderentity _ salesorder =NewSalesorderentity (refno );Using(Dataaccessadapterbase adapter = getcompanydataaccessadapter ()){BoolFound = Adapter. fetchentity (_ salesorder, prefetchpath,Null, Fieldlist );If(! Found)Throw NewFoundation. Common. recordnotfoundexception ("Invalid sales order");}Return_ Salesorder ;}
At the interface layer, the following code reads the sales ticket
Public OverrideEntitybase2 loadentity (StringCustomerno) {isalesordermanager manager = clientproxyfactory. createproxyinstance <isalesordermanager> (); iprefetchpath2 prefetchpath =NewPrefetchpath2 ((Int) Foundation. customerservices. entitytype. salesorderentity); prefetchpath. Add (salesorderentity. prefetchpathsalesorderdetails); salesorderentity customer = manager. getsalesorder (customerno, prefetchpath );ReturnCustomer ;}
This greatly simplifies the database access method. But at the same time, it also brings some inconvenience. For example, since ORM generates SQL statements in the form of dynamic SQL, it cannot trace the original SQL statements that are finally sent to the SQL server in the form of parameter transmission. Second, the behaviors produced by ORM are somewhat different from those produced by patchwork SQL.
For example, salesorderentity order = new salesorderentity ();
Order. Description = NULL; => the SQL statement is salesorder. description is null.
Order. Description = string. Empty; => the SQL statement is salesorder. Description =''
That is, null and null strings have different meanings. In this case, it is very difficult to track SQL queries for problems.
Before you release the ORM profile, you need to configure the configuration file as follows to track the SQL statements generated by llbl, and apply the standard. Net trace mechanism.
< System. Diagnostics > <! -- Llblgen trace Trace Level: 0-Disabled 3-Info 4-verbose <Switches> <Add name = "sqlserverdqe" value = "0"/> <Add name = "ormgeneral" value = "0"/> <Add name = "ormstatemanagement" value = "0"/> <Add name = "ormpersistenceexecution" value = "0"/> </Switches> <Trace autoflush = "true"> <Listeners> <Add name = "textwritertracelistener" Type = "system. Diagnostics. textwritertracelistener" Initializedata = "E: \ ERP \ erp_log.txt"/> </Listeners> </Trace> --> </ System. Diagnostics >
In this way, the SQL statements generated by llbl Gen are written to a text file for diagnosing the problem.
With the help of the. NET standard trace mechanism, I have rewritten this trace to send the SQL statements generated by llbl Gen to the specifiedProgramIn the window, the Code is as follows:
Public Class Ormtracelistener: tracelistener { Static Socket m_socclient; Static Bool M_server = False ; Static Ormtracelistener () {m_server = False ; Onconnect ( "127.0.0.1" , 8221 );} Public Override Void Write ( String Message ){ If (! String. isnullorempty (Message) & m_server & needsend (Message) onsenddata (Message );} Public Override Void Writeline ( String Message ){ If (! String. isnullorempty (Message) & m_server & needsend (Message) onsenddata (Message + environment. newline );}}
After such settings, the pseudo SQL we tracked looks like this
Generated SQL Query: Select [Enterprise]. [DBO]. [ User ]. [Userid] As [Userid], [enterprise]. [DBO]. [User ]. [Username], [enterprise]. [DBO]. [ User ]. [Password], [enterprise]. [DBO]. [ User ]. [Usergroup], [enterprise]. [DBO]. [ User ]. [Suincluded], [enterprise]. [DBO]. [ User ]. [Defaultlang], [enterprise]. [DBO]. [ User ]. [Createddate], [enterprise]. [DBO]. [ User ]. [Createdby], [enterprise]. [DBO]. [ User ]. [Reviseddate], [enterprise]. [DBO]. [ User ]. [Revisedby], [enterprise]. [DBO]. [ User ]. [Email], [enterprise]. [DBO]. [ User ]. [Allcompany], [enterprise]. [DBO]. [ User ]. [Allcustomer], [enterprise]. [DBO]. [ User ]. [Allvendor], [enterprise]. [DBO]. [ User ]. [Buyer], [enterprise]. [DBO]. [ User ]. [Salesman], [enterprise]. [DBO]. [ User ]. [Allreportview] From [Enterprise]. [DBO]. [ User ] Where ([Enterprise]. [DBO]. [ User ]. [Userid] = @ userid1 )) Parameter : @ Userid1: String. Length: 10. Precision : 0. Scale: 0. Direction:Input . Value : "Mis ".
This cannot be directly executed in the query analyzer and must be converted.
This is very close to the ORM profile goal. The official ORM profile tool is designed to optimize the above goal.
To enable your program to start SQL tracking, you only need to add the following statement at the place where the program starts.
SD. Tools. ormprofiler. Interceptor. interceptorcore. initialize ("Enterprise Solution");
You can also call the preceding statements to start SQL tracing. There is code in the help document. Copy a copy here for your reference.
// Thanks to Craig stuntz Private Static Void Startprofiler (){ If (Appsettings [ "Ormprofiler" ] = "1" ) {System. reflection. Assembly orminterceptor = system. reflection. Assembly. Load ( "SD. Tools. ormprofiler. Interceptor. efv4" ); Type Interceptor = orminterceptor. GetType ( "SD. Tools. ormprofiler. Interceptor. interceptorcore" ); Interceptor. invokemember ( "Initialize" , System. reflection. bindingflags. Public | system. reflection. bindingflags. invokemethod | system. reflection. bindingflags. Static,Null , Null , New [] { "My. Web" }, System. Globalization. cultureinfo. currentuiculture );}}
Reflection reduces the dependency on Assembly compilation. You only need to copy the following assembly to the tracked application directory.
- SD. Tools. ormprofiler. Interceptor. dll (or for efv4: SD. Tools. ormprofiler. Interceptor. efv4.dll)
- SD. Tools. ormprofiler. Shared. dll
- SD. Tools. bclextensions. dll
- SD. Tools. algorithmia. dll
In this way, the SQL statement generated by llbl Gen of the current program will be sent to the primary form of The ORM profile for analysis.
The second connections in chronological order tab is often used for problem searching.
It displays the SQL statements for database connection and sending. As you can see, the ORM profile can not only trace SQL, but also be used for performance analysis. For example, this SQL statement returns four pieces of data, and the DB read time is 123.20 Ms. In the panel below, you can see various SQL analysis. On the SQL panel, the displayed SQL is similar to the preceding one. If you want to copy the SQL statement to the query analyzer, the result is as follows:
Select[Framework]. [DBO]. [User]. [Userid]As[Userid], [framework]. [DBO]. [User]. [Username], [framework]. [DBO]. [User]. [Password], [framework]. [DBO]. [User]. [Usergroup], [framework]. [DBO]. [User]. [Suincluded]From[Framework]. [DBO]. [User]Where([Framework]. [DBO]. [User]. [Userid] ='Mis'/* @ P1 */))
As you can see, this tool has directly filled the parameters in the SQL statement and can be directly executed in the query analyzer without any modification.
Orm profile calls every trace session snapshot. In the SQL document, this word is translated as a snapshot and can be understood as a trace session. It can be saved on the hard disk for further analysis.
For the win froms program, if initialize and WPF programs are called in the main method, initialize is called in the rewritten application onstartup, and the Web application is in the global. the application_start method in asax calls initialize.
In actual products, profile will produce performance loss, occupying additional memory and sending bytes to the orm profiler main program. After the interceptorcore. initialize method is called, the tracing starts. We can use the method
Interceptorcore. disablemessagesending (); stop tracking
You can also enable tracing again. Apply the following method:
Interceptorcore. enablemessagesending ();
If you do not want to enable the ORM profile to intercept SQL tracing messages every time, you can save SQL tracing directly to a file in the code, use the ORM profile to open the tracking file for analysis. The following code is from the help file for your reference.
// Default ctor call, no filters, default analysis settings. You can specify filter settings and // Analysis settings in an overload of the ctor. VaR snapshot = New Snapshot (); snapshot. Record (); // Execute database activity here. //... // Make sure the interceptor has flushed all messages over the named pipe. Interceptorcore. Flush (); // Messages arrive on another thread, so we have to wait here before all messages have arrived Thread. Sleep (200 ); // After the pause, we can stop recording. Snapshot. Stop (); // If you 've specified filter settings in the ctor call, you have to do the following: Snapshot. applyfilters (); // If you want to perform analysis in Code prior to saving the snapshot, you shoshould // Uncomment the following line. Note: requires license file to be present. // Snapshot. applyanalysis (); // The snapshot can now be saved: String Error = String . Empty; snapshot. savetofile (filename, Out Error );
The ORM profile tool is quite flexible and can be used for programming to integrate into your own products to add efficient tools for your system stability and performance improvement.