http://tech.it168.com/db/s/2006-07-29/200607291423202.shtml
事务写法
begin transaction
commit transaction
--提交
rollback transaction
--回滚
//执行事务处理
public void DoTran()
{ //建立连接并打开
SqlConnection myConn=GetConn();
myConn.Open();
SqlCommand myComm=new SqlCommand();
//SqlTransaction myTran=new SqlTransaction();
//注意,SqlTransaction类无公开的构造函数
SqlTransaction myTran;
//创建一个事务
myTran=myConn.BeginTransaction();
try
{
//从此开始,基于该连接的数据操作都被认为是事务的一部分
//下面绑定连接和事务对象
myComm.Connection=myConn;
myComm.Transaction=myTran; //定位到pubs数据库
myComm.CommandText="USE pubs";
myComm.ExecuteNonQuery();//更新数据
//将所有的计算机类图书
myComm.CommandText="UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE
'Pc%'";
myComm.ExecuteNonQuery();
//提交事务
myTran.Commit();
}
catch(Exception err)
{
throw new ApplicationException("事务操作出错,系统信息:"+err.Message);
}
finally
{
myConn.Close();
}
}
private SqlConnection GetConn()
{
string strSql="Data Source=localhost;Integrated Security=SSPI;user id=sa;password=";
SqlConnection myConn=new SqlConnection(strSql);
return myConn;
}
}
public class Test
{
public static void Main()
{
DbTranSql tranTest=new DbTranSql();
tranTest.DoTran();
Console.WriteLine("事务处理已经成功完成。");
Console.ReadLine();
}
}
在很多的项目开发中会遇到这样的情况:比如论坛注册用户,注册成功是分为两个部分的,第一个部分是先
向用户表里插入一条记录,再向操作日志表里插入一条记录.这样要分为两步走,因为只有在第一条插入成
功后才能执行第二条语句.万一第一条成功了,在执行第二条的时候遇到突发事件,如电脑死机了,停电了等
.系统应该把此用户的状态回复到注册前的状态.也就是说此用户注册不成功...
要实现以上的功能,就需要用到sql的事务处理了.下面给个简单的实例:
SqlConnection con = new SqlConnection("server=(local);DataBase=db;User ID=sa;PWD=");
con.Open();
SqlTransaction st = con.BeginTransaction();//通过SqlConnection的BeginTransaction方法创建名为
st的对象Transaction
SqlCommand com = con.CreateCommand();
com.Transaction = st;//将SqlTransaction对象分配给SqlCommand对象的Transaction属性
try
{
//向用户表中插入注册信息
com.CommandText = "";//这里省略了插入语句,你要自己写了.呵呵....
com.ExecuteNonQuery();
//向日志表中插入数据
com.CommandText = "";//这里也省略了插入语句,你也要自己写了.呵呵....
com.ExecuteNonQuery();
st.Commit();//提交事物
Response.Write("<script>alert('注册成功!');location='javascript:history.go
(-1)'</script>");
}
catch (Exception error)
{
st.Rollback();//回滚事物
}
public void RunSqlTransaction(string myConnString)
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100,
´Description´)";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101,
´Description´)";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the
transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}