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();
}
}
}