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
3 源代码 https://github.com/chxl800/EFMysqlMvcIoc
1 项目层次结构
2 Common层 基础层存放基础，公共的方法， Model层 存放实体类的地方。
3 DAL层 操作数据库整合
3.1 整合MySQL + EF 手动版
DBEntities.cs 数据库上下
using System.Data.Entity; using System.Data.Entity.ModelConfiguration.Conventions; using Model; namespace DAL.Base { public class DBEntities : DbContext { public DBEntities() : base("name=DBEntities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { //解决EF动态建库数据库表名变为复数问题 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); } public virtual DbSet<User> User { get; set; } } }
DBContextFactory.cs 数据库上下工厂 实例在线程内部是唯一的
using System.Data.Entity; using System.Runtime.Remoting.Messaging; namespace DAL.Base { 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; } } }
IBaseDAL.cs BaseDAL.cs EF CRUD 增删改查 基础方法
using System; using System.Collections.Generic; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using System.Linq.Expressions; namespace DAL.Base { public class BaseDAL<TEntity> : IBaseDAL<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 } }
4 UserDAL 用类操作 继承 BaseDAL.cs 就拥有增删改查 基础方法
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using DAL.Base; using Model; namespace DAL { public interface IUserDAL : IBaseDAL<User> { } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using DAL.Base; using Model; namespace DAL.Impl { public class UserDAL : BaseDAL<User>, IUserDAL { } }
5 UserService 类调用
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Model; namespace Service { public interface IUserService { /// <summary> /// 查询全部用户 /// </summary> /// <returns></returns> List<User> GetUsers(); } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using DAL; using Model; namespace Service.Impl { public class UserService : IUserService { private IUserDAL userDAL; public UserService(IUserDAL userDAL) { this.userDAL = userDAL; } /// <summary> /// 查询全部用户 /// </summary> /// <returns></returns> public List<User> GetUsers() { List<User> users= userDAL.QueryWhere(s => true); return users; } } }
6 UI 整合
6.1 Web.config 增加代码 支持MySQL 和 数据库链接字符串，
<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>
6.2 AutoFacConfig.cs ， 各个层 UI，Service , DAL层 ， IOC ，构造方法 接口参数 注入
using System; using System.Collections.Generic; using System.Linq; using System.Reflection; using System.Web; using System.Web.Mvc; using Autofac; using Autofac.Integration.Mvc; namespace UI.App_Start { public class AutoFacConfig { /// <summary> /// 负责调用autofac框架实现业务逻辑层和数据仓储层程序集中的类型对象的创建 /// 负责创建MVC控制器类的对象(调用控制器中的有参构造函数),接管DefaultControllerFactory的工作 /// </summary> public static void Register() { //实例化一个autofac的创建容器 var builder = new ContainerBuilder(); //告诉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(new AutofacDependencyResolver(container)); } } }
6.3 Global.asax
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using System.Web.Optimization; using System.Web.Routing; using UI.App_Start; namespace UI { public class MvcApplication : System.Web.HttpApplication { protected void Application_Start() { AreaRegistration.RegisterAllAreas(); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); AutoFacConfig.Register(); } } }
6.4 新增类MyJsonResult.cs， Json 方法的重写 返回JsonResult
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using Newtonsoft.Json; using Newtonsoft.Json.Serialization; namespace UI.Common { public class MyJsonResult : JsonResult { public override void ExecuteResult(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 = new JsonSerializerSettings(); //设置序列化时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
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using UI.Common; namespace UI.Controllers { public class BaseController : Controller { protected override JsonResult Json(object data, string contentType, System.Text.Encoding contentEncoding, JsonRequestBehavior behavior) { return new MyJsonResult { Data = data, ContentEncoding = contentEncoding, ContentType = contentType, JsonRequestBehavior = behavior, }; } } }
6.6 HomeController.cs
using System.Collections.Generic; using System.Web.Mvc; using Model; using Service; namespace UI.Controllers { public class HomeController : BaseController { private IUserService userService; public HomeController(IUserService userService) { this.userService = userService; } /// <summary> /// 查询全部用户 /// </summary> /// <returns></returns> public ActionResult All() { List<User> users = userService.GetUsers(); return Json(users, JsonRequestBehavior.AllowGet); } /// <summary> /// 新增 /// </summary> /// <returns></returns> public ActionResult Add(User user) { return Json(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
using System; namespace 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; } } }
