Imports guinet
Imports Newtonsoft.Json

''' <summary>
''' Schnittstelle zwischen GUIXT und MS SQL-Server 
''' </summary>
Public Class iface

    Private guixt As New guinet.guixt

    ''' <summary>
    ''' Datenbankverbindung wird geprüft.
    ''' </summary>
    ''' <param name="ConnectionString">Verbindungszeichenfolge</param>
    ''' <returns>Gibt eine leere Zeichenfolge ("") oder die Fehlermeldung zurück.</returns>
    Public Function CheckConnection(ConnectionString As String) As String
        Try

            'Beispiel für eine Verbindungszeichenfolge mit SQL Server-Authentifizierung:
            'Data Source=MySQLServer;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyUsername;Password=MyPassword
            'Beispiel für eine Verbindungszeichenfolge mit Windows-Authentifizierung:
            'Data Source=MySQLServer;Initial Catalog=MyDatabase;Integrated Security=True;

            'Initialisiert eine neue Instanz der SqlConnection-Klasse mit der Verbindungszeichenfolge.
            Dim cnn As New SqlClient.SqlConnection(ConnectionString)
            'Öffnet die Datenbankverbindung.
            cnn.Open()
            'Schließt die Verbindung mit der Datenbank.
            cnn.Close()

            'leere Zeichenfolge ("")
            Return String.Empty

        Catch ex As Exception
            'Fehlermeldung
            Return ex.Message
        End Try
    End Function

    ''' <summary>
    ''' Ermittelt die Datensätze der SELECT-SQL-Anweisung und schreibt das Ergebnis auf die angegebene GuiXT-Langtextvariablen (data) im JSON-Format zurück. 
    ''' </summary>
    ''' <param name="ConnectionString">Verbindungszeichenfolge</param>
    ''' <param name="cmdText">Text der SELECT-SQL-Anweisung.</param>
    ''' <param name="data">Name der GuiXT-Langtextvariablen</param>
    ''' <returns>Gibt eine leere Zeichenfolge ("") oder die Fehlermeldung zurück.</returns>
    Public Function SelectRow(ConnectionString As String, cmdText As String, data As String) As String
        Try

            ' Beispiele für SELECT-SQL-Anweisungen:
            ' SELECT [DISTINCT] <Name der Spalten> FROM <Name der Tabelle oder Abfrage> [WHERE ...] [GROUP BY ...]
            ' cmdText = "SELECT * FROM Customers"
            ' cmdText = "SELECT * FROM Customers WHERE CustomerID = 'TEST'"
            ' cmdText = "SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin'"
            ' cmdText = "SELECT DISTINCT Country FROM Customers ORDER BY Country"
            ' cmdText = "SELECT DISTINCT City FROM Customers ORDER BY City"
            ' cmdText = "SELECT DISTINCT City FROM Customers WHERE Country = '?' ORDER BY City"
            ' cmdText = "SELECT * FROM Customers WHERE Country = '?'"
            ' cmdText = "SELECT * FROM Customers WHERE Country = '?' AND City = '?'"

            'Initialisiert eine neue Instanz der SqlConnection-Klasse mit der Verbindungszeichenfolge.
            Dim cnn As New SqlClient.SqlConnection(ConnectionString)
            'Öffnet die Datenbankverbindung.
            cnn.Open()

            'Initialisiert eine neue Instanz einer Transact-SQL-Anweisung.
            Dim cmd As New SqlClient.SqlCommand(cmdText, cnn)
            'Initialisiert eine neue Instanz einer Tabelle.
            Dim dt As New DataTable
            'Füllt die Tabelle mit Werten und Informationen über Spalten und Primärschlüssel aus der Datenquelle.
            dt.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo))
            'Schließt die Verbindung mit der Datenbank.
            cnn.Close()

            'Konvertiert die Daten der Tabelle in das JSON-Format.
            Dim JsonString As String = JsonConvert.SerializeObject(dt)

            'Legt den Wert einer GuiXT-Langtextvariablen fest.
            Dim RetVal As Boolean = guixt.SetText(data, JsonString)

            'leere Zeichenfolge ("")
            Return String.Empty

        Catch ex As Exception
            'Fehlermeldung
            Return ex.Message
        End Try
    End Function

    ''' <summary>
    ''' Aktualisiert die im JSON-Format übergebenen Datensätze in der Datenbank.
    ''' </summary>
    ''' <param name="ConnectionString">Verbindungszeichenfolge</param>
    ''' <param name="tableName">Name der Ziel-Tabelle</param>
    ''' <param name="row">Datensatzzeile(n) im JSON-Format</param>
    ''' <param name="rowtextname">Name der GuiXT-Langtextvariablen für die Datensatzzeile(n) im JSON-Format</param>
    ''' <returns>Gibt eine leere Zeichenfolge ("") oder die Fehlermeldung zurück.</returns>
    Public Function UpdateRow(ConnectionString As String, tableName As String, row As String, Optional rowtextname As String = "") As String
        Try

            'Hole den Wert einer GuiXT-Langtextvariablen.
            If rowtextname.Trim.Length > 0 Then
                row = guixt.GetText(rowtextname)
            End If

            'JSON-Zeichenfolge wird für die Konvertierung aufbereitet:
            '- Sonderzeichen ChrW(31) (Silbentrennung) wird entfernt
            '- Eckige Klammern (Tabellenkennzeichen) wird hinzugefügt
            row = "[" & row.Replace(ChrW(31), "'") & "]"

            'Konvertierung des JSON-Formats in eine Tabelle.
            Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(row)

            'Initialisiert eine neue Instanz der SqlConnection-Klasse mit der Verbindungszeichenfolge.
            Dim cnn As New SqlClient.SqlConnection(ConnectionString)
            'Öffnet die Datenbankverbindung.
            cnn.Open()

            'Leere Tabelle mit Spalten- und Primärschlüssel-Informationen.
            Dim dtSchema As DataTable = GetDtSchema(cnn, tableName)

            'Liste der Spaltennamen ohne Primärschüssel
            Dim lCols As List(Of String) = GetColumnsWithoutPk(dtSchema)
            'Liste der Primärschüsselnamen
            Dim lPks As List(Of String) = GetColumnsPk(dtSchema)
            'Liste der Spaltenname mit Parameternamen ohne Primärschlüssel
            Dim lColsUpdate As List(Of String) = GetColumnsWithoutPkUpdate(dtSchema)
            'Liste der Primärschlüsselnamen mit Parameternamen
            Dim lPksWhere As List(Of String) = GetColumnsPkWhere(dtSchema)

            'Sicherheitsabfrage, existiert kein Primärschlüssel wird die Funktion abgebrochen.
            If lPks.Count = 0 Then
                cnn.Close()
                Throw New Exception("No PrimaryKey!")
            End If

            'UPDATE-SQL-Anweisung
            'z.B.: UPDATE Customers SET CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE CustomerID = @CustomerID
            Dim sb As New System.Text.StringBuilder
            sb.Append("UPDATE " & tableName & " SET ")
            sb.Append(String.Join(", ", lColsUpdate.ToArray))
            sb.Append(" WHERE ")
            sb.Append(String.Join(" AND ", lPksWhere.ToArray))
            Dim cmdText As String = sb.ToString

            'Initialisiert eine neue Instanz einer UPDATE-SQL-Anweisung mit Text und Parametern.
            Dim cmd As New SqlClient.SqlCommand(cmdText, cnn)
            cmd.CommandText = cmdText
            For Each columnName As String In lCols
                cmd.Parameters.Add(GetParameter(dtSchema, columnName))
            Next
            For Each columnName As String In lPks
                cmd.Parameters.Add(GetParameter(dtSchema, columnName))
            Next

            Dim i As Integer = 0
            Dim r As Integer = 0

            Dim s As String = String.Empty
            Dim sbError As New System.Text.StringBuilder

            'Zeilen der Tabelle werden durchlaufen.
            For Each dr As DataRow In dt.Rows
                i += 1
                s = String.Empty

                'Die Werte der Parameter der UPDATE-SQL-Anweisung werden belegt.
                For Each dc As DataColumn In dtSchema.Columns
                    If dr(dc.ColumnName).ToString.Trim.Length = 0 Then
                        cmd.Parameters("@" & dc.ColumnName).Value = DBNull.Value
                    Else
                        cmd.Parameters("@" & dc.ColumnName).Value = dr(dc.ColumnName)
                    End If

                    If dt.PrimaryKey.Contains(dc) Then
                        If s.Length = 0 Then
                            s = String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                        Else
                            s = " AND " & String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                        End If
                    End If
                Next
                Try
                    'Führt die Update-SQL-Anweisung aus und gibt die Anzahl der betroffenen Zeilen zurück.
                    r = cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    'Fehlermeldung
                    s = s & " ERROR: " & ex1.Message.Replace(vbNewLine, " ")
                    sbError.AppendLine(String.Format("{0,3}|{1,6}|{2}", i, r, s))
                End Try

            Next

            'Schließt die Verbindung mit der Datenbank.
            cnn.Close()

            'Fehlermeldung, falls vorhanden.
            Return sbError.ToString

        Catch ex As Exception
            'Fehlermeldung
            Return ex.Message
        End Try
    End Function

    ''' <summary>
    ''' Fügt die im JSON-Format übergebenen Datensätze in die Datenbank ein.
    ''' </summary>
    ''' <param name="ConnectionString">Verbindungszeichenfolge</param>
    ''' <param name="tableName">Name der Ziel-Tabelle</param>
    ''' <param name="row">Datensatzzeile(n) im JSON-Format</param>
    ''' <param name="rowtextname">Name der GuiXT-Langtextvariablen für die Datensatzzeile(n) im JSON-Format</param>
    ''' <returns>Gibt eine leere Zeichenfolge ("") oder die Fehlermeldung zurück.</returns>
    Public Function InsertRow(ConnectionString As String, tableName As String, row As String, Optional rowtextname As String = "") As String
        Try

            'Hole den Wert einer GuiXT-Langtextvariablen.
            If rowtextname.Trim.Length > 0 Then
                row = guixt.GetText(rowtextname)
            End If

            'JSON-Zeichenfolge wird für die Konvertierung aufbereitet:
            '- Sonderzeichen ChrW(31) (Silbentrennung) wird entfernt
            '- Eckige Klammern (Tabellenkennzeichen) wird hinzugefügt
            row = "[" & row.Replace(ChrW(31), "'") & "]"

            'Konvertierung des JSON-Formats in eine Tabelle.
            Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(row)

            'Initialisiert eine neue Instanz der SqlConnection-Klasse mit der Verbindungszeichenfolge.
            Dim cnn As New SqlClient.SqlConnection(ConnectionString)
            'Öffnet die Datenbankverbindung.
            cnn.Open()

            'Leere Tabelle mit Spalten- und Primärschlüssel-Informationen.
            Dim dtSchema As DataTable = GetDtSchema(cnn, tableName)

            'Liste der Spaltennamen
            Dim lCols As List(Of String) = GetColumns(dtSchema)

            'INSERT-SQL-Anweisung
            'z.B.: INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)
            Dim sb As New System.Text.StringBuilder
            sb.Append("INSERT INTO " & tableName & " (")
            sb.Append(String.Join(", ", lCols.ToArray))
            sb.Append(") VALUES (")
            sb.Append("@" & String.Join(", @", lCols.ToArray))
            sb.Append(")")
            Dim cmdText As String = sb.ToString

            'Initialisiert eine neue Instanz einer INSERT-SQL-Anweisung mit Text und Parametern.
            Dim cmd As New SqlClient.SqlCommand(cmdText, cnn)
            cmd.CommandText = cmdText
            For Each columnName As String In lCols
                cmd.Parameters.Add(GetParameter(dtSchema, columnName))
            Next

            Dim i As Integer = 0
            Dim r As Integer = 0
            Dim s As String = String.Empty
            Dim sbError As New System.Text.StringBuilder

            'Zeilen der Tabelle werden durchlaufen.
            For Each dr As DataRow In dt.Rows
                i += 1
                s = String.Empty

                'Die Werte der Parameter der INSERT-SQL-Anweisung werden belegt.
                For Each dc As DataColumn In dtSchema.Columns
                    If dr(dc.ColumnName).ToString.Trim.Length = 0 Then
                        cmd.Parameters("@" & dc.ColumnName).Value = DBNull.Value
                    Else
                        cmd.Parameters("@" & dc.ColumnName).Value = dr(dc.ColumnName)
                    End If

                    If dt.PrimaryKey.Contains(dc) Then
                        If s.Length = 0 Then
                            s = String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                        Else
                            s = " AND " & String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                        End If
                    End If
                Next
                Try
                    'Führt die INSERT-SQL-Anweisung aus und gibt die Anzahl der betroffenen Zeilen zurück.
                    r = cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    'Fehlermeldung
                    s = s & " ERROR: " & ex1.Message.Replace(vbNewLine, " ")
                    sbError.AppendLine(String.Format("{0,3}|{1,6}|{2}", i, r, s))
                End Try

            Next

            'Schließt die Verbindung mit der Datenbank.
            cnn.Close()

            'Fehlermeldung, falls vorhanden.
            Return sbError.ToString

        Catch ex As Exception
            'Fehlermeldung
            Return ex.Message
        End Try
    End Function

    ''' <summary>
    ''' Löscht die im JSON-Format übergebenen Datensätze aus der Datenbank.
    ''' </summary>
    ''' <param name="ConnectionString">Verbindungszeichenfolge</param>
    ''' <param name="tableName">Name der Ziel-Tabelle</param>
    ''' <param name="row">Datensatzzeile(n) im JSON-Format</param>
    ''' <param name="rowtextname">Name der GuiXT-Langtextvariablen für die Datensatzzeile(n) im JSON-Format</param>
    ''' <returns>Gibt eine leere Zeichenfolge ("") oder die Fehlermeldung zurück.</returns>
    Public Function DeleteRow(ConnectionString As String, tableName As String, row As String, Optional rowtextname As String = "") As String
        Try

            'Hole den Wert einer GuiXT-Langtextvariablen.
            If rowtextname.Trim.Length > 0 Then
                row = guixt.GetText(rowtextname)
            End If

            'JSON-Zeichenfolge wird für die Konvertierung aufbereitet:
            '- Sonderzeichen ChrW(31) (Silbentrennung) wird entfernt
            '- Eckige Klammern (Tabellenkennzeichen) wird hinzugefügt
            row = "[" & row.Replace(ChrW(31), "'") & "]"

            'Konvertierung des JSON-Formats in eine Tabelle. 
            Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(row)

            'Initialisiert eine neue Instanz der SqlConnection-Klasse mit der Verbindungszeichenfolge.
            Dim cnn As New SqlClient.SqlConnection(ConnectionString)
            'Öffnet die Datenbankverbindung.
            cnn.Open()

            'Leere Tabelle mit Spalten- und Primärschlüssel-Informationen.
            Dim dtSchema As DataTable = GetDtSchema(cnn, tableName)

            'Liste der Primärschüsselnamen
            Dim lPks As List(Of String) = GetColumnsPk(dtSchema)
            'Liste der Primärschlüsselnamen mit Parameternamen
            Dim lPksWhere As List(Of String) = GetColumnsPkWhere(dtSchema)

            'Sicherheitsabfrage, existiert kein Primärschlüssel wird die Funktion abgebrochen.
            If lPks.Count = 0 Then
                cnn.Close()
                Throw New Exception("No PrimaryKey!")
            End If

            'DELETE-SQL-Anweisung
            'z.B.: DELETE FROM Customers WHERE CustomerID = @CustomerID
            Dim sb As New System.Text.StringBuilder
            sb.Append("DELETE FROM " & tableName & " WHERE ")
            sb.Append(String.Join(" AND ", lPksWhere.ToArray))
            Dim cmdText As String = sb.ToString

            'Initialisiert eine neue Instanz einer DELETE-SQL-Anweisung mit Text und Parametern.
            Dim cmd As New SqlClient.SqlCommand(cmdText, cnn)
            cmd.CommandText = cmdText
            For Each columnName As String In lPks
                cmd.Parameters.Add(GetParameter(dtSchema, columnName))
            Next

            Dim i As Integer = 0
            Dim r As Integer = 0
            Dim s As String = String.Empty
            Dim sbError As New System.Text.StringBuilder

            'Zeilen der Tabelle werden durchlaufen.
            For Each dr As DataRow In dt.Rows
                i += 1
                s = String.Empty

                'Die Werte der Parameter der DELETE-SQL-Anweisung werden belegt.
                For Each dc As DataColumn In dtSchema.PrimaryKey
                    If dr(dc.ColumnName).ToString.Trim.Length = 0 Then
                        cmd.Parameters("@" & dc.ColumnName).Value = DBNull.Value
                    Else
                        cmd.Parameters("@" & dc.ColumnName).Value = dr(dc.ColumnName)
                    End If

                    If s.Length = 0 Then
                        s = String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                    Else
                        s = " AND " & String.Format("{0} = {1}", dc.ColumnName, dr(dc.ColumnName).ToString)
                    End If
                Next
                Try
                    'Führt die DELETE-SQL-Anweisung aus und gibt die Anzahl der betroffenen Zeilen zurück.
                    r = cmd.ExecuteNonQuery()
                Catch ex1 As Exception
                    'Fehlermeldung
                    s = s & " ERROR: " & ex1.Message.Replace(vbNewLine, " ")
                    sbError.AppendLine(String.Format("{0,3}|{1,6}|{2}", i, r, s))
                End Try

            Next

            'Schließt die Verbindung mit der Datenbank.
            cnn.Close()

            'Fehlermeldung, falls vorhanden
            Return sbError.ToString

        Catch ex As Exception
            'Fehlermeldung
            Return ex.Message
        End Try
    End Function

    ''' <summary>
    ''' Gibt eine Leere Tabelle mit Spalten- und Primärschlüssel-Informationen zurück.
    ''' </summary>
    ''' <param name="cnn">Instanz der SqlConnection-Klasse</param>
    ''' <param name="tableName">Name der Tabelle</param>
    ''' <returns>Leere Tabelle mit Spalten- und Primärschlüssel-Informationen.</returns>
    Private Function GetDtSchema(cnn As SqlClient.SqlConnection, tableName As String) As DataTable
        'Transact-SQL-Anweisung
        Dim cmdText As String = String.Format("SELECT TOP 1 * FROM {0}", tableName)
        'Initialisiert eine neue Instanz einer Transact-SQL-Anweisung.
        Dim cmd As New SqlClient.SqlCommand(cmdText, cnn)
        'Initialisiert eine neue Instanz einer Tabelle.
        Dim dt As New DataTable
        'Füllt die Tabelle mit Werten und Informationen über Spalten und Primärschlüssel aus der Datenquelle.
        dt.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly))
        'Gibt die Tabelle zurück.
        Return dt
    End Function

    ''' <summary>
    ''' Gibt eine Liste aller Spaltennamen der Tabelle zurück.
    ''' </summary>
    ''' <param name="dt">Tabelle</param>
    ''' <returns>Liste der Spaltennamen</returns>
    Private Function GetColumns(dt As DataTable) As List(Of String)
        Dim l As New List(Of String)
        For Each dc As DataColumn In dt.Columns
            l.Add(dc.ColumnName)
        Next
        Return l
    End Function

    ''' <summary>
    ''' Gibt eine Liste der Primärschüsselnamen der Tabelle zurück.
    ''' </summary>
    ''' <param name="dt">Tabelle</param>
    ''' <returns>Liste der Primärschüsselnamen</returns>
    Private Function GetColumnsPk(dt As DataTable) As List(Of String)
        Dim l As New List(Of String)
        For Each dc As DataColumn In dt.PrimaryKey
            l.Add(dc.ColumnName)
        Next
        Return l
    End Function

    ''' <summary>
    ''' Gibt eine Liste der Spaltennamen ohne Primärschüssel der Tabelle zurück.
    ''' </summary>
    ''' <param name="dt">Tabelle</param>
    ''' <returns>Liste der Spaltennamen ohne Primärschüssel</returns>
    Private Function GetColumnsWithoutPk(dt As DataTable) As List(Of String)
        Dim l As New List(Of String)
        For Each dc As DataColumn In dt.Columns
            If Not dt.PrimaryKey.Contains(dc) Then
                l.Add(dc.ColumnName)
            End If
        Next
        Return l
    End Function

    ''' <summary>
    ''' Gibt eine Liste der Spaltennamen mit Parameternamen ohne Primärschlüssel für die UPDATE-SQL-Anweisung zurück. 
    ''' </summary>
    ''' <param name="dt">Tabelle</param>
    ''' <returns>Liste der Spaltenname mit Parameternamen ohne Primärschlüssel</returns>
    Private Function GetColumnsWithoutPkUpdate(dt As DataTable) As List(Of String)
        Dim l As New List(Of String)
        For Each dc As DataColumn In dt.Columns
            If Not dt.PrimaryKey.Contains(dc) Then
                l.Add(String.Format("{0} = @{0}", dc.ColumnName))
            End If
        Next
        Return l
    End Function

    ''' <summary>
    ''' Gibt eine Liste der Primärschlüsselnamen mit Parameternamen für die WHERE-Klause der SQL-Anweisung zurück. 
    ''' </summary>
    ''' <param name="dt">Tabelle</param>
    ''' <returns>Liste der Primärschlüsselnamen mit Parameternamen</returns>
    Private Function GetColumnsPkWhere(dt As DataTable) As List(Of String)
        Dim l As New List(Of String)
        For Each dc As DataColumn In dt.PrimaryKey
            l.Add(String.Format("{0} = @{0}", dc.ColumnName))
        Next
        Return l
    End Function

    ''' <summary>
    ''' Gibt eine Instanz eines Parameters zurück, für die Transact-SQL-Anweisung.
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="columnName"></param>
    ''' <returns>SqlParameter</returns>
    Private Function GetParameter(dt As DataTable, columnName As String) As SqlClient.SqlParameter
        Dim parameterName As String = "@" & columnName
        Dim dbType As SqlDbType = GetDBType(dt.Columns(columnName).DataType)
        Dim para As New SqlClient.SqlParameter(parameterName, dbType)
        Return para
    End Function

    ''' <summary>
    ''' Gibt den SqlDbType zum DbType zurück.
    ''' </summary>
    ''' <param name="theType">DbType</param>
    ''' <returns>SqlDbType</returns>
    Private Function GetDBType(ByVal theType As System.Type) As SqlDbType
        Dim p1 As System.Data.SqlClient.SqlParameter
        Dim tc As System.ComponentModel.TypeConverter
        p1 = New System.Data.SqlClient.SqlParameter()
        tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType)

        If tc.CanConvertFrom(theType) Then
            p1.DbType = CType(tc.ConvertFrom(theType.Name), DbType)
        Else
            Try
                p1.DbType = CType(tc.ConvertFrom(theType.Name), DbType)
            Catch __unusedException1__ As Exception
            End Try
        End If

        Return p1.SqlDbType
    End Function

End Class