public void OrderByExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection());
Query Top10PurchaseOrder() => purchaseOrderDal.NewQuery().Limit(10);
// Below two statements shows two different ways of doing ordering
// If multiple columns have different directions of ordering
// (ascending / descending), use the second way
var top10LatestPurchaseOrdersQuery = purchaseOrderDal
.OrderBy(
Top10PurchaseOrder(),
ascending: false,
nameof(PurchaseOrder.OrderDate),
nameof(PurchaseOrder.ExpectedDeliveryDate)
);
top10LatestPurchaseOrdersQuery = purchaseOrderDal
.OrderBy(
Top10PurchaseOrder(),
(
column: nameof(PurchaseOrder.OrderDate),
ascending: false
),
(
column: nameof(PurchaseOrder.ExpectedDeliveryDate),
ascending: true
)
);
var top10LatestPurchaseOrders = purchaseOrderDal.Query(top10LatestPurchaseOrdersQuery).ToList();
Console.WriteLine($"The top 10 latest purchase orders are retrieved");
}
public void InsertExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection());
var po = new PurchaseOrder
{
SupplierID = 2,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = DateTime.UtcNow.AddDays(3),
IsOrderFinalized = true,
LastEditedBy = 1001
};
DbUtils.WithTransaction(transaction =>
{
var purchaseOrderId = purchaseOrderDal.Insert(po,
columnName =>
{
return(columnName switch
{
nameof(PurchaseOrder.OrderDate) => "CONVERT (date, SYSUTCDATETIME())", // Only the date part
nameof(PurchaseOrder.LastEditedWhen) => purchaseOrderDal.DbProvider.UtcNowExpression,
_ => null
});
}
public async Task UpdateAllAsyncExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection(),
sqlInfo =>
{
Console.WriteLine(sqlInfo.Sql);
});
var now = DateTime.UtcNow.TruncateDateTimeToSeconds();
var today = now.Date;
var pos = new List <PurchaseOrder>
{
new PurchaseOrder
{
SupplierID = 2,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = today.AddDays(3),
IsOrderFinalized = true,
LastEditedBy = 1001,
LastEditedWhen = now,
OrderDate = today
},
new PurchaseOrder
{
SupplierID = 3,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = today.AddDays(4),
IsOrderFinalized = true,
LastEditedBy = 1001,
LastEditedWhen = now,
OrderDate = today
},
new PurchaseOrder
{
SupplierID = 4,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = today.AddDays(5),
IsOrderFinalized = true,
LastEditedBy = 1001,
LastEditedWhen = now,
OrderDate = today
}
};
await purchaseOrderDal.InsertAllAsync(pos);
var insertedPos = await purchaseOrderDal.QueryByParametersAsync(new
{
LastEditedWhen = now
});
await purchaseOrderDal.ChangeSupplier(insertedPos, 5);
/* Delete the just inserted PurchaseOrders so the side facts are the smallest */
await purchaseOrderDal.DeleteAllAsync(insertedPos);
}
public void GetMinValueOfColumnExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection());
var earliestExpectedDeliveryDate = purchaseOrderDal.GetEarliestExpectedDeliveryDate();
Console.WriteLine($"The earliest expected delivery date is {earliestExpectedDeliveryDate}");
}
public async Task SimpleInclude1()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection(), sqlInfo =>
{
_testOutputHelper.WriteLine($"The sql is {sqlInfo.Sql}");
});
var poWithLines = await purchaseOrderDal.GetPurchaseOrderWithLines(1);
poWithLines.Should().NotBeNull("GetPurchaseOrderWithLines doesn't work as expected");
poWithLines.PurchaseOrderLines.Should().NotBeNullOrEmpty();
}
public void GetCountExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection());
var totalCountOfPurchaseOrders = purchaseOrderDal.GetCount <int>();
Console.WriteLine($"There are {totalCountOfPurchaseOrders} purchase orders");
var countOfPurchaseOrdersSinceJanuary31st = purchaseOrderDal.GetCountOfPurchaseOrdersSince(DateTime.Parse("2016-01-31"));
Console.WriteLine($"There are {countOfPurchaseOrdersSinceJanuary31st} purchase orders since 2016-01-31");
}
public async Task MultipleInclude()
{
var purchaseOrderDal = new PurchaseOrderDal(
CreateDapperConnection(),
sqlInfo => _testOutputHelper.WriteLine($"The sql is {sqlInfo.Sql}")
);
var purchaseOrder = await purchaseOrderDal.GetPurchaseOrderWithLinesAndPackageType4(5);
purchaseOrder.PurchaseOrderLines.Should().NotBeNull();
purchaseOrder.PurchaseOrderLines.ForEach(x => x.PackageType.Should().NotBeNull());
}
public async Task ManualDeepInclude()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection(), sqlInfo =>
{
_testOutputHelper.WriteLine($"The sql is {sqlInfo.Sql}");
});
var poWithLinesAndPackageType = await purchaseOrderDal.GetPurchaseOrderWithLinesAndPackageType(1);
poWithLinesAndPackageType.Should().NotBeNull("GetPurchaseOrderWithLinesAndPackageType doesn't work as expected");
poWithLinesAndPackageType.PurchaseOrderLines.Should().NotBeNullOrEmpty();
poWithLinesAndPackageType.PurchaseOrderLines.ForEach(x => x.PackageType.Should().NotBeNull());
}
public async Task DeleteByIdAsyncExample()
{
var dapperConnection = CreateDapperConnection();
var purchaseOrderDal = new PurchaseOrderDal(dapperConnection);
var po = new PurchaseOrder
{
SupplierID = 2,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = DateTime.UtcNow.AddDays(3),
IsOrderFinalized = true,
LastEditedBy = 1001
};
var purchaseOrderId = await purchaseOrderDal.InsertAsync(po,
columnName =>
{
if (columnName == nameof(PurchaseOrder.OrderDate))
{
// Only the date part
return("CONVERT (date, SYSUTCDATETIME())");
}
if (columnName == nameof(PurchaseOrder.LastEditedWhen))
{
return(purchaseOrderDal.DbProvider.UtcNowExpression);
}
return(null);
}
);
Console.WriteLine($"The ID of just inserted PurchaseOrder is {purchaseOrderId}");
var orderDate = po.OrderDate;
Console.WriteLine($"The order date of just inserted PurchaseOrder is {orderDate}");
var lastEditWhen = po.LastEditedWhen;
Console.WriteLine($"The last edit time of just inserted PurchaseOrder is {lastEditWhen}");
po = await purchaseOrderDal.GetAsync(po.ID);
po.Should().NotBeNull("Oops, GetAsync didn't work as expected");
/* Delete the just inserted PurchaseOrder so the side facts are the smallest */
await purchaseOrderDal.DeleteByIdAsync(po.ID);
po = await purchaseOrderDal.GetAsync(po.ID);
po.Should().BeNull("Oops, DeleteByIdAsync didn't work as expected");
}
public void GetAllExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection());
var top100PurchaseOrders = purchaseOrderDal.GetAll(100);
foreach (var po in top100PurchaseOrders)
{
Console.WriteLine($"PO's ID is {po.ID}, PO's expected delivery data is {po.ExpectedDeliveryDate}");
}
// If limit parameter not provided, or is 0, will retrieve all records
var allPurchaseOrders = purchaseOrderDal.GetAll().ToArray();
Console.WriteLine("All purchase orders retrieved");
}
public async Task UpdateAsyncExample()
{
var purchaseOrderDal = new PurchaseOrderDal(CreateDapperConnection(), sqlInfo => Console.Write(sqlInfo.Sql));
var po = new PurchaseOrder
{
SupplierID = 2,
ContactPersonID = 1001,
DeliveryMethodID = 1,
ExpectedDeliveryDate = DateTime.UtcNow.AddDays(3),
IsOrderFinalized = true,
LastEditedBy = 1001
};
var purchaseOrderId = await purchaseOrderDal.InsertAsync(po,
columnName =>
{
return(columnName switch
{
nameof(PurchaseOrder.OrderDate) => "CONVERT (date, SYSUTCDATETIME())", // Only the date part
nameof(PurchaseOrder.LastEditedWhen) => purchaseOrderDal.DbProvider.UtcNowExpression,
_ => null
});