Summary of transactions in. Net

1> transactions at the store procedure level.
CREATE PROCEDURE Addinfo
(@studentname varchar (),....)
As
BEGIN TRANSACTION
Insert ...
Insert ....
If .....
ROLLBACK TRANSACTION
Update ...
.....
Commit TRANSACTION
Note: You can use Save transaction to select the location of the rollback in the stored procedure

2> transaction processing at the database level.
Imports System.Data.SqlClient namespaces need to be imported.

' This function would 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> transaction processing at the page level, also known as COM-level transactions.
Imports System.Data.Sqlclient and Imports System.EnterpriseServices required


' This function would 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
Note: Use ContextUtil static method SetComplete and SetAbort to commit and rollback.