Get SQL without DbMonitor?

Get SQL without DbMonitor?

Postby hudgeo » Tue 02 Feb 2010 21:26

Is there a way I can retrieve the SQL being generated and ran programmatically? In other words, without using OracleMonitor and the dbMonitor tool?

Thanks
hudgeo
 
Posts: 10
Joined: Mon 01 Feb 2010 19:09

Postby Shalex » Wed 03 Feb 2010 10:05

If you don't want to use the dbMonitor application for tracing generated SQL, there are additional ways:
1) subscribe to TraceEvent (Inherited from Devart.Common.DbMonitor) of the OracleMonitor class. An example of its usage is available at http://www.devart.com/dotconnect/oracle/docs/ , the Index tab, the TraceEvent Event section;
2) in the case of LINQ, please use the Log property of DataContext:
Code: Select all
      DataContext db = ...
      StringBuilder sb = new StringBuilder();
      db.Log = new StringWriter(sb);

3) Entity Framework: the select queries can be traced with the ObjectQuery.ToTraceString Method (http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring.aspx).
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Postby hudgeo » Wed 03 Feb 2010 14:07

Thanks, although I am not sure any of these 3 will help as:

1) This solution only addresses half of the problem as I did not want to use dbMonitor or OracleMonitor and this still uses OracleMonitor and requires it be active. Part of the problem with that is there are hidden performance problems with many exceptions being generated and catched when dbMonitor is not running. See http://www.devart.com/forums/viewtopic. ... c20343527d

2) We are using Entity Framework and not linq so I'm dealing with an ObjectContext not a DataContext so there is no Log property.

3) ObjectQuery : this may work but all the examples I see are building a string to be executed so it seems pointless. Not sure how to get an IQueryable linq expression into an ObjectQuery to use the ToTraceString() method.
hudgeo
 
Posts: 10
Joined: Mon 01 Feb 2010 19:09

Postby Shalex » Thu 04 Feb 2010 10:57

1) We will post here when we correct the behaviour of the OracleMonitor class (the exceptions issue when the dbMonitor tool is not launched).

3) IQueryable linq expression can be cast to ObjectQuery:
Code: Select all
         var query = from d in context.Depts
                 where d.Deptno < 1000
                 select d;
         string sql = (query as ObjectQuery).ToTraceString();
         foreach (Dept item in query) {
           // ...
         }
Last edited by Shalex on Thu 04 Feb 2010 16:03, edited 1 time in total.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Postby hudgeo » Thu 04 Feb 2010 13:35

Perfect, thanks much
hudgeo
 
Posts: 10
Joined: Mon 01 Feb 2010 19:09

Postby StanislavK » Thu 18 Feb 2010 13:43

We have added the UseApp property to the DbMonitor class, and thus to the descendant OracleMonitor class as well. It specifies whether the DbMonitor instance in the application will try to connect dbMonitor.exe.

Please try the new 5.55.97 Beta build of dotConnect for Oracle. It can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(trial version) or from Registered Users' Area (for users with active subscription only). For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=17143
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle