public void should_execute_non_query()
{
StoredProcedure.Execute("DeleteOne", new { Id = 6 }).ShouldEqual(1);
var result = StoredProcedure.ExecuteScalar <int>("GetCount");
result.ShouldEqual(9);
}
public async Task Query()
{
IQuery <string> query = _database.Products.Query().Join(e => e.Owner).Where(e => e.IsAvailable).OrderBy(e => e.Name).Offset(10).Limit(20).Select(e => e.Name);
IDelete batchDelete = _database.Products.Delete().Where(e => e.IsAvailable);
IInsert batchInsert = _database.Products.Insert(new Product(), new Product());
IUpdate batchUpdate = _database.Products.Update().Where(e => e.IsAvailable).Set(e => new Product {
IsAvailable = false
});
IUpdate singleUpdate = _database.Products.Update().Set(e => e.NetPrice, e => e.NetPrice * 1.1m).Set(e => e.DateAdded, DateTime.UtcNow);
var(queryResult, deleteAffected, updateAffected, insertAffected) = _database.Execute(query, batchDelete, batchUpdate, batchInsert);
(queryResult, deleteAffected, updateAffected, insertAffected) = await _database.ExecuteAsync(query, batchDelete, batchUpdate, batchInsert);
string querySql = query.ToSqlString();
string deleteSql = batchDelete.ToSqlString();
string updateSql = batchUpdate.ToSqlString();
string insertSql = batchInsert.ToSqlString();
var product1 = await _database.Products.Query().Where(e => e.Id == 1).Single().ExecuteAsync();
var product2 = await _database.Products.Query().Where(e => e.Id == 2).Single().ExecuteAsync();
var updatedExplicitly = await _database.Products.Update(product1, product2).ExecuteAsync();
var product3 = await _database.Products.Query().Where(e => e.Id == 3).Single().ExecuteAsync();
var product4 = await _database.Products.Query().Where(e => e.Id == 4).Single().ExecuteAsync();
var deletedExplicitly = await _database.Products.Delete(product3, product4).ExecuteAsync();
var copyInsert = _database.ArchivedProducts.InsertFrom(_database.Products.Query().Where(p => !p.IsAvailable && p.DateAdded < DateTime.UtcNow.AddYears(-1)).Select(e => new ArchivedProduct { /* ... */
}));
var copiedRows = await copyInsert.ExecuteAsync();
queryResult = _database.ExecuteSql <List <string> >(querySql, new { x = 1 });
queryResult = await _database.ExecuteSqlAsync <List <string> >(querySql, new { x = 1 });
IStoredProcedure <List <ProductDto> > searchedProductsProcedure = _database.SearchProducts("name");
var searchedProducts = searchedProductsProcedure.Execute();
searchedProducts = await searchedProductsProcedure.ExecuteAsync();
searchedProducts = _database.Execute(searchedProductsProcedure);
searchedProducts = await _database.ExecuteAsync(searchedProductsProcedure);
IFunction <decimal> priceFunction = _database.CalculatePrice(123);
var price = priceFunction.Execute();
price = await priceFunction.ExecuteAsync();
price = _database.Execute(priceFunction);
price = await _database.ExecuteAsync(priceFunction);
_database.Insert(new Product()).Execute();
var product5 = _database.Query <Product>().Where(e => e.Id == 5).Execute();
_database.Update(product5).Execute();
_database.Delete(product5).Execute();
var q1 = _database.Products.Query().Where(e => e.OwnerId == 5);
var q2 = _database.Products.Query().Where(e => e.OwnerId == 6);
var q3 = _database.Products.Query().Where(e => e.OwnerId == 7);
List <Product> unionList123 = q1.Union(q2).UnionAll(q3).Execute();
List <Product> unionList = _database.Products
.Query().Where(e => e.OwnerId == 5)
.UnionAll()
.Query().Where(e => e.OwnerId == 6)
.Execute();
var product7 = _database.Query <Product>(options => options.AsEditable().WithComment("my query tag").WithHint("NOLOCK"))
.Where(e => e.Id == 7).Execute();
// WITH CTE explicit
var cte1 = _database.Query <Product>();
var cte2 = _database.Query(cte1).Where(e => e.IsAvailable);
var withQuery = _database.Query(cte2).Where(e => e.NetPrice > 0);
// Recursive CTE
var managerQuery = _database.Query <Employee>().Where(e => e.ManagerId == null)
.Select(e => new EmployeeDto {
Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = 1
});
var employeeQuery = _database.Query <Employee>().Join(managerQuery, (l, r) => l.ManagerId == r.Id)
.Where((e, mq) => e.Id == 234)
.Select((e, mq) => new EmployeeDto {
Id = e.Id, Name = e.Name, ManagerId = e.ManagerId, Level = mq.Level + 1
});
var hierarchyUnion = managerQuery.UnionAll(employeeQuery);
var employeeHierarchy = _database.Query(hierarchyUnion)
.OrderBy(e => e.Level)
.ThenOrderByDescending(e => e.ManagerId)
.Select(e => new EmployeeHierarchy
{
Employee = e.Name,
Level = e.Level,
Manager = _database.Query <Employee>().Where(m => m.Id == e.ManagerId).Select(m => m.Name).First().Execute()
})
.Distinct()
.Execute();
// Subquery
_database.Products.Query().Select(e => new ProductDto()).OrderBy(e => e.Price).Execute();
// Multiquery
_database.Query <Product, Owner, Employee>();
_database.Query(_database.Products, _database.AvailableProducts, hierarchyUnion)
.Join(managerQuery, (p, _, _, m) => p.Id == m.Id);
// Group by
var groupedProducts = _database.Query <Product>()
.GroupBy(p => new { p.Id, p.Name })
.Having(p => p.Key.Id > 4 && p.Count() > 5 && p.Max(e => e.NetPrice) > 50)
.Select(p => new
{
p.Key.Id,
p.Key.Name,
Count = p.Count(),
Max = p.Max(e => e.NetPrice),
CountAvailable = p.Where(e => e.IsAvailable).Count(),
MaxAvailable = p.Where(e => e.IsAvailable).Max(e => e.NetPrice),
AllAvailable = p.Where(e => e.IsAvailable).Select(e => new { e.Name, e.NetPrice }).AsList()
})
.Execute();
// Mapping
Product[] productArray = _database.Query <Product>().AsArray().Execute();
Dictionary <int, Product> productByOwnerDict = _database.Query <Product>().AsDictionary(e => e.OwnerId).Execute();
}
public static TReturn Execute <TReturn>(this IStoredProcedure storedProcedure,
string name, object parameters)
{
return(storedProcedure.Execute <TReturn>(name, parameters.AsDictionary()));
}
public int ExecuteStoredProcedure(IStoredProcedure storedProcedure)
{
GetOrCreateContext(storedProcedure.GetType());
return(storedProcedure.Execute(_wrappedContext));
}