begin trans and commit trans in vb.net

Question

Hi all,

I m handling master/detail records scenario by following method:

SAVEMASTER()

SAVEDETAIL(intMasterID)

I want to ask a question that how can i apply Begin Trans, Commit Trans & RollBack Trans for the above Scenario that if master is saved then the detail should be saved. Otherwise it should rollback.

Thanks in adv.

Answer 1

please try this model

        Dim SqlTran As SqlTransaction = Nothing
        Dim SqlCmd As New SqlCommand
        SqlCmd.Connection = Gc_Con 'Connection object
         Try
            SqlTran = Gc_Con.BeginTransaction(IsolationLevel.ReadCommitted)
            SqlCmd.Transaction = SqlTran
            SqlCmd.CommandTimeout = 300
        Catch ex As Exception
            MsgBox("Data Access Error" + ex.message, MsgBoxStyle.Critical, Me.Text)
        End Try
       Try
            AddParamSalesMaster(SqlCmd) ' a function to add parameters for master with a out parameter @returnvalue
            SqlCmd.CommandType = CommandType.StoredProcedure
            SqlCmd.CommandText = "sp_salesmaster_ins"
            SqlCmd.ExecuteNonQuery()
            Dim Ll_SalesId As Long
 
            Ll_SalesId = SqlCmd.Parameters("@returnvalue").Value
            If Ll_SalesId < 0 Then ' do as per your situation
                Throw New Exception("Insertion Failed")
            End If

' now do the detail insertion, if any system error happens , in catch all previous transactions will roll backed.
' or if any business kind of error happens you can throw an error to rollback all 

'if everything perfect then commit
		SqlTran.Commit() 
	Catch ex As Exception
            SqlTran.Rollback()
             Cmb_Customer.Focus()
            Exit Sub
        Finally
            SqlTran.Dispose()
        End Try
	

Answer 2

Hi,

Check the below link for

Performing a Transaction Using ADO.NET

---

PS.Shakeer Hussain

Answer 3

Hi,

You can also make stored Procedure to do your required task. Better to do in sp level so that you can get more better performance.

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/