ClipboardWatcher/app/DD_Clipboard_Searcher/frmConnection.vb

Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Microsoft.Win32
Imports DigitalData.Modules.Database.Constants
Imports DigitalData.Modules

Public Class frmConnection
    Private Sub TBDD_CONNECTIONBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Validate()
        TBDD_CONNECTIONBindingSource.EndEdit()
        TableAdapterManager.UpdateAll(MyDataset)
    End Sub

    Private Sub frmConnection_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Load_Connections()

        Dim oSystemConnections = LoadOdbcConnections(Registry.LocalMachine)
        Dim oUserConnections = LoadOdbcConnections(Registry.CurrentUser)

        For Each oDSN In oSystemConnections
            ODBCServerTextBox.Items.Add(oDSN)
        Next

        For Each oDSN In oUserConnections
            ODBCServerTextBox.Items.Add(oDSN)
        Next
    End Sub

    Private Sub Load_Connections()
        Try
            TBDD_CONNECTIONTableAdapter.Connection.ConnectionString = MyConnectionString
            TBDD_CONNECTIONTableAdapter.Fill(MyDataset.TBDD_CONNECTION)
        Catch ex As Exception
            Logger.Error(ex)
            MsgBox("Fehler beim Laden der Verbindungen: " & vbNewLine & ex.Message, MsgBoxStyle.Critical, Text)
        End Try
    End Sub

    Private Function LoadOdbcConnections(Root As RegistryKey) As List(Of String)
        Dim oConnections As New List(Of String)

        Try
            Dim oPath As String = "SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources"
            Dim oKey As RegistryKey = Root.OpenSubKey(oPath, False)

            If oKey Is Nothing Then
                Return oConnections
            End If

            For Each oName In oKey.GetValueNames
                oConnections.Add(oName)
            Next

            Return oConnections
        Catch ex As Exception
            Logger.Error(ex)
            Return oConnections
        End Try
    End Function



    Private Sub TBDD_CONNECTIONBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles TBDD_CONNECTIONBindingSource.AddingNew
        MyDataset.TBDD_CONNECTION.AKTIVColumn.DefaultValue = True
        MyDataset.TBDD_CONNECTION.ERSTELLTWERColumn.DefaultValue = Environment.UserName
        MyDataset.TBDD_CONNECTION.SQL_PROVIDERColumn.DefaultValue = "MS-SQL"
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs)
        ConnectionTest(SQL_PROVIDERComboBox.Text, chkWinAuth.Checked, SERVERTextBox.Text, USERNAMETextBox.Text, PASSWORDTextBox.Text, DATENBANKComboBox.Text)
    End Sub

    Private Sub ConnectionTest(ConnectionType As String, WinAuth As Boolean, Server As String, UserId As String, Password As String, Database As String)
        Dim oConnectionString As String = String.Empty

        Select Case ConnectionType
            Case PROVIDER_MSSQL
                If WinAuth Then
                    oConnectionString = $"Data Source={Server};Initial Catalog={Database};Trusted_Connection=True;"
                Else
                    oConnectionString = $"Server={Server};Database={Database};User Id={UserId};Password={Password};"
                End If

                Using oConnection As New SqlConnection(oConnectionString)
                    Try
                        oConnection.Open()
                        MsgBox("Die Verbindung wurde erfolgreich aufgebaut!", MsgBoxStyle.Information, Text)
                    Catch ex As Exception
                        Logger.Error(ex)
                        MsgBox("Fehler beim Verbindungsaufbau (MSSQL): " & vbNewLine & ex.Message, MsgBoxStyle.Exclamation, Text)
                    End Try
                End Using
            Case PROVIDER_ORACLE
                Try
                    oConnectionString = $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={Server})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={Database})));User Id={UserId};Password={Password};"
                    Dim oOracle As New Database.Oracle(LogConfig, oConnectionString)

                    If oOracle.DBInitialized Then
                        MsgBox("Die Verbindung wurde erfolgreich aufgebaut!", MsgBoxStyle.Information, Text)
                    Else
                        MsgBox("Fehler beim Verbindungsaufbau (ORACLE): Fehler im Log", MsgBoxStyle.Critical, Text)
                    End If
                Catch ex As Exception
                    Logger.Error(ex)
                    MsgBox("Fehler beim Verbindungsaufbau (ORACLE): " & vbNewLine & ex.Message, MsgBoxStyle.Critical, Text)
                End Try
            Case PROVIDER_ODBC
                Try
                    oConnectionString = $"DSN={Server};UID={UserId};PWD={Password}"

                    Using oConnection As New OdbcConnection(oConnectionString)
                        oConnection.Open()
                        MsgBox("Die Verbindung wurde erfolgreich aufgebaut!", MsgBoxStyle.Information, Text)
                    End Using
                Catch ex As Exception
                    Logger.Error(ex)
                    MsgBox("Fehler beim Verbindungsaufbau (ODBC): " & vbNewLine & ex.Message, MsgBoxStyle.Critical, Text)
                End Try
        End Select


    End Sub

    Private Sub DATENBANKComboBox_Click(sender As Object, e As EventArgs) Handles DATENBANKComboBox.Click
        Cursor = Cursors.WaitCursor
        Dim oConnectionBuilder As New SqlConnectionStringBuilder

        If SERVERTextBox.Text = String.Empty Then
            MsgBox("Bitte geben sie einen Server an!")
            Exit Sub
        End If

        If SQL_PROVIDERComboBox.Text = PROVIDER_ORACLE Then
            Exit Sub
        End If

        If chkWinAuth.Checked Then
            oConnectionBuilder.DataSource = SERVERTextBox.Text
            oConnectionBuilder.IntegratedSecurity = True
        Else
            If USERNAMETextBox.Text = String.Empty Or PASSWORDTextBox.Text = String.Empty Then
                MsgBox("Bitte geben sie Benutzer und Passwort an!", MsgBoxStyle.Exclamation, Text)
                Exit Sub
            End If

            oConnectionBuilder.DataSource = SERVERTextBox.Text
            oConnectionBuilder.IntegratedSecurity = False
            oConnectionBuilder.UserID = USERNAMETextBox.Text
            oConnectionBuilder.Password = PASSWORDTextBox.Text
        End If

        Try
            Using oConnection As New SqlConnection(oConnectionBuilder.ToString)
                oConnection.Open()

                Using oCmd As New SqlCommand("sp_databases", oConnection) With {.CommandType = CommandType.StoredProcedure}
                    Using oReader As SqlDataReader = oCmd.ExecuteReader
                        If oReader.HasRows Then
                            DATENBANKComboBox.Items.Clear()
                            Do While oReader.Read()
                                DATENBANKComboBox.Items.Add(oReader.Item("Database_Name"))
                            Loop
                            DATENBANKComboBox.DroppedDown = True
                        Else
                            MsgBox("The standard-databases could not be retrieved." & vbNewLine & "Check rights in sql-server for user", MsgBoxStyle.Exclamation)
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            Logger.Error(ex)
            MsgBox("Error while loading Databases:" & vbCrLf & ex.Message, MsgBoxStyle.Critical, Text)
        Finally
            Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub chkWinAuth_CheckedChanged(sender As Object, e As EventArgs) Handles chkWinAuth.CheckedChanged
        If chkWinAuth.Checked Then
            USERNAMETextBox.Enabled = False
            USERNAMETextBox.Text = "WINAUTH"
            PASSWORDTextBox.Enabled = False
            PASSWORDTextBox.Text = ""
        Else
            USERNAMETextBox.Enabled = True
            USERNAMETextBox.Text = ""
            PASSWORDTextBox.Enabled = True
            PASSWORDTextBox.Text = ""
        End If
    End Sub

    Private Sub BarButtonItem1_ItemClick(sender As Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonAdd.ItemClick
        TBDD_CONNECTIONBindingSource.AddNew()
    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonDelete.ItemClick
        Try
            If GUIDTextBox.Text <> String.Empty Then
                Dim oSQL = $"SELECT dbo.FNCW_GET_SEARCH_COUNT_FOR_CONNECTION({GUIDTextBox.Text})"
                Dim oCount = MyDB_DDECM.GetScalarValue(oSQL)

                If oCount IsNot Nothing AndAlso oCount = 0 Then
                    Dim oResult As MsgBoxResult = MsgBox("Wollen Sie die Verbindung wirklich löschen?", MsgBoxStyle.YesNo, Text)
                    If oResult = MsgBoxResult.Yes Then
                        TBDD_CONNECTIONTableAdapter.Delete(GUIDTextBox.Text)
                    End If
                Else
                    MsgBox($"Die Verbindung '{BEZEICHNUNGTextBox.Text}' kann nicht gelöscht werden, da sie von mindestens einer Suche verwendet wird!", MsgBoxStyle.Exclamation, Text)
                End If
            End If
        Catch ex As Exception
            Logger.Error(ex)
            MsgBox("Fehler beim Löschen der Verbindung: " & vbNewLine & ex.Message, MsgBoxStyle.Critical, Text)
        End Try
    End Sub

    Private Sub BarButtonItem3_ItemClick(sender As Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonSave.ItemClick
        Validate()
        TBDD_CONNECTIONBindingSource.EndEdit()
        TableAdapterManager.UpdateAll(MyDataset)
    End Sub

    Private Sub BarButtonItem1_ItemClick_1(sender As Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
        ConnectionTest(SQL_PROVIDERComboBox.Text, chkWinAuth.Checked, SERVERTextBox.Text, USERNAMETextBox.Text, PASSWORDTextBox.Text, DATENBANKComboBox.Text)
    End Sub

    Private Sub SQL_PROVIDERComboBox_SelectedValueChanged(sender As Object, e As EventArgs) Handles SQL_PROVIDERComboBox.SelectedValueChanged
        If SQL_PROVIDERComboBox.Text = PROVIDER_ODBC Then
            DATENBANKComboBox.Enabled = False
            ODBCServerTextBox.Enabled = True
            chkWinAuth.Enabled = False
        Else
            DATENBANKComboBox.Enabled = True
            ODBCServerTextBox.Enabled = False
            chkWinAuth.Enabled = True
        End If
    End Sub

    Private Sub ODBCServerTextBox_SelectedValueChanged(sender As Object, e As EventArgs) Handles ODBCServerTextBox.SelectedValueChanged
        If ODBCServerTextBox.Text <> String.Empty Then
            SERVERTextBox.Text = ODBCServerTextBox.Text
            BEZEICHNUNGTextBox.Text = ODBCServerTextBox.Text
        End If
    End Sub
End Class