Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get raw sql string in Sql Kata

Tags:

c#

sqlkata

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?

like image 247
mwilson Avatar asked Jun 13 '19 02:06

mwilson


People also ask

How do I get query string in SQL Server?

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.

How do I display a string in SQL?

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

How do I match a starting string in SQL?

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]" .

What is SqlKata?

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.


2 Answers

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

like image 92
amd Avatar answered Jan 03 '23 06:01

amd


If you are injecting QueryFactory dependency, you can use its compiler:

var query = new Query("Users")... etc

var rawSQL = queryFactory.Compiler.Compile(query).RawSql;
like image 21
Oleksiy Avatar answered Jan 03 '23 05:01

Oleksiy