ITSM/Vertragsverwaltung - Kopie (2)/DB/MyDBObjects/MyMitarbeiter.vb

Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Security.Principal

Namespace TKB.VV.DB

    Public Class MyMitarbeiter
        Inherits TKB.VV.DB.clsMitarbeiter
        ''' <summary>
        ''' Alle Mitarbeiter inkl. vollständigem Name (Fullname) auslesen
        ''' </summary>
        ''' <returns>Datatable</returns>
        ''' <remarks></remarks>
        Public Function SelectAllwithFullname() As DataTable
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            scmCmdToExecute.CommandText = "dbo.[my_mitarbeiter_SelectAll]"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            Dim dtToReturn As DataTable = New DataTable("mitarbeiter")
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)

            ' // Use base class' connection object
            scmCmdToExecute.Connection = m_scoMainConnection

            Try
                scmCmdToExecute.Parameters.Add(New SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, m_iErrorCode))

                If m_bMainConnectionIsCreatedLocal Then
                    ' // Open connection.
                    m_scoMainConnection.Open()
                Else
                    If m_cpMainConnectionProvider.bIsTransactionPending Then
                        scmCmdToExecute.Transaction = m_cpMainConnectionProvider.stCurrentTransaction
                    End If
                End If

                ' // Execute query.
                sdaAdapter.Fill(dtToReturn)
                m_iErrorCode = New SqlInt32(CType(scmCmdToExecute.Parameters.Item("@iErrorCode").Value, SqlInt32))

                If Not m_iErrorCode.Equals(New SqlInt32(LLBLError.AllOk)) Then
                    ' // Throw error.
                    Throw New Exception("Stored Procedure 'pr_mitarbeiter_SelectAll' reported the ErrorCode: " & m_iErrorCode.ToString())
                End If

                Return dtToReturn
            Catch ex As Exception
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("clsMitarbeiter::SelectAll::Error occured.", ex)
            Finally
                If m_bMainConnectionIsCreatedLocal Then
                    ' // Close connection.
                    m_scoMainConnection.Close()
                End If
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try
        End Function

        ''' <summary>
        ''' Mitarbeiter mittels TGNummer auslesen
        ''' </summary>
        ''' <returns>Datatable</returns>
        ''' <remarks></remarks>
        Public Function SelectWithTGNummer() As DataTable
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            scmCmdToExecute.CommandText = "dbo.[my_mitarbeiter_SelectWithTGNummer]"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure
            Dim dtToReturn As DataTable = New DataTable("mitarbeiter")
            Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)

            ' // Use base class' connection object
            scmCmdToExecute.Connection = m_scoMainConnection
            Try

                scmCmdToExecute.Parameters.Add(New SqlParameter("@stgnummer", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Me.Get_Username))

                scmCmdToExecute.Parameters.Add(New SqlParameter("@iErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, m_iErrorCode))

                If m_bMainConnectionIsCreatedLocal Then
                    ' // Open connection.
                    m_scoMainConnection.Open()
                Else
                    If m_cpMainConnectionProvider.bIsTransactionPending Then
                        scmCmdToExecute.Transaction = m_cpMainConnectionProvider.stCurrentTransaction
                    End If
                End If

                ' // Execute query.
                sdaAdapter.Fill(dtToReturn)
                m_iErrorCode = New SqlInt32(CType(scmCmdToExecute.Parameters.Item("@iErrorCode").Value, SqlInt32))

                If Not m_iErrorCode.Equals(New SqlInt32(LLBLError.AllOk)) Then
                    ' // Throw error.
                    Throw New Exception("Stored Procedure 'pr_mitarbeiter_SelectAllWithTGNummer' reported the ErrorCode: " & m_iErrorCode.ToString())
                End If
                Return dtToReturn
            Catch ex As Exception
                MsgBox(ex.Message)
                ' // some error occured. Bubble it to caller and encapsulate Exception object
                Throw New Exception("clsMitarbeiter::SelectWithTGNummer::Error occured." + ex.Message, ex)
            Finally
                If m_bMainConnectionIsCreatedLocal Then
                    ' // Close connection.
                    m_scoMainConnection.Close()
                End If
                scmCmdToExecute.Dispose()
                sdaAdapter.Dispose()
            End Try
        End Function
        ''' <summary>
        ''' Security-Deklaration zum Auslesen der UserID vom Windows-User
        ''' </summary>
        ''' <remarks></remarks>
        Dim ouser As New WindowsPrincipal(WindowsIdentity.GetCurrent)
        ''' <summary>
        ''' Windows-User auslesen
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function Get_Username() As String
            With ouser.Identity.Name
                Return (.Substring(.IndexOf("\") + 1))
            End With
        End Function

        Public Function Check_Aktive_Funktion() As Boolean
            Dim scmCmdToExecute As SqlCommand = New SqlCommand()
            scmCmdToExecute.CommandText = "dbo.[my_mitarbeiter_Check_Aktive_Funktion]"
            scmCmdToExecute.CommandType = CommandType.StoredProcedure

            ' // Use base class' connection object
            scmCmdToExecute.Connection = m_scoMainConnection
            Try
                scmCmdToExecute.Parameters.Add(New SqlParameter("@imitarbeiternr", SqlDbType.Int, 4, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, Globals.clsmitarbeiter.iMitarbeiternr.Value))

                scmCmdToExecute.Parameters.Add(New SqlParameter("@iaktiv", SqlDbType.Int, 4, ParameterDirection.Output, True, 10, 0, "", DataRowVersion.Proposed, 0))
                Try
                    scmCmdToExecute.Connection.Open()
                Catch
                End Try
                scmCmdToExecute.ExecuteNonQuery()
                    If scmCmdToExecute.Parameters("@iaktiv").Value = 0 Then
                        Return False
                    Else
                        Return True
                    End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                    ' // some error occured. Bubble it to caller and encapsulate Exception object
                    Throw New Exception("clsMitarbeiter::SelectWithTGNummer::Error occured." + ex.Message, ex)
                Finally

                Try
                    scmCmdToExecute.Connection.Close()
                    scmCmdToExecute.Dispose()
                Catch ex As Exception

                End Try

            End Try
        End Function
    End Class
End Namespace