C# (CSharp) OdbcMySqlOnDuplicateKeyUpdate.ToSql 예제들

예제 #1

파일: OnDuplicateKeyUpdateTest.cs 프로젝트: zzl1010/FreeSql

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

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(100, 'title-100', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`), 
`time` = VALUES(`time`)", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

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

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(100, 'title-100', '2000-01-01 00:00:00.000'), (101, 'title-101', '2000-01-01 00:00:00.000'), (102, 'title-102', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`), 
`time` = VALUES(`time`)", odku2.ToSql());
            odku2.ExecuteAffrows();
        }

예제 #2

파일: OnDuplicateKeyUpdateTest.cs 프로젝트: zzl1010/FreeSql

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

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200')
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`), 
`time` = '2000-01-01 00:00:00.000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

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

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
ON DUPLICATE KEY UPDATE
`title` = VALUES(`title`), 
`time` = CASE `id` 
WHEN 200 THEN '2000-01-01 00:00:00.000' 
WHEN 201 THEN '2000-01-01 00:00:00.000' 
WHEN 202 THEN '2000-01-01 00:00:00.000' END", odku2.ToSql());
            odku2.ExecuteAffrows();


            g.mysql.Delete <TestOnDuplicateKeyUpdateInfo>(new[] { 200, 201, 202 }).ExecuteAffrows();
            odku1 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new TestOnDuplicateKeyUpdateInfo {
                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 `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200')
ON DUPLICATE KEY UPDATE
`time` = '2000-01-01 00:00:00.000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            odku2 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new[] {
                new TestOnDuplicateKeyUpdateInfo {
                    id = 200, title = "title-200", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 201, title = "title-201", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    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 `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
ON DUPLICATE KEY UPDATE
`time` = CASE `id` 
WHEN 200 THEN '2000-01-01 00:00:00.000' 
WHEN 201 THEN '2000-01-01 00:00:00.000' 
WHEN 202 THEN '2000-01-01 00:00:00.000' END", odku2.ToSql());
            odku2.ExecuteAffrows();
        }

예제 #3

파일: OnDuplicateKeyUpdateTest.cs 프로젝트: zzl1010/FreeSql

        public void Set()
        {
            g.mysql.Delete <TestOnDuplicateKeyUpdateInfo>(new[] { 400, 401, 402 }).ExecuteAffrows();
            var odku1 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new TestOnDuplicateKeyUpdateInfo {
                id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
            }).NoneParameter().InsertIdentity()).Set(a => a.time, DateTime.Parse("2020-1-1"));

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            var odku2 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new[] {
                new TestOnDuplicateKeyUpdateInfo {
                    id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 401, title = "title-401", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 402, title = "title-402", time = DateTime.Parse("2000-01-01")
                }
            }).NoneParameter().InsertIdentity()).Set(a => a.time, DateTime.Parse("2020-1-1"));

            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000'), (401, 'title-401', '2000-01-01 00:00:00.000'), (402, 'title-402', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000'", odku2.ToSql());
            odku2.ExecuteAffrows();


            var dt2020 = DateTime.Parse("2020-1-1");

            g.mysql.Delete <TestOnDuplicateKeyUpdateInfo>(new[] { 400, 401, 402 }).ExecuteAffrows();
            odku1 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new TestOnDuplicateKeyUpdateInfo {
                id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
            }).NoneParameter().InsertIdentity()).Set(a => a.time == dt2020);
            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000'", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            odku2 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new[] {
                new TestOnDuplicateKeyUpdateInfo {
                    id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 401, title = "title-401", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 402, title = "title-402", time = DateTime.Parse("2000-01-01")
                }
            }).NoneParameter().InsertIdentity()).Set(a => a.time == dt2020);
            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000'), (401, 'title-401', '2000-01-01 00:00:00.000'), (402, 'title-402', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000'", odku2.ToSql());
            odku2.ExecuteAffrows();


            g.mysql.Delete <TestOnDuplicateKeyUpdateInfo>(new[] { 400, 401, 402 }).ExecuteAffrows();
            odku1 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new TestOnDuplicateKeyUpdateInfo {
                id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
            }).NoneParameter().InsertIdentity()).Set(a => new { time = dt2020, title = a.title + "123" });
            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000', `title` = concat(`title`, '123')", odku1.ToSql());
            Assert.Equal(1, odku1.ExecuteAffrows());

            odku2 = new OdbcMySqlOnDuplicateKeyUpdate <TestOnDuplicateKeyUpdateInfo>(g.mysql.Insert(new[] {
                new TestOnDuplicateKeyUpdateInfo {
                    id = 400, title = "title-400", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 401, title = "title-401", time = DateTime.Parse("2000-01-01")
                },
                new TestOnDuplicateKeyUpdateInfo {
                    id = 402, title = "title-402", time = DateTime.Parse("2000-01-01")
                }
            }).NoneParameter().InsertIdentity()).Set(a => new { time = dt2020, title = a.title + "123" });
            Assert.Equal(@"INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(400, 'title-400', '2000-01-01 00:00:00.000'), (401, 'title-401', '2000-01-01 00:00:00.000'), (402, 'title-402', '2000-01-01 00:00:00.000')
ON DUPLICATE KEY UPDATE
`time` = '2020-01-01 00:00:00.000', `title` = concat(`title`, '123')", odku2.ToSql());
            odku2.ExecuteAffrows();
        }