Lehrlingsparcours/_archiv/LP/LPUpdate/Form1.vb

Imports System.Data.SQLite
Imports System.IO
Public Class Form1
    Dim ierror As Integer = 0
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Me.OpenFileDialog1.DefaultExt = "*.db"
        Me.OpenFileDialog1.Filter = "SQLite-Datenbanken (*.db)|*.db|Alle Dateien (*.*)|*.*"
        Me.OpenFileDialog1.ShowDialog()
        If OpenFileDialog1.FileName <> "" Then Me.TextBox1.Text = OpenFileDialog1.FileName
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Me.OpenFileDialog2.DefaultExt = "*.db"
        Me.OpenFileDialog2.Filter = "SQLite-Datenbanken (*.sql)|*.sql|Alle Dateien (*.*)|*.*"
        Me.OpenFileDialog2.ShowDialog()
        If OpenFileDialog2.FileName <> "" Then Me.TextBox2.Text = OpenFileDialog2.FileName
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Me.lstStatus.Items.Clear()
        Dim savefilename As String = System.IO.Path.GetDirectoryName(Me.TextBox1.Text) + "\" + System.IO.Path.GetFileName(Me.TextBox1.Text) + "_" + Format(Now, "yyyyMMddHHmmss") + "_sik" + ".db"
        System.IO.File.Copy(Me.TextBox1.Text, savefilename)
        lstStatus.Items.Add("Datei-Sicherung erstellt:" + savefilename)
        Dim readFile As System.IO.TextReader = New StreamReader(Me.TextBox2.Text)
        Dim s As String
        Dim sql As String = ""
        s = readFile.ReadLine

        While s.ToString <> "//END Of Updates"
            Select Case s
                Case "//BEGIN Of Statement"
                    sql = ""
                Case "//END Of Statement"
                    Run_Statement(sql)
                Case Else
                    Try
                        If s.Substring(0, 2) <> "//" Then
                            sql = sql + " " + s
                        End If
                    Catch
                    End Try
            End Select
            s = readFile.ReadLine()
        End While
        FileClose(1)
        If Me.CheckBox1.Checked Then MsgBox("Update durchgeführt. Anzahl Fehler: " + ierror.ToString)
        Me.lstStatus.Items.Add("Update durchgeführt. Anzahl Fehler: " + ierror.ToString)
    End Sub
    Sub Run_Statement(ByVal sql As String)
        Dim datenbank As String = "data source=" + Me.TextBox1.Text & ";"
        Dim SQLconnect As New SQLite.SQLiteConnection()
        Dim SQLcommand As SQLiteCommand
        Dim dbconnection As String = "data source=" + Me.TextBox1.Text & ";"
        SQLconnect.ConnectionString = dbconnection
        SQLconnect.Open()
        SQLcommand = SQLconnect.CreateCommand

        Dim s As String = sql
        Try
            If s.IndexOf("Lastentry_") > 0 Then
                get_next_key(s)
            End If
            If s.IndexOf("FileUpload") > 0 Then
                fileuplaod(sql)
            Else
                If Me.CheckBox1.Checked Then
                    If MsgBox("Scipt ausführen:" + vbCrLf + vbCrLf + s, MsgBoxStyle.YesNo + MsgBoxStyle.Question) = MsgBoxResult.Yes Then
                        SQLcommand.CommandText = s
                        Try
                            SQLcommand.ExecuteNonQuery()
                            lstStatus.Items.Add("OK:" + SQLcommand.CommandText)
                        Catch ex As Exception
                            lstStatus.Items.Add("Error:" + SQLcommand.CommandText + "||" + ex.Message)
                            ierror = ierror + 1
                        End Try
                    End If
                Else
                    SQLcommand.CommandText = s
                    Try
                        SQLcommand.ExecuteNonQuery()
                        lstStatus.Items.Add("OK:" + SQLcommand.CommandText)
                    Catch ex As Exception
                        lstStatus.Items.Add("Error:" + SQLcommand.CommandText + "||" + ex.Message)
                        ierror = ierror + 1
                    End Try
                End If
            End If
        Catch ex As Exception
            If Me.CheckBox1.Checked = True Then
                MsgBox(ex.Message)
                lstStatus.Items.Add("Error:" + SQLcommand.CommandText + "||" + ex.Message)
                ierror = ierror + 1
            Else
                lstStatus.Items.Add("Error:" + SQLcommand.CommandText + "||" + ex.Message)
                ierror = ierror + 1
            End If
        End Try

        SQLcommand.Dispose()
        SQLconnect.Close()
    End Sub

    Private Sub get_next_key(ByRef s As String)
        Dim x As String = s
        Dim i As Integer = s.IndexOf("Lastentry_")
        Dim a As String = s.Substring(0, i)
        Dim b As String = s.Substring(i, Len(s) - Len(a))
        Dim c As String = b.Substring(b.IndexOf("Lastentry_") + 10, b.IndexOf(",") - 10)

        Dim table As String = c
        Dim i1 As Integer = s.IndexOf("(")
        Dim i2 As Integer = s.IndexOf(",")

        Dim aa As String = s.Substring(i1 + 1, i2 - (i1 + 1))
        Dim sql As String = "Select * from " + table + " order by " + aa + " desc"

        Dim SQLconnect As New SQLite.SQLiteConnection()
        SQLconnect.ConnectionString = "data source=" + Me.TextBox1.Text & ";"
        SQLconnect.Open()
        Dim ds As New DataSet
        Dim da As New SQLiteDataAdapter("", SQLconnect)
        Dim sqlcmd As New SQLiteCommand
        sqlcmd.Connection = SQLconnect
        sqlcmd.CommandType = CommandType.Text
        da.SelectCommand.CommandText = sql
        da.Fill(ds, "Daten")
        SQLconnect.Close()
        Dim key As Integer = ds.Tables(0).Rows(0).Item(0) + 1
        s = s.Replace("Lastentry_" + table, key.ToString)
    End Sub

    Sub fileuplaod(ByVal sql As String)
        Dim splitter() As String
        splitter = sql.Split("|")
        Dim ssql = "Select * from " + splitter(2).ToString + " where " + splitter(4)

        Dim filename As String = System.IO.Path.GetDirectoryName(Me.TextBox2.Text) + "\" + splitter(1)
        Dim Connection As New SQLiteConnection()
        Dim DA As New SQLiteDataAdapter(ssql, Connection)
        Dim cb As SQLiteCommandBuilder = New SQLiteCommandBuilder(DA)
        Dim ds As New DataSet()
        Dim fs As New FileStream(filename, FileMode.OpenOrCreate, FileAccess.Read)
        Dim mydata(fs.Length) As Byte
        fs.Read(mydata, 0, fs.Length)
        fs.Close()
        Try
            Connection.ConnectionString = "data source=" + Me.TextBox1.Text & ";"
            Connection.Open()
            DA.Fill(ds, "RptFile")
            Dim myRow As DataRow
            If ds.Tables(0).Rows.Count = 0 Then
                If Me.CheckBox1.Checked = True Then
                    MsgBox("Datei kann nicht gespeichert werden: " + splitter(1).ToString, MsgBoxStyle.Critical)
                End If
                lstStatus.Items.Add("Error: Datei konnte nicht gespichert werden. " + filename)
                ierror = ierror + 1
                Exit Sub
            Else
                myRow = ds.Tables(0).Rows(0)
                myRow.Item(splitter(3)) = mydata
                DA.Update(ds, "RptFile")
                lstStatus.Items.Add("OK: Fileupload erfolgreich: " + filename)
            End If
        Catch ex As Exception
            If Me.CheckBox1.Checked = True Then MsgBox(ex.Message)
            lstStatus.Items.Add("Error: Datei konnte nicht gespichert werden.")
            ierror = ierror + 1
        End Try
        fs = Nothing
        cb = Nothing
        ds = Nothing
        DA = Nothing
        Connection.Close()
        Connection = Nothing
    End Sub

    Private Sub lstStatus_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lstStatus.SelectedIndexChanged
        Me.TextBox3.Text = Me.lstStatus.SelectedItem
    End Sub

    Private Sub Schliessen_Click(sender As System.Object, e As System.EventArgs) Handles Schliessen.Click
        Me.Close()
    End Sub
End Class