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?
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.