' EXAMPLE - Perf02 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 the least transactionally safe method
' available (TFSS).  It then reads (and sums) the rows.  Finally it deletes
' the rows.  The times to execute each of these operations is reported.
' 
' 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
'         ExecuteScalar() 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 Perf02
    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, "Perf02")

        ' Attempt to create the database
        Dim cmds(0) As String
        cmds(0) = "CREATE TABLE Simple (int_col INTEGER)"
        CreateThisDatabase(conn, "Perf02", 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("Perf02")
        sw.Stop()

        Console.WriteLine("Initializing with STANDALONE 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
    Sub AddRows(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()

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

        For ii As Int32 = 1 To NumRows
            cmd.ExecuteNonQuery()
        Next

        sw.Stop()

        Console.WriteLine("Add " & NumRows & " rows:" & vbTab & vbTab & vbTab & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
    End Sub

    ' Read all 50,000 rows, summing up the values stored in each one
    Sub ReadRows(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT SUM(int_col) FROM Simple"

        Dim total As Int32 = Convert.ToInt32(cmd.ExecuteScalar())

        sw.Stop()

        Console.WriteLine("Scanning, reading, summing " & NumRows & " rows:" & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
        Console.WriteLine(vbTab & "Sum = " & total)
    End Sub

    ' Update each row, setting its value to one more than it was
    Sub UpdateRows(conn As Rdm.RdmConnection)
        Dim sw As Stopwatch = Stopwatch.StartNew()

        Dim cmd As Rdm.RdmCommand = conn.CreateCommand()
        cmd.CommandText = "UPDATE Simple SET int_col = int_col + 1"
        cmd.ExecuteNonQuery()

        sw.Stop()

        Console.WriteLine("Updating " & NumRows & " rows:" & vbTab & vbTab & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
    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"
        cmd.ExecuteNonQuery()

        sw.Stop()

        Console.WriteLine("Deleting " & NumRows & " rows:" & vbTab & vbTab & vbTab & vbTab & sw.ElapsedMilliseconds & " millisecs")
    End Sub

    Sub Main()
        Console.WriteLine("Perf02 Example")
        Console.WriteLine()

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

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

        ' Add 50,000 rows to a table
        AddRows(conn)

        ' Read all the rows in the table
        ReadRows(conn)

        ' Update all the rows in the table
        UpdateRows(conn)

        ' Read all the rows in the table again
        ReadRows(conn)

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

        ' Cleanup
        Cleanup(conn)

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