private static async Task Step11AnalyticAndWindowFunctions(ISqDatabase database) { var cUserName = CustomColumnFactory.String("Name"); var cNum = CustomColumnFactory.Int64("Num"); var cFirst = CustomColumnFactory.String("First"); var cLast = CustomColumnFactory.String("Last"); var user = new TableUser(); await Select( (user.FirstName + " " + user.LastName) .As(cUserName), RowNumber() /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .As(cNum), FirstValue(user.FirstName + " " + user.LastName) /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .FrameClauseEmpty() .As(cFirst), LastValue(user.FirstName + " " + user.LastName) /*.OverPartitionBy(some fields)*/ .OverOrderBy(user.FirstName) .FrameClause( FrameBorder.UnboundedPreceding, FrameBorder.UnboundedFollowing) .As(cLast)) .From(user) .Query(database, r => Console.WriteLine( $"Num: {cNum.Read(r)}, Name: {cUserName.Read(r)}, " + $"First: {cFirst.Read(r)}, Last: {cLast.Read(r)}")); }
private static async Task Step12Merge(ISqDatabase database) { var data = new[] { new { FirstName = "Francois", LastName = "Sturman2" }, new { FirstName = "Allina", LastName = "Freeborne2" }, new { FirstName = "Maye", LastName = "Malloy" }, }; var action = CustomColumnFactory.String("Actions"); var inserted = CustomColumnFactory.NullableInt32("Inserted"); var deleted = CustomColumnFactory.NullableInt32("Deleted"); var tableUser = new TableUser(); await MergeDataInto(tableUser, data) .MapDataKeys(s => s .Set(s.Target.FirstName, s.Source.FirstName)) .MapData(s => s .Set(s.Target.LastName, s.Source.LastName)) .WhenMatchedThenUpdate() .AlsoSet(s => s .Set(s.Target.Version, s.Target.Version + 1) .Set(s.Target.ModifiedAt, GetUtcDate())) .WhenNotMatchedByTargetThenInsert() .AlsoInsert(s => s .Set(s.Target.Version, 1) .Set(s.Target.ModifiedAt, GetUtcDate())) .Output((t, s, m) => m.Inserted(t.UserId.As(inserted)).Deleted(t.UserId.As(deleted)).Action(action)) .Done() .Query(database, r => Console.WriteLine( $"UserId Inserted: {inserted.Read(r)},UserId Deleted: {deleted.Read(r)} , Action: {action.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}"); } }
public void FullTest() { const int usersCount = 3; var data = new List <UserData>(usersCount); for (int i = 0; i < usersCount; i++) { data.Add(new UserData { UserId = i % 2 == 0 ? 0 : i, FirstName = "First" + i, LastName = "Last" + i, EMail = $"user{i}@company.com", RegDate = new DateTime(2020, 01, 02) }); } DateTime utcNow = new DateTime(2020, 10, 03, 10, 17, 12, 131); var recordIndex = CustomColumnFactory.Int32("Index"); var inserted = CustomColumnFactory.Int32("InsertedUserId"); var deleted = CustomColumnFactory.Int32("DeletedUserId"); var action = CustomColumnFactory.String("Action"); var mergeOutput = SqQueryBuilder .MergeDataInto(Tables.User(), data) .MapDataKeys(s => s.Set(s.Target.UserId, s.Source.UserId)) .MapData(s => s .Set(s.Target.FirstName, s.Source.FirstName) .Set(s.Target.LastName, s.Source.LastName) .Set(s.Target.Email, s.Source.EMail) .Set(s.Target.RegDate, s.Source.RegDate)) .MapExtraData(s => s.Set(recordIndex, s.Index)) .AndOn((t, s) => t.UserId.WithSource(s) != 0) .WhenMatchedThenUpdate() .AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1) .Set(s.Target.Modified, utcNow)) .WhenNotMatchedByTargetThenInsert() .ExcludeKeys() .Exclude(t => new[] { t.Email.ColumnName, t.LastName.ColumnName }) .AlsoInsert(s => s .Set(s.Target.LastName, "Fake") .Set(s.Target.Created, utcNow) .Set(s.Target.Modified, utcNow) .Set(s.Target.Version, 1)) .WhenNotMatchedBySourceThenDelete() .Output((t, s, m) => m .Inserted(t.UserId.As(inserted)) .Inserted(t.UserId.As(deleted)) .Column(recordIndex.WithSource(s)) .Action(action)) .Done(); var actual = mergeOutput?.ToSql(); var expected = "MERGE [dbo].[user] [A0] USING (" + "VALUES (0,'First0','Last0','*****@*****.**','2020-01-02',0)," + "(1,'First1','Last1','*****@*****.**','2020-01-02',1)," + "(0,'First2','Last2','*****@*****.**','2020-01-02',2)" + ")[A1]([UserId],[FirstName],[LastName],[Email],[RegDate],[Index]) " + "ON [A0].[UserId]=[A1].[UserId] AND [A1].[UserId]!=0 " + "WHEN MATCHED THEN UPDATE SET " + "[A0].[FirstName]=[A1].[FirstName]," + "[A0].[LastName]=[A1].[LastName]," + "[A0].[Email]=[A1].[Email]," + "[A0].[RegDate]=[A1].[RegDate]," + "[A0].[Version]=[A0].[Version]+1," + "[A0].[Modified]='2020-10-03T10:17:12.131' " + "WHEN NOT MATCHED THEN INSERT" + "([FirstName],[RegDate],[LastName],[Created],[Modified],[Version]) " + "VALUES([A1].[FirstName],[A1].[RegDate],'Fake','2020-10-03T10:17:12.131','2020-10-03T10:17:12.131',1) " + "WHEN NOT MATCHED BY SOURCE THEN DELETE " + "OUTPUT INSERTED.[UserId] [InsertedUserId],INSERTED.[UserId] [DeletedUserId],[A1].[Index],$ACTION [Action];"; Assert.AreEqual(expected, actual); }