SQL Simple Utilities

Imports System.Data.SqlClient
Imports System.IO
Imports System.Threading

Public Class FormExecScripts

    Private mProcessType As ProcessType
    Private mLastIndex As Integer
    Private mThreadStart As ThreadStart
    Private mStarted As Boolean

    Private mOriginalSettings As String
    Private mExludeControlSettings As Control()

    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
        mExludeControlSettings = {TextBoxLog}
        ToolStripFile.Visible = False
        ToolStripExecution.Visible = False
        StatusStrip1.Visible = False
        ToolStripButtonNew.Enabled = False
        ToolStripButtonOpen.Enabled = True
        ToolStripButtonImport.Enabled = True
        ToolStripButtonSave.Enabled = False
        ToolStripButtonRun.Enabled = False
        ToolStripButtonPause.Enabled = False
        ToolStripButtonCancel.Enabled = False
        ToolStripButtonDown.Enabled = False
        ToolStripButtonUp.Enabled = False
    End Sub

    Private Sub LogWriteLine(ByVal s As String)
        TextBoxLog.AppendText(s & vbCrLf)
    End Sub

    Private Sub SetVisisbleTextBoxes(ByVal b As Boolean)
        LabelFolder.Visible = b
        TextBoxFolder.Visible = b
        LabelFile.Visible = Not b
        TextBoxFile.Visible = Not b
    End Sub

    Private Sub SetEnabledButtons(ByVal b As Boolean)
        ToolStripButtonNew.Enabled = b
        ToolStripButtonOpen.Enabled = b
        ToolStripButtonImport.Enabled = b
        ToolStripButtonSave.Enabled = b
        ToolStripButtonRun.Enabled = b
        ToolStripButtonPause.Enabled = Not b
        ToolStripButtonCancel.Enabled = Not b
        ToolStripButtonDown.Enabled = b
        ToolStripButtonUp.Enabled = b
    End Sub

    Private Sub SetEnabledButtonsSave(ByVal b As Boolean)
        ToolStripButtonNew.Enabled = b
        ToolStripButtonSave.Enabled = b
    End Sub

    Private Sub GetFolderFromDialog(ByVal t As TextBox)
        Dim f As New FolderBrowserDialog
        f.SelectedPath = t.Text
        If f.ShowDialog = Windows.Forms.DialogResult.OK Then
            t.Text = f.SelectedPath
        End If
    End Sub

    Private Sub UpdateCounter()
        ToolStripStatusLabel.Text = CStr(ListView1.Items.Count)
        ToolStripButtonRun.Enabled = ListView1.Items.Count > 0
        ToolStripButtonNew.Enabled = ListView1.Items.Count > 0
        ToolStripButtonDown.Enabled = ListView1.Items.Count > 1
        ToolStripButtonUp.Enabled = ListView1.Items.Count > 1
    End Sub

    Private Sub ReadListFile(ByVal l As ListView, ByVal fullPath As String)
        Dim sr As StreamReader = Nothing
        Try
            Cursor = Cursors.WaitCursor
            l.BeginUpdate()
            l.Items.Clear()
            sr = New StreamReader(fullPath)
            Dim i As Integer = 0
            Dim r As String = ""
            If Not sr.EndOfStream Then
                r = sr.ReadLine
                i += 1
            End If
            While Not sr.EndOfStream
                Dim s As String = sr.ReadLine
                s = r & s
                ListViewAddItem(l, s, i)
                i += 1
            End While
        Finally
            sr.Close()
            l.EndUpdate()
            Cursor = Cursors.Default
        End Try
    End Sub

    Private Function GetFirstNotMatched(ByVal l As ListView, ByVal s As String, ByVal i As Integer) As Integer
        While i < l.Items.Count AndAlso l.Items(i).Text.StartsWith(s, StringComparison.InvariantCultureIgnoreCase)
            i += 1
        End While
        Return i
    End Function

    Private Function GetSourceRoot(ByVal l As ListView) As String
        If l.Items.Count > 0 Then
            Dim s As String = Path.GetDirectoryName(l.Items(0).Text)
            Dim i As Integer = 1
            While i < l.Items.Count
                i = GetFirstNotMatched(l, s, i)
                If i < l.Items.Count Then
                    s = Path.GetDirectoryName(s)
                End If
            End While
            If s <> "" Then s &= Path.DirectorySeparatorChar
            Return s
        End If
        Return ""
    End Function

    Public Sub SaveListFile(ByVal lv As ListView, ByVal fullPath As String)
        Dim sw As StreamWriter = Nothing
        Try
            Dim s As String = GetSourceRoot(lv)
            sw = New StreamWriter(fullPath)
            sw.WriteLine(s)
            For i As Integer = 0 To lv.Items.Count - 1
                Dim f As String = Replace(lv.Items(i).Text, s, "", , , CompareMethod.Text)
                sw.WriteLine(f)
            Next
        Finally
            sw.Close()
        End Try
    End Sub

    Private Sub MoveItem(ByVal xitem As ListViewItem, ByVal pos As Integer)
        Dim l As ListView = xitem.ListView
        l.Items.Remove(xitem)
        If pos > l.Items.Count Then
            pos = 0
        End If
        If pos < 0 Then
            pos = l.Items.Count
        End If
        l.Items.Insert(pos, xitem)
    End Sub

    Private Sub UpdateBuildOrderColumn(ByVal l As ListView, ByVal pos As Integer, ByVal len As Integer)
        For i As Integer = pos To pos + len - 1
            l.Items(i).SubItems(ColumnHeaderOrder.Text).Text = CStr(i + 1)
        Next
    End Sub

    Private Sub MoveListViewItem(ByRef lv As ListView, ByVal moveUp As Boolean)
        Try
            Dim curIndex As Integer
            Dim j As Integer
            If lv.SelectedItems.Count > 0 Then
                lv.BeginUpdate()
                j = lv.SelectedItems.Count - 1
                For i As Integer = 0 To lv.SelectedItems.Count - 1
                    If moveUp Then
                        curIndex = lv.SelectedItems(i).Index
                        MoveItem(lv.SelectedItems(i), curIndex - 1)
                    Else
                        curIndex = lv.SelectedItems(j).Index
                        MoveItem(lv.SelectedItems(j), curIndex + 1)
                    End If
                    j = j - 1
                Next
                Dim pos As Integer = lv.SelectedItems(0).Index
                Dim len As Integer = lv.SelectedItems.Count + 1
                If Not moveUp Then pos -= 1
                UpdateBuildOrderColumn(lv, pos, len)
                lv.EndUpdate()
                SetEnabledButtonsSave(True)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ProcessInit()
        mLastIndex = 0
        TextBoxLog.Text = ""
        ListViewInit(ListView1)
    End Sub

    Private Sub ProcessExec1()
        Try
            If ListView1.Items.Count > 0 Then
                Using cn As New SqlConnection(CtrlConnection1.ConnectionString)
                    cn.Open()
                    While mStarted AndAlso mLastIndex < ListView1.Items.Count
                        Dim l As ListViewItem = ListView1.Items(mLastIndex)
                        Try
                            l.ImageIndex = ImageIndexes.InProgress
                            l.EnsureVisible()
                            Application.DoEvents()
                            ExecuteSql(cn, l.Text)
                            l.ImageIndex = ImageIndexes.Ok
                            l.ForeColor = ColorOk
                            mLastIndex += 1
                            Application.DoEvents()
                        Catch ex As Exception
                            l.ImageIndex = ImageIndexes.Nok
                            l.ForeColor = ColorNok
                            Throw ex
                        End Try
                    End While
                    If mLastIndex = ListView1.Items.Count Then mLastIndex = 0
                End Using
            End If
        Catch ex As Exception
            SetEnabledButtons(True)
            LogWriteLine(ex.Message)
        End Try
    End Sub

    Private Sub ProcessResume()
        Select Case mProcessType
            Case ProcessType.PTExport : mThreadStart = AddressOf ProcessExec1
        End Select
        Timer1.Start()
    End Sub

    Private Sub ListViewDeleteSelected()
        For i As Integer = 0 To ListView1.SelectedItems.Count - 1
            ListView1.Items.Remove(ListView1.SelectedItems(0))
        Next i
        UpdateCounter()
        SetEnabledButtonsSave(True)
    End Sub

    Private Sub HandleNew()
        Try
            ListView1.Items.Clear()
            UpdateCounter()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub HandleOpen()
        Try
            SetVisisbleTextBoxes(False)
            Dim ofd As New OpenFileDialog
            ofd.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*"
            ofd.FilterIndex = 1
            ofd.FileName = TextBoxFile.Text
            If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
                TextBoxFile.Text = ofd.FileName
                ReadListFile(ListView1, ofd.FileName)
            End If
            UpdateCounter()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub HandleImport()
        Try
            SetVisisbleTextBoxes(True)
            Dim f As New FolderBrowserDialog
            f.SelectedPath = TextBoxFolder.Text
            If f.ShowDialog = Windows.Forms.DialogResult.OK Then
                Me.Cursor = Cursors.WaitCursor
                Me.ListView1.BeginUpdate()
                TextBoxFolder.Text = f.SelectedPath
                Dim stringArray() As String = {"*.sql"}
                Dim files As System.Collections.ObjectModel.ReadOnlyCollection(Of String) = _
                    My.Computer.FileSystem.GetFiles(TextBoxFolder.Text, FileIO.SearchOption.SearchAllSubDirectories, stringArray)
                Dim i As Integer
                For i = 0 To files.Count - 1
                    ListViewAddItem(ListView1, files(i), ListView1.Items.Count + 1)
                Next
                SetEnabledButtonsSave(True)
                UpdateCounter()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Me.ListView1.EndUpdate()
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub HandleSave()
        Try
            If TextBoxFile.Text <> "" Then
                SaveListFile(ListView1, TextBoxFile.Text)
                ToolStripButtonSave.Enabled = False
            Else
                HandleSaveAs()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub HandleSaveAs()
        Try
            Dim sfd As New SaveFileDialog
            sfd.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*"
            sfd.FilterIndex = 1
            sfd.FileName = TextBoxFile.Text
            If sfd.ShowDialog = Windows.Forms.DialogResult.OK Then
                SaveListFile(ListView1, sfd.FileName)
                ToolStripButtonSave.Enabled = False
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub HandleExit()
        Me.Close()
    End Sub

    Private Sub ListView_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick
        ListViewOnColumnClick(ListView1, e)
    End Sub

    Private Sub ListView_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.DoubleClick
        If ListView1.SelectedItems.Count >= 1 Then
            StartIDE(ListView1.SelectedItems(0).Text)
        End If
    End Sub

    Private Sub ListView_DragEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles ListView1.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            e.Effect = DragDropEffects.All
        End If
    End Sub

    Private Sub ListView_DragDrop(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles ListView1.DragDrop
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            ' Assign the files to an array.
            Dim myFiles() As String = CType(e.Data.GetData(DataFormats.FileDrop), String())
            For i As Integer = 0 To myFiles.Length - 1
                ListViewAddItem(ListView1, myFiles(i), ListView1.Items.Count + 1)
            Next
            SetEnabledButtonsSave(True)
            UpdateCounter()
        End If
    End Sub

    Private Sub ListView_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ListView1.KeyDown
        If e.Control AndAlso e.KeyValue = Keys.A Then
            For i As Integer = 0 To ListView1.Items.Count - 1
                ListView1.Items(i).Selected = True
            Next
        ElseIf e.KeyValue = Keys.Delete Then
            ListViewDeleteSelected()
        ElseIf e.KeyValue = Keys.Escape Then
            For i As Integer = 0 To ListView1.Items.Count - 1
                ListView1.Items(i).Selected = False
            Next
        End If
    End Sub

    Private Sub ListView_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
        If e.Button = MouseButtons.Right Then
            If ListView1.SelectedItems.Count >= 1 Then
                ContextMenuStrip1.Show(CType(sender, Control), e.X, e.Y)
            End If
        End If
    End Sub

    Private Sub ToolStripButtonNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonNew.Click
        HandleNew()
    End Sub

    Private Sub ToolStripButtonOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonOpen.Click
        HandleOpen()
    End Sub

    Private Sub ToolStripButtonImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonImport.Click
        HandleImport()
    End Sub

    Private Sub ToolStripButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonSave.Click
        HandleSave()
    End Sub

    Private Sub ToolStripButtonRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonRun.Click
        Try
            If mLastIndex = 0 Then
                ProcessInit()
                mProcessType = ProcessType.PTExport
            End If
            ProcessResume()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ToolStripButtonPause_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonPause.Click
        mStarted = False
        SetEnabledButtons(True)
    End Sub

    Private Sub ToolStripButtonCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonCancel.Click
        mStarted = False
        SetEnabledButtons(True)
        mLastIndex = 0
    End Sub

    Private Sub ToolStripButtonUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonUp.Click
        Me.MoveListViewItem(ListView1, True)
    End Sub

    Private Sub ToolStripButtonDown_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButtonDown.Click
        Me.MoveListViewItem(ListView1, False)
    End Sub

    Private Sub ToolStripMenuItemDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemDelete.Click
        Try
            ListViewDeleteSelected()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Try
            Timer1.Stop()
            mStarted = True
            SetEnabledButtons(False)
            mThreadStart()
            SetEnabledButtons(True)
        Catch ex As Exception
            LogWriteLine(ex.Message)
        End Try
    End Sub

    Private Sub ToolStripMenuItemNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemNew.Click
        HandleNew()
    End Sub

    Private Sub ToolStripMenuItemOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemOpen.Click
        HandleOpen()
    End Sub

    Private Sub ToolStripMenuItemImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemImport.Click
        HandleImport()
    End Sub

    Private Sub ToolStripMenuItemSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemSave.Click
        HandleSave()
    End Sub

    Private Sub ToolStripMenuItemSaveAs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemSaveAs.Click
        HandleSaveAs()
    End Sub

    Private Sub ToolStripMenuItemExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemExit.Click
        HandleExit()
    End Sub

    Private Sub FormExecScripts_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        SetVisisbleTextBoxes(True)
        mOriginalSettings = GetControlValues(Me, mExludeControlSettings)
        Try
            SetControlValues(Me, My.Settings.ControlValuesExecScripts)
            CtrlConnection1.LoadDatabases()
            CopyControlValuesFromTag(CtrlConnection1)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        End Try
    End Sub

    Private Sub FormExecScripts_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.ControlValuesExecScripts = GetControlValues(Me, mExludeControlSettings)
        My.Settings.Save()
    End Sub

End Class