代码如下:
/// <summary>
/// 事务测试
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public int ServiceTest(string str)
{
int ret=0;
using (OdbcConnection conn = new OdbcConnection(str))
{
conn.Open();
OdbcCommand command = conn.CreateCommand();
OdbcTransaction transaction;
//启动事务
transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
try
{
//获取插入数据后的ID
command.CommandText = "insert into com_user (`username`,`password`,`logintime`,`loginip`,`lock`) values('bbb','bbb',222,'bbb',0);SELECT LAST_INSERT_ID();";//SELECT LAST_INSERT_ID()是mysql提供的方法
int aa = command.ExecuteNonQuery();
//获取插入的ID
int newId = Convert.ToInt32(command.ExecuteScalar());
//下面的sql语句是错误的,为了catch错误回滚
command.CommandText = "insert into com_user_1 (`username`,`password`,`logintime`,`loginip`,`lock`) values('aaa','aaa',111,'aaa',0)";
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
ret = -1;
}
finally
{
conn.Close();
}
}
return ret;
}
1、刚开始的时候事务无法回滚,主要原因是数据库表引擎设置问题,需要设置成InnoDB模式。
2、数据库表结构:。