using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;

namespace ConAppSqlite
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //CreateDB();
                ShowCount();
                InsertToDB();
                ShowCount();
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
        }

        static void InsertToDB()
        {
            var sw = Stopwatch.StartNew();

            using (var conn = new SQLiteConnection("Data Source = my.db"))
            using (var cmd = new SQLiteCommand(conn))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    for (int i = 0; i < 100000; i++)
                    {
                        cmd.CommandText = @"INSERT INTO [person] values (@id, @name, @date);";

                        cmd.Parameters.Add("@id", DbType.Int32).Value = i;
                        cmd.Parameters.Add("@name", DbType.String).Value = "Name" + i;
                        cmd.Parameters.Add("date", DbType.DateTime).Value = DateTime.UtcNow;

                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
            }

            sw.Stop(); Console.WriteLine(sw.Elapsed);
        }

        static void ShowCount()
        {
            using (var conn = new SQLiteConnection("Data Source = my.db"))
            using (var cmd = new SQLiteCommand(conn))
            {
                conn.Open();

                cmd.CommandText = @"SELECT COUNT(*) FROM [person];";

                cmd.CommandType = CommandType.Text;
                var count = (long)cmd.ExecuteScalar();

                Console.WriteLine("Count: " + count);
            }
        }

        static void CreateDB()
        {
            SQLiteConnection.CreateFile("my.db");

            using (var conn = new SQLiteConnection("Data Source = my.db"))
            using (var cmd = new SQLiteCommand(conn))
            {
                conn.Open();

                cmd.CommandText = @"CREATE TABLE [person] (
                    [id] integer,
                    [name] nvarchar(100),
                    [dob] datetime );";

                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
        }
    }
}