In .NET consulting land many years ago I wanted the following: a faster way to build reports (primarily tables, sometimes with charts) for clients. I found the vast majority of work I was doing was taking basic SQL queries and getting them into a fast, pretty JS/HTML front-end. There are some solutions out there but they're pretty enterprisey and clunky and expensive (e.g. SSRS and their ilk).

I ended up finding https://datatables.net to take care of the front-end side of things and duct-taped that to a mini-ORM (https://github.com/schotime/NPoco) which came with a simple parameter-safe query generator and ended up with the ability to basically write 1 POCO defining columns and their metadata combined with 1 POCO that contained an in-code SQL query. Once defined, all a developer would need to do from the front-end is ask for the column POCO and suddenly a fully server-side powered table would appear with full support for (server-side!) pagination, page saving, export to CSV/Excel, column hiding/showing, column rearrangement, etc.

I had to write a ton of glue and learned a massive amount about SQL Server during the process. While the mini-ORM gave me a very simple way to inject things like the WHERE clauses and ORDER/SORT BYs, I ended up writing all of the actual logic to convert the request coming from DataTables to actual SQL. At first I thought I was writing an ORM, but really it was just a very specific use-case query builder.

Around the time I got that much of it working a co-worker put me into contact with a friend of his at another consulting shop nearby who he swore was doing the exact same thing as me but with Dapper (https://github.com/DapperLib/Dapper). A few beers and weeks later we had both sat down and evaluated each others work and decided to join forces. He ended up preferring NPoco and I ended up preferring a TON of his query generation (he also had support for basically all the non-complex types (read: hierarchy and friends), so it was an awesome merger).

In modern times, we've got to the point where you can now write a single C# class that inherits from our IMagicalQuery with a single SQL statement inside. Using the magic of Roslyn and a Visual Studio plugin we wrote for ourselves, you can smack a button and it will generate all the other supporting classes needed and update them if your query changes at all (one of our biggest problems was changing the query required you to change [DataAttribute] values on the matching column metadata class, which was very easy to forget to do and annoying).

We also have the awesome feature that came out of necessity of removing joins when hiding columns. Say we've got a 30+ JOIN query (yes, that is necessary for some of our clients; no, it shouldn't be; we don't always get to design the databases!) and the client hides 1 column out of 3 that depend on the JOIN Foobar table - nothing happens! But if they hide all 3 our magical query builder will drop that join entirely from the actual SQL that gets passed to the mini-ORM. The query in our C# class is untouched, it just deletes the offending join out before shipping it to SQL. We've been able to get some awesome performance out of that.

It is sadly never going to be something we release because the reality is that it is a monster made by two savages. Technically either of us can push it out at any time - we both agreed on MIT from the get-go as we both also agreed it's a hydra that no one would ever want to buy. That said we've both profited - our shoppe is now known as the one that can get you the data your people need 10x faster than the other guys. We do SMB consulting so word of mouth is king.

I probably made it sound all roses and kittens so let's talk negatives. 1) SQL Server only. We have tons of SQL Server specific dialect stuff because, well, 99% of our clients use SQL Server. 2) We only support the bits of SQL we need. Initially it was the basics: numerics, approximate numerics, datetimes, strings. Later on we added TVP support, some very limited spacial support, sql_variant. But it has all been as-needed. 3) Adding features that go up the entire vertical is...an experience. Pretty much only my partner in crime or myself touch that. It basically requires you to get the following into your brain: the entire DataTables API, the 5 or so ES6 modules that wrap around DataTables that handle type conversions, data conversions, query mapping stuff, etc., then the 20+ or so C# classes that do all the heavy lifting to ensure type safety doesn't go boom, SQL generation, all the magical performance features we added like JOIN removal, etc., then you also need to know how NPoco is using that data in case something goes funky there. I generally set aside a full day or two whenever we need to add a full-stack feature cause it's just a headache. 4) We write SQL in SSMS then literally copy paste it into a C# @"" string and then parse it with Roslyn later and rewrite it. I know exactly where I'm going in the afterlife...

For future stuff we are planning on a version that generates a stored procedure directly into a SSDT project so that we can actually store our SQL in a project that...is meant to store SQL (what a novel idea!). It would simplify our C# query to literally just be "EXEC <proc> <here's some params!>" but we haven't quite figured out how we're going to pull it off without losing features.

user:	blyat
created:	September 15, 2016
karma:	9