c ef mysql_Vs2017 FrameWork EF Mysql Mvc 三层整合1

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

三层 DAL，Service，UI 都要引用； UI 层 增加其他引用  Autofac.Mvc5 4.0.1  Autofac 4.0.1

1 项目层次结构

2 Common层   基础层存放基础，公共的方法， Model层 存放实体类的地方。

3 DAL层   操作数据库整合

3.1  整合MySQL + EF  手动版

DBEntities.cs  数据库上下

usingSystem.Data.Entity;usingSystem.Data.Entity.ModelConfiguration.Conventions;usingModel;namespaceDAL.Base

{public classDBEntities : DbContext

{publicDBEntities()

:base("name=DBEntities")

{

}protected override voidOnModelCreating(DbModelBuilder modelBuilder)

{//解决EF动态建库数据库表名变为复数问题

modelBuilder.Conventions.Remove();

}public virtual DbSet User { get; set; }

}

}

DBContextFactory.cs  数据库上下工厂  实例在线程内部是唯一的

usingSystem.Data.Entity;usingSystem.Runtime.Remoting.Messaging;namespaceDAL.Base

{public classDBContextFactory

{///

///帮我们返回当前线程内的数据库上下文，如果当前线程内没有上下文，那么创建一个上下文，并保证///上线问实例在线程内部是唯一的///

///

public staticDbContext GetDbContext()

{

DbContext dbContext= CallContext.GetData(typeof(DBContextFactory).Name) asDbContext;if (dbContext == null)

{

dbContext= newDBEntities();

CallContext.SetData(typeof(DBContextFactory).Name, dbContext);

}returndbContext;

}

}

}

IBaseDAL.cs  BaseDAL.cs EF CRUD  增删改查 基础方法

usingSystem;usingSystem.Collections.Generic;usingSystem.Data.Entity;usingSystem.Data.Entity.Infrastructure;usingSystem.Linq;usingSystem.Linq.Expressions;namespaceDAL.Base

{public class BaseDAL : IBaseDAL where TEntity : class{//1.0 实例化EF上下文

DbContext db =DBContextFactory.GetDbContext();//2.0 定义DbSet 对象

public DbSet_dbset;//3.0 在构造函数的初始化_dbset

publicBaseDAL()

{

_dbset= db.Set();

}#region 1.0 增

public virtual voidAdd(TEntity model)

{//1.0 参数合法性验证

if (model == null)

{throw new Exception("BaseRepository泛型类中，新增操作的实体不能为空");

}//2.0 进行新增操作

_dbset.Add(model);

}#endregion

#region 2.0 删

public virtual voidDelete(TEntity model)

{//1.0 参数合法性验证

if (model == null)

{throw new Exception("BaseRepository泛型类中，删除操作的实体不能为空");

}

_dbset.Attach(model);

_dbset.Remove(model);

}#endregion

#region 3.0 改

///

///编辑，约定model 是一个自定义的实体，没有追加到EF容器中的///

///

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 inpropertyNames)

{

entry.Property(item).IsModified= true;

}

}#endregion

#region 4.0 查

///

///带条件查询///

///

///

public virtual List QueryWhere(Expression> where)

{return _dbset.Where(where).ToList();

}#endregion

#region 5.0 统一保存

///

///统一将EF容器对象中的所有代理类生成相应的sql语句发给db服务器执行///

///

public virtual intSaveChanges()

{try{returndb.SaveChanges();

}catch(Exception ex)

{throwex;

}

}#endregion}

}

4 UserDAL 用类操作  继承 BaseDAL.cs     就拥有增删改查 基础方法

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingDAL.Base;usingModel;namespaceDAL

{public interface IUserDAL : IBaseDAL{

}

}usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingDAL.Base;usingModel;namespaceDAL.Impl

{public class UserDAL : BaseDAL, IUserDAL

{

}

}

5  UserService 类调用

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingModel;namespaceService

{public interfaceIUserService

{///

///查询全部用户///

///

ListGetUsers();

}

}usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingDAL;usingModel;namespaceService.Impl

{public classUserService : IUserService

{private IUserDAL userDAL;

public UserService(IUserDAL userDAL)

{

this.userDAL = userDAL;

}///

///查询全部用户///

///

public ListGetUsers()

{

List users= userDAL.QueryWhere(s => true);returnusers;

}

}

}

6 UI 整合

6.1 Web.config 增加代码   支持MySQL 和 数据库链接字符串，

6.2 AutoFacConfig.cs   ，  各个层 UI，Service , DAL层 ，  IOC ，构造方法 接口参数 注入

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Reflection;usingSystem.Web;usingSystem.Web.Mvc;usingAutofac;usingAutofac.Integration.Mvc;namespaceUI.App_Start

{public classAutoFacConfig

{///

///负责调用autofac框架实现业务逻辑层和数据仓储层程序集中的类型对象的创建///负责创建MVC控制器类的对象(调用控制器中的有参构造函数),接管DefaultControllerFactory的工作///

public static voidRegister()

{//实例化一个autofac的创建容器

var builder = newContainerBuilder();//告诉Autofac框架，将来要创建的控制器类存放在哪个程序集

Assembly controllerAss = Assembly.Load("UI");

builder.RegisterControllers(controllerAss);//告诉autofac框架注册数据仓储层所在程序集中的所有类的对象实例

Assembly respAss = Assembly.Load("DAL");//创建respAss中的所有类的instance以此类的实现接口存储

builder.RegisterTypes(respAss.GetTypes()).AsImplementedInterfaces();//告诉autofac框架注册业务服务层所在程序集中的所有类的对象实例

Assembly serviceAss = Assembly.Load("Service");//创建respAss中的所有类的instance以此类的实现接口存储

builder.RegisterTypes(serviceAss.GetTypes()).AsImplementedInterfaces();//创建一个Autofac的容器

var container =builder.Build();//将MVC的控制器对象实例 交由autofac来创建

DependencyResolver.SetResolver(newAutofacDependencyResolver(container));

}

}

}

6.3 Global.asax

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.Mvc;usingSystem.Web.Optimization;usingSystem.Web.Routing;usingUI.App_Start;namespaceUI

{public classMvcApplication : System.Web.HttpApplication

{protected voidApplication_Start()

{

AreaRegistration.RegisterAllAreas();

FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);

RouteConfig.RegisterRoutes(RouteTable.Routes);

BundleConfig.RegisterBundles(BundleTable.Bundles);

AutoFacConfig.Register();

}

}

}

6.4 新增类MyJsonResult.cs，   Json  方法的重写   返回JsonResult

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.Mvc;usingNewtonsoft.Json;usingNewtonsoft.Json.Serialization;namespaceUI.Common

{public classMyJsonResult : JsonResult

{public override voidExecuteResult(ControllerContext context)

{if (context == null)

{throw new ArgumentNullException("context");

}if (this.JsonRequestBehavior == JsonRequestBehavior.DenyGet && string.Equals(context.HttpContext.Request.HttpMethod, "GET", StringComparison.OrdinalIgnoreCase))

{throw new InvalidOperationException("JsonRequest_GetNotAllowed");

}

HttpResponseBase response=context.HttpContext.Response;if (!string.IsNullOrEmpty(this.ContentType))

{

response.ContentType= this.ContentType;

}else{

response.ContentType= "application/json";

}if (this.ContentEncoding != null)

{

response.ContentEncoding= this.ContentEncoding;

}if (this.Data != null)

{

JsonSerializerSettings settings= newJsonSerializerSettings();//设置序列化时key为驼峰样式,开头字母小写输出 controller调用Josn(对象)

settings.ContractResolver = new CamelCasePropertyNamesContractResolver();//原样输出//options.SerializerSettings.ContractResolver = new DefaultContractResolver();//时间格式

settings.DateFormatString = "yyyy-MM-dd HH:mm:ss";

response.Write(JsonConvert.SerializeObject(this.Data, settings));

}

}

}

}

6.5 新增 BaseController.cs 基类， Json  方法的重写   返回JsonResult

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Web;usingSystem.Web.Mvc;usingUI.Common;namespaceUI.Controllers

{public classBaseController : Controller

{protected override JsonResult Json(object data, stringcontentType, System.Text.Encoding contentEncoding, JsonRequestBehavior behavior)

{return newMyJsonResult

{

Data=data,

ContentEncoding=contentEncoding,

ContentType=contentType,

JsonRequestBehavior=behavior,

};

}

}

}

6.6 HomeController.cs

usingSystem.Collections.Generic;usingSystem.Web.Mvc;usingModel;usingService;namespaceUI.Controllers

{public classHomeController : BaseController

{privateIUserService userService;publicHomeController(IUserService userService)

{this.userService =userService;

}///

///查询全部用户///

///

publicActionResult All()

{

List users =userService.GetUsers();returnJson(users, JsonRequestBehavior.AllowGet);

}///

///新增///

///

publicActionResult Add(User user)

{returnJson(user, JsonRequestBehavior.AllowGet);

}

}

}

7  运行结果

user.sql 脚本

CREATE TABLE `user` (

`Id`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户Id',

`TenantId`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租户ID',

`UserName`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名，根据用户名姓获取拼音',

`RealName`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户姓名',

`UserCode`varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户编号，可以作为登录',

`Password`varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',

`Salt`varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '盐值，随机Guid',

`Mobile`char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',

`Email`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',

`UserType`int(11) NOT NULL DEFAULT 0 COMMENT '用户类型，0普通用户，1超管',

`Status`int(11) NOT NULL DEFAULT 0 COMMENT '用户状态，0正常，1已删除，2禁用',

`Creator`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',

`CreateTime`datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',

`Reviser`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',

`ReviseTime`datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',

`LoginTime`datetime(0) NULL DEFAULT NULL COMMENT '最新登录时间',

`IP`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '最新登录IP',

`LastLoginTime`datetime(0) NULL DEFAULT NULL COMMENT '上次登录时间',

`LastIP`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上次登录IP',PRIMARY KEY(`Id`) USING BTREE,UNIQUE INDEX`UserCode`(`UserCode`) USING BTREE,UNIQUE INDEX`Mobile`(`Mobile`) USING BTREE,INDEX`TenantId`(`TenantId`) USING BTREE

) ENGINE= InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Compact;

User.cs

usingSystem;namespaceModel

{///

///账号表///

public classUser

{///

///

///

public string Id { get; set; }///

///

///

public string TenantId { get; set; }///

///

///

public string UserName { get; set; }///

///

///

public string RealName { get; set; }///

///

///

public string UserCode { get; set; }///

///

///

public string Password { get; set; }///

///

///

public string Salt { get; set; }///

///

///

public string Mobile { get; set; }///

///

///

public string Email { get; set; }///

///

///

public int UserType { get; set; }///

///

///

public int Status { get; set; }///

///

///

public string Creator { get; set; }///

///

///

public DateTime CreateTime { get; set; }///

///

///

public string Reviser { get; set; }///

///

///

public DateTime ReviseTime { get; set; }///

///

///

public DateTime? LoginTime { get; set; }///

///

///

public string IP { get; set; }///

///

///

public DateTime? LastLoginTime { get; set; }///

///

///

public string LastIP { get; set; }

}

}