using System;
using System.Diagnostics;

/* Raima.Rdm is the system module that contains the ADO.NET
 * data provider for RDM */
using Raima.Rdm;

/* EXAMPLE - Perf02 for C# 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 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.
 */

namespace Perf02_CS_ADO
{
    class Program
    {
        const int NumRows = 50000;

        static void DropDatabase(
            RdmConnection conn,
            string name)
        {
            RdmCommand cmd = 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();

                cmd.CommandText = "COMMIT";
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
        }

        static void CreateThisDatabase(
            RdmConnection conn,
            string name,
            string[] cmds)
        {
            RdmCommand cmd = conn.CreateCommand();

            /* Create the specified database.  Make sure the database is
             * closed when we are done so other databases can be created
             * after this if necessary. */
            cmd.CommandText = "CREATE DATABASE " + name;
            cmd.ExecuteNonQuery();

            foreach (string c in cmds)
            {
                cmd.CommandText = c;
                cmd.ExecuteNonQuery();
            }

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

            cmd.CommandText = "CLOSE " + name;
            cmd.ExecuteNonQuery();
        }

        static void CreateDatabases(
            RdmConnection conn)
        {
            string[] cmds =
            {
                "CREATE TABLE Simple (int_col INTEGER)"
            };

            /* Drop any existing copy of the database */
            DropDatabase(conn, "Perf02");

            /* Attempt to create the database */
            CreateThisDatabase(conn, "Perf02", cmds);
        }

        /* Initialization - Open the connection and create the database */
        static void Initialize(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();
            conn.Open();
            CreateDatabases(conn);
            conn.ChangeDatabase("Perf02");
            sw.Stop();

            Console.WriteLine("Initializing with STANDALONE configuration");
            Console.WriteLine("Preparing a new database:\t\t\t" + sw.ElapsedMilliseconds + " millisecs");
            Console.WriteLine("\tOne, simple row type");
            Console.WriteLine("\tOn disk");
            Console.WriteLine("\tOpened exclusively");
        }

        /* Cleanup - Close the connection */
        static void Cleanup(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();
            conn.Close();
            sw.Stop();

            Console.WriteLine("Cleanup:\t\t\t\t\t" + sw.ElapsedMilliseconds + " millisecs");
        }

        /* 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 */
        static void AddRows(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();

            RdmCommand cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO Simple VALUES 1";

            for (int ii = 0; ii < NumRows; ii++)
            {
                cmd.ExecuteNonQuery();
            }

            sw.Stop();

            Console.WriteLine("Add " + NumRows + " rows:\t\t\t\t\t" + sw.ElapsedMilliseconds + " millisecs");
        }

        /* Read all 50,000 rows, summing up the values stored in each one */
        static void ReadRows(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();

            RdmCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT SUM(int_col) FROM Simple";

            int total = (int) cmd.ExecuteScalar();

            sw.Stop();

            Console.WriteLine("Scanning, reading, summing " + NumRows + " rows:\t\t" + sw.ElapsedMilliseconds + " millisecs");
            Console.WriteLine("\tSum = " + total);
        }

        /* Update each row, setting its value to one more than it was */
        static void UpdateRows(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();

            RdmCommand cmd = conn.CreateCommand();
            cmd.CommandText = "UPDATE Simple SET int_col = int_col + 1";

            cmd.ExecuteNonQuery();

            sw.Stop();

            Console.WriteLine("Updating " + NumRows + " rows:\t\t\t\t" + sw.ElapsedMilliseconds + " millisecs");
        }

        /* Delete all rows */
        static void DeleteRows(RdmConnection conn)
        {
            Stopwatch sw = Stopwatch.StartNew();

            RdmCommand cmd = conn.CreateCommand();
            cmd.CommandText = "DELETE FROM Simple";

            cmd.ExecuteNonQuery();

            sw.Stop();

            Console.WriteLine("Deleting " + NumRows + " rows:\t\t\t\t" + sw.ElapsedMilliseconds + " millisecs");
        }

        static void Main(string[] args)
        {
            Console.WriteLine("Perf02 Example");
            Console.WriteLine();

            /* Create the connection with a simple connection string */
            RdmConnection conn = new 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();
        }
    }
}