事务的回滚对于拥有自增主键的表来说,insert的数据是被删除,update和delete的数据是被恢复
建表代码:
create table users(
userid int primary key identity(1,1),
uname varchar(100),
utime datetime
)
c#调用代码
private void selectInsertSelectUpdateSelectDeleteSelect()
{
SqlConnection conn = getConn();
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
SqlDataAdapter cmdSelect = new SqlDataAdapter("select * from users", conn);
cmdSelect.SelectCommand.Transaction = tran;
DataTable dtSelect = new DataTable();
SqlCommand cmdInsert = new SqlCommand("insert into users(uname, utime) values('cc', GETDATE())", conn);
cmdInsert.Transaction = tran;
SqlDataAdapter cmdSelect_1 = new SqlDataAdapter("select * from users", conn);
cmdSelect_1.SelectCommand.Transaction = tran;
DataTable dtSelect_1 = new DataTable();
SqlCommand cmdUpdate = new SqlCommand("update users set utime=getdate()", conn);
cmdUpdate.Transaction = tran;
SqlDataAdapter cmdSelect_2 = new SqlDataAdapter("select * from users", conn);
cmdSelect_2.SelectCommand.Transaction = tran;
DataTable dtSelect_2 = new DataTable();
SqlCommand cmdDelete = new SqlCommand("delete from users where userid=(select MAX(userid) from users)", conn);
cmdDelete.Transaction = tran;
SqlDataAdapter cmdSelect_3 = new SqlDataAdapter("select * from users", conn);
cmdSelect_3.SelectCommand.Transaction = tran;
DataTable dtSelect_3 = new DataTable();
cmdSelect.Fill(dtSelect);
bindData(dtSelect, gv);
cmdInsert.ExecuteNonQuery();
cmdSelect_1.Fill(dtSelect_1);
bindData(dtSelect_1, gv1);
cmdUpdate.ExecuteNonQuery();
cmdSelect_2.Fill(dtSelect_2);
bindData(dtSelect_2, gv2);
cmdDelete.ExecuteNonQuery();
cmdSelect_3.Fill(dtSelect_3);
bindData(dtSelect_3, gv3);
/*
* 此处丢异常,然后回滚
*/
int x = 1;
int y = 0;
int z = x / y;
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
Response.Write("fuck 有异常,老子回滚了");
}
finally
{
conn.Close();
}
}
private void bindData(DataTable dt, GridView gv)
{
gv.DataSource = dt;
gv.DataBind();
}
private SqlConnection getConn()
{
string connStr = "server=.;uid=sa;pwd=123456;database=test;";
return new SqlConnection(connStr);
}