Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace := "http://tempuri.org/")> _
Public Class services
    Inherits System.Web.Services.WebService

#Region " Web Services Designer Generated Code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Web Services Designer.
        InitializeComponent()

        'Add your own initialization code after the InitializeComponent() call

    End Sub

    'Required by the Web Services Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Web Services Designer
    'It can be modified using the Web Services Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        components = New System.ComponentModel.Container()
    End Sub

    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        'CODEGEN: This procedure is required by the Web Services Designer
        'Do not modify it using the code editor.
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

#End Region


    <WebMethod()> Public Function GetBalance(ByVal acno As String) As Double
        Dim con As New SqlConnection(Application("cs"))
        Try
            con.Open()
            Dim cmd As New SqlCommand("select curbal from accounts where acno = @acno", con)
            cmd.Parameters.Add("@acno", SqlDbType.VarChar, 10).Value = acno
            Dim curbal As Double
            curbal = cmd.ExecuteScalar()
            Return curbal
        Catch ex As Exception
            Return -1
        Finally
            con.Close()
        End Try
    End Function


    <WebMethod()> Public Function GetTopTrans(ByVal acno As String, ByVal n As Integer) As DataSet
        Dim con As New SqlConnection(Application("cs"))
        Try
            con.Open()
            Dim da As New SqlDataAdapter("select  top  " & n & "  *  from transactions where acno = @acno order by tid desc", con)
            da.SelectCommand.Parameters.Add("@acno", SqlDbType.VarChar, 10).Value = acno
            Dim ds As New DataSet()
            da.Fill(ds, "trans")
            Return ds
        Catch ex As Exception
            Return Nothing
        Finally
            con.Close()
        End Try
    End Function

    <WebMethod(messagename:="GetTop10")> Public Function GetTopTrans(ByVal acno As String) As DataSet
        Return GetTopTrans(acno, 10)
    End Function

    <WebMethod()> Public Function Deposit(ByVal acno As String, ByVal tamt As Double, ByVal tdesc As String) As Boolean
        Dim con As New SqlConnection(Application("cs"))
        Dim trans As SqlTransaction
        Try
            con.Open()
            trans = con.BeginTransaction
            Dim cmd As New SqlCommand("update accounts set curbal  = curbal + " & tamt & " where acno = @acno", con)
            cmd.Parameters.Add("@acno", SqlDbType.VarChar, 10).Value = acno
            cmd.Transaction = trans

            If cmd.ExecuteNonQuery() = 0 Then
                trans.Rollback()
                Return False
            End If

            '  insert into transactions
            cmd.CommandText = "select isnull(max(tid),0) + 1 from transactions"
            Dim tid As Integer

            tid = cmd.ExecuteScalar

            cmd.CommandText = "insert into transactions values (@tid,@acno,'d',@tamt,getdate(),null,@tdesc,null)"
            cmd.Parameters.Add("@tid", SqlDbType.Int).Value = tid
            cmd.Parameters.Add("@tamt", SqlDbType.Money).Value = tamt
            cmd.Parameters.Add("@tdesc", SqlDbType.VarChar, 50).Value = tdesc

            If cmd.ExecuteNonQuery() = 0 Then
                trans.Rollback()
                Return False
            Else
                trans.Commit()
                Return True
            End If
        Catch ex As Exception
            trans.Rollback()
            Return False
        Finally
            con.Close()
        End Try
    End Function




End Class