SqlKata.Execution about querybuilder HOT 4 CLOSED

So there were some mistakes made ;) xUnit is a little different. But as of now:

-Only sqlserver tests, i'll try to go and scaffold out postgres and mysql + setup the engines in appveyor
-Added testSettings.dev.json to .gitignore so we can have local non appveyor connection strings ;D
-Fixed an error in my ways and removed my GetAwaiter().GetResult() that was deadlocking my xunit tests. updated xunit

I found some tests DeepJoin / CteAndBindings that fail, they didn't have the fact attribute applied and some assert(s) that were the wrong direction.

I'll try to commit some stuff later but it looks like xunit is still paralellizing some tests causing the db ones to fail.

from querybuilder.

Hi Thanks for the efforts :), actually I didn't have the time to review what you have applied,

Could you explain more what do you mean exactly by following Dapper format ?

For the XQuery casting, actually we will use the factory pattern, so developers will not fall into this problem, anyway we can enhance it, maybe by throwing a valid exception.

a) TargetFrameworks have been extended: Thanks this is a necessity
b) Since we will use the factory pattern, we can skip this
c) Thanks, let me know how can I help

from querybuilder.

@ahmad-moussawi
That's why I had published some unit tests to show the api format, but by the dapper format, I mean the way its' API is interacted with.

For example: (given a local variable called 'conn')
var dClass = await conn.SingleAsync<SomeClass>("SELECT TOP(1) * FROM [table]");

I believe the execution API should also map directly on IDbConnection. Example using SqlKata.Execution2:
var query = new Query("table").SelectRaw("*").Limit(1);
..
var dClass = await conn.SingleAsync(query);

The implementation I provided is also factory-esque pattern as well, I will try to explain some below and I'm going to try and push up a few more unit tests later to show the different variations.

Firstly, before I explain; SqlKata.Execution currently accepts a parameter of Query (and not XQuery) to which is then blindly cast which I feel is bad form considering it completely subverts all compile time checks. Throwing an exception isn't making things better because the failure would only happen at runtime and also not very helpful. There the only recourse is to change all public methods to accept XQuery for that particular implementation (including the extension methods). Because this is perfectly valid in that implementation:

var query = new Query("TABLE").Select("Integer").First()
(throws InvalidCastException)

On to the explanation;
The compilation of a Query to a SqlResult itself should be done by a factory while having nothing to with execution, and that is what I have implemented in my branch.

note: QueryBuilder.cs, QueryBuilderSettings.cs, QueryBuilderExtensions.cs

We provide the ability for a global default settings object for ease of use, while providing an overload that accepts an explicit settings object so that end-users can customize the compilation.

example: 95% of the app is destined for SqlServer, but the remaining 5% are calls into postgres and mysql separately or some other proportion.

The settings object used by the factory can be extended, this means that end-users can subclass and use their own settings object or write extensions methods ontop of the settings object, allowing for them or us to further build extensions directly on-top of SqlKata.

As for execution

• There are quite a few distinct dapper calls, all of which are useful. Why write them by hand?
• Some of the more useful methods require parameters like transaction, and timeout which yours have left out. Default parameters means we don't need to duplicate many overloads.

//SqlKata.Execution
public static T First<T>(this Query query)
{

    var xQuery = (XQuery)query;

    var compiled = xQuery.Compiler.Compile(query.Limit(1));

    return xQuery.Connection.QueryFirst<T>(compiled.Sql, compiled.Bindings);

}
//SqlKata.Execution2
public static Task<T> QueryFirstAsync<T>(this IDbConnection cnn, Query query, QueryBuilderSettings settings = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
    var result = query.Build(settings);
    return cnn.QueryFirstAsync<T>(result.Sql, result.Bindings, transaction, commandTimeout, commandType);
}

In use

//SqlKata.Execution2
var pageSz = 250;
var query = new Query("TABLE").SelectRaw("*"); 
dynamic page = await Connection.PaginateAsync(query, 1, pageSz);
var typedPage = await Connection.PaginateAsync<SomeClass>(query, 1, pageSz);
var someClasses = await Connection.FirstOrDefaultAsync(query);

//SqlKata.Execution2
//For an explicit compiler that is not set to the global.
//Static assignment used for clairity/example.
static class MyOwnAppSettings
{
    public static readonly QueryBuilderSettings PostgresQuerySettings = new QueryBuilderSettings()
    {
        Compiler = new PostgresCompiler();
    }
}

//elsewhere 
var pageSz = 250;
var query = new Query("TABLE").SelectRaw("*"); 
dynamic page = await Connection.PaginateAsync(query, 1, pageSz, MyOwnAppSettings.PostgresQuerySettings);
var typedPage = await Connection.PaginateAsync<SomeClass>(query, 1, pageSz,MyOwnAppSettings.PostgresQuerySettings);
var someClasses = await Connection.FirstOrDefaultAsync(query,MyOwnAppSettings.PostgresQuerySettings);

from querybuilder.

Hi, I really believe that letting the query to manage the execution by itself is more easier than using an external class to manage this

var users = factory.Create("Users").Get();

instead of

var query = new Query("Users");
var users = connection.Query(query);

since in the latest the developer should always care about the connection, while the goal of SqlKata is to take the developer away as much as possible from dealing with low level API, thus it's not coupled with Dapper's API.

We tend for the approach to configure once and use everywhere, for example DI containers, factory patterns and so on.

For the casting exception, I agree with you, but currently this is the only way, I think clear documentation can solve this issue.

from querybuilder.