Imports System.IO
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class DB
    Public dsDaten As New DataSet
    Public dsdaten1 As New DataSet
    Public dsdatenblacklist As New DataSet
    Public dadaten As SqlDataAdapter
    Public dadatenblacklist As SqlDataAdapter
    Dim sql As String


    Public Sub Get_Data(ByVal user As String)
        Dim cn As New SqlConnection(Globals.sConnectionstring)
        Dim scmCmdToExecute As SqlCommand = New SqlCommand()
        Dim i As Integer
        Dim connopen As Boolean = False
        Dim sdaAdapter As SqlDataAdapter = New SqlDataAdapter(scmCmdToExecute)
        scmCmdToExecute.CommandText = "dbo.sp_scan_korrektur"
        scmCmdToExecute.CommandType = CommandType.StoredProcedure
        scmCmdToExecute.Connection = cn
        Try
            scmCmdToExecute.Parameters.Add(New SqlParameter("@fnkt", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, 1))
            scmCmdToExecute.Parameters.Add(New SqlParameter("@user", SqlDbType.VarChar, 255, ParameterDirection.Input, True, 10, 0, "", DataRowVersion.Proposed, user))
            Try
                scmCmdToExecute.Connection.Open()
            Catch ex As Exception
                'MsgBox(ex.Message)
            End Try
            sdaAdapter.Fill(dsDaten, "Daten")

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally

            scmCmdToExecute.Connection.Close()
            scmCmdToExecute.Dispose()
        End Try
    End Sub


    Sub Update_Daten()
        Try
            Dim cb As New SqlCommandBuilder(dadatenblacklist)
            dadatenblacklist.Update(dsdatenblacklist, dsdatenblacklist.Tables(0).TableName)
            'MsgBox("Änderungen wurden gespeichert")
        Catch ex As Exception
            MsgBox("Felher: " + ex.Message)
        End Try
    End Sub

    Sub Update_Daten1()
        Dim dt As New DataTable
        dt = dsdatenblacklist.Tables(0).Copy


        dsdatenblacklist.Tables.Clear()
        dsdatenblacklist.Tables.Add(dt.Copy)
        Try
            Dim cb As New SqlCommandBuilder(dadatenblacklist)
            dadatenblacklist.Update(dsdatenblacklist, dsdatenblacklist.Tables(0).TableName)
            'MsgBox("Änderungen wurden gespeichert")
        Catch ex As Exception
            MsgBox("Felher: " + ex.Message)
        End Try

    End Sub
End Class


Public Class DB_Connection

        ''' <summary>
        ''' Liest sämtlcihe CFG-Dateien mit dem Namen "Vertragsverwaltung...". Sind meherere Dateien vorhanden,
        ''' wird ein Auswahldialog zur Datenbank-Selektion angezeigt.
        ''' Standardmässig wird Vertragsverwaltung.cfg als CFG-Datei benutzt.
        ''' 
        ''' Die CFG-Datei ist verschlüsselt und wird über die Crypto-Funktionen entschlüsselt.
        ''' </summary>
        ''' <remarks></remarks>
        Shared Sub New()
            Dim path As String = ""
            Dim fc As Integer = 0
            If fc < 2 Then Globals.ConnectionFilename = "edokaconn.cfg"
            Dim ofile As System.IO.File
            Dim oread As System.IO.StreamReader

            oread = ofile.OpenText(ApplicationPath() + "\" + Globals.ConnectionFilename)
            sConnectionstring = oread.ReadLine
            sConnectionstring = Crypto.DecryptText(sConnectionstring, "HutterundMueller")
            sConnectionstring = Left(sConnectionstring, Len(sConnectionstring) - 1)
            Globals.sConnectionstring = sConnectionstring
            oread.Close()

            Dim connection As New SqlConnection()
            Dim da As New SqlDataAdapter("Select  beschreibung from pluginparameter where pluginparamnr=1", connection)
            Dim CB As SqlCommandBuilder = New SqlCommandBuilder(da)
            Dim ds As New DataSet()
            Try
                connection.ConnectionString = Globals.sConnectionstring
                connection.Open()
                da.Fill(ds, "params")
                path = ds.Tables(0).Rows(0).Item(0)
            Catch ex As Exception
                path = ""
                CB = Nothing
            End Try

            ds = Nothing
            da = Nothing
            connection.Close()
            connection = Nothing

            Globals.ConnectionFilename = "edokaconn.cfg"
            oread = ofile.OpenText(path + "\" + Globals.ConnectionFilename)
            sConnectionstring = oread.ReadLine
            sConnectionstring = Crypto.DecryptText(sConnectionstring, "HutterundMueller")
            sConnectionstring = Left(sConnectionstring, Len(sConnectionstring) - 1)
            Globals.sConnectionstring = sConnectionstring
            oread.Close()
        End Sub
    End Class