C# (CSharp) OdbcKingbaseESOnConflictDoUpdate.ToSqlの例

コード例 #1

ファイル: OnConflictDoUpdateTest.cs プロジェクト: zzl1010/FreeSql

        public void ExecuteAffrows()
        {
            g.kingbaseES.Delete <TestOnConflictDoUpdateInfo>(new[] { 100, 101, 102 }).ExecuteAffrows();
            var odku1 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new TestOnConflictDoUpdateInfo {
                id = 100, title = "title-100", time = DateTime.Parse("2000-01-01")
            }).NoneParameter().InsertIdentity());

            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"", ""TIME"") VALUES(100, 'title-100', '2000-01-01 00:00:00.000000')
ON CONFLICT(""ID"") DO UPDATE SET
""TITLE"" = EXCLUDED.""TITLE"", 
""TIME"" = EXCLUDED.""TIME""", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            var odku2 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new[] {
                new TestOnConflictDoUpdateInfo {
                    id = 100, title = "title-100", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 101, title = "title-101", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 102, title = "title-102", time = DateTime.Parse("2000-01-01")
                }
            }).NoneParameter().InsertIdentity());

            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"", ""TIME"") VALUES(100, 'title-100', '2000-01-01 00:00:00.000000'), (101, 'title-101', '2000-01-01 00:00:00.000000'), (102, 'title-102', '2000-01-01 00:00:00.000000')
ON CONFLICT(""ID"") DO UPDATE SET
""TITLE"" = EXCLUDED.""TITLE"", 
""TIME"" = EXCLUDED.""TIME""", odku2.ToSql());
            odku2.ExecuteAffrows();
        }

コード例 #2

ファイル: OnConflictDoUpdateTest.cs プロジェクト: zzl1010/FreeSql

        public void IgnoreColumns()
        {
            g.kingbaseES.Delete <TestOnConflictDoUpdateInfo>(new[] { 200, 201, 202 }).ExecuteAffrows();
            var odku1 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new TestOnConflictDoUpdateInfo {
                id = 200, title = "title-200", time = DateTime.Parse("2000-01-01")
            }).IgnoreColumns(a => a.time).NoneParameter().InsertIdentity());

            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"") VALUES(200, 'title-200')
ON CONFLICT(""ID"") DO UPDATE SET
""TITLE"" = EXCLUDED.""TITLE"", 
""TIME"" = '2000-01-01 00:00:00.000000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            var odku2 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new[] {
                new TestOnConflictDoUpdateInfo {
                    id = 200, title = "title-200", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 201, title = "title-201", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 202, title = "title-202", time = DateTime.Parse("2000-01-01")
                }
            }).IgnoreColumns(a => a.time).NoneParameter().InsertIdentity());

            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"") VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
ON CONFLICT(""ID"") DO UPDATE SET
""TITLE"" = EXCLUDED.""TITLE"", 
""TIME"" = CASE EXCLUDED.""ID"" 
WHEN 200 THEN '2000-01-01 00:00:00.000000' 
WHEN 201 THEN '2000-01-01 00:00:00.000000' 
WHEN 202 THEN '2000-01-01 00:00:00.000000' END::timestamp", odku2.ToSql());
            odku2.ExecuteAffrows();


            g.kingbaseES.Delete <TestOnConflictDoUpdateInfo>(new[] { 200, 201, 202 }).ExecuteAffrows();
            odku1 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new TestOnConflictDoUpdateInfo {
                id = 200, title = "title-200", time = DateTime.Parse("2000-01-01")
            }).IgnoreColumns(a => a.time).NoneParameter().InsertIdentity()).IgnoreColumns(a => a.title);
            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"") VALUES(200, 'title-200')
ON CONFLICT(""ID"") DO UPDATE SET
""TIME"" = '2000-01-01 00:00:00.000000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            odku2 = new OdbcKingbaseESOnConflictDoUpdate <TestOnConflictDoUpdateInfo>(g.kingbaseES.Insert(new[] {
                new TestOnConflictDoUpdateInfo {
                    id = 200, title = "title-200", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 201, title = "title-201", time = DateTime.Parse("2000-01-01")
                },
                new TestOnConflictDoUpdateInfo {
                    id = 202, title = "title-202", time = DateTime.Parse("2000-01-01")
                }
            }).IgnoreColumns(a => a.time).NoneParameter().InsertIdentity()).IgnoreColumns(a => a.title);
            Assert.Equal(@"INSERT INTO ""TESTONCONFLICTDOUPDATEINFO""(""ID"", ""TITLE"") VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
ON CONFLICT(""ID"") DO UPDATE SET
""TIME"" = CASE EXCLUDED.""ID"" 
WHEN 200 THEN '2000-01-01 00:00:00.000000' 
WHEN 201 THEN '2000-01-01 00:00:00.000000' 
WHEN 202 THEN '2000-01-01 00:00:00.000000' END::timestamp", odku2.ToSql());
            odku2.ExecuteAffrows();
        }