实现Oracle，Mysql，SqlServer数据库的codefirst的操作

# 实现Oracle，Mysql，SqlServer数据库的codefirst的操作
项目版本 ----.NET Framework4.5

NuGet安装程序包

NLog---4.7.6
EntityFramework---6.4.4
Oracle---Oracle.ManagedDataAccess.EntityFramework ---12.1.22
Mysql---MySql.Data.Entities ---6.8.3
SqlServer---EntityFramework---6.4.4自带的

## 使用app.config来获取配置文件，app.config如下：

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  </configSections>
  <connectionStrings>
    <add name="XXDB" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3308;uid=root;database=TestMysql;pwd=123;CharSet=UTF8" />
    <!--oracle数据库配置-->
    <!--<add name="OracleContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=**SERVICE_NAME**)));Persist Security Info=True;User ID=**User ID**;Password=**Password**"/>-->
    <add name="Oracle" connectionString="Data Source=localhost:1521/orcl;Persist Security Info=True;User ID=test;Password=test;" providerName="Oracle.ManagedDataAccess.Client" />
    <!--mysql数据库配置-->
    <add name="Mysql" connectionString="server=localhost;port=3306;uid=root;database=TestMysql;pwd=123456;" providerName="MySql.Data.MySqlClient" />
    <!--sqlserver数据库配置-->
    <add name="SqlServer" connectionString="server=localhost;user id=sa;pwd=sa123;database=TestMySqlServer;" providerName="System.Data.SqlClient" />
    <!--sqlite数据库配置-->
    <!--<add name="PrintCertificate.Properties.Settings.JiaoxiaoSqlServerConnectionString" connectionString="Data Source=localhost;Initial Catalog=JiaoxiaoSqlServer;User ID=sa;Password=sa123" providerName="System.Data.SqlClient" />-->
    <!--sqlite数据库配置 注意上面的配置文件，需要将原来的【System.Data.SQLite.EF6】改为【System.Data.SQLite】，然后新增连接字符串（providerName="System.Data.SQLite.EF6" -->
    <!--BinaryGUID=False原因是GUID的文本表示和二进制的存储顺序并不是完全一样,在连接字符串中加入【BinaryGUID=False”】，指定GUID按照字符串存储，也就是说Model中的字段类型依然是GUID，但是数据中却存的是text。-->
    <add name="Sqlite" connectionString="Data Source=E:\\666.db;BinaryGUID=False;foreign keys=true;Version=3;" providerName="System.Data.SQLite.EF6" />
    <!--<add name="SqliteContext" connectionString="Data Source=E:\wlj\Person\LinjieTools\LinjieTools\FaceVerify\FaceVerify\bin\Debug\FaceVerify1.0.0.0.db" providerName="System.Data.SQLite" />-->
    <!--<add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=oracle_user;Password=oracle_user_password;Data Source=oracle" />-->
  </connectionStrings>
  <appSettings>
    <!--数据库类型，Oracle，Mysql，SqlServer，Sqlite，DB2等-->
    <add key="dbtype" value="Sqlite" />
    <!--这里需要指定schema，默认:sqlserver是dbo，mysql是...,oracle是用户名大写,下面只有oracle才需要的-->
    <add key="DefaultSchema" value="TEST" />
    <!--<add key="DbVersion" value="11" />-->
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <providers>
      <!--SQLite DbProvider-->
      <!--注意上面的配置文件，需要将原来的【System.Data.SQLite.EF6】改为【System.Data.SQLite】，然后新增连接字符串（providerName="System.Data.SQLite.EF6"）-->
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <!--SqlServer DbProvider-->
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <!--MySql DbProvider-->
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
      <!--<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>-->
      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
      <remove invariant="Oracle.ManagedDataAccess.Client" />
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
  </system.data>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-6.8.8.0" newVersion="6.8.8.0" />
      </dependentAssembly>
      <dependentAssembly>
        <publisherPolicy apply="no" />
        <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral" />
        <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.121.2.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) " />
      </dataSources>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

注意：经测试oracle，sqlserver，mysqlcodefirst都可以生成数据库，但是sqlite只能生成数据库文件，无法生成表

代码：

using SQLite.CodeFirst;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Configuration;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;

namespace FaceVerify
{
    public class SqliteDbContext : DbContext
    {
        public virtual DbSet<TblLogRecord1> TblLogRecord1s { get; set; }
        public virtual DbSet<TblEvaluateRecord1> TblEvaluateRecors { get; set; }
        public virtual DbSet<TblTaskExecuteRecord1> TblTaskExecuteRecord1s { get; set; }

        public SqliteDbContext(string nameOrConnectionString) : base(nameOrConnectionString)// base($"Data Source={nameOrConnectionString}")
        {
            //this.Configuration.LazyLoadingEnabled = false;//延迟加载
            //Database.SetInitializer<JiaXiaoDbContext>(new CreateDatabaseIfNotExists<JiaXiaoDbContext>());//不存在创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseAlways<JiaXiaoDbContext>());//重建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseIfModelChanges<JiaXiaoDbContext>());//当模型改变的时重新创建数据库
        }

        public SqliteDbContext(DbConnection existingConnection, bool contextOwnsConnection) : base(existingConnection, contextOwnsConnection)// base($"Data Source={nameOrConnectionString}")
        {
            //this.Configuration.LazyLoadingEnabled = false;//延迟加载
            //Database.SetInitializer<JiaXiaoDbContext>(new CreateDatabaseIfNotExists<JiaXiaoDbContext>());//不存在创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseAlways<JiaXiaoDbContext>());//重建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseIfModelChanges<JiaXiaoDbContext>());//当模型改变的时重新创建数据库           
        }


        //public SqliteDbContext() : base(new SQLiteConnection() { ConnectionString = new SQLiteConnectionStringBuilder() { DataSource = "EntityFramework.db", ForeignKeys = true }.ConnectionString }, true)
        //{
        //    //SQLiteConnection.Open();
        //}

        //protected override void OnModelCreating(DbModelBuilder modelBuilder)
        //{
        //    var model = modelBuilder.Build(Database.Connection);
        //    IDatabaseCreator sqliteDatabaseCreator = new SqliteDatabaseCreator();
        //    sqliteDatabaseCreator.Create(Database, model);

        //    //To add when exception is fixed (thought it was causing the issue, but is is not)
        //    //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        //}

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            #region MyRegion
            //modelBuilder.Configurations.Add(EntityTypeConfiguration)
            //一：数据库不存在时重新创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new CreateDatabaseIfNotExists<JiaXiaoDbContext>());
            //二：每次启动应用程序时创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseAlways<JiaXiaoDbContext>());
            //三：模型更改时重新创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseIfModelChanges<JiaXiaoDbContext>());
            //四：从不创建数据库
            //Database.SetInitializer<JiaXiaoDbContext>(null);
            //Database.SetInitializer<JiaXiaoDbContext>(new DropCreateDatabaseIfModelChanges<JiaXiaoDbContext>());

            //清除自动生成的数据表名被复数的问题
            //modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
            //自定义表名
            //modelBuilder.Entity<JiaXiao>().ToTable("dt_JiaXiao").HasKey(a => a.Id).Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            //modelBuilder.Entity<JiaXiao>().Property(x => x.Id).ValueGeneratedNever();

            //modelBuilder.Entity<TblLogRecord1>().ToTable("dt_TblLogRecord1").HasKey(a => a.Id)/*.Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)*/;
            //modelBuilder.Entity<TblEvaluateRecord1>().ToTable("dt_TblEvaluateRecord1").HasKey(a => a.Id)/*.Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)*/;
            //modelBuilder.Entity<TblTaskExecuteRecord1>().ToTable("dt_TblTaskExecuteRecord1").HasKey(a => a.Id)/*.Property(a => a.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)*/;

            //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            //Database.SetInitializer(new CreateDatabaseIfNotExists<SqliteDbContext>());
            //SqliteCreateDatabaseIfNotExists创建数据库
            //Database.SetInitializer(new SqliteCreateDatabaseIfNotExists<SqliteDbContext>(modelBuilder)); 
            #endregion

            //判断当前数据库是Oracle 需要手动添加Schema(DBA提供的数据库账号名称)
            //if (Database.IsOracle())
            //if (ConfigurationManager.AppSettings.Get("dbtype") == DBType.Oracle.ToString().Trim().ToLower())
            if (Enum.TryParse(ConfigurationManager.AppSettings.Get("dbtype"), out DBType dbtype) && Enum.IsDefined(typeof(DBType), dbtype))
            {
                switch (dbtype)
                {
                    case DBType.Oracle:
                        //这里需要指定schema，默认:sqlserver是dbo，mysql是，，，这里需要指定schema，默认:sqlserver是dbo，mysql是...,下面只有oracle才需要的，版本DbVersion默认oracle 11 
                        modelBuilder.HasDefaultSchema(ConfigurationManager.AppSettings.Get("DefaultSchema"));
                        break;
                    case DBType.Mysql:
                    case DBType.SqlServer:
                    case DBType.DB2:
                    default:
                        break;
                    case DBType.Sqlite:
                        var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<SqliteDbContext>(modelBuilder);
                        Database.SetInitializer(sqliteConnectionInitializer);
                        break;
                }
            }
            else
            {
                throw new Exception("数据库类型不对，不是数据库类型oracle，sqlserver，mysql，sqlite，db2支持内的一种！");
            }


            base.OnModelCreating(modelBuilder);

            //
            //modelBuilder.Configurations.AddFromAssembly(typeof(SqliteDbContext).Assembly);
            //Database.SetInitializer(new MyDbInitializer(Database.Connection.ConnectionString, modelBuilder));


            //var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<SqliteDbContext>(modelBuilder);
            //Database.SetInitializer(sqliteConnectionInitializer);

            ////SqliteDatabaseCreator Sample
            //var model = modelBuilder.Build(Database.Connection);
            //IDatabaseCreator sqliteDatabaseCreator = new SqliteDatabaseCreator();
            //sqliteDatabaseCreator.Create(Database, model);

            ////SqliteSqlGenerator Sample
            //var model = modelBuilder.Build(Database.Connection);
            //ISqlGenerator sqlGenerator = new SqliteSqlGenerator();
            //string sql = sqlGenerator.Generate(model.StoreModel);


            //修改sqlite数据库文件的位置
            //App.config配置文件中的连接字符串 中|DataDirectory|，connectionString = "data source=|DataDirectory|\database\data.db"
            //string fileName = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            //AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\" + Assembly.GetExecutingAssembly().GetName().Name + @"\");
        }
    }



    public class TblLogRecord1
    {
        [Key]
        //[Autoincrement]
        //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        //使用FluentAPI没有用，所以改用数据注解：Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
        //public string LogDate { get; set; }
        //public string LogLevel { get; set; }
        //public string Logger { get; set; }
        //public string Message { get; set; }
        //public string Exception { get; set; }
        public string MachineName { get; set; }
    }

    public class TblEvaluateRecord1
    {
        [Key]
        //[Autoincrement]
        //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        //public long UserId { get; set; }
        //public long DepartmentId { get; set; }
        //public long TradeCodeId { get; set; }
        //public int EvaluateValue { get; set; }
        public string ClientIP { get; set; }//adb连接方式时，ip是客户端的
        //public string Comment { get; set; }
        //public string InitiateEvaluateTime { get; set; }
        //public string EvaluateTime { get; set; }
    }

    public class TblTaskExecuteRecord1
    {
        [Key]
        //[Autoincrement]
        //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        //public long? TaskPublishRecordId { get; set; }
        //public ScheduleType ScheduleType { get; set; }
        public string CommandJson { get; set; }
        //public long? DeviceClientId { get; set; }
        //public long? DesktopClientId { get; set; }
        //public string ClientType { get; set; }
        //public string PublishTime { get; set; }
        //public bool IsPublishSucceed { get; set; }
        //public string PublishRemark { get; set; }//备注，一般是错误消息
        //public string ExecuteTime { get; set; }
        //public bool IsExecuteSucceed { get; set; }
        //public string ExecuteRemark { get; set; }//备注，一般是错误消息
    }

    /// <summary>
    /// 任务类型
    /// </summary>
    public enum ScheduleType
    {
        AutoSchedule,//任务调度（自动调度）
        ReSchedule,//失败后重新调度（手动发起重新调度）
        ManualTempSchedule//临时调度（页面上手动发起一个命令）
    }
}

 

运行代码如下：

using NLog;
using NLog.LayoutRenderers;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using static System.Net.WebRequestMethods;

namespace FaceVerify
{
    public static class Program
    {
        private static Logger _logger;
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);

            try
            {
                //var logdbfile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"{Application.ProductName + Application.ProductVersion}.db");
                //if (!File.Exists(logdbfile ?? SqliteCommon.LogDBFilePath))
                //{
                //    //如果数据库文件log.db不存在，则创建
                //    SQLiteConnection.CreateFile(logdbfile ?? SqliteCommon.LogDBFilePath);
                //}

                //using (SqliteDbContext sqliteDbContext = new SqliteDbContext(logdbfile ?? SqliteCommon.LogDBFilePath))


                //using (SqliteDbContext sqliteDbContext = new SqliteDbContext("name=Mysql"))
                //using (SqliteDbContext sqliteDbContext = new SqliteDbContext("name=SqlServer"))
                using (SqliteDbContext sqliteDbContext = new SqliteDbContext("name=Sqlite"))
                //using (SqliteDbContext sqliteDbContext = new SqliteDbContext(new SQLiteConnection() { ConnectionString = "Data Source = E:\\wlj\\Person\\LinjieTools\\LinjieTools\\FaceVerify\\FaceVerify\\bin\\Debug\\FaceVerify1.0.0.0666.db; BinaryGUID = False; foreign keys = true; Version = 3;" }, true))
                {
                    //sqliteDbContext.Database.Create();
                    bool isCretaed = sqliteDbContext.Database.CreateIfNotExists();
                    sqliteDbContext.TblLogRecord1s.Add(new TblLogRecord1
                    {
                        Id = DateTime.Now.Millisecond,
                        //LogDate = DateTime.Now.ToString(),
                        //LogLevel = LogLevel.Warn.Name,
                        //Logger = LogManager.GetCurrentClassLogger().Name,
                        //Message = $"{DateTime.Now} 测试",
                        //Exception = null,
                        //MachineName = Environment.MachineName
                    });
                    sqliteDbContext.SaveChanges();

                }
                //InitDB initDB = new InitDB();
                //var connectionString = initDB.Init(SqliteCommon.LogDBFilePath, 15);
                //LoggerConfig loggerConfig = new LoggerConfig(connectionString);
                //loggerConfig.EnsureNLogConfig(LogLevel.Trace.ToString());
                _logger = LogManager.GetCurrentClassLogger();
                //XamlResourceHelper.ChangeLanguage(EvMSSettingHelper.AppSetting.ProgramConfig.Language);
                //保证只能运行一个实例
                EnsureOnlyOneInstance();

#if DEBUG
                _logger.Info($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}： 启动成功！");
#endif

                Application.Run(new Form1());
            }
            catch (Exception ex)
            {
                _logger.Error(ex.Message);
            }
            finally
            {
                _logger = null;
                LogManager.Shutdown();
            }
        }

        #region 确保只有一个实例在运行
        /// <summary>
        /// 确保只有一个实例在运行，并且重复运行时，自动激活实例窗口
        /// </summary>
        private static void EnsureOnlyOneInstance()
        {
            Process instance = RunningInstance(); //Get the running instance.   
            if (instance != null)
            {
                //There is another instance of this process.   
                HandleRunningInstance(instance);
                Environment.Exit(0);
            }
        }

        /// <summary>
        /// 检测进程是否在运行
        /// </summary>
        /// <returns></returns>
        private static Process RunningInstance()
        {

            Process current = Process.GetCurrentProcess();
            Process[] processes = Process.GetProcessesByName(current.ProcessName);
            //Loop through the running processes in with the same name  

            foreach (Process process in processes)
            {
                //Ignore   the   current   process   
                if (process.Id != current.Id)
                {
                    //Make sure that the process is running from the exe file.   
                    if (Assembly.GetExecutingAssembly().Location.Replace("/", "\\") == current.MainModule.FileName)
                    {
                        //Return the other process instance.   
                        return process;
                    }
                }
            }
            //No other instance was found, return null. 
            return null;
        }

        /// <summary>
        /// 设置窗体弹出显示
        /// </summary>
        /// <param name="instance"></param>
        private static void HandleRunningInstance(Process instance)
        {
            //Make   sure   the   window   is   not   minimized   or   maximized   
            ShowWindowAsync(instance.MainWindowHandle, _ws_SHOWNORMAL);
            //Set   the   real   intance   to   foreground   window
            SetForegroundWindow(instance.MainWindowHandle);
        }

        [DllImport("User32.dll")]
        private static extern bool ShowWindowAsync(IntPtr hWnd, int cmdShow);
        [DllImport("User32.dll")]
        private static extern bool SetForegroundWindow(IntPtr hWnd);


        private const int _ws_SHOWNORMAL = 1;
        #endregion
    }
}

 

测试运行结果：