示例#1
0
        public void TestTransaction()
        {
            var sqlCmd = $"{ConvertSeparate(Insert, DatabaseType.PostgreSQL)}; update account set \"name\" = 'Eddie in transaction' where id in (SELECT currval(pg_get_serial_sequence('account','id')));";
            var result = Brook.Load("posql").Transaction(sqlCmd, new List <DbParameter[]> {
                new[]
                {
                    Brook.Load("posql").Parameter("@name", "QQ1"),
                    Brook.Load("posql").Parameter("@email", $"*****@*****.**")
                }, new[]
                {
                    Brook.Load("posql").Parameter("@name", "QQ2"),
                    Brook.Load("posql").Parameter("@email", $"*****@*****.**")
                }
            });

            if (!result.Ok)
            {
                TestContext.WriteLine($"Why:{result.Err}");
            }

            result = Brook.Load("posql").Transaction("insert into account (name,email)values('Eddie', '@email');");
            if (!result.Ok)
            {
                TestContext.WriteLine($"Why:{result.Err}");
            }

            var qq = Brook.Load("posql").First <Account>("SELECT id AS \"Id\", name AS \"Name\", email AS \"Email\",time AS \"Time\" FROM account order by id desc limit 1;");


            SqlMapper db = null;

            try
            {
                db = Brook.Load("posql");
                var name = $"我是交易1-{DateTime.Now:HHmmss}";
                db.BeginTransaction();
                db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
                {
                    new[]
                    {
                        db.Parameter("@name", name),
                        db.Parameter("@email", $"{name}@sqlserver.com")
                    }
                });
                var t = db.Table(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
                TestContext.WriteLine($"[Table] Id = {t.Rows[0]["Id"]} Name = {t.Rows[0]["Name"]} Email = {t.Rows[0]["Email"]} ");
                db.CommitTransaction();

                name = $"我是交易2-{DateTime.Now:HHmmss}";
                db.BeginTransaction();
                db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
                {
                    new[]
                    {
                        db.Parameter("@name", name),
                        db.Parameter("@email", $"{name}@sqlserver.com")
                    }
                });
                var account = db.First <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
                TestContext.WriteLine($"[First] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
                var accounts = db.Query <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
                TestContext.WriteLine($"[Query] Id = {accounts[0].Id} Name = {accounts[0].Name} Email = {accounts[0].Email}");

                db.CommitTransaction();

                name = $"我是交易3-{DateTime.Now:HHmmss}";
                db.BeginTransaction();
                db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
                {
                    new[]
                    {
                        db.Parameter("@name", name),
                        db.Parameter("@email", $"{name}@sqlserver.com")
                    }
                });
                db.RollbackTransaction();

                name = $"我不是交易1-{DateTime.Now:HHmmss}";
                db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
                {
                    new[]
                    {
                        db.Parameter("@name", name),
                        db.Parameter("@email", $"{name}@sqlserver.com")
                    }
                });
                var ds = db.DataSet(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
                TestContext.WriteLine($"[DataSet] Id = {ds.Tables[0].Rows[0]["Id"]} Name = {ds.Tables[0].Rows[0]["Name"]} Email = {ds.Tables[0].Rows[0]["Email"]}");


                name = $"我是交易4-{DateTime.Now:HHmmss}";
                db.BeginTransaction();
                db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
                {
                    new[]
                    {
                        db.Parameter("@name", name),
                        db.Parameter("@email", $"{name}@sqlserver.com")
                    }
                });
                db.ChangeDatabase("postgres");
                //throw new Exception("QQ");
            }
            catch (Exception e)
            {
                TestContext.WriteLine(e);
                if (db != null)
                {
                    db.RollbackTransaction();
                    db.Dispose();
                }
            }
        }