A much smarter way to work with SQL.
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. 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, with all the 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 :-)
The extension puts a command in your Tools menu that will run all queries in your application and regenerate all wrapper classes. As such, you can test all your queries without further effort, 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.
Who would have thought that in 2016 you could do something original with ADO and a few hundred lines of code. Nonetheless, this seems to be a not-yet-explored approach to the database problem. This is an alpha version, and your comments would be very welcome. To use this tool, install the VSIX. Then use the provided QueryFirst template each time you want to create a query. Each time you save your .sql file, the wrapper and results classes will be regenerated.
QueryFirst needs a "design time connection string". This connection will be used to test-run your .sql and generate the wrapper. Create a "qfconfig.json" somewhere in your project or solution, file beside or above your .sql. The minimum qfconfig has one property: "defaultConnection":
At runtime, you need to supply a class, QfRuntimeConnection, with a static method GetConnection(). You must supply this class, but you can always manage the connection yourself if you have other ideas. The compiler will show you the way !
1.0.4 Visual Studio 2019 compatibility. Thanks Adrian Bathurst.
1.0.0 QueryFirst is now compatible with .NET Core. For this reason, we no longer use app or web.config. You will need to create a qfconfig.json file, beside or above your QueryFirst .sql files. See the readme section above. We also noticed a couple of problems getting the
0.7 Supports VS 2017. The structure of the POCO has changed - to properties with real backing fields. Should suit interfaces and serialization. A number of bug fixes.
0.6 Postgres and MySql support. BREAKING CHANGE. QueryFirst now requires a connection, not a connection string, at runtime. Documentation for Postgres and MySql coming soon!
0.5 Scaffolding Inserts and Updates. Type "Insert into [myTable]..." or "Update [myTable]..." in a QueryFirst query, save the file and QueryFirst will scaffold your inserts and updates. This is a one-shot. You edit and maintain the SQL generated.
And SelfTest. If you need to deploy a new version of an application against an existing database, wouldn't it be nice to integration-test the binary against the production db, just in case your update scripts are less than perfect? Put in your app settings, and add a project reference to Xunit and QfSchemaTools.dll. QueryFirst will generate a SelfTest() method that will test-run your queries against the production DB, and verify the actual columns returned match those present when the app was compiled. Killer feature.
Also, for projects without an app or web.config, or for whatever reason, you can now specify the design time datasource directly in query file. Put --QfDefaultConnection=[myConnectionString] somewhere in your SQL.
0.4 Loadsa goodies... QueryFirst now generates interfaces for your wrapper classes, handles UUIDs correctly, no longer uses AddWithValue() to add parameters and deals correctly with non-queries. Don't delete the partial results class file. We use it to scrape the namespace.
0.3 Less static and more virtual for easier unit testing. You will need to instantiate the wrapper class. Improved handling of nullable types thankyou peterthomastlc.