# 实现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
}
}
测试运行结果: