1> Store procedure level transactions. Create Procedure AddInfo (@studentname varchar (20), ....) as begin Transactioninsert ... Insert .... if ..... rollback transactionUpdate ........ commit transaction Note: You can use Save Transaction to select the Roll-Rolling Location 2> Database Level Transaction Processing during the stored procedure. You need to import the imports system.data.sqlclient namespace. '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 Student (studentid) 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 SqlTransactionTry 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 exception transaction.rollback () Success = false messagebox.show (ex.Message) Finally
CNN.Close () END TRY RETURN SUCCESS
End Function3> Transaction processing, also known as COM level transaction. Imports System.Data.Sqlclient need to import and 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 exception = false contextutil.setabort () MessageBox.show (ex.Message) FinallyCnN.close () end Try Return Success
END FUNCTION Note: Use ContextUtil's static method setComplete and setabort to submit and roll back.
