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 name=@name ", cnn)

Dim cmdGetParentid As New SqlCommand("select parentid from parent where name=@name", 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 name=@name ", cnn)

Dim cmdGetParentid As New SqlCommand("select parentid from parent where name=@name", 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)