QueryFirst - Frictionless Data Access
The QueryFirst extension for visual studio generates a C# wrapper for your .sql files. This is the original version for VS <= 2019. For VS 2022, go here
Author your SQL in a real environment
Syntax checking- Intellisense for tables and columns - Get familiar with your data
A generated C# wrapper
To use QueryFirst, install the extension, then use the QueryFirst .sql template under Add => New item => Visual C# Items
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.
As such, 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. In your application code, running your query takes one line of code, and returns a POCO (or an IEnumerable of POCOs, or a List 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, with best practices baked in. 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 :-)
QueryFirst puts a command in your Tools menu that will run all queries in your application and regenerate all wrapper classes. As such, you can integration-test all your queries at any time, 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 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.
QueryFirst, first published in 2016, represents an original approach to the database problem in any language. Your comments are very welcome.
We expect that QueryFirst will quickly become your favourite data access method. Please urgently let us know if this is not the case.
* only with the help of EF reverse POCO generator (not actually part of EF), but you'll need to open your solution as administrator, find the .tt and right-click on 'run custom tool'. Not kidding.
** Dapper makes parameters much easier, so you're encouraged, but not obliged, to use them.