Hi dears,

I have one web service which is consumed as an API from my own web application. The web app will display some purchase order numbers to the user and the user can enter the issuing quantity for each POs (Purchase Orders). Additionally user can attach some files which is to be uploaded to the server along with this. Upon clicking the submit button provided on the web page, it will loop through POs and update each one's issuing quantity in the database. And, as a last process it will upload the attachments. Here, if processing of one PO fails, I need to rollback all the PO related transactions as well as attachments.
I would like to get help on accomplishing this mechanism to work. I have googled and got some info like the 'Transaction="Required"' page directive, 'TransactionOption.Required' attribute for the web method, ContextUtil.SetComplete(), ContextUtil.SetAbort(). But I couldn't manage to get to know about how this can be used.

I have created a sample web service and web application to demonstrate this. Let me show you what I have done so far.

This is my sample table in the database.
Name:  TABLE.JPG
Views: 361
Size:  21.4 KB

My web method in the web service is

Code:
 <WebMethod(False, TransactionOption.Required)> Public Function CalculateMarks(ByVal Student_ID As Integer) As Integer
        Using Cnn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConStr)
            Dim Cmd As SqlClient.SqlCommand = Cnn.CreateCommand
            Cnn.Open()
            Cmd.CommandType = CommandType.Text
            Try
                SQLS = "UPDATE MARKS SET DIVISION_RESULT = MATHS / ENGLISH WHERE ID = " & Student_ID & ""
                Cmd.CommandText = SQLS
                Cmd.ExecuteNonQuery()

                ContextUtil.SetComplete()
                Return 1
            Catch ex As Exception
                ContextUtil.SetAbort()
                Return 0
            End Try
        End Using
    End Function
The above method is being called from my web application like this
Code:
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button2.Click
        For i As Integer = 1 To 4
            EbsService.CalculateMarks(i)
        Next
    End Sub
I have written the 'transaction' page directive in the .aspx page like this
Code:
<%@ Page Transaction="Required" Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="Transactional._Default" %>
The expected result is:
The web method is expected to update 'DIVISION_RESULT' column with the result of 'MATHS / ENGLISH'. As the column 'ENGLISH' in the third row has the value 0, a 'division by zero' exception is expected, thereby rolling back all the transactions and the table remain intact.

But the result I got as of now is
Name:  RESULT.JPG
Views: 319
Size:  21.6 KB

Please guide me to the best approach to accomplish the expected result with samples.
Thank you.