'Get raw sql string in Sql Kata
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?
Solution 1:[1]
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.
Solution 2:[2]
If you are injecting QueryFactory
dependency, you can use its compiler:
var query = new Query("Users")... etc
var rawSQL = queryFactory.Compiler.Compile(query).RawSql;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | amd |
Solution 2 | Oleksiy |