1

Some context:

I have a project with both MSSQL and Oracle connections using the classes below:

Oracle.ManagedDataAccess.Client.OracleConnection; // from nuget: Oracle.ManagedDataAccess
System.Data.SqlClient.SqlConnection; // from .net framework System.Data.dll

In sql server exists a tool called profile. I'm not knowledgeable in oracle enough to know a similar tool, but I'm guess it has one.

For many reasons (db team not allowed, low performance, etc...) I cannot turn on SQL Server Profiler.

Try 1:

So I'm researching for something in c# that allow me to get flux of SQL passed by IDbConnection (and IDbCommand, or others).

Till now I wasn't lucky.

Try 2:

I'm even try create myself a wrapper class to involve a IDbConnection and it's helpers (like IDbCommand):

public class ProfiledConnection : IDbConnection
{
    private readonly IDbConnection _con;

    public ProfiledConnection(IDbConnection originalConnection)
    {
        _con = originalConnection;
    }

    public void Dispose() => _con.Dispose();

    public IDbTransaction BeginTransaction() 
    {
        // some code to log what's going on
        return _con.BeginTransaction();
    }

    public IDbCommand CreateCommand()
    {
        // some code to log what's going on
        return new ProfiledCommand(_con.CreateCommand());
    }

    // all IDbConnection members implemented with the same approach...
}

public class ProfiledCommand : IDbCommand
{
    private readonly IDbCommand _cmd;

    public ProfiledCommand(IDbCommand originalCommand)
    {
        // some code to log what's going on
        _cmd = originalCommand;
    }

    public void Dispose() => _cmd.Dispose();

    public void Prepare()
    {
        // some code to log what's going on
        _cmd.Prepare();
    }

    // all IDbCommand members implemented with the same approach...
}

But I wasn't successful with this either. I have unit tests of my application and when I changed the connection factory to return something like:

new ProfiledConnection(new OracleConnection("myconnectionstring"));

Many tests failed, with all different types of errors, it was so frustrating, I were so sure I was going in the right direction.

Request:

Now I'm lost, can anyone suggest some component, package or approach to accomplish a "SQL Profile inside the application".

Jonny Piazzi
  • 3,684
  • 4
  • 34
  • 81
  • What information are you trying to gather and why? Do you have DBAs who can run a profiler session for you? – MJH Apr 04 '18 at 16:15
  • What you have there looks a lot like what miniprofiler does - via `ProfiledDbConnection`; maybe just try installing it [from nuget](https://miniprofiler.com/dotnet/NuGet)? but: without knowing which errors you're seeing, it is hard to comment on whether it'll work for you; obviously, `ProfiledDbConnection` has seen some pretty vigorous usage, though, so there's a good chance it'll work – Marc Gravell Apr 04 '18 at 16:25
  • I'm trying to see all queries (DDL, DML) that are generated from ORMs. – Jonny Piazzi Apr 04 '18 at 16:52
  • Which ORM are you using specifically? For instance, if it's Entity Framework 6+ this solution seems like it could accomplish what you're looking for: https://stackoverflow.com/a/20751723/1549649 – jozolo Sep 21 '18 at 15:54

0 Answers0