codefirst 自动更新mysql数据库的示例

using MySql.Data.EntityFramework;
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations;
namespace MySQLRobotDAL
{
    // Code-Based Configuration and Dependency resolution
    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class MySQLContext: DbContext
    {
        static string connectString = "";//用于储存连接的连接字符串;

        public DbSet<Data> Datas { get; set; }
        //public RobotManage() : base("name=connectionString") { }
        public MySQLContext() : base(connectString) { }//connectString用于初始化migration

        // Constructor to use on a DbConnection that is already opened
        public MySQLContext(DbConnection existingConnection, bool contextOwnsConnection)
          : base(existingConnection, contextOwnsConnection)
        {
            connectString = existingConnection.ConnectionString;
            Database.SetInitializer<MySQLContext>(new MigrateDatabaseToLatestVersion<MySQLContext, ReportingDbMigrationsConfiguration>());
        }
        internal sealed class ReportingDbMigrationsConfiguration : DbMigrationsConfiguration<MySQLContext>
        {
            public ReportingDbMigrationsConfiguration()
            {
                AutomaticMigrationsEnabled = true;//任何Model Class的修改將會直接更新DB
                AutomaticMigrationDataLossAllowed = true;
            }
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //modelBuilder.Entity<Car>().MapToStoredProcedures();
        }
    }
    public class Data
    {
        [System.ComponentModel.DataAnnotations.Key]
        public int LogId { get; set; }
        public string RobotName { get; set; }
    }
}

使用方式

        public int MysqlCommand(string sql){
            using (var db = new MySQLContext(connection, false))
            {
                // var cmd = connection.CreateCommand();
                return db.Database.ExecuteSqlCommand(sql);
            }
        }

        public List<T> Select<T>(string sql)
        {
            //MySqlConnection connection = new MySqlConnection(connectString);
            using (var db = new MySQLContext(connection, false))
            {
                // var cmd = connection.CreateCommand();
                return db.Database.SqlQuery<T>(sql).ToList();
            }
        }
        //
                public int Save<Tmodel>(Tmodel model)
        {
            // MySqlConnection connection =
            using (var db = new MySQLContext(connection, false))
            {
                // db.Alarms.Add(alarm);

                string typename = model.GetType().Name;
                switch (typename)
                {
                    case "Alarm":
                        db.Alarms.Add(model as Alarm);
                        break;
                    case "Data":
                        db.Datas.Add(model as Data);
                        break;
                    default:
                        throw new Exception("找不到合适的MysqlModel,请查看是否用错了MysqlModel");

                }

               return db.SaveChanges();
            }
        }

上述判断也可以使用

   public int Save<Tmodel>(Tmodel model){
            object obj = model;
            if (model.GetType() == typeof(Modeltype.Data)) {}
   }

使用范围

,使用codefirst方式创建并可以运行软件自动创建数据库和修改数据表.

已知问题

当创建完数据库后 删除某个数据表时无法重新创建数据表.

安装

1. 安装mysql-installer-community-8.0.19.0.msi 其中包括mysql server 8.0.19 connector/net 8.0.19
2. 新建一个项目
3. 引入Entity Framework 6.4.4
   4 引入MySql.Data.EntityFramework -Version 8.0.21
   按照连接进行设置

codefirst更新,一下可以不使用,已经改为程序自动更新.

1. enable-migration 或 Enable-Migrations -ProjectName Demo.Domain -StartUpProjectName MigrationsDemo
2. add-migration xxx 添加新的更新项
3. update-database 直接更新到数据库,目前可以带数据更新但是如果某个字段名改变则会该字段的数据消失,其他不变.