Fluent query manager based on SQLKata.
$ dotnet add package SqlKata.QueryManSimple and very concrete, fluent query manager based on SQLKata and Dapper.
Linq, HQL or QueryOver)Using dotnet cli
$ dotnet add package SqlKata.QueryMan
Using Nuget Package Manager
PM> Install-Package SqlKata.QueryMan
If you need transaction filter
$ dotnet add package SqlKata.QueryMan.AspNetCore
or
PM> Install-Package SqlKata.QueryMan.AspNetCore
Init QueryMan (freeman way):
using QueryMan;
using SqlKata.Compilers;
using System.Data.SQLite;
// Method 1
using (var Db = new QueryRunner(new SQLiteConnection(ConnectionString), new SqliteCompiler()))
{
// Man, do you queries here
}
// Method 2
Db = new QueryRunner(new SQLiteConnection(ConnectionString), new SqliteCompiler());
// Man, do you queries here
Db.Dispose();
Please remember to always
Disposeyour QueryRunner instance when you finish.
Init QueryMan (asp.net core):
using QueryMan;
using QueryMan.AspNetCore;
using SqlKata.Compilers;
using System.Data.SQLite;
public class Startup
{
public void ConfigureServices(IServiceCollection services)
{
services.ConfigureQueryMan(new QueryManConfig()
{
Connection = (s) => new SQLiteConnection(s.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")),
Compiler = (s) => new SqliteCompiler(),
});
// Optionally you can globally enable database transactions against every http request.
// As alternative use [TransactionActionFilter] attribute against your controllers or actions.
// It will create a new database transaction before every request
// and perform a commit when the action (request) executed without erros, otherwise it will rollback all db changes.
services.AddControllers(o => o.Filters.Add<TransactionActionFilter>());
}
}
// Your controller
public class CustomerController : ControllerBase
{
private readonly QueryRunner _db;
public PageBController(QueryRunner db)
{
_db = db;
}
[HttpGet]
public Customer Get(string id)
{
return _db.Get<Customer>(id);
}
[HttpPost]
[TransactionActionFilter] // It will execute sql queries inside the transaction.
public void Put(Customer customer)
{
_db.SaveOrUpdate(customer);
}
}
The preffered way to have an entity against your datatable:
[Table("customers")]
public class Customer
{
protected Customer() // Used by ObservableProxy to create a proxy object and filling with data from database.
{
}
public Customer(string name) // Used by developers to create a new object before it will be saved to the db. See how the `mandatory Name` is implemented by `protected set`.
{
SetName(name);
}
public virtual void SetName(string name)
{
if (string.IsNullOrEmpty(name))
throw new ArgumentNullException(nameof(name));
Name = name;
}
[Key] // Primary key column
public virtual string Id { get; protected set; } // "virtual" allows proxy to override properties, otherwise this property won't be treated as a database column and won't pull or persist any data when you pull data as proxies.
[Column("CustomerName")] // Custom column in the database (by defaut it will use a property name).
public virtual string Name { get; protected set; }
}Save to db:
var customer = new Customer("Customer 1");
Db.BeginTransaction();
await Db.SaveOrUpdateAsync(customer);
Db.Commit();
customer = await Db.GetAsync<Customer>(customer.Id);
Assert.Equal("Customer 1", customer.Name);Get by Id:
var customer = await Db.GetAsync<Customer>(id);Get from db:
Customer customer = null;
(string CustomerId, string CustomerName) model = default;
var query = Db.Query<Customer>()
.Select(() => model.CustomerId, () => customer.Id)
.Select(() => model.CustomerName, () => customer.Name)
;
var result = await Db.ToListNoProxy<(string CustomerId, string CustomerName)>(query);
AssertHelper.CollectionContainsAll(result,
item => item.CustomerName == "Customer 1"
);Get from db with GroupBy:
Contact contact = null;
Customer customer = null;
(string CustomerName, int ContactCount) model = default;
var query = Db.Query(() => customer)
.LeftJoin(() => contact, () => contact.CustomerId, () => customer.Id)
.Select(() => model.CustomerName, () => customer.Name)
.SelectCount(() => model.ContactCount, () => contact.Id)
.GroupBy()
;
var result = await Db.ToListNoProxy<(string CustomerName, int ContactCount)>(query);
AssertHelper.CollectionContainsAll(result,
item => item.CustomerName == "Customer 1a" && item.ContactCount == 1,
item => item.CustomerName == "Customer 2" && item.ContactCount == 0
);Batch Update:
Customer customer = null;
var query = Db.SelectAll(() => customer);
var result = await Db.ToList<Customer>(query);
foreach (var (c, i) in result.Select((c, i) => (c, i)))
{
c.SetName($"Customer No {i+1}");
}
Db.BeginTransaction();
await Db.SaveOrUpdateAsync(result);
Db.Commit();
If you have any issues please provide us with Unit Test Example.
Please create an issue ticket to become a contributer.
Donate with nano.
Thank you!