C#.NET FRAMEWORK 4.6 EF 连接MYSQL5.7

C#.NET FRAMEWORK 4.6 EF 连接MYSQL5.7

环境：

.NET FRAMEWORK 4.6

MYSQL版本：5.7.40. 

 

 

表结构：

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(255) DEFAULT NULL COMMENT '真实姓名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_userid` varchar(255) DEFAULT NULL COMMENT '创建用户ID',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表';

 

 

1.nuget 引用 EntityFramework 、和 MySql.Data.EntityFramework。  

EntityFramework  版本：6.4.4，

MySql.Data.EntityFramework 版本：8.0.30。

下载 MySql.Data.EntityFramework  时会自动 下载 MySql.Data 这个库。

2.新建实体类：

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WinFormEfMysql8.db
{
    public class sys_user
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  //设置自增
        public long id { get; set; }

        public string user_name { get; set; }

        public string real_name { get; set; }
        public string password { get; set; }
        public string remark { get; set; }
        public string create_userid { get; set; }

        //public string UpdateUserid { get; set; }

        public DateTime create_time { get; set; }
        public DateTime? update_time { get; set; }


    }
}

3.新建DbContext：

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace WinFormEfMysql8.db
{
    public class EfTestDbContext : DbContext
    {
        public EfTestDbContext() : base("EfTestDbContext")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //移除复数表名
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<sys_user> sys_user { get; set; }
    }
}

 

4.检查Web.config 或 app.config

4.1 检查 entityFramework - providers 配置节（这里是nuget 自动生成的），有没有：

<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework" />

没有则加上。

4.2 检查 system.data - DbProviderFactories 配置节（一般需要手动加上的），有没有：

    <!-- 这里是 手动加上的 -->
    <system.data>
        <DbProviderFactories>
            <remove invariant="MySql.Data.MySqlClient"/>
            <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.30.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
        </DbProviderFactories>
    </system.data>

 

没有则加上。

 

4.3 连接字符串：

<connectionStrings>
    <!-- mysql5.7要显示指定charset，和数据库保持一致，否则提交中文到库里面会乱码 -->
    <add name="EfTestDbContext" connectionString="Data Source=128.0.14.43; port=3306;Database=db3;uid=jojo;pwd=Gameking2008*;charset=utf8mb4;" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

完整配置文件示例：

<?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" />
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
    </startup>

    <!-- 这里是 手动加上的 -->
    <connectionStrings>
        <!-- mysql5.7要显示指定charset，和数据库保持一致，否则提交中文到库里面会乱码 -->
        <add name="EfTestDbContext" connectionString="Data Source=128.0.14.43; port=3306;Database=db3;uid=jojo;pwd=Gameking2008*;charset=utf8mb4;" providerName="MySql.Data.MySqlClient"/>
    </connectionStrings>

    <entityFramework>
        <providers>
            <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
            <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework, Version=8.0.30.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
            </provider>
        </providers>
    </entityFramework>

    <!-- 这里是 手动加上的 -->
    <system.data>
        <DbProviderFactories>
            <remove invariant="MySql.Data.MySqlClient"/>
            <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.30.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
        </DbProviderFactories>
    </system.data>

    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Runtime.CompilerServices.Unsafe" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
                <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="System.Buffers" publicKeyToken="cc7b13ffcd2ddd51" culture="neutral" />
                <bindingRedirect oldVersion="0.0.0.0-4.0.3.0" newVersion="4.0.3.0" />
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

 

5.使用代码：

using System;
using System.Linq;
using System.Windows.Forms;
using WinFormEfMysql8.db;

namespace WinFormEfMysql8
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                sys_user u = new sys_user();
                u.user_name = "test1" + DateTime.Now.ToString("HHmmss");
                u.create_time = DateTime.Now;
                u.update_time = DateTime.Now;
                using (var db = new EfTestDbContext())
                {
                    db.sys_user.Add(u);
                    db.SaveChanges();
                }
                MessageBox.Show("添加成功！");
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                if(ex.InnerException!=null)
                    msg +=" INNER EX:" +ex.InnerException.Message;
                MessageBox.Show(msg);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                using (var db = new EfTestDbContext())
                {
                    var uu = db.sys_user.FirstOrDefault();
                    if (uu == null)
                        MessageBox.Show("表里无数据");
                    else
                        MessageBox.Show("uu.user_name:" + (uu.user_name ?? ""));
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                using (var db = new EfTestDbContext())
                {
                    var uu = db.sys_user.FirstOrDefault();
                    if (uu == null)
                        MessageBox.Show("表里无数据");
                    else
                    { 
                        uu.real_name = "真名：" + DateTime.Now.ToString("HHmmss");                        
                        db.SaveChanges();
                        MessageBox.Show("修改名字成功。");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

 

--

在DbContext中要注意：modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();      否则实体类名和表名无法映射。