We recommend powerful ORM profiler and a powerful tool for tracking ORM problems.

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.