static async Task Main(string[] args)
{
//Setup - Register the Dapper SqlMapper
Console.WriteLine("Setting up...");
CustomMapper customMapper = new CustomMapper();
customMapper.RegisterDapperDtoMap();
DataAccessService dataAccessService = new DataAccessService();
//Example #1 - Async execution without return recordset
Console.WriteLine("Example #1: Running queries without return recordset");
//Initialize the database using this example, which the data will be used for the rest of this demo
await dataAccessService.ExecuteAsync("DROP TABLE IF EXISTS beer");
await dataAccessService.ExecuteAsync("DROP TABLE IF EXISTS brewery");
await dataAccessService.ExecuteAsync("CREATE TABLE beer(Name VARCHAR(50), Company VARCHAR(50), Style VARCHAR(50))");
await dataAccessService.ExecuteAsync("CREATE TABLE brewery(Name VARCHAR(50), Country VARCHAR(50), CEO VARCHAR(50), Year_Est INT)");
await dataAccessService.ExecuteAsync("INSERT INTO beer VALUES('Miller Lite', 'Molson Coors Beverage Company', 'Pale Lager')");
await dataAccessService.ExecuteAsync("INSERT INTO beer VALUES('Corona Extra', 'AB InBev', 'Pale Lager')");
await dataAccessService.ExecuteAsync("INSERT INTO beer VALUES('Somersby cider', 'Carlsberg A/S', 'Cider')");
await dataAccessService.ExecuteAsync("INSERT INTO beer VALUES('Heineken Lager Beer', 'Heineken N.V', 'Pale Lager')");
await dataAccessService.ExecuteAsync("INSERT INTO brewery VALUES('AB InBev', 'Belgium', 'Carlos Brito', 2008)");
await dataAccessService.ExecuteAsync("INSERT INTO brewery VALUES('Heineken N.V', 'Netherlands', 'Jean-François van Boxmeer', 1864)");
await dataAccessService.ExecuteAsync("INSERT INTO brewery VALUES('Carlsberg A/S', 'Denmark', 'Cees ''t Hart', 1847)");
await dataAccessService.ExecuteAsync("INSERT INTO brewery VALUES('Molson Coors Beverage Company', 'USA', 'Gavin Hattersley', 2005)");
//Example #2 - Async execution with return recordset mapped to a class
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("Example #2 - Async execution with return recordset mapped to a class");
var example2Result = await dataAccessService.QueryAsync <Beer>("Select * from beer");
if (example2Result.IsSuccess)
{
var result = example2Result.Result;
result.ForEach(x => Console.WriteLine("Example 2 - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
}
/*
* Example #3 - Async execution with return recordset mapped to a class
* This example shows how object is mapped to class when database column name is different than variable name in the class
* Example uses brewery class, which has the variable YearEstablished, which is mapped to database column name Year_Est
*
* This is accomplished by:
* 1) Creating a custom Attribute class, see MapToAttribute.cs
* 2) Init the mapping via CustomMapper.cs, which uses Dapper SqlMapper. Class variable will be scanned for MapTo attribute,
* which can override the default variable name when it is used to map against the database column
*/
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("Example #3 - Async execution with return recordset mapped to a class ");
var example3Result = await dataAccessService.QueryAsync <Brewery>("Select * from brewery");
if (example3Result.IsSuccess)
{
var result = example3Result.Result;
result.ForEach(x => Console.WriteLine("Example 3 - Retrieved Beer: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
}
//Example #4 - Async execution of query with parameter returning recordset mapped to a class
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("Example #4 - Async execution of query with parameter returning recordset mapped to a class");
var example4parametersSet1 = new DynamicParameters();
example4parametersSet1.Add("Style", "Cider");
var example4Result = await dataAccessService.QueryAsync <Beer>("Select * from beer where Style = @Style", false, example4parametersSet1); //condition equals
var example4parametersSet2 = new DynamicParameters();
example4parametersSet2.Add("Name", "%er%");
var example4Result2 = await dataAccessService.QueryAsync <Brewery>("Select * from brewery where Name like @Name", false, example4parametersSet2); //condition like
if (example4Result.IsSuccess)
{
var result = example4Result.Result;
result.ForEach(x => Console.WriteLine("Example 4 (Select Query; Condition equal) - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
}
if (example4Result2.IsSuccess)
{
var result = example4Result2.Result;
result.ForEach(x => Console.WriteLine("Example 4 (Select Query; Condition like) - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
}
//Example #5 - Async execution of query which returns multiple record set mapped to a tuple of classes
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("//Example #5 - Async execution of query which returns multiple record set mapped to a tuple of classes");
var example5Result = await dataAccessService.QueryMultipleAsync <Beer, Brewery>("Select * from beer; Select * from brewery");
if (example5Result.IsSuccess)
{
List <Beer> beerList = example5Result.ListT1;
List <Brewery> breweryList = example5Result.ListT2;
Console.WriteLine("Expanding the first record set - beer:");
beerList.ForEach(x => Console.WriteLine("Example 5 - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
Console.WriteLine("Expanding the second record set - brewery:");
breweryList.ForEach(x => Console.WriteLine("Example 5 - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
}
//Example #6 - Async execution of query which returns multiple record set mapped to a tuple of classes (with empty resultset)
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("//Example #6 - Async execution of query which returns multiple record set mapped to a tuple of classes (with empty resultset)");
var example6Result = await dataAccessService.QueryMultipleAsync <Beer, Brewery, Brewery>("Select * from beer; Select * from brewery where 1=2; Select * from brewery");
if (example6Result.IsSuccess)
{
List <Beer> beerList = example6Result.ListT1;
List <Brewery> breweryList = example6Result.ListT2;
List <Brewery> breweryList2 = example6Result.ListT3;
Console.WriteLine("Expanding the first record set - beer:");
beerList.ForEach(x => Console.WriteLine("Example 6 - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
Console.WriteLine("Expanding the second record set (This should be an empty record set) - brewery:");
breweryList.ForEach(x => Console.WriteLine("Example 6 - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
Console.WriteLine("Expanding the third record set - brewery:");
breweryList2.ForEach(x => Console.WriteLine("Example 6 - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
}
//Example #7 - Async execution of query with multiple record set, delayed resolving of recordsets (can be used when # of record sets returned > 7)
Console.WriteLine("\n\n\n\n\n\n\n\n");
Console.WriteLine("//Example #7 - Async execution of query with multiple record set, delayed resolving of recordsets (can be used when # of record sets returned > 7)");
Type[] mapToTypes = { typeof(Beer), typeof(Brewery), typeof(Brewery), typeof(Beer) }; //Type array
var example7Result = await dataAccessService.QueryMultipleAsync("Select * from beer; Select * from brewery; Select * from brewery; Select * from beer", false, null, mapToTypes);
if (example7Result.IsSuccess)
{
//resolve after retrieving through casting
List <IEnumerable <object> > result = example7Result.Result;
List <Beer> beerList = result[0].Cast <Beer>().ToList();
List <Brewery> breweryList = result[1].Cast <Brewery>().ToList();
List <Brewery> breweryList2 = result[2].Cast <Brewery>().ToList();
List <Beer> beerList2 = result[3].Cast <Beer>().ToList();
Console.WriteLine("Expanding the first record set - beer:");
beerList.ForEach(x => Console.WriteLine("Example 7 - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
Console.WriteLine("Expanding the second record set - brewery:");
breweryList.ForEach(x => Console.WriteLine("Example 7 - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
Console.WriteLine("Expanding the third record set - brewery:");
breweryList2.ForEach(x => Console.WriteLine("Example 7 - Retrieved Brewery: [" + "Name: " + x.Name + " | Country: " + x.Country
+ " | CEO: " + x.Ceo + " | Year Established: " + x.YearEstablished + "]"));
Console.WriteLine("Expanding the fourth record set - beer:");
beerList.ForEach(x => Console.WriteLine("Example 7 - Retrieved Beer: [" + "Name: " + x.Name + " | Company: " + x.Company + " | Style: " + x.Style + "]"));
}
Console.ReadLine();
}