SqlQueryTools is a visual studio extension that generates code behind files for sql files. The code behinde file contains a static class with the same name as the sql file. That class has a const called query, with the content of the sql file. It also contains a const for every parameter that is declared in the sql file.
Before SqlQueryTools start generating the code behind file it validates the content of the sql file with a SQL Server database.
The generated code can be used to execute the query with an ORM like Dapper
Download from releases.
Or install it from the Visual Studio Marketplace.
You can add a new sql file by right clicking on a .cs file and select 'Sql Query Tools/Add Sql File'. This will add a nested sql file below the selected csharp file.
The following dialog will pop up, enter the filename and click 'Add file'.
The added sql file will have the following content.
Before you start writing your query you should set the SQL server connection string that Sql Query Tools should use to validate the query. The connection string can be set in the .csproj file you just added the sql file to.
As of version 1.4 you can use the the properties window to edit the connection string, you just need to select the project file in the sollution explorer and open the properties window.
Now you can start writing your sql query. You can remove all comments but if you use parameters in your query you should put the parameter declarations above the 'End Of Parameter Declaration' marker. And your actual query below the marker. The generated const string will only contain the lines below the marker, if the sql file doesn't contain a marker line all lines will be put in the const string.
All comments that you keep in your sql file will not be put in the const string.
Below you can find an example of a query.
When you save the sql file, Sql Query Tools will start generating the code behind. The progress can be found in the Sql Query Tools output panel. Here you will also find more info when something went wrong.
And the generated code for the example query from above will look like this.
The solution explorer will now look like this.
If you don't like the file suffixes or the parameter declaration marker. These can be changed via 'Tools/Options/SqlQueryTools'
By default SqlQueryTools was generating const fields for every parameter that you declared in the parameter declaration section.
As of version 1.4 this can be turned of in the properties window while the sql file is selected in the solution explorer.
In 'Tools/Options/SqlQueryTools' you can change the default for new files that are created with the SqlQueryTools 'Add Sql File' context menu item.
From version 1.4 you can also ask SqlQueryTools to generate a poco class for the result that you get back from your query.
Lets use the following sql as an example
The code generated for this sql with 'Generate Poco' turned on would be the following:
You can turn on this feature at sql file level by using the properties window of the file, or change the default for new files via 'Tools/Options/SqlQueryTools'.
Table varable declaration support was added in version 1.5. Before this version the code generation failed with the error: Variable @VariableName is used in the query, but it is not defined in the 'Parameter Declaration'.
To make this possible the parsing of the sql code has been rewritten. The extra advantage is that we don't need the 'End Of Parameter Declaration' marker line anymore. So whem you create a new Sql Tools file it is no longer added. In existing files the line is ignored as any other comment line.
MIT