I am using SqlKata purely to build sql queries in C#. I am wanting to take the output of my built up Query
, get the raw (compiled) sql string, and execute it against SQL.
I thought this would do it:
var factory = new QueryFactory(null, new SqlServerCompiler());
var query = new Query();
...
var sqlText = factory.Compiler.Compile(query).Sql;
But this gives this:
SELECT TOP (@p0) [AllStarFull].[GameNumber], [AllStarFull].[LeagueId], [AllStarFull].[PlayedInGame] FROM [AllStarFull]
This throws an exception because (@p0)
is a param, and not the actual value.
In the documentation, it mentions to bring in Logger
but I don't really need logging functionality (right now).
https://sqlkata.com/docs/execution/logging
var db = new QueryFactory(connection, new SqlServerCompiler());
// Log the compiled query to the console
db.Logger = compiled => {
Console.WriteLine(compiled.ToString());
};
var users = db.Query("Users").Get();
Is there anyway to get the raw sql string from the Query
with all params populated?
Dynamic SQL lets you create a query string based off of user input. SQL Server allows you to create dynamic SQL statements. The statements use a SQL string varchar data type, then you execute the command. Even though the SQL value is a string, SQL Server executes the string as if it is SQL code.
:Explanation: Note: You can use literal string (enclosed in single or double quotation mark) just like we use a column name in the SELECT statement. If you use the literal string with a column then it will be displayed in every row of the query results.
To do this, you can use the character class [sp] to match the first letter, and you can use the character class [aeiou] for the second letter in the string. You also need to use the character to match the start of the string, ^ , so all together you'll write "^[sp][aeiou]" .
It uses the parameter binding technique, to prevent SQL injection. It supports Operator whitelisting. Multiple Database Vendors. It Supports SqlServer, MySql, PostgreSql, Oracle, SQLite and Firebird. Flexible and Rich API.
If you need just to build the SQL there is no need to include the SqlKata.Execution
package (which is include the QueryFactory class).
The simplest way is:
using SqlKata;
using SqlKata.Compilers;
// Create an instance of SQLServer
var compiler = new SqlServerCompiler();
var query = new Query("Users").Where("Id", 1).Where("Status", "Active");
SqlResult result = compiler.Compile(query);
string sql = result.Sql;
List<object> bindings = result.Bindings; // [ 1, "Active" ]
as mentioned in the docs you can use the result.ToString()
to get the full query
var sql = result.ToString();
but this is not a good practice, the correct way is to use the parameterized query with bindings to execute it.
taken from https://sqlkata.com/docs#compile-only-example
If you are injecting QueryFactory
dependency, you can use its compiler:
var query = new Query("Users")... etc
var rawSQL = queryFactory.Compiler.Compile(query).RawSql;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With