SQLite

gistfile1.txt

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;

namespace SQLReader
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Read();
            //Write();
        }

        public static void Write()
        {
            SQLiteConnection connection = OpenConnection();

            CreateTable(connection);

            string updateSql = "INSERT INTO KeyValue (Id, Key, Value) VALUES (0, @0, @1) ON CONFLICT(Key) DO UPDATE SET Value=@1 WHERE Key=@0";

            SQLiteCommand updateCmd = new SQLiteCommand(updateSql, connection);
            updateCmd.Parameters.Add(new SQLiteParameter("@0"));
            updateCmd.Parameters.Add(new SQLiteParameter("@1"));


            int numWrites = 0;
            int totalStringValues = 1000;
            List<string> values = new List<string>(totalStringValues);
            Random random = new Random();

            // generate string values
            for (int i = 0; i < totalStringValues; i++)
            {
                values.Add(Guid.NewGuid().ToString());
            }

            Stopwatch sw = new Stopwatch();
            sw.Start();
            string key = "MyKey";

            while (true)
            {
                updateCmd.Parameters[0].Value = key;
                updateCmd.Parameters[1].Value = values[random.Next() % totalStringValues];
                updateCmd.ExecuteNonQuery();
                numWrites++;

                if (sw.ElapsedMilliseconds > 1000)
                {
                    ClearCurrentConsoleLine();
                    Console.WriteLine("Writes per second:" + numWrites);
                    Console.SetCursorPosition(0, Console.CursorTop - 1);
                    numWrites = 0;
                    sw.Restart();
                }
            }
            // Close the database connection
            connection.Close();
        }

        private static SQLiteConnection OpenConnection()
        {
            // Connect to a new SQLite database
            //SQLiteConnection.CreateFile("KeyValueDatabase.sqlite");
            SQLiteConnectionStringBuilder sQLiteConnectionStringBuilder = new SQLiteConnectionStringBuilder();
            sQLiteConnectionStringBuilder.Version = 3;
            sQLiteConnectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Wal;
            sQLiteConnectionStringBuilder.DataSource = "settings.dat";
            sQLiteConnectionStringBuilder.Flags = SQLiteConnectionFlags.NoConnectionPool | SQLiteConnectionFlags.NoVerifyTextAffinity | SQLiteConnectionFlags.NoVerifyTypeAffinity;
            sQLiteConnectionStringBuilder.SyncMode = SynchronizationModes.Normal;

            SQLiteConnection connection = new SQLiteConnection(sQLiteConnectionStringBuilder.ToString());
            connection.Open();
            return connection;
        }

        private static void CreateTable(SQLiteConnection connection)
        {
            string createTableSql = "CREATE TABLE IF NOT EXISTS KeyValue (Id INTEGER PRIMARY KEY, Key TEXT UNIQUE, Value TEXT)";
            SQLiteCommand createTableCmd = new SQLiteCommand(createTableSql, connection);
            createTableCmd.ExecuteNonQuery();
        }

        public static void Read()
        {
            SQLiteConnection connection = OpenConnection();
            CreateTable(connection);

            string selectSql = "SELECT Value FROM KeyValue WHERE Key=@0";

            SQLiteCommand selectCmd = new SQLiteCommand(selectSql, connection);
            selectCmd.Parameters.Add(new SQLiteParameter("@0"));

            int numReads = 0;
            Stopwatch sw = new Stopwatch();
            sw.Start();
            string key = "MyKey";
            string value = null;

            while (true)
            {
                selectCmd.Parameters[0].Value = key;
                using (var reader = selectCmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        value = reader.GetString(0);
                        numReads++;
                    }
                }

                if (sw.ElapsedMilliseconds > 1000)
                {
                    ClearCurrentConsoleLine();
                    Console.WriteLine("Reads per second:" + numReads);
                    Console.SetCursorPosition(0, Console.CursorTop - 1);

                    numReads = 0;
                    sw.Restart();
                }
            }
            // Close the database connection
            connection.Close();
        }

        public static void ClearCurrentConsoleLine()
        {
            int currentLineCursor = Console.CursorTop;
            Console.SetCursorPosition(0, Console.CursorTop);
            Console.Write(new string(' ', Console.WindowWidth));
            Console.SetCursorPosition(0, currentLineCursor);
        }
    }
}