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}"); } }