.net 中的事務總結

來源:互聯網
上載者:User
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)
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來提交和復原。





相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。