protected override IExprSubQuery CreateQuery()
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
return(Select(
tCustomer.CustomerId.As(this.CustomerId),
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(Literal(1), SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(Literal(2), SqlType.Int16))
.Else(Null)
.As(this.Type),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(this.Name)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.Done());
}
private static async Task Step13TempTables(ISqDatabase database)
{
var tmp = new TempTable();
var tableUser = new TableUser();
var tableCompany = new TableCompany();
await database.Statement(tmp.Script.Create());
//Users
await InsertInto(tmp, tmp.Name)
.From(Select(tableUser.FirstName + " " + tableUser.LastName)
.From(tableUser))
.Exec(database);
//Companies
await InsertInto(tmp, tmp.Name)
.From(Select(tableCompany.CompanyName)
.From(tableCompany))
.Exec(database);
await Select(tmp.Columns)
.From(tmp)
.OrderBy(tmp.Name)
.Query(database,
r => Console.WriteLine($"Id: {tmp.Id.Read(r)}, Name: {tmp.Name.Read(r)}"));
//Dropping the temp table is optional
//It will be automatically removed when
//the connection is closed
await database.Statement(tmp.Script.Drop());
}
private static async Task Step7CreatingCustomers(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var tSubCustomer = new TableCustomer();
//Users
await InsertInto(tCustomer, tCustomer.UserId)
.From(
Select(tUser.UserId)
.From(tUser)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.UserId == tUser.UserId))))
.Exec(database);
//Companies
await InsertInto(tCustomer, tCustomer.CompanyId)
.From(
Select(tCompany.CompanyId)
.From(tCompany)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.CompanyId == tCompany.CompanyId))))
.Exec(database);
}
private static async Task Step6CreatingOrganizations(ISqDatabase database)
{
var tCompany = new TableCompany();
Console.WriteLine("Companies:");
await InsertDataInto(tCompany, new[] { "Microsoft", "Google" })
.MapData(s => s.Set(s.Target.CompanyName, s.Source))
.AlsoInsert(s => s
.Set(s.Target.Version, 1)
.Set(s.Target.ModifiedAt, GetUtcDate()))
.Output(tCompany.CompanyId, tCompany.CompanyName)
.Query(database,
r => Console.WriteLine($"Id: {tCompany.CompanyId.Read(r)}, Name: {tCompany.CompanyName.Read(r)}"));
}
private static async Task Step8JoinTables(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var cType = CustomColumnFactory.Int16("Type");
var cName = CustomColumnFactory.String("Name");
var customers = await Select(
tCustomer.CustomerId,
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(1, SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(2, SqlType.Int16))
.Else(Null)
.As(cType),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(cName)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.QueryList(database,
r => (Id: tCustomer.CustomerId.Read(r), CustomerType: cType.Read(r), Name: cName.Read(r)));
foreach (var customer in customers)
{
Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, Type: {customer.CustomerType}");
}
}
