' EXAMPLE - Perf06 for Visual Basic using ADO.NET
' This is a simple performance example creates 50,000 rows in a simple table
' in an on-disk database, using different transaction sizes (i.e. different
' number of rows inserted as part of each transaction) to demonstrate the
' performance difference when using small vs. large transactions.
' 
' The following methods are illustrated:
'     Raima.Rdm.RdmConnection:
'         ChangeDatabase(string) Method
'         Close() Method
'         CreateCommand() Method
'         Open() Method
'         RdmConnection() Constructor
'     Raima.Rdm.RdmCommand:
'         CommandText Property
'         ExecuteNonQuery() Method
' 
' They are fully document in http://docs.raima.com/rdm/.
' 
' For simplicity this example does not catch any thrown execptions
' but good programming practices would dictate that this occur in
' a full application.

' Raima.Rdm is the system module that contains the ADO.NET
' data provider for RDM
Imports Rdm = Raima.Rdm

Module Perf06
    Public Const NumRows = 50000

    Sub DropDatabase(conn As Rdm.RdmConnection, name As String)
        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()

        ' Attempt to drop this database.  If the database does not already
        ' exist this will fail and we simply ignore the failure for this
        ' sample.
        Try
            cmd.CommandText = "DROP DATABASE " + name
            cmd.ExecuteNonQuery()
        Catch ex As Exception
        End Try

    End Sub

    Sub CreateThisDatabase(conn As Rdm.RdmConnection, name As String, cmds() As String)
        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()

        cmd.CommandText = "CREATE DATABASE " + name
        cmd.ExecuteNonQuery()

        For Each c In cmds
            cmd.CommandText = c
            cmd.ExecuteNonQuery()
        Next

        cmd.CommandText = "COMMIT"
        cmd.ExecuteNonQuery()

        cmd.CommandText = "CLOSE " + name
        cmd.ExecuteNonQuery()
    End Sub

    Sub CreateDatabases(conn As Rdm.RdmConnection)
        ' Drop any existing copy of the databases.
        DropDatabase(conn, "Perf06")

        ' Attempt to create the database
        Dim cmds(0) As String
        cmds(0) = "CREATE TABLE Simple (int_col INTEGER)"
        CreateThisDatabase(conn, "Perf06", cmds)
    End Sub

    ' Initialization - Open the connection and create the database
    Sub Initialize(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()
        conn.Open()
        CreateDatabases(conn)
        conn.ChangeDatabase("Perf06")
        sw.Stop()

        Console.WriteLine("Initializing with DIRECT-LINK configuration")
        Console.WriteLine("Preparing a new database:" & vbTab & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
        Console.WriteLine(vbTab & "One, simple row type")
        Console.WriteLine(vbTab & "On disk")
        Console.WriteLine(vbTab & "Opened exclusively")
    End Sub

    ' Cleanup - Close the connection
    Sub Cleanup(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()
        conn.Close()
        sw.Stop()

        Console.WriteLine("Cleanup:" & vbTab & vbTab & vbTab & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
    End Sub

    ' Add 50,000 rows - there is no 'quick' method of inserting rows in
    ' bulk in SQL, so we have to execute the INSERT command 50,000 times.
    ' When adding rows, break them into transactions of 'transactionSize'
    ' rows per transaction. */
    Sub AddRows(conn As Rdm.RdmConnection, transactionSize As Int32)
        Console.Write("Add {0} rows with {1,-5} rows/transaction:", NumRows, transactionSize)

        Dim sw As Stopwatch = Stopwatch.StartNew()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "INSERT INTO Simple VALUES 1"

        Dim count As Int32 = 0
        Dim trans As Rdm.RdmTransaction

        For ii As Int32 = 1 To NumRows
            If count = 0 Then
                trans = conn.BeginTransaction()
            End If

            cmd.ExecuteNonQuery()

            count = count + 1
            If count = transactionSize Then
                trans.Commit()
                count = 0
            End If
        Next

        If count > 0 Then
            trans.Commit()
        End If

        sw.Stop()

        Console.WriteLine(vbTab & "{0} millisecs", sw.ElapsedMilliseconds)
    End Sub

    ' Delete all rows
    Sub DeleteRows(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "DELETE FROM Simple"

        Dim trans As Rdm.RdmTransaction = conn.BeginTransaction()
        cmd.ExecuteNonQuery()
        trans.Commit()

        sw.Stop()
    End Sub

    Sub Main()
        Dim transactionSizes(5) As Int32
        transactionSizes(0) = 50000
        transactionSizes(1) = 10000
        transactionSizes(2) = 5000
        transactionSizes(3) = 1000
        transactionSizes(4) = 500
        transactionSizes(5) = 100

        Console.WriteLine("Perf06 Example")
        Console.WriteLine()

        ' Create the connection with a simple connection string
        Dim conn As Rdm.RdmConnection = New Rdm.RdmConnection("host=local;openmode=x;tfstype=tfst;autocommit=off")

        ' Open the connection, delete any existing database, and create the
        ' database anew */
        Initialize(conn)

        Console.WriteLine()

        For Each transactionSize In transactionSizes
            ' Add 50,000 rows to a table
            AddRows(conn, transactionSize)

            ' Remove all the rows in the table
            DeleteRows(conn)
        Next

        Console.WriteLine()

        ' Cleanup
        Cleanup(conn)

        Console.WriteLine()
        Console.WriteLine("Hit <Enter> to continue")
        Console.ReadLine()
    End Sub
End Module
