1>存儲過程級別的事務。
create procedure addinfo
(@studentname varchar(20),....)
as
begin transaction
insert ......
insert....
if .....
rollback transaction
update.....
.....
commit transaction
注：可以在存儲過程中使用save transaction選擇回滾的位置
2>數據庫級別的事務處理。
需要導入imports system.data.sqlclient名稱空間。
'this function will add student's infomation and its parent's information concurrently !
'so we should use transaction !
public shared function insertinfo(byval student as clsstudent, byval parent as clsparent) as boolean
dim success as boolean = true
dim cmdstudent as new sqlcommand("insert into student(name,sex,classname) values(@name,@sex,@classname)", cnn)
dim cmdparent as new sqlcommand("insert into parent(name,sex,salary) values(@name,@sex,@salary)", cnn)
dim cmdgetstudentid as new sqlcommand("select studentid from student where [email protected] ", cnn)
dim cmdgetparentid as new sqlcommand("select parentid from parent where [email protected]", cnn)
dim cmdstudentparent as new sqlcommand("insert into studentparent(studentid,parentid)values(@studentid,@parentid)", cnn)
cmdstudent.parameters.add("@name", student.name)
cmdstudent.parameters.add("@sex", student.sex)
cmdstudent.parameters.add("@classname", student.classname)
cmdparent.parameters.add("@name", parent.name)
cmdparent.parameters.add("@sex", parent.sex)
cmdparent.parameters.add("@salary", parent.salary)
cmdgetstudentid.parameters.add("@name", student.name)
cmdgetparentid.parameters.add("@name", parent.name)
dim transaction as sqltransaction
try
cnn.open()
transaction = cnn.begintransaction
cmdstudent.transaction = transaction
cmdparent.transaction = transaction
cmdgetstudentid.transaction = transaction
cmdgetparentid.transaction = transaction
cmdstudentparent.transaction = transaction
dim studentid, parentid as integer
cmdstudent.executenonquery()
cmdparent.executenonquery()
studentid = cmdgetstudentid.executescalar
parentid = cmdgetparentid.executescalar
cmdstudentparent.parameters.add("@studentid", studentid)
cmdstudentparent.parameters.add("@parentid", parentid)
cmdstudentparent.executenonquery()
transaction.commit()
catch ex as exception
transaction.rollback()
success = false
messagebox.show(ex.message)
finally
cnn.close()
end try
return success
end function
3>頁面級別的事務處理，也稱com級別的事務。
需要導入imports system.data.sqlclient和imports system.enterpriseservices
'this function will add student's infomation and its parent's information concurrently !
'so we should use transaction !
public shared function insertinfo(byval student as clsstudent, byval parent as clsparent) as boolean
dim success as boolean = true
dim cmdstudent as new sqlcommand("insert into student(name,sex,classname) values(@name,@sex,@classname)", cnn)
dim cmdparent as new sqlcommand("insert into parent(name,sex,salary) values(@name,@sex,@salary)", cnn)
dim cmdgetstudentid as new sqlcommand("select studentid from student where [email protected] ", cnn)
dim cmdgetparentid as new sqlcommand("select parentid from parent where [email protected]", cnn)
dim cmdstudentparent as new sqlcommand("insert into studentparent(studentid,parentid)values(@studentid,@parentid)", cnn)
cmdstudent.parameters.add("@name", student.name)
cmdstudent.parameters.add("@sex", student.sex)
cmdstudent.parameters.add("@classname", student.classname)
cmdparent.parameters.add("@name", parent.name)
cmdparent.parameters.add("@sex", parent.sex)
cmdparent.parameters.add("@salary", parent.salary)
cmdgetstudentid.parameters.add("@name", student.name)
cmdgetparentid.parameters.add("@name", parent.name)
dim transaction as sqltransaction
try
cnn.open()
dim studentid, parentid as integer
cmdstudent.executenonquery()
cmdparent.executenonquery()
studentid = cmdgetstudentid.executescalar
parentid = cmdgetparentid.executescalar
cmdstudentparent.parameters.add("@studentid", studentid)
cmdstudentparent.parameters.add("@parentid", parentid)
cmdstudentparent.executenonquery()
contextutil.setcomplete()
catch ex as exception
success = false
contextutil.setabort()
messagebox.show(ex.message)
finally
cnn.close()
end try
return success
end function
注：運用contextutil的靜態方法setcomplete和setabort來提交和回滾。
