EDOKA_Client/Backup/DataAccess/Anwendung.vb

Imports System.Data
Imports System.Data.SqlClient

'''<summary>DataAccess Klasse von der Tabelle Anwendung</summary>
Public Class Anwendung
    '''<summary>gibt eine anwendung zurück</summary>
    '''<param name="ds">für datenrückgabe</param>
    '''<param name="anwendungsNr"></param>
    Public Shared Sub GetAnwendung(ByRef ds As DataSet, ByVal anwendungsNr As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim da As New SqlDataAdapter()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_GetAnwendung"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@DokumentartNr", anwendungsNr))
            da.SelectCommand = sqlCmd
            da.Fill(ds, "Anwendung")

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Dokumentart.GetAnwendung", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
            da.Dispose()
        End Try
    End Sub

    '''<summary>gibt eine liste aller anwendungen zurück</summary>
    '''<param name="ds">für datenrückgabe</param>
    '''<param name="aktiv">1 nur aktive; 0 nur inaktive; -1 alle</param>
    Public Shared Sub List(ByRef ds As DataSet, ByVal aktiv As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim da As New SqlDataAdapter()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_ListAnwendung"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            If aktiv <> -1 Then
                sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            End If

            da.SelectCommand = sqlCmd
            da.Fill(ds, "Anwendungen")

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Anwendung.List", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
            da.Dispose()
        End Try
    End Sub


    '''<summary>erstellt eine anwendung</summary>
    '''<param name="anwendungNr"></param>
    '''<param name="bezeichnung"></param>
    '''<param name="beschreibung"></param>
    '''<param name="mandantnr"></param>
    '''<param name="aktiv"></param>
    '''<param name="mutierer"></param>
    '''<returns>error</returns>
    Public Shared Function Add(ByRef anwendungNr As Integer, ByVal bezeichnung As String, _
        ByVal beschreibung As String, ByVal mandantnr As Integer, ByVal aktiv As Boolean, _
        ByVal mutierer As Integer) As Integer

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_InsAnwendung"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@bezeichnung", NullHandler.CToSqlString(bezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@beschreibung", NullHandler.CToSqlString(beschreibung)))
            sqlCmd.Parameters.Add(New SqlParameter("@mandantnr", NullHandler.CToSqlInt(mandantnr)))
            sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            sqlCmd.Parameters.Add(New SqlParameter("@mutierer", mutierer))

            sqlCmd.Parameters.Add(New SqlParameter("@AnwendungNr", 0))
            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@AnwendungNr").Direction = ParameterDirection.Output
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            If CInt(sqlCmd.Parameters("@Return").Value) = 0 Then
                anwendungnr = CInt(sqlCmd.Parameters("@AnwendungNr").Value)
            End If

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Anwendung.Add", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function


    '''<summary>Löscht eine bestehende anwendung</summary>
    '''<param name="anwendungNr"></param>
    '''<returns>error</returns>
    Public Shared Function Delete(ByVal anwendungNr As Integer) As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_DelAnwendung"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@@anwendungnr", anwendungNr))
            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Anwendung.Delete", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function


    '''<summary>aktualisiert eine anwendung</summary>
    '''<param name="anwendungNr">kritetrium (wird nicht aktualisiert)</param>
    '''<param name="bezeichnung"></param>
    '''<param name="beschreibung"></param>
    '''<param name="mandantnr"></param>
    '''<param name="aktiv"></param>
    '''<param name="mutierer"></param>
    '''<returns>error</returns>
    Public Shared Function Update(ByVal anwendungNr As Integer, ByVal bezeichnung As String, _
            ByVal beschreibung As String, ByVal mandantnr As Integer, ByVal aktiv As Boolean, _
            ByVal mutierer As Integer) As Integer

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = Common.Datenbank.GetDSN()
            sqlCmd.CommandText = "sp_SetAnwendung"
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Connection = sqlConn

            sqlCmd.Parameters.Add(New SqlParameter("@AnwendungNr", anwendungNr))
            sqlCmd.Parameters.Add(New SqlParameter("@bezeichnung", NullHandler.CToSqlString(bezeichnung)))
            sqlCmd.Parameters.Add(New SqlParameter("@beschreibung", NullHandler.CToSqlString(beschreibung)))
            sqlCmd.Parameters.Add(New SqlParameter("@mandantnr", NullHandler.CToSqlInt(mandantnr)))
            sqlCmd.Parameters.Add(New SqlParameter("@Aktiv", aktiv))
            sqlCmd.Parameters.Add(New SqlParameter("@mutierer", mutierer))

            sqlCmd.Parameters.Add(New SqlParameter("@Return", DBNull.Value))
            sqlCmd.Parameters("@Return").Direction = ParameterDirection.ReturnValue

            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()

            Return CInt(sqlCmd.Parameters("@Return").Value)

        Catch ex As Exception
            TKBLib.Errorhandling.TraceHelper.Msg("EDOKALib.DA.Anwendung.Update", ex.Message + " " + ex.StackTrace, TraceLevel.Error)
            Throw ex
        Finally
            sqlConn.Dispose()
            sqlCmd.Dispose()
        End Try
    End Function
End Class