Dapper 数据库增量升工具类

C# sqlite 数据库增量升级工具类，实现了 自动创建版本库、增量升级 。实现原理就是根据数据库版本和程序比较，如果大于就执行sql文件

调用方法

 bool result =  SqlUpdateHelper.SqlUpdate.AutoUpdate(sqlFilePath);

sqlFilePath是sql所在的文件夹

依赖Dapper 和sqlite的库

using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using Dapper;

namespace Util
{
    class SqlUpdateHelper
    {

        private SqlUpdateHelper()
        {
        }

        private void Init()
        {
            using (var dbCon = Util.DataBaseHelper.SqLiteDBCon)
            {
                int reslut = dbCon.Query<int>("select COUNT(*) from sqlite_master where `type`='table' and `name`=@name", new { name = "db_version" }).ToList().FirstOrDefault();
                if (reslut == 0)
                {
                    CreateDbVersionTable();
                }
               
            }
        }

        private int CreateDbVersionTable()
        {
            string createSql = "CREATE TABLE \"db_version\" (\n" +
                        "  \"version\" TEXT,\n" +
                        "  \"update_time\" TEXT\n" +
                        ");";

            string InserSql = "INSERT INTO db_version(\"version\", \"update_time\") VALUES ('1.0.0.0', '" + DateTime.Now.ToString() + "');";

            using (var dbCon = Util.DataBaseHelper.SqLiteDBCon)
            {
                dbCon.Open();
                //开始事务
                IDbTransaction transaction = dbCon.BeginTransaction();
                try
                {
                    int reslut = dbCon.Execute(createSql, transaction);
                    reslut = dbCon.Execute(InserSql, transaction);
                    //提交事务
                    transaction.Commit();
                    return reslut;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        private bool UpdateDbVersion(string version, IDbTransaction transaction = null, SQLiteConnection dbCon =null)
        {
            string sql = "UPDATE db_version SET \"version\" = '" + version + "', \"update_time\" = '" + DateTime.Now.ToString() + "';";

            if (dbCon == null) 
            {
                using (dbCon = DataBaseHelper.SqLiteDBCon)
                {
                    return dbCon.Execute(sql, transaction) > 0;
                }
            }

            return dbCon.Execute(sql, transaction) > 0;
        }

        private string GetDbVersion()
        {
            string sql = "SELECT db_version.version FROM db_version;";
            using (var dbCon = DataBaseHelper.SqLiteDBCon)
            {
                return dbCon.Query<string>(sql).ToList().FirstOrDefault();
            }
        }


        public bool AutoUpdate(string sqlFilePath)
        {
            // 初始化操作
            Init();

            Version oldVersion = new Version(GetDbVersion());
            Version newVersion = Assembly.GetExecutingAssembly().GetName().Version;
            var sqlFiles =  GetSqlFiles(sqlFilePath);

            if (sqlFiles.Length == 0) 
            {
                return true;
            }

            using (var dbCon = DataBaseHelper.SqLiteDBCon)
            {
                dbCon.Open();
                IDbTransaction transaction = dbCon.BeginTransaction();
                try
                {
                    foreach (var item in sqlFiles)
                    {
                        Version version = new Version(Path.GetFileNameWithoutExtension(item.FullName));
                        if (version > oldVersion && version <= newVersion)
                        {
                            string sql = File.ReadAllText(item.FullName);
                            dbCon.Execute(sql);
                        }
                    }
                    UpdateDbVersion(newVersion.ToString(), transaction, dbCon);
               
                    transaction.Commit();
                    foreach (var item in sqlFiles)
                    {
                        File.Delete(item.FullName);
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    //LogHelper.Logger.Error("升级数据库失败：",ex);
                    return false;
                }
            }
          
        }
     
        public static FileInfo[] GetSqlFiles(string path)
        {

            DirectoryInfo folder = new DirectoryInfo(path);
            if (folder.Exists) 
            {
                var sqlFiles = folder.GetFiles("*.sql");
                return sqlFiles;
            }
            return new FileInfo[0];
        }
     
        public static SqlUpdateHelper SqlUpdate { get; } = new SqlUpdateHelper();

    }
}