10 minutes to change your mind about data access?

Hope you're concentrating. It goes pretty quick...

I fixed the Impedance Mismatch!

Finally ! Someone fixed the Object-Relational Impedance Mismatch. Me! So am I a genius, or a fantasist? Objectively the chances are strongly in favour of fantasist. Time to let the internets decide. The suspense is killing me.

QueryFirst. Your SQL is Coming OUT.

Query-first is a visual studio extension for working intelligently with SQL in C# projects. Use the provided .sql template to develop your queries. When you save the file, Query-first runs your query, retrieves the schema and generates two classes and an interface: a wrapper class with methods Execute(), ExecuteScalar(), ExecuteNonQuery() etc, its corresponding interface, and a POCO encapsulating a line of results.

Write your SQL in the SQL window! Syntax validated, Intellisense for your tables and columns.

The generated C# wrapper for your query is nested below the query in Solution Explorer

Your query stays intact in its own .sql file. It can be edited, validated and test-run "in-situ" with design-time mock inputs and intellisense for your tables and columns. For performance, it is compiled into your binary as a ManifestResourceStream. In your application code, running your query takes one line of code, and returns a POCO (or an IEnumerable of POCOs) with meaningful parameter and property names, so enabling intellisense for your input parameters and results. The interface and POCO are directly usable for unit testing.

The generated code stands alone. There is no runtime dll and no reflection. The only dependencies are System libraries. You can quietly forget about ADO : Command, Connection, Reader and parameter creation are all handled for you. At no point do you have to remember the name of a column, or its type, or its index in the reader. And you've absolutely nothing new  to learn, provided you still remember how to write SQL :-)

The extension puts a command in your Tools menu that will run all queries in your application and regenerate all wrapper classes. As such, all queries in your app are continuously integration-tested against the dev DB, and changes in your database schema will directly produce compilation errors in your application. If the query is broken, the wrapper classes will not compile. If the query runs but your application code tries to access columns that are no longer present in the result, the error will point at the line in your code that tries to access the missing property.

Interested? Stay tuned :-)

Why?

Thank you for asking! SQL was never the problem. SQL is not a low level language. "Wrapping" SQL is just an insane idea in my humble view. A query, by contrast, has simple, discoverable, inputs and outputs. Wrapping a query is not difficult. QueryFirst currently counts 2k loc. One day, it can be bug-free, if it isn't already :-) The generated code has no conditionals.

Weird Cultural Practice

SQL in string literals is just the weirdest cultural practice. What other terms can describe it? Is there any equivalent in all computing? When HTML appeared, we were "brute force" generating HTML for, what, five minutes before we came up with "code in markup" strategies? How do you explain that, after 30 years of using SQL, there seems to be zero awareness of the cost, the craziness, of burying SQL in string literals, using host language conditionals and control structures to spit out SQL when SQL has conditionals and control structures? Dapper, Massive and PetaPoco, modern tools that have put SQL back in the toolset, do nothing to challenge this long-running oddity. One prominent developer told me he didn't see the problem, his SQL in string literals was covered by unit tests! This is a language, that can be validated and integration-tested, but not if it's buried in double quotes, it's final form only discoverable by running the program.

I didn't invent putting sql in it's own file. But by automating a couple of steps, I made the right way the easiest way, and removed some possibilities that developers are much better off without.

Leverage powerful tools

The SQL window in Visual Studio is just sitting there waiting to be useful. The editor window connects to your database, for context sensitive code completion. SQL Server has a marvelous stored proc, sp_describe_undeclared_parameters. So why not write your SQL in a real environment, and you never have to remember a column name, or a db type, or its C# equivalent? When the code wrapper is generated, the type information flows straight out of the database and into your app.

The long shadow of ORM's and n-tier

Even when folk go back to SQL for data access, the grip of ORMs and n-tier on the imagination is frightening. Folk are trying to use Dapper to write a DAL for every conceivable access before they tackle their domain layer. If queries can be easy to write and continually tested, we should write more of them, and bring back just what we need, just when we need it. 

If you're using an RDBMS, then data reliably stored in a properly normalized schema is an outcome in itself, and a lot of your code should be preoccupied with getting this right. Your RDBMS is a lot more than an interchangeable persistence store for your objects. I can completely see the point in keeping  your domain objects persistence-ignorant, but this is achieved just by putting persistence in separate classes (or in queries). Casting those classes to the opposite end of the application, in a separate project or layer, risks making artificially distant elements that are in fact tightly coupled, and is not going to help  developers focus on what needs to happen in the DB.

I'm raving, I should stop!