Database Copy and Merge program

DatabaseCopy.cs

using System;
using System.Diagnostics;
using ServiceStack.OrmLite;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;

namespace Database.Copy
{
    class Program
    {
        static bool isCreateNew = true;

        static void Main(string[] args)
        {
            var sourceConnectrionString = "Data Source=LOCALHOST;Initial Catalog=QA;";
            var destinationConnectionString = "Data Source=LOCALHOST;Initial Catalog=Temp;Integrated Security=True;Connect Timeout=30";

            DatabaseHelper.ProviderName = "System.Data.SqlClient";
            DatabaseHelper.ConnectionString = sourceConnectrionString;
            var tables = DatabaseHelper.LoadTables(false);

            var sourceDb = new UnitOfWork();
            var destDb = new UnitOfWork();

            var stopwatch = new Stopwatch();
            stopwatch.Start();

            using (sourceDb.Open(sourceConnectrionString))
            {
                using (destDb.Open(destinationConnectionString))
                {
                    foreach (var table in tables)
                    {
                        var type = Assembly.Load("OrmLite.Model").GetTypes().First(t => t.Name == table.Name);

                        CopyTable(sourceDb, destDb, type);
                    }
                    
                    //CopyTable<User>(sourceDb, destDb);

                    //CopyDocument<Settings>(sourceDb, destDb);
                }
            }

            stopwatch.Stop();
            Console.WriteLine($"Database copied in {stopwatch.Elapsed}. Press any key to exit.");
            Console.ReadKey();
        }

        private static void MergeTable<T>(IUnitOfWork sourceDb, IUnitOfWork destDb)
        {
            if (isCreateNew)
            {
                destDb.Db.CreateTableIfNotExists<T>();
                Console.WriteLine($"Created table {typeof(T).FullName}.");
            }

            var count = 0;
            var query = sourceDb.Db.SelectLazy<T>();
            foreach (var n in query)
            {
                try
                {
                    if (destDb.Db.Exists<T>(n))
                        destDb.Repository.Update(n);
                    else
                        destDb.Repository.Insert_NoReturnId(n);
                }
                catch (Exception ex)
                {
                    //Console.WriteLine($"Exception occurred for {typeof(T).FullName}: {typeof(T).ToString()} Exception: {ex}");
                }

                count++;
            }
            //destDb.Repository.DeleteAll<T>();
            //var rows = sourceDb.Repository.All<T>();
            //var count = destDb.Repository.InsertRange(rows);

            Console.WriteLine($"Copied {count} rows for {typeof(T).FullName}.");
        }

        private static void CopyTable(IUnitOfWork sourceDb, IUnitOfWork destDb, Type type)
        {
            var destTypedApi = destDb.Db.CreateTypedApi(type);

            if (isCreateNew)
            {
                destDb.Db.CreateTableIfNotExists(type);
                Console.WriteLine($"Created table {type.FullName}.");
            }

            var rows = sourceDb.Db.Select<dynamic>($"SELECT * FROM {type.Name}");

            destTypedApi.DeleteAll();

            if (rows == null || rows.Count == 0)
                return;

            var props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(x => x.GetSetMethod() != null);

            // create an instance of the type
            var obj = Activator.CreateInstance(type);

            // set property values using reflection
            var values = (IDictionary<string, object>)rows[0];

            foreach (var row in rows)
            {
                foreach (var prop in props)
                {
                    
                    prop.SetValue(obj, values[prop.Name]);
                }
                destTypedApi.Insert(obj);
            }
            //destTypedApi.InsertAll(rows);

            Console.WriteLine($"Copied {rows.Count} rows for {type.Name}.");
        }

        //private static object CopyProperties(object source, object dest) {
        //    var props = currentType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
        //        .Where(x => x.GetSetMethod() != null);

        //    // create an instance of the type
        //    var obj = Activator.CreateInstance(currentType);

        //    // set property values using reflection
        //    var values = (IDictionary<string, object>)myDynamic;
        //    foreach (var prop in props)
        //        prop.SetValue(obj, values[prop.Name]);
        //}

        private static void CopyTable<T>(IUnitOfWork sourceDb, IUnitOfWork destDb)
        {
            if (isCreateNew)
            {
                destDb.Db.CreateTableIfNotExists<T>();
                Console.WriteLine($"Created table {typeof(T).FullName}.");
            }

            var count = 0;
            var rows = sourceDb.Repository.All<T>();
            //var query = sourceDb.Db.SelectLazy<T>();

            using (var destTrans = destDb.Db.OpenTransaction())
            {
                destDb.Repository.DeleteAll<T>();

                count = TryInsertRange(destDb, rows) ??
                        SafeInsertRange(destDb, rows);

                destTrans.Commit();
            }

            Console.WriteLine($"Copied {count} rows for {typeof(T).FullName}.");
        }

        private static void CopyDocument<T>(IUnitOfWork sourceDb, IUnitOfWork destDb) where T : IHasId<long>
        {
            destDb.TableStorageRepository.DeleteAll<T>();
            var rows = sourceDb.TableStorageRepository.All<T>();
            var count = destDb.TableStorageRepository.InsertRange(rows);

            Console.WriteLine($"Copied {count} rows for {typeof(T).FullName}.");
        }

        private static int? TryInsertRange<T>(IUnitOfWork destDb, IEnumerable<T> rows)
        {
            try
            {
                return destDb.Repository.InsertRange(rows);
            } catch (Exception ex)
            {
                return null;
            }
        }

        private static int SafeInsertRange<T>(IUnitOfWork destDb, IEnumerable<T> rows)
        {
            var count = 0;

            foreach (var n in rows)
            {
                try
                {
                    destDb.Repository.Insert_NoReturnId(n);
                }
                catch (Exception ex)
                {
                    //Console.WriteLine($"Exception occurred for {typeof(T).FullName}: {typeof(T).ToString()} Exception: {ex}");
                }

                count++;
            }

            return count;
        }
    }
}