Get SQL without DbMonitor?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hudgeo
Posts: 10
Joined: Mon 01 Feb 2010 19:09

Get SQL without DbMonitor?

Post by 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

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by 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 ... tring.aspx).

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

Post by 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.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by 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.

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

Post by hudgeo » Thu 04 Feb 2010 13:35

Perfect, thanks much

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by 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

Post Reply