Introduction
"In the .NET world (and beyond), data access is a cluster-f#$%ed echo chamber of half-assed rocket engineering and cargo cultism based on decade-old cathedralized thinking and corporate naval -gazing"
Rob Conery
Background
With the rise of micro-orms, folks are coming back to SQL for data access. With good reason. SQL remains the most expressive, complete, performant way of interacting with relational databases, and relational databases aren't going away anytime soon.
How, then, is it possible that we still see, all over the place, SQL queries constructed with string methods. This is a language. That can be parsed, validated and tested, but not if it's inside double quotes. How would we be using SQL in C# applications if they really wanted us to be using SQL?
You only need to ask the question to answer it: SQL should be in its own file, without exception. For performance, the file should be compiled into the application, and accessed as a manifest resource stream. Accessing query results with DataReader is painful, error prone and hampers testing, so naturally we want POCOs with proper datatypes and meaningful property names. Once we have the query, we can infer the types of the input parameters and the structure of the POCO, so generating the ADO code that takes your inputs, executes your query and fills your POCOs is monkey's work. These days, we get computers to do monkey's work...
tldr; What we want is a Visual Studio extension that takes your SQL, validates it against your DB, then generates the class that runs it, and the POCO for the results.
 |
Edit your queries in a SQL window, connected to the DB. Syntax validation, Intellisense, stepwise debugging, test runs. |
 |
Every time you save, QueryFirst (re)generates the wrapper class that runs your query and the POCO for the results. |
 |
Executing your query is just a method call, returning a POCO. No connection, no command, no reader. No dynamics, end to end type safety. Intellisense everywhere. |
Using this thing is a revelation. The reward to effort payoff is so great I have to call it a discovery more than an invention. Your SQL lives. It's validated, easy to read and easy to maintain. Visual Studio helps out with Intellisense for tables and columns, query execution plans, executing the query directly in the editor window, etc. Then, as soon as you save, your query is discoverable in code. You have Intellisense for the input parameters and the results, and all the advantages of strong typing without ever having to worry about a type. The love flows straight out of the DB and into your application. And if you change your DB schema, you can retest all queries and regenerate all wrapper classes. If a query throws errors, the wrapper class won't compile. If your app code references a column that's no longer present in the result set, the compilation error points to the line in your code that has the invalid access.
One sure sign of the poor status of SQL is that we frequently jump through hoops to do stuff in C# that's trivial if you do it directly in SQL. Dynamic parameter lists and pagination are just two common examples. Dynamic parameter lists happen when you only know at runtime which columns are to be filtered on. It's not uncommon to see this dealt with in C# with "if" statements breaking up the composition of the SQL. Nightmare. How much simpler to just do this?
select * from table1
where (col1 = @param1 or @param1 is null)
and (col2 = @param2 or @param2 is null)
and (col3 = @param3 or @param3 is null)
OPTION (RECOMPILE)
The OPTION RECOMPILE is important because SQL Server will actually optimize the query differently depending on which parameters are supplied and even what values they contain. This isn't stuff we should run away from. This method is not ideal for all cases, and there are plenty of other options here.
We should get to know SQL again, and we should be intolerant of all the stuff you have to wade through to use it.
Using the Code
Download and install the VSIX here. You will need to create a connection string, QfDefaultConnection, in your app or web.config. For the runtime datasource, create a class QfRuntimeConnection with a static method GetConnectionString(). Create your queries using the QueryFirst template, filed under "Visual C# items".
History
No known issues, but this is an alpha. Your comments are keenly sought!
