what is the integration point to track ado.net queries

[joeaudette]

I have a project I'm working on that has a pluggable data layer. When I use the one implemented with Entity Framework 7 then glimpse shows me the number of DB queries generated by the web request.
When I use old fashioned ado.net code glimpse is not showing me the number of db queries. I'm trying to figure out what I need to change in my ado.net code so that glimpse will be able to see the queries generated by my code.
I know that back in mvc 5 I was able to get glimpse to show me db queries by hooking into the DBProviderFactory that was configured in web.config. Obviously this has changed since web.config is now only for IIS settings.
Can anyone provide me a clue about how to make my ado.net code glimpse friendly?

[ScorchedHands]

I'm looking for the same thing. I have alot of ADO code that I'd like to profile.

[avanderhoorn]

At the moment we don't have this extension point built. The intention is that you shouldn't have to do anything manually here as we are working with the ado.net team to instrument it as low down as we possibly can. The end result is that it will just work and the numbers show up regardless of if you are using EF or not.

[Jetski5822]

@avanderhoorn hey man, whats the word on this? I ended up creating proxy objects with Diagnostic sources pumping stuff out, however no CommandText shown yet as its not in hud

[avanderhoorn]

Currently we got an extension point put into SQL ADO.Net... Unfortunately there wasn't any real place that we could get support across all ado providers out of the box. The ado infrastructure only has abstract implementations at the level we need to tap.

The end result is that we need to ping the other provider implementers and get them to implement the required diagnostic hooks. This shouldn't be too onerous on their part as Diagnostics Source makes this easy, but we just need to contact them and/or send in the PR. To date I haven't had time to do this myself. I'm hoping to do this sooner rather than later, but if you have a need for a provider outside of SQL, I'm happy to give you the info you need to do a PR and implement the Glimpse side to get it up and running. Let me know what you think.

[Jetski5822]

Sweet - do you know what the code looks like for the SQL ADO.net extension point?

I did this.. https://github.com/OrchardCMS/Orchard2/blob/eb4c431ad1bf369a252c8e17e612477d91f2d13b/src/Orchard.Data/Diagnostics/DiagnosticDbCommand.cs#L101

Similar to the EF Extension points, and seems to work fine for the time being. I have noticed that the CommandType and CommandParameters are commented out due to serialisation issues. How do you plan on bringing them back in?

Also how will we display the CommandText, as its being pushed in, but not displayed

[avanderhoorn]

As far as SQL goes, if you have a look here and do a search for _diagnosticListener you will see how we implemented it SQLClient... the long and the short though, it that its fairly similar. Additionally you could imagine similar code in the other ado providers.

Longer term you shouldn't need the DiagnosticDbCommand at all, but in the more short term you definitely wont need it for SQL Client. Glimpse just hasn't been updated yet to tap SQL Client as apposed to EF. The only slight issue that I have to work through is given that the other ADO providers don't have Diagnostic Source events yet, I'd like to keep the EF event listeners on for non SQL Server databases but off for SQL Server. There isn't really an obvious way to achieve this atm. Its semi important as I don't want to generate duplicate events.

From an Orchard perspective, if Orchard v2 doesn't use EF, the DiagnosticDbCommand could still be used to capture commands from non SQL Server providers. But a similar problem arrises with how to know if DiagnosticDbCommand should be used (i.e. only in non SQL Server cases) and how deal with other providers implementing diagnostic source events over time. The obvious answer would be to just send in PRs to those providers sooner rather than later, but as mentioned I don't have time atm :( That said your PR has all the key points from a Diagnostics Source perspective and you can see that what we did for SQL Client is very similar, if you had the desire you could send a PR to the providers that are the biggest for the orchard community outside of SQL Server. Here is the PR that captures Diagnostics Source going into SQL Client.

Regarding CommandParameters, they will definitely be coming back. Just need to get the time to make sure we treat that data safely from a serialization perspective.

As for why you commands aren't showing up. Not sure. My first instinct was that you weren't using the correct message types, but its clear that you are. My second instinct is that its the fault of the client that is currently in this repo. Specifically, this client is limits what Commands it shows to various scopes... i.e. it will only show Commands that occur within an action or a view component or before or after the action (I think thats he case with the later - its been a while as a replacement client is in the works). This means if Orchards pipeline causes commands to occur outside of these boundaries, they might not show up. Outside of that, it could be a correlation issue (how the begining and end are matched in the current client) or something else. If you are keen, you could try debugging the client a little and see what comes of it. Let me know if any of that rings a bell.