1 运行环境 vs2017 Net FromWork 4.6.2 手动版 没有 ado.net 实体数据模型
2 NuGet MySql.Data.Entity 6.10.9, MySql.Data 6.10.9,EntityFramework 6.2.0,Json.Net 1.0.16
1 项目创建流程视图
2 App.config 配置 需要手动添加标红了 注意 MySql Data版本号
<?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.2" />
</startup>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="mssqllocaldb" />
</parameters>
</defaultConnectionFactory>
<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.Entity.EF6, Version=6.10.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
</provider></providers>
</entityFramework>
<connectionStrings>
<add name="DBEntities" connectionString="server=localhost;port=3306;uid=root;pwd=root;database=demodb;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
<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=6.10.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
</configuration>
3 DBEntities.cs 数据库上下文
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration.Conventions;
using EFMysqlFrameWork.Model;
namespace EFMysqlFrameWork
{
public class DBEntities : DbContext
{
public DBEntities()
: base("name=DBEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//解决EF动态建库数据库表名变为复数问题
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
public DbSet<User> User { get; set; }
}
}
4 DBContextFactory.cs 增加工厂模式
using System.Data.Entity;
using System.Runtime.Remoting.Messaging;
namespace EFMysqlFrameWork
{
public class DBContextFactory
{
/// <summary>
/// 帮我们返回当前线程内的数据库上下文,如果当前线程内没有上下文,那么创建一个上下文,并保证
/// 上线问实例在线程内部是唯一的
/// </summary>
/// <returns></returns>
public static DbContext GetDbContext()
{
DbContext dbContext = CallContext.GetData(typeof(DBContextFactory).Name) as DbContext;
if (dbContext == null)
{
dbContext = new DBEntities();
CallContext.SetData(typeof(DBContextFactory).Name, dbContext);
}
return dbContext;
}
}
}
5 BaseDAL.cs 操作数据库CRUD 基类
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
namespace EFMysqlFrameWork
{
public class BaseDAL<TEntity> where TEntity : class
{
//1.0 实例化EF上下文
DbContext db = DBContextFactory.GetDbContext();
//2.0 定义DbSet<T> 对象
public DbSet<TEntity> _dbset;
//3.0 在构造函数的初始化_dbset
public BaseDAL()
{
_dbset = db.Set<TEntity>();
}
#region 1.0 增
public virtual void Add(TEntity model)
{
//1.0 参数合法性验证
if (model == null)
{
throw new Exception("BaseRepository泛型类中,新增操作的实体不能为空");
}
//2.0 进行新增操作
_dbset.Add(model);
}
#endregion
#region 2.0 删
public virtual void Delete(TEntity model)
{
//1.0 参数合法性验证
if (model == null)
{
throw new Exception("BaseRepository泛型类中,删除操作的实体不能为空");
}
_dbset.Attach(model);
_dbset.Remove(model);
}
#endregion
#region 3.0 改
/// <summary>
/// 编辑,约定model 是一个自定义的实体,没有追加到EF容器中的
/// </summary>
/// <param name="model"></param>
public virtual void Edit(TEntity model, string[] propertyNames)
{
//0.0 关闭EF的实体属性合法性检查
db.Configuration.ValidateOnSaveEnabled = false;
//1.0 参数合法性验证
if (model == null)
{
throw new Exception("BaseRepository泛型类中,编辑操作的实体不能为空");
}
if (propertyNames == null || propertyNames.Length == 0)
{
throw new Exception("BaseRepository泛型类中,编辑操作的属性数组必须至少有一个值");
}
//2.0 将model追加到EF容器中的
DbEntityEntry entry = db.Entry(model);
entry.State = EntityState.Unchanged;
foreach (var item in propertyNames)
{
entry.Property(item).IsModified = true;
}
}
#endregion
#region 4.0 查
/// <summary>
/// 带条件查询
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual List<TEntity> QueryWhere(Expression<Func<TEntity, bool>> where)
{
return _dbset.Where(where).ToList();
}
#endregion
#region 5.0 统一保存
/// <summary>
/// 统一将EF容器对象中的所有代理类生成相应的sql语句发给db服务器执行
/// </summary>
/// <returns></returns>
public virtual int SaveChanges()
{
try
{
return db.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
}
6 Program.cs 测试类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EFMysqlFrameWork.Common;
using EFMysqlFrameWork.Model;
using Json.Net;
namespace EFMysqlFrameWork
{
class Program
{
static void Main(string[] args)
{
BaseDAL<User> dal = new BaseDAL<User>();
User user = new User();
user.Id = Guid.NewGuid().ToString().Replace("-","");
dal.Add(user);
dal.SaveChanges();
List<User> list = dal.QueryWhere(s => true);
//json转化
var userJson = JsonNet.Serialize(list, JsonHelper.dateConverter);
//var userList = JsonNet.Deserialize<List<User>>(userJson, JsonHelper.dateConverter);
Console.WriteLine(userJson);
Console.Read();
}
}
}
7 运行结果
8 User.cs
using System;
namespace EFMysqlFrameWork.Model
{
/// <summary>
/// 账号表
/// </summary>
public class User
{
/// <summary>
///
/// </summary>
public string Id { get; set; }
/// <summary>
///
/// </summary>
public string TenantId { get; set; }
/// <summary>
///
/// </summary>
public string UserName { get; set; }
/// <summary>
///
/// </summary>
public string RealName { get; set; }
/// <summary>
///
/// </summary>
public string UserCode { get; set; }
/// <summary>
///
/// </summary>
public string Password { get; set; }
/// <summary>
///
/// </summary>
public string Salt { get; set; }
/// <summary>
///
/// </summary>
public string Mobile { get; set; }
/// <summary>
///
/// </summary>
public string Email { get; set; }
/// <summary>
///
/// </summary>
public int UserType { get; set; }
/// <summary>
///
/// </summary>
public int Status { get; set; }
/// <summary>
///
/// </summary>
public string Creator { get; set; }
/// <summary>
///
/// </summary>
public DateTime CreateTime { get; set; }
/// <summary>
///
/// </summary>
public string Reviser { get; set; }
/// <summary>
///
/// </summary>
public DateTime ReviseTime { get; set; }
/// <summary>
///
/// </summary>
public DateTime? LoginTime { get; set; }
/// <summary>
///
/// </summary>
public string IP { get; set; }
/// <summary>
///
/// </summary>
public DateTime? LastLoginTime { get; set; }
/// <summary>
///
/// </summary>
public string LastIP { get; set; }
}
}
9 JsonHelper.cs
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Text;
using Json.Net;
namespace EFMysqlFrameWork.Common
{
public static partial class JsonHelper
{
public static JsonConverter<DateTime> dateConverter = new JsonConverter<DateTime>(
dt => dt.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture),
s => DateTime.ParseExact(s, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture));
}
}