Atualiza estrutura de dados com base em arquivos SQL do assembly.

SchemaUpdater.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Models.Schema
{
    public class SchemaUpdater
    {
        private static string TABLE_NAME = "DBO.SCHEMAVERSION";
        private static string VERSION_FIELD = "VERSION";
        private static string DATE_FIELD = "CREATEDAT";
        private static bool _executed = false;

        public static void Update(string cnx)
        {
            try
            {
                if (_executed)
                    return;

                Init(cnx);
                Update0(cnx);
            }
            catch(Exception ex)
            {
                Logger.Log("Erro ao atualizar estrutura do banco de dados.");
                Logger.Log(ex);
            }
            finally
            {
                _executed = true;
            }
        }

        private static void Init(string cnx)
        {
            var ass = typeof(SchemaUpdater).Assembly;
            var res = ass.GetManifestResourceNames().Where(t => t.Contains("Schema.S") && t.ToLower().EndsWith(".sql"));
            foreach (var it in res)
            {
                using (var reader = ass.GetManifestResourceStream(it))
                using (var str = new StreamReader(reader))
                {
                    var sql = str.ReadToEnd();
                    ExecuteSql(cnx, sql);
                }
            }
        }

        private static void Update0(string cnx)
        {
            var ass = typeof(SchemaUpdater).Assembly;
            var res = ass.GetManifestResourceNames().Where(t => t.Contains("Schema.A") && t.ToLower().EndsWith(".sql"));
            foreach (var it in res)
            {
                if (ExecuteScalar<int>(cnx, $"SELECT 1 FROM {TABLE_VERSION} WHERE {VERSION_FIELD} = @P0", it) == 1)
                    continue;

                Logger.Log("Atualizando: " + it);

                using (var reader = ass.GetManifestResourceStream(it))
                using(var str = new StreamReader(reader))
                {
                    var sql = str.ReadToEnd();
                    ExecuteSql(cnx, sql);
                    InsertSchema(cnx, it);
                }
            }

            Logger.Log("Estrutura atualizada com sucesso!");
        }

        private static void InsertSchema(string cnx, string it)
        {
            ExecuteSql(cnx,
                $"INSERT INTO {TABLE_VERSION} ({VERSION_FIELD}, {DATE_FIELD}) VALUES (@P0, @P1);",
                it, 
                DateTime.Now);
        }

        private static T ExecuteScalar<T>(string cnx, string sql, params object[] ps)
        {
            var val = ExecuteSql(true, cnx, sql, ps);
            if (val == null)
                return default(T);

            return (T)Convert.ChangeType(val, typeof(T));
        }

        private static void ExecuteSql(string cnx, string sql, params object[] ps)
        {
            ExecuteSql(false, cnx, sql, ps);
        }

        private static object ExecuteSql(bool scalar, string cnx, string sql, params object[] ps)
        {
            ps = ps ?? new object[0];

            using (var conn = new SqlConnection(cnx))
            using (var cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = sql;

                for(var i = 0; i<ps.Length;i++)
                {
                    cmd.Parameters.AddWithValue("@P" + i, ps[i]);
                }

                if (scalar)
                    return cmd.ExecuteScalar();

                cmd.ExecuteNonQuery();
                return null;
            }
        }
    }

    public class Logger
    {
        private static int MAX_SIZE = (1024 * 100); // 100Kb
        private static string _path;
        private static string _logger;

        public static void Init(string path)
        {
            _path = path;
            _logger = typeof(Logger).Assembly.GetName().Name;
        }

        public static void Log(Exception ex)
        {
            Log(ex.Message);
            Log(ex.StackTrace);
        }

        public static void Log(string str)
        {
            try
            {
                var path = Path.Combine(_path, "logs");
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);

                var file = Path.Combine(path, _logger + ".txt");
                var info = new FileInfo(file);
                if (info.Exists && info.Length > MAX_SIZE)
                {
                    var rename = string.Format("{1}-{0:yyyyMMdd-HHmmss}.txt", DateTime.Now, _logger);
                    File.Move(file, Path.Combine(path, rename));
                }

                var format = string.Format("[{0:dd/MM HH:mm:ss}] {1}\n", DateTime.Now, str);
                File.AppendAllText(file, format);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Erro ao gravar log: " + ex.Message);
            }
        }
    }
}