Esempio n. 1
0
        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());
        }
Esempio n. 2
0
        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());
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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)}"));
        }
Esempio n. 5
0
        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}");
            }
        }