'****************************************************************************
' (c) Copyright IBM Corp. 2007 All rights reserved.
'
' The following sample of source code ("Sample") is owned by International
' Business Machines Corporation or one of its subsidiaries ("IBM") and is
' copyrighted and licensed, not sold. You may use, copy, modify, and
' distribute the Sample in any form without payment to IBM, for the purpose of
' assisting you in the development of your applications.
'
' The Sample code is provided to you on an "AS IS" basis, without warranty of
' any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
' IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
' not allow for the exclusion or limitation of implied warranties, so the above
' limitations or exclusions may not apply to you. IBM shall not be liable for
' any damages you suffer as a result of using, copying, modifying or
' distributing the Sample, even if IBM has been advised of the possibility of
' such damages.
'****************************************************************************
'
' SOURCE FILE NAME: SubCOM.vb
'
' SAMPLE: Creates a library assembly SubCOM.dll for LCTrans.vb
' LCTrans.vb refers to the classes and methods defined in this file.
'
' SQL Statements USED:
'         SELECT
'         UPDATE
'
' DB2 .NET Data Provider Classes USED:
'         DB2Connection
'         DB2Command
'         
'****************************************************************************
'
' Creating the library assembly SubCOM.dll
'
' 1. Create a new key pair and store it in SubCOM.snk by entering the 
'    following at the command prompt:
'
'      sn -k SubCOM.snk
'
' 2. Compile the SubCOM.vb file with bldapp.bat by entering the following 
'    at the command prompt:
'
'      bldapp SubCOM
'
'****************************************************************************
'
' For more information on the sample programs, see the README file.
'
' For information on developing applications, see the Application
' Development Guide.
'
' For information on using SQL statements, see the SQL Reference.
'
' For the latest information on programming, compiling, and running DB2
' applications, visit the DB2 Information Center at
'     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
'
'****************************************************************************

Imports System.EnterpriseServices
Imports System
Imports System.IO
Imports System.Data
Imports System.Xml
Imports IBM.Data.DB2
Imports Microsoft.VisualBasic
Imports System.Reflection
Imports System.Runtime.InteropServices

' Set the assembly attributes
<Assembly: AssemblyTitle("")> 
<Assembly: AssemblyDescription("")> 
<Assembly: AssemblyCompany("")> 
<Assembly: AssemblyProduct("")> 
<Assembly: AssemblyCopyright("")> 
<Assembly: AssemblyTrademark("")> 
<Assembly: CLSCompliant(True)> 
<Assembly: Guid("E36428D9-79DB-4086-AF76-1583A27EECAE")> 
<Assembly: AssemblyVersion("1.0.*")> 
<Assembly: AssemblyKeyFile("SubCOM.snk")> 

Namespace SubCOM

    <Transaction(TransactionOption.Required)> _
    Public Class UpdateSalaryCOM
        Inherits ServicedComponent

        Public Function UpdateEmpSalary(ByVal IntEmpID As Integer, ByVal DecIncrease As Decimal, ByVal StrUID As String, ByVal StrPWD As String) As Integer
            ' Update salary in this Sub component
            UpdateEmpSalary = UpdateSalary(IntEmpID, DecIncrease, StrUID, StrPWD)
            Select Case UpdateEmpSalary
                Case -1
                    'UpdateSalary() failed, abort the operation
                    ContextUtil.SetAbort()
                    Console.WriteLine("        SubCOM: Transaction Aborted.")
                    Console.WriteLine()
                Case 0
                    'UpdateSalary() succeeded, commit the operation
                    ContextUtil.SetComplete()
                    Console.WriteLine("        SubCOM: Transaction Committed.")
                    Console.WriteLine()
            End Select

            Return UpdateEmpSalary

        End Function


        ' This method updates the salary of employee id = empID in the
        ' STAFF table of the SAMPLE database
        Private Function UpdateSalary(ByVal IntEmpID As Integer, ByVal DecIncrease As Decimal, ByVal StrUID As String, ByVal StrPWD As String) As Integer

            Dim strSELECT As String
            Dim strUPDATE As String
            Dim intEmpYears As Integer
            Dim decEmpSalary As Decimal
            Dim objConnection As DB2Connection
            Dim objAdapter As DB2DataAdapter
            Dim objCommand As DB2Command
            Dim objDataset As DataSet

            If StrUID Is Nothing Then
                objConnection = New DB2Connection("database=sample;enlist=false")
            Else
                objConnection = New DB2Connection("database=sample;UID=" & StrUID & _
                                                  ";PWD=" & StrPWD & ";enlist=false")
            End If

            ' Connect to the SAMPLE database using the entered user id and password 
            Try
                objConnection.Open()
                'For convenience, we display the message to the console. 
                'In a production system, you should write the info to a log file.
                Console.WriteLine(vbNewLine & "        SubCOM: Connected to the SAMPLE database.")
            Catch E As Exception
                Console.WriteLine("        SubCOM: Failed to open connection to the SAMPLE database!")
                Console.WriteLine(E.ToString())
                Return -1
            End Try

            strSELECT = "select SALARY, YEARS from STAFF where ID = " & IntEmpID
            strUPDATE = "update STAFF set SALARY = ? where ID = " & IntEmpID

            Try

                objAdapter = New DB2DataAdapter(strSELECT, objConnection)
                objAdapter.UpdateCommand = New DB2Command(strUPDATE, objConnection)
                objAdapter.UpdateCommand.Parameters.Add(New DB2Parameter("SALARY", DB2Type.Decimal, 8, ParameterDirection.Input, False, 0, 0, "SALARY", DataRowVersion.Current, 0))

                objDataset = New DataSet()
                objAdapter.Fill(objDataset)

                intEmpYears = objDataset.Tables(0).Rows(0).Item("YEARS")
                decEmpSalary = objDataset.Tables(0).Rows(0).Item("SALARY") + DecIncrease
                objDataset.Tables(0).Rows(0).Item("SALARY") = decEmpSalary

                If (decEmpSalary) > 20000 And intEmpYears < 5 Then
                    Throw (New Exception("Salary too large for a new employee." & _
                                         " An employee for less than 5 years" & _
                                         " should not have a salary greater than" & _
                                         " 20000."))
                End If

                Console.WriteLine("        SubCOM: UPDATE STAFF SET SALARY = " & Convert.ToString(decEmpSalary) & " WHERE ID = " & Convert.ToString(IntEmpID))
                objAdapter.Update(objDataset)
                Console.WriteLine("        SubCOM: SALARY UPDATED SUCCESSFULLY")
                Return 0

            Catch E As Exception
                Console.WriteLine("        SubCOM: FAILED TO UPDATE SALARY!")
                Console.WriteLine(E.ToString())
                Return -1
            Finally
                objConnection.Close()
                Console.WriteLine("        SubCOM: Connection Closed.")
            End Try

        End Function

    End Class

End Namespace