DataTable caching performance

Imports System.Configuration
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.IO
Imports System.Reflection
Imports System.Threading.Tasks
Imports System.Windows.Forms

Public Class Form1

    Private mStopWatch As New Stopwatch
    Private mType As Type
    Private mSql As String
    Private mWithCache As Boolean
    Private mCancel, mStart As Boolean
    Private mCancelSuites As Boolean
    Private mDB As New DBAccess
    Private mCounts() As Integer

    Private TestCaches As String() = {"without Cache", "with Cache"}

    Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.
    End Sub

    Private Function NZ(ByVal v As Object) As Integer
        If IsNumeric(v) Then Return v
    End Function

    Private Sub StringAppend(ByRef s As String, ByVal t As String, Optional ByVal u As String = "")
        If u <> "" Then
            s &= t & u
        End If
    End Sub

    Private Function GetSelectStmt(ByVal whereClause As String, ByVal orderByClause As String)
        Dim s As String = "SELECT * FROM Items"
        StringAppend(s, " WHERE ", whereClause)
        StringAppend(s, " ORDER BY ", orderByClause)
        Return s
    End Function

    Private Function GetSelectStmt()
        Return GetSelectStmt(TextBoxCriteria.Text, TextBoxSort.Text)
    End Function

    Private Sub TruncateTable()
        mDB.ExecuteCommand("TRUNCATE TABLE Items", System.Data.CommandType.Text)
    End Sub

    Private Sub SaveTable()
        Dim ds As New DataSet
        ds.Merge(ItemsData1)
        mDB.Update(ds, ItemsData1.Items.TableName)
    End Sub

    Private Sub FillDataSet(ByVal items() As String)
        ItemsData1 = New ItemsData
        'ItemsData1.Clear()
        If items IsNot Nothing Then
            For Each f In items
                Dim dr As ItemsData.ItemsRow = ItemsData1.Items.Rows.Add
                dr.Name = f
            Next
        End If
        ItemsData1BindingSource.DataSource = ItemsData1
    End Sub

    Private Sub FillGrid(ByVal items() As String, ByVal x As Integer, ByVal y As Integer)
        For i = 0 To UBound(items)
            DataGridView1.Rows(x + i).Cells(y).Value = i + 1
            DataGridView1.Rows(x + i).Cells(y + 1).Value = items(i)
        Next
    End Sub

    Private Function GetSelectedItem(ByVal c As ComboBox) As Object
        Return c.Invoke( _
            Function() As Object
                Return c.SelectedItem
            End Function)
    End Function

    Private Function GetComboBoxParallelMethodSelectedItem() As Object
        Return ComboBoxParallelMethod.Invoke( _
            Function() As Object
                Return ComboBoxParallelMethod.SelectedItem
            End Function)
    End Function

    Private Sub WriteOutput(ByVal s As String)
        TextBoxOutput.Invoke(Sub() TextBoxOutput.AppendText(s & vbCrLf))
    End Sub

    Private Function FormatElapsedTime() As String
        Dim ts As TimeSpan = mStopWatch.Elapsed
        Return String.Format("{0}min {1}sec", ts.Minutes, ts.Seconds + ts.Milliseconds / 1000)
    End Function

    Private Sub GetFileFromDialog(ByVal t As TextBox, ByRef v As String, ByVal filter As String)
        Dim f As New OpenFileDialog
        f.InitialDirectory = Path.GetDirectoryName(v)
        f.FileName = Path.GetFileName(v)
        f.Filter = filter
        If f.ShowDialog = Windows.Forms.DialogResult.OK Then
            t.Text = f.FileName
            v = f.FileName
        End If
    End Sub

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

    Private Sub MyMerge(ByVal ds As DataSet)
        If ds IsNot Nothing Then
            ItemsData1.Merge(ds)
        Else
            ItemsData1.Clear()
        End If
    End Sub

    Private Sub FillComboboxDataObject()
        For Each t In DBAccess.DataAccessClasses
            ComboBoxDataObject.Items.Add(t)
        Next
        ComboBoxDataObject.SelectedIndex = 0
    End Sub

    Private Sub AddRandoms(ByVal n As Integer)
        Dim r As New Random(Now.Ticks Mod Integer.MaxValue)
        For i As Integer = 1 To n
            Dim dr As ItemsData.ItemsRow = ItemsData1.Items.Rows.Add
            dr.Name = r.Next
        Next
    End Sub

    Private Function NrTestSuites() As Integer
        Return NrTestCriteria * DBAccess.DataAccessClasses.Length * TestCaches.Length
    End Function

    Private Function GetTestSuite() As String()
        Dim criteria() As String = GetTestCriteria(ItemsData1.Items.NameColumn)
        Dim items(NrTestSuites() - 1) As String
        Dim i As Integer
        For Each criterio In criteria
            For Each t As Type In DBAccess.DataAccessClasses
                For Each ch In TestCaches
                    items(i) = criterio & " | " & t.FullName & " | " & ch
                    i += 1
                Next
            Next
        Next
        Return items
    End Function

    Private Sub InitCache()
        mDB.Init(ComboBoxDataObject.SelectedItem, GetSelectStmt)
    End Sub

    Private Sub RunTestLoop(ByVal i As Integer)
        Dim count = 0
        Dim db As New DBAccess
        db.Init(mType, mSql)
        While Not mStart
        End While
        While Not mCancel
            Try
                Dim ds As DataSet = db.GetDataBaseOrCache(ItemsData1.Items.TableName, mSql, mWithCache)
                count += 1
                'count = Math.Max(count, ds.Tables(0).Rows.Count)
            Catch ex As Exception
                WriteOutput(String.Format("RunTestLoop Thread {0}, Table size={1}, nr threads={2}, sql={3}, withCache={4}, ex={5}", i, ItemsData1.Items.Count, UBound(mCounts) + 1, mSql, mWithCache, ex.Message))
            End Try
        End While
        mCounts(i) = count
    End Sub

    Private Sub ProcessParallel(ByVal nrThreads As Integer)
        Dim p As New ParallelOptions() With {.MaxDegreeOfParallelism = nrThreads}
        Parallel.For(0, nrThreads, p, AddressOf RunTestLoop)
    End Sub

    Private Sub ParallelFor(ByVal nrThreads As Integer)
        Dim t As New Threading.Thread(AddressOf ProcessParallel)
        t.Start(nrThreads)
        mStart = True
        Threading.Thread.Sleep(1000)
        mCancel = True
        t.Join()
    End Sub

    Private Sub ParallelThreads(ByVal nrThreads As Integer)
        Dim threads(nrThreads - 1)
        For i = 0 To UBound(threads)
            threads(i) = New Threading.Thread(AddressOf RunTestLoop)
        Next
        For i = 0 To UBound(threads)
            threads(i).Start()
        Next
        mStart = True
        Threading.Thread.Sleep(1000)
        mCancel = True
        For i = 0 To UBound(threads)
            threads(i).Join()
        Next
    End Sub

    Private Function RunParallelTest(ByVal m As MethodInfo, ByVal nrThreads As Integer) As Integer
        mStart = False
        mCancel = False
        ReDim mCounts(nrThreads - 1)
        m.Invoke(Me, New Object() {nrThreads})
        Dim count As Integer
        For Each i In mCounts
            count += i
        Next
        Return count
    End Function

    Private Function RunParallelTest() As Integer
        Dim m As MethodInfo = _
            Me.GetType.GetMethod(ComboBoxParallelMethod.SelectedItem, BindingFlags.Instance + BindingFlags.NonPublic)
        Dim nrThreads = TextBoxNrThreads.Text
        mType = ComboBoxDataObject.SelectedItem
        mSql = GetSelectStmt()
        Return RunParallelTest(m, nrThreads)
    End Function

    Private Function RunTestSuiteRow( _
        ByVal i As Integer, ByVal s As String, ByVal m As MethodInfo, ByVal nrThreads As Integer) As Integer
        Try
            Dim t() As String = s.Split(New String() {" | "}, StringSplitOptions.None)
            mType = DBAccess.GetDataAccessClass(t(1))
            If mType Is Nothing Then
                Throw New Exception(String.Format("No type found with name = {0}", t(1)))
            End If
            mWithCache = t(2) = TestCaches(1)
            mSql = GetSelectStmt(t(0), "")
            mDB.Init(mType, mSql)
            mDB.ClearCache()
            Dim count = RunParallelTest(m, nrThreads)
            Application.DoEvents()
            Return count
        Catch ex As Exception
            Throw New Exception(String.Format("Test {0}: {1}", i, ex.Message))
        End Try
    End Function

    Private Sub RunTestSuiteRow(ByVal dr As ItemsData.ItemsRow, ByVal m As MethodInfo, ByVal nrThreads As Integer)
        Try
            dr.Message = RunTestSuiteRow(dr.Items_Id, dr.Name, m, nrThreads)
        Catch ex As Exception
            Throw New Exception(String.Format("Test {0}: {1}", dr.Items_Id, ex.Message))
        End Try
    End Sub

    Private Sub RunTestSuiteFromDataset(ByVal m As MethodInfo, ByVal nrThreads As Integer)
        For Each dr As ItemsData.ItemsRow In ItemsData1.Items
            dr.Message = ""
        Next
        For Each dr As ItemsData.ItemsRow In ItemsData1.Items
            RunTestSuiteRow(dr, m, nrThreads)
        Next
        InitCache()
    End Sub

    Private Sub RunTestSuiteFromGrid(ByVal y As Integer, ByVal m As MethodInfo, ByVal nrThreads As Integer)
        For i = 2 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows(i).Cells(y).Value = _
                RunTestSuiteRow( _
                    DataGridView1.Rows(i).Cells(0).Value, DataGridView1.Rows(i).Cells(1).Value, m, nrThreads)
        Next
    End Sub

    Private Function GCol(ByVal i As Integer, ByVal j As Integer, ByVal n As Integer) As Integer
        Return i * n + j + 3
    End Function

    Private Sub GridView1AddTestSuites( _
        ByVal step1 As Integer, ByVal maxValue As Integer, ByVal maxThreads As Integer)
        DataGridView1.DataSource = Nothing
        While DataGridView1.Rows.Count > 0
            DataGridView1.Rows.RemoveAt(0)
        End While
        While DataGridView1.Columns.Count > 3
            DataGridView1.Columns.RemoveAt(3)
        End While
        MessageDataGridViewTextBoxColumn.Visible = False
        DataGridView1.Rows.Add()
        DataGridView1.Rows.Add()
        DataGridView1.Rows(0).Cells(1).Value = "Table size"
        DataGridView1.Rows(1).Cells(1).Value = "Nr of threads"
        For i As Integer = 0 To maxValue / step1 - 1
            'For j As Integer = 0 To maxThreads
            For j As Integer = 0 To 0
                'Dim k As Integer = GCol(i, j, maxThreads + 1)
                Dim k As Integer = GCol(i, j, 1)
                Dim l As Integer = (i + 1) * step1
                'Dim m As Integer = 2 ^ j
                Dim m As Integer = maxThreads
                Dim s As String = l & ":" & m
                Dim c As New DataGridViewTextBoxColumn() With {.Name = s, .Width = 50}
                c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                DataGridView1.Columns.Add(c)
                DataGridView1.Rows(0).Cells(k).Value = l
                DataGridView1.Rows(1).Cells(k).Value = m
            Next
        Next
        For i = 0 To NrTestSuites() - 1
            DataGridView1.Rows.Add()
        Next
        'ItemsData1 = New ItemsData
    End Sub

    Private Sub RunTestSuites(ByVal y As Integer)
        mCancelSuites = False
        Dim m As MethodInfo = _
            Me.GetType.GetMethod(GetSelectedItem(ComboBoxParallelMethod), BindingFlags.Instance + BindingFlags.NonPublic)
        While y < DataGridView1.ColumnCount AndAlso Not mCancelSuites
            Dim p = NZ(DataGridView1.Rows(0).Cells(y - 1).Value)
            Dim d = DataGridView1.Rows(0).Cells(y).Value - p
            If d > 0 Then
                AddRandoms(d)
                SaveTable()
                FillGrid(GetTestSuite(), 2, 0)
            End If
            RunTestSuiteFromGrid(y, m, DataGridView1.Rows(1).Cells(y).Value)
            y += 1
        End While
    End Sub

    Private Sub InitAndRunTestSuites()
        Dim step1 = 100, maxValue = 200, maxThreads = 5
        GridView1AddTestSuites(step1, maxValue, maxThreads)
        TruncateTable()
        RunTestSuites(3)
    End Sub

    Private Sub BindGrid()
        DataGridView1.DataSource = ItemsData1BindingSource
        DataGridView1.DataMember = ItemsData1.Items.TableName
    End Sub

    Private Sub LoadData(ByVal withCache As Boolean)
        Try
            Cursor.Current = Cursors.WaitCursor
            BindGrid()
            ItemsData1.Clear()
            mStopWatch.Restart()
            Dim s As String = GetSelectStmt()
            MyMerge(mDB.GetDataBaseOrCache(ItemsData1.Items.TableName, s, withCache))
            mStopWatch.Stop()
            Cursor.Current = Cursors.Default
            MsgBox("Data loaded in " & FormatElapsedTime())
        Catch ex As Exception
            Cursor.Current = Cursors.Default
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            FillComboboxDataObject()
            ComboBoxParallelMethod.SelectedIndex = 0
            If My.Settings.ConnectionString <> "" Then
                TextBoxConnectionString.Text = My.Settings.ConnectionString
            Else
                TextBoxConnectionString.Text = ConfigurationManager.ConnectionStrings(MySQLDataAccess.DB1).ConnectionString
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Save()
    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try
            RunTestSuites(3)
        Catch ex As Exception
            e.Result = ex.Message
        End Try
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        If e.Result IsNot Nothing Then
            MsgBox(e.Result)
        Else
            MsgBox("Ok")
        End If
        InitCache()
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        If e.RowIndex >= 0 Then
        End If
    End Sub

    Private Sub ToolStripMenuItemClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemClear.Click
        ItemsData1 = New ItemsData
        ItemsData1BindingSource.DataSource = ItemsData1
        BindGrid()
    End Sub

    Private Sub ToolStripMenuItemAddRandom_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemAddRandom.Click
        Try
            ItemsData1BindingSource.SuspendBinding()
            AddRandoms(TextBoxNrRandoms.Text)
            ItemsData1BindingSource.ResumeBinding()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ToolStripMenuItemRecreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemRecreateTable.Click
        Try
            mDB.ExecuteCommand(My.Resources.SqlCreateTableItems, System.Data.CommandType.Text)
            MsgBox("Table Items created")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ToolStripMenuItemFillTestSuite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItemFillTestSuite.Click
        Try
            Dim items() As String = GetTestSuite()
            FillDataSet(items)
            BindGrid()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonSaveDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSaveDB.Click
        Try
            Cursor.Current = Cursors.WaitCursor
            mStopWatch.Restart()
            SaveTable()
            ItemsData1.AcceptChanges()
            mStopWatch.Stop()
            Cursor.Current = Cursors.Default
            MsgBox("Changes saved to db in " & FormatElapsedTime())
        Catch ex As Exception
            Cursor.Current = Cursors.Default
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLoad.Click
        LoadData(True)
    End Sub

    Private Sub ButtonLoadDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLoadDB.Click
        LoadData(False)
    End Sub

    Private Sub ButtonTruncateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonTruncateDB.Click
        Try
            TruncateTable()
            MsgBox("Table Items trunated")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonClearCache_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonClearCache.Click
        Try
            mDB.ClearCache(ItemsData1.Items.TableName)
            MsgBox("Cache cleared")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonGetCacheKeys_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonGetCacheKeys.Click
        Try
            BindGrid()
            Dim s() As String = mDB.GetCacheKeys
            FillDataSet(s)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub RunRepeated(ByVal b As Boolean)
        Try
            mWithCache = b
            Dim n = RunParallelTest()
            MsgBox(String.Format("{0} iterations completed", n))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonRepeatedLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRepeatedLoad.Click
        RunRepeated(True)
    End Sub

    Private Sub ButtonRepeatedLoadDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRepeatedLoadDB.Click
        RunRepeated(False)
    End Sub

    Private Sub ButtonRunTestSuite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRunTestSuite.Click
        Try
            Dim m As MethodInfo = _
                Me.GetType.GetMethod(ComboBoxParallelMethod.SelectedItem, BindingFlags.Instance + BindingFlags.NonPublic)
            RunTestSuiteFromDataset(m, TextBoxNrThreads.Text)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonPrepareTestSuites_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonPrepareTestSuites.Click
        GridView1AddTestSuites(TextBoxNrRandoms.Text, TextBoxMaxTableSize.Text, TextBoxNrThreads.Text)
    End Sub

    Private Sub ButtonRunTestSuites_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRunTestSuites.Click
        Try
            TruncateTable()
            BackgroundWorker1.RunWorkerAsync()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub ButtonPause_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonPause.Click
        mCancelSuites = True
        MsgBox("Application will be paused after current test suite.")
    End Sub

    Private Sub ButtonResume_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonResume.Click
        Dim y = 3
        While DataGridView1.Rows(3).Cells(y).Value IsNot Nothing
            y += 1
        End While
        RunTestSuites(y)
    End Sub

    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        BackgroundWorker1.CancelAsync()
        Timer1.Stop()
    End Sub

    Private Sub ComboBoxDataObject_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxDataObject.SelectedIndexChanged
        Try
            InitCache()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub TextBoxCriteria_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxCriteria.TextChanged
        Try
            InitCache()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub TextBoxSort_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxSort.TextChanged
        Try
            InitCache()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class