Why not ORM: OpenAccess Watcher, Part 3

In part 1 of this ongoing series, we dispelled ORM myths, shaking the errant assumptions that many developers still hold about ORM. In part 2, we took a deeper look at why and how you can learn to trust the SQL an ORM produces. In part 3, we'll examine a simple tool I've crafted to help with that SQL visualization when using Telerik's OpenAccess ORM.

WHAT DOES THE TOOL DO?

The goal for this tool is simple: make it simple to visualize and summarize in real-time the SQL output from any OpenAccess-powered application. My self-imposed requirements were simple, too:

• The tool must add value above and beyond what OpenAccess' built-in logging provides
• The tool must be easy to setup with any application (win or web)
• At most, 1 assembly reference, 1 line of "initialization" code
• The tool must work with "new" (Visual Designer) and "classic" (IObjectScope) OpenAccess

THE RESULT: OPENACESS WATCHER

The tool, which I've dubbed OpenAccess Watcher, is composed of two parts:

1. The Watcher- This is a simple WinForms application built with the Telerik RadControls for WinForms and the SharpDevelop TextEditor (for syntax highlighting) that "listens" for messages from OpenAccess applications.
2. The Sender- This is a custom OpenAccess TraceListener that builds on the built-in OpenAccess logging to format and send messages in real-time to The Watcher, which in turn handles the visualization. There is also some advanced use of JustMock's CodeWeaver, but more on that in a moment.

The key thing to understand is that (for the most part) this tool is working with the same data you might find in the OpenAccess logs. It's value is in the summary stats it provides, the easier data navigation and visualization, and the "real-time" nature of the log output.

USING OPENACCESS WATCHER

Sticking to my requirements, OpenAccess Watcher requires minimal setup. To use it in an OpenAccess-powered app, you must simply:

1. Add a reference to the assembly that contains "The Sender" (OpenAccessWatcher.Listeners.dll)
2. Initialize the custom TraceListener during your application startup
• For Web, this means 1 line of code in Application_Start in Global.asax
• For Win, this means 1 line of code in your Program's Main method

That's it! Two steps. Now all you need to do is open "The Watcher" and start your application. If everything is working, you'll start seeing OpenAccess generated SQL show-up in The Watcher. (A complete "Getting Started" readme is available with the code.)

UNDERSTANDING THE LOG DATA

Since this tool does depend heavily on the OpenAccess log data, it's important to understand what's available. First, what's available is directly connected to the level of logging you've configured. The three common modes are:

• Normal - This is good for capturing the basic SQL statements, but omits most of the log data that can be used to interpret other behaviors (like row count).
• Verbose - This is good for capturing most SQL behavior, including connections, but it can significantly slow the execution of some applications.
• All - This gives you maximum logging data and enables you to measure things like total rows returned and total data readers opened. Unfortunately, this can really slow down bigger apps.

My suggestion: Start with verbose. If that makes your app unbearably slow, try using normal. If your app runs tolerably with verbose and you want to try to get event more data, then give all a try.

It should also go without saying, these are not settings for production or for measuring application speed. These are settings specifically designed for visualizing SQL behavior during development, with the goal being to use the info for tuning ORM performance configuration.

EXAMPLE APP

To test OpenAccess Watcher, I've got a simple WinForms application with a RadGrid that loads data using the OpenAccessDataSource. When I open the page with this grid, I see the following in Watcher:

There are three primary areas of information:

1. SQL Activity Log (A) - This is a raw log of the SQL queries generated by OpenAccess (pulled from the OpenAccess logs). This data is displayed in a RadGrid for WinForms, so you have the complete ability to sort, filter, and group this data.
2. SQL Visualizer (B) - Using the open source ShareDevelop TextBox, this area better visualizes the selected SQL statement so that you can easily review the SQL with syntax highlighting.
3. Statistics Summary (C) - This area displays the values from various logging counters that summarize OpenAccess database activity. The version of OpenAccess you're using and your logging settings will impact that values that are available here.

While I'll save a full discussion of how to work with this data for Part 4, it's worth noting now that there is not a one-to-one relationship between SQL queries and database connections. OpenAccess typically prepares and executes multiple queries in batch, so don't freak out if you see larger numbers of queries.

ADVANCED LOGGING: CODE INJECTION

As I alluded to above, I am making use of Telerik's very cool CodeWeaver library, available with JustMock, to do IL injection with OpenAccess assemblies. This allows the custom TraceListener to go beyond tracking OpenAccess log data and inject code in to key OpenAccess methods that is used for gathering statistics.

Newer versions of OpenAccess (Q3 2010+) have convenient IntelliTrace and CommandWrapper classes through which all OpenAccess connections and transactions pass, so by injecting some counters in these methods I am able to report on connections and transactions even when logging is tuned-down or disabled.

For older versions OpenAccess, I am not able to do counters as easily, but there is still some injection in to the very low-level Transaction and Connection runtime implementations to help gather as many stats as possible for the visualizer.

In all cases, the code injection tooling will only work when the JustMock profiler is enabled. When not available, you'll get fewer stats.

IT WORKS ON MY MACHINE DISCLAIMER

This is an experimental project. It is not guaranteed to work with your setup. If it doesn't, I'm sorry. Send me feedback and I'll see what I can do to make it cover more scenarios. Known caveats with this preview:

• I'm primarily focusing on SQL Server scenarios today. It should work for other OpenAccess providers, but I haven't tested that yet.
• I've only tested this with .NET 4.0 projects. I'm not sure what will happen if you try to use the custom TraceListener with older .NET versions.
• Your application probably needs to be running in full trust to support the reflection and code injection tooling that's going on.
• The UI of the WinForms app can "hang" when huge volumes of logging data are being collected. Just wait a few seconds and it should become more responsive.

THE BITS

Disclaimer out of the way, here are the bits. Enjoy and share your feedback.

In Part 4, I'll show you how you can use The Watcher to start learning and optimizing the SQL generated by OpenAccess. Since OpenAccess has powerful configuration and optimization options, it's surprisingly easy (compared to other ORMs) to tweak once you have a clear understanding of what's happening.

Why not ORM: Trusting SQL, Part 2

In part 1, we examined and busted 5 common ORM myths. Actually, we busted 4.5. We agreed that one myth, that you can't trust the SQL an ORM produces, is plausible. Let's bust that myth, at least bust it for OpenAccess ORM, today.

IN TOOLS I TRUST

One of the biggest barriers to adopting any time saving development tool is trust. You have to trust that the work the tool is doing to save you time is of equal or better quality than the same work you would otherwise do by hand. A development tool is in many ways like a co-worker or contractor. You have to get to know the results they produce before you can stop worrying about including their output in your project.

This is true for refactoring tools (like JustCode), UI components (like the RadControls), and especially true for data tools like OpenAccess ORM.

But how do you go about "trusting" a tool like OpenAccess ORM? Transparency.

OPEN THE BLACK BOX

With ORMs, many fears root in the perceived "black box" nature of what's happening under the covers. Many developers are familiar with hand crafting EACH-and-EVERY-sql-statement, and it can be unsettling to let some ORM robot start automagically creating your SQL based on abstracted LINQ. What if the SQL is inefficient? What if way too much SQL is being generated? What if the ORM "select *" my database?!

The result of this fear is often cut-and-run. Dump (or don't start using) the ORM and stick with time-wasting, but 100% transparent hand coding.

What developers need is an easy way to open the ORM black box and see what's really happening. Cut-out the "what ifs," and replace them with "when I do X, the ORM will do Y."

WHAT *IS* REALLY HAPPENING?

While ORMs like OpenAccess are unbelievably sophisticated, giving you the power to map database objects to code and even update schemas from code-based models, at the very root of the implementation is code you know. Standard (best practice) .NET for moving data between various databases. In the case of SQL Server, you'll find ADO.NET and SqlCommands, Transactions, and Connections.

The important thing to know is that this "standard" code is built by super-smart developers that are 100% focused on best practice data programming. Think about that. When you use a well built ORM, it's like adding a .NET data programming expert to your team, freeing you to really focus and be an expert on other layers of your app.

Then there's the SQL. And here it's worth noting, not all ORMs are created equal. The way an ORM converts LINQ or OQL or whatever in to SQL, and the ability on top of that to optimize the SQL through configuration, is often what makes one ORM better than another. With a quality ORM, though, you get SQL generated by programmers that are again 100% focused on SQL programming.

Now, much like you must "speak Google" to get the search results you expect, you usually need to learn how to "speak ORM" to get the SQL output you expect. That means you need to form an understanding of how the abstracted data languages (usually LINQ) produce SQL so you know when extra optimization configurations are needed. Do that, and there is no reason the SQL created by an ORM will be any different than the SQL you would create by hand (it may even be better!).

A good ORM is more than a tool. It's a .NET data programming and SQL expert. But how do you prove that to yourself? And how do you learn to speak ORM?

THE ORM LOOKING GLASS

You should never blindly trust any tool. Just as I'd expect you to inspect the HTML produced by the Telerik Extensions for ASP.NET MVC, I'd expect you to inspect the SQL produced by OpenAccess. Doing so not only helps you learn when and how to optimize ORM programming, but it helps you build the trust in your essential data tool.

Fortunately, OpenAccess provides a couple ways to open the "black box" out of the box:

1. Logging
   If you've never used OpenAccess logging, you may be amazed at what can be easily logged with the simple change to a couple of config settings. All SQL statements, parameter values, database connections, and even returned rows can be captured by OpenAccess logging for your thorough review. Logs can be output to the console, a text file, or even directly to a in-memory StringWriter, and volume of data that is captured depends on your logging level. More on that in the next post.
2. IntelliTrace
   For added runtime debugging transparency, OpenAccess (as of Q3 2010) provides deep insight with integrated IntelliTrace support in Visual Studio 2010. As you step and debug your app, IntelliTrace can reveal the SQL OpenAccess is producing and executing.

Both of these approaches are useful, but they do require you to manually "parse" the logs or trace stacks to understand what's happening when your program runs. Is there an easier way to see the bigger picture?

WATCHING OPENACCESS

Taking advantage of the OpenAccess logging output via a custom TraceListener (along with a few other tricks for newer versions of OpenAccess), I've created a simple tool designed to help you better visualize what OpenAccess is doing when your application runs.

The advantages of this tool:

• Easier to visualize, syntax-highlighted SQL
• Convenient log sorting, filtering, and grouping (provided by RadGridView)
• Summary values for key behaviors (total connections, total queries, total transactions, etc.)
• Real-time visualization of OpenAccess output

In part 3, I'll provide a more complete overview of this new tool and provide a basic working version that you can try with your own OpenAccess-powered applications. The main idea behind this tool: make it dead simple to open the OpenAccess black box and visualize the "real" SQL.

Whether you use built-in logging output or a visualization tool like this, take the time to get to know your ORM. If you can build a trust with a powerful ORM, like OpenAccess, you'll leave hand-coding data access in the past where it belongs and never look back.

Can you trust the SQL produced by an ORM? Definitely! You just need to take a look. It's not in a book…it's in a log file.

Troubleshooting OpenAccess ORM VEnhance errors

I'm working on what I promise will be a very cool and useful tool/demo for OpenAccess ORM (more on that very soon), and in the process I was reminded of some of the challenges that can pop-up when you start manually "enhancing" OpenAccess-enabled assemblies. Manual enhancing is something you usually do when your project configuration and build steps require maximum control, or when you're trying to use specific versions of the enhancer (VEnhance.exe) with your code. In other words, it's not something faced by every project, and new OpenAccess projects using the new DataContext are event less likely to require manual VEnhance steps.

Still, if you prefer the pre-visual designer approach to OpenAccess, or if you're maintaining code that uses a specific VEnhance version, here are a few quick tips that should help you quick resolve any build-time VEnhance crashes:

1. VEnhance Config: If you're working with .NET 4, a common cause of VEnhance crashes is failure to include the "venhance.exe.config" file in the same directory as the "venhance.exe" executable. This simple config file is needed with .NET 4 to tell the enhancer to use use Framework v4 assemblies when enhancing your code. The contents of the config file are simply:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
  <supportedRuntime version="v4.0"/>
</startup>
</configuration>

2. VEnhance Version: Another common mistake is mis-matching VEnhance versions. That is, using a VEnhance version that is different from the version of the OpenAccess assemblies you're referencing. With the latest VEnhance versions, you can actually use a newer VEnhance.exe with older versions of OpenAccess, so just make sure you're using the latest VEnhance executable. If you find yourself in doubt, though, it can't hurt to make sure both your referenced assemblies and VEnhance version match.
3. Supporting DLLs: The VEnhance tool needs a few external assemblies to do its magic, so make sure they are accessible by the executable (usually best kept in the same directory). If you are copying VEnhance.exe to a project-relative location (good practice for creating "upgrade proof" projects), don't forget to bring these files along: Telerik.OpenAccess.SDK.dll, (and in newer OA versions) Mono.Cecil.dll, and Mono.Cecil.Pdb.dll
4. Manually Run VEnhance: Finally, if after checking your VEnhance version and making sure the necessary config file is in-place, you can always run VEnhance manually. The advantage to doing this instead of using Visual Studio post-build events is that you tend to get much more descriptive errors that help with troubleshooting (versus the totally unhelpful crash message pictured above). To manually enhance:
1. In Visual Studio, select your OpenAccess-enabled project and set the project "Enhancing" attribute to "False" (disables IDE integration "auto" enhancing)
2. Build your project (should build and do no OpenAccess enhancing)
3. Open a command prompt in the directory containing "venhance.exe"
4. Run the command "venhance <path to your compiled DLL>" (In newer versions of VEnhance.exe, it will be: venhance -assembly:"<path to dll>"
5. Inspect the command prompt output for runtime errors or SUCCESS!

There are other options you can use with manual enhancing, too, like the "-verbose+" command combined with "-verboseMode:4" to see detailed output in the command window about what VEnhance is doing. If nothing else has solved your problem, this technique should at least tell you what's wrong.

Don't get stuck or frustrated with useless VEnhance Windows crash dialogs. Try these troubleshooting steps and get back on the road to using one of the most powerful and enjoyable ORMs available for .NET development!

Telerik reveals new product line: Telerik OpenAccess ORM

Big- no-  HUGE news today. For some time now Telerik has been alluding to a new product line that will be joining the Telerik offering in Q3 of this year, and now the veil is finally lifted! In Q3 2008, Telerik will introduce Telerik OpenAccess ORM. That's right, Telerik is moving beyond UI components to do for data development what we've done for UI development. Telerik OpenAccess ORM, formerly Vanatec OpenAccess, will make process of developing a robust, scalable, datastore-agnostic data layer as easy as building rich, high-performance UIs for ASP.NET, WinForms, WPF, and Silverlight with the RadControls. We know we're not the only game in town for ORMs, but just as we entered the UI component market and corrected the oversights of "incumbent" vendors, we plan to enhance the ORM experience for .NET developers and bring Telerik's trademark service, documentation, and support to .NET ORMs.

This is an exciting time for Telerik and we're very eager to ship this product to you. We know there are going to be lots of questions after today's announcement, so stay tuned to Telerik Watch and the Telerik.com blogs for lots of additional details in the coming days and weeks leading up to the Q3 release. For now, spread the word and get ready for "more than you expect" in you data development!