一、开发环境
VS2017、SQLServer数据库
二、CodeFirst实现
CodeFirst的实现,主要是通过EntityTypeConfiguration 配置映射关系来实现的
1、创建一个WebApi接口项目,名称命名为APIDemo
2、解决方案中添加APIModel、APIDAL、APIBLL三个类库
最终的项目结构为
3、在APIModel中添加BaseModel.cs和AccountInfo.cs两个类,其中BaseModel为基础实体类,AccountInfo为用户登录信息的实体类,代码如下
(1)BaseModel实体类代码
using System;
using System.ComponentModel.DataAnnotations;
namespace APIModel
{
/// <summary>
/// 基础类
/// </summary>
public abstract class BaseModel
{
/// <summary>
/// 主键ID
/// </summary>
[Key]
public Guid ID { get; set; } =Guid.NewGuid();
/// <summary>
/// 创建人
/// </summary>
[Required(AllowEmptyStrings =true),StringLength(50)]
public string CreatedBy { get; set; }
/// <summary>
/// 创建时间
/// </summary>
public DateTime CreateTime { get; set; } = DateTime.Now;
/// <summary>
/// 修改人
/// </summary>
[StringLength(50)]
public string ModifyBy { get; set; }
/// <summary>
/// 修改时间
/// </summary>
public DateTime ?ModifyTime { get; set; }
/// <summary>
/// 是否删除
/// </summary>
public bool IsDelete { get; set; } = false;
}
}
(2)AccountInfo实体类代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace APIModel
{
/// <summary>
/// 登录账户信息
/// </summary>
public class AccountInfo:BaseModel
{
/// <summary>
/// 登录账号
/// </summary>
public string LoginID { get; set; }
/// <summary>
/// 登录密码
/// </summary>
public string PassWord { get; set; }
/// <summary>
/// 用户姓名
/// </summary>
public string UserTrueName { get; set; }
/// <summary>
/// 手机号码
/// </summary>
public string MobileNumber { get; set; }
/// <summary>
/// 所属部门
/// </summary>
public string DepartmentName { get; set; }
/// <summary>
/// 用户角色
/// </summary>
public string RoleName { get; set; }
}
}
4、APIDAL项目从NuGet中搜索并添加EntityFramework,然后添加对APIModel项目的引用,以上两个操作完成后,再依次创建APIDbContext.cs、APIDbSeed.cs、BaseModelConfig.cs和AccountInfoConfig.cs四个类文件,代码如下
(1)APIDbContext代码
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using APIModel;
namespace APIDAL
{
/// <summary>
/// 数据库上下文
/// </summary>
public class APIDbContext : DbContext
{
public APIDbContext():base("name=MyConnection")
{
Database.SetInitializer(new APIDbSeed());
//Database.SetInitializer<APIDbContext>(null);//从不创建数据库
//Database.SetInitializer<APIDbContext>(new CreateDatabaseIfNotExists<APIDbContext>());//数据库不存在时创建数据库
//Database.SetInitializer<APIDbContext>(new DropCreateDatabaseAlways<APIDbContext>());//每次启动时创建数据库
//Database.SetInitializer<APIDbContext>(new DropCreateDatabaseIfModelChanges<APIDbContext>());//模型更改时创建数据库
}
#region DBSet
public virtual DbSet<AccountInfo> AccountInfo { get; set; }
#endregion
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingEntitySetNameConvention>();
//modelBuilder.Configurations.Add(new AccountInfoConfig());//使用单个文件生成数据表
//使用加载数据集的方式生成数据表
try
{
var path = AppDomain.CurrentDomain.BaseDirectory;
var files = Directory.GetFiles(path, "*DAL.dll");
if (files.Length == 0)
{
path = Path.Combine(path, "bin");
files = Directory.GetFiles(path, "*DAL.dll");
}
foreach (var file in files)
{
modelBuilder.Configurations.AddFromAssembly(Assembly.LoadFile(file));
}
}
catch
{
var path = AppDomain.CurrentDomain.BaseDirectory;
var files = Directory.GetFiles(path, "*DAL.dll");
if (files.Length == 0)
{
path = Path.Combine(path, "bin");
files = Directory.GetFiles(path, "*DAL.dll");
}
foreach (var file in files)
{
modelBuilder.Configurations.AddFromAssembly(Assembly.LoadFile(file));
}
}
}
}
}
注:base("name=MyConnection")设置的为数据库连接,此时,需要在webconfig配置文件中添加数据库连接配置,如下
<!--数据库连接-->
<connectionStrings>
<add name="MyConnection" connectionString="Server=.;Database=APIDemo;uid=sa;pwd=123456" providerName="System.Data.SqlClient" />
</connectionStrings>
(2) APIDbSeed代码
using System;
using System.Data.Entity;
namespace APIDAL
{
public class APIDbSeed:DropCreateDatabaseIfModelChanges<APIDbContext>
{
protected override void Seed(APIDbContext context)
{
base.Seed(context);
}
}
}
(3)BaseModelConfig代码
using System;
using System.Data.Entity.ModelConfiguration;
using APIModel;
namespace APIDAL
{
/// <summary>
/// EntityTypeConfiguration 配置基础实体类映射关系
/// </summary>
public class BaseModelConfig
{
public static void SetBaseEntityConfig<T>(EntityTypeConfiguration<T> baseEntity)
where T :BaseModel
{
baseEntity.HasKey(x => x.ID);
baseEntity.Property(x => x.CreatedBy).HasMaxLength(50).IsRequired();
baseEntity.Property(x => x.CreateTime).HasColumnType("datetime");
baseEntity.Property(x => x.ModifyBy).HasMaxLength(50);
baseEntity.Property(x => x.ModifyTime).HasColumnType("datetime");
baseEntity.Property(x => x.IsDelete);
}
}
}
(4)AccountInfoConfig代码
using System.Data.Entity.ModelConfiguration;
using APIModel;
namespace APIDAL
{
/// <summary>
/// EntityTypeConfiguration 配置账户信息映射关系
/// </summary>
public class AccountInfoConfig:EntityTypeConfiguration<AccountInfo>
{
public AccountInfoConfig()
{
BaseModelConfig.SetBaseEntityConfig(this);
Property(x=>x.LoginID).HasMaxLength(50);
Property(x => x.PassWord).HasMaxLength(50);
Property(x => x.UserTrueName).HasMaxLength(50);
Property(x => x.MobileNumber).HasMaxLength(50);
Property(x => x.DepartmentName).HasMaxLength(50);
Property(x => x.RoleName).HasMaxLength(50);
}
}
}
5、APIBLL项目从NuGet中搜索并添加EntityFramework,然后添加对APIModel和APIDAL项目的引用,以上两个操作完成后,添加BaseModelAdapter.cs和AccountInfoAdapter.cs类文件,代码如下
(1)BaseModelAdapter代码
using System;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using APIModel;
using APIDAL;
namespace APIBLL
{
/// <summary>
/// 基本BLL
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseModelAdapter<T>where T:BaseModel
{
protected APIDbContext apiDbContext;
/// <summary>
/// 新增数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Add(T entity)
{
try
{
apiDbContext.Entry<T>(entity).State = EntityState.Added;
int rowsAffected=apiDbContext.SaveChanges();
return (rowsAffected>0)?true:false;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Update(T entity)
{
try
{
apiDbContext.Set<T>().Attach(entity);
apiDbContext.Entry<T>(entity).State = EntityState.Modified;
int rowsAffected = apiDbContext.SaveChanges();
return (rowsAffected > 0) ? true : false;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool Delete(Guid id,bool IsDelete=false)
{
var model = GetByID(id);
if (model != null)
{
try
{
apiDbContext.Set<T>().Attach(model);
if (IsDelete)
{
apiDbContext.Entry<T>(model).State = EntityState.Deleted;
}
else
{
model.IsDelete = true;
apiDbContext.Entry<T>(model).State = EntityState.Modified;
}
int rowsAffected = apiDbContext.SaveChanges();
return (rowsAffected > 0) ? true : false;
}
catch (Exception ex)
{
return false;
}
}
else
{
return false;
}
}
/// <summary>
/// 根据ID获取数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T GetByID(Guid id)
{
try
{
Expression<Func<T, bool>> whereLambda = x => x.ID.Equals(id);
var modelList = apiDbContext.Set<T>().Where<T>(whereLambda).AsQueryable().ToList();
return (modelList.Any()) ? modelList.FirstOrDefault() : null;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="whereLambda"></param>
/// <returns></returns>
public List<T> GetListByWhere(Expression<Func<T, bool>> whereLambda)
{
try
{
var list=apiDbContext.Set<T>().Where<T>(whereLambda).OrderByDescending(x => x.CreateTime).AsQueryable().ToList();
return list;
}
catch (Exception ex)
{
return null;
}
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="whereLambda"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<T> GetPageListByWhere(Expression<Func<T, bool>> whereLambda,int pageIndex,int pageSize,out int totalCount)
{
try
{
int skipCount = (pageIndex - 1) * pageSize;
totalCount = apiDbContext.Set<T>().Where<T>(whereLambda).Count();
var list = apiDbContext.Set<T>().Where<T>(whereLambda).OrderByDescending(x=>x.CreateTime).Skip(skipCount).Take(pageSize).AsQueryable().ToList();
return list;
}
catch (Exception ex)
{
totalCount = 0;
return null;
}
}
}
}
(2)AccountInfoAdapter代码
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using APIModel;
using APIDAL;
namespace APIBLL
{
public class AccountInfoAdapter:BaseModelAdapter<AccountInfo>
{
public AccountInfoAdapter()
{
apiDbContext = new APIDbContext();
}
public AccountInfoAdapter(APIDbContext aPIDbContext)
{
apiDbContext = aPIDbContext;
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool Save(AccountInfo item)
{
var model = GetByID(item.ID);
if (model == null)
{
return Add(item);
}
else
{
return Update(item);
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public List<AccountInfo> GetListByWhere(Hashtable parameters)
{
Expression<Func<AccountInfo, bool>> whereSearch = x => x.IsDelete == false;
if (parameters != null)
{
if (parameters["UserTrueName"] != null && !string.IsNullOrWhiteSpace(parameters["UserTrueName"].ToString()))
{
}
}
return GetListByWhere(whereSearch);
}
/// <summary>
/// 获取分页数据列表
/// </summary>
/// <param name="parameters"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<AccountInfo> GetPageListByWhere(Hashtable parameters, int pageIndex, int pageSize, out int totalCount)
{
Expression<Func<AccountInfo, bool>> whereSearch = x => x.IsDelete == false;
if (parameters != null)
{
if (parameters["UserTrueName"] != null && !string.IsNullOrWhiteSpace(parameters["UserTrueName"].ToString()))
{
}
}
return GetPageListByWhere(whereSearch,pageIndex,pageSize, out totalCount);
}
}
}
6、APIDemo项目从NuGet中搜索并添加EntityFramework,然后添加对APIModel、APIDAL、APIBLL项目的引用,完成以上操作后,在Models文件夹下添加ApiResult.cs和AccountRequest.cs文件,在Controllers文件夹下添加AccountController.cs文件,代码如下
(1)ApiResult代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace APIDemo.Models
{
/// <summary>
/// api返回类
/// </summary>
/// <typeparam name="T"></typeparam>
public class ApiResult<T>
{
/// <summary>
/// 返回数据
/// </summary>
public T data { get; set; }
/// <summary>
/// 数据记录总条数 分页查询时获取
/// </summary>
public int totalcount { get; set; }
/// <summary>
/// 返回结果(true;false)
/// </summary>
public bool success { get; set; }
/// <summary>
/// 结果代码
/// </summary>
public int code { get; set; }
/// <summary>
/// 消息代码
/// </summary>
public string msgcode { get; set; }
/// <summary>
/// 消息
/// </summary>
public string message { get; set; }
public ApiResult()
{
success = true;
code = 200;
}
}
/// <summary>
/// api返回类
/// </summary>
/// <typeparam name="T"></typeparam>
public class ApiResult
{
/// <summary>
/// 返回结果(true;false)
/// </summary>
public bool success { get; set; }
/// <summary>
/// 结果代码
/// </summary>
public int code { get; set; }
/// <summary>
/// 消息代码
/// </summary>
public string msgcode { get; set; }
/// <summary>
/// 消息
/// </summary>
public string message { get; set; }
public ApiResult()
{
success = true;
code = 200;
}
}
}
(2) AccountRequest代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace APIDemo.Models
{
public class AccountRequest
{
}
/// <summary>
/// 保存参数
/// </summary>
public class AccountSaveRequest
{
/// <summary>
/// 主键ID
/// </summary>
public Guid ID { get; set; }
/// <summary>
/// 登录账号
/// </summary>
public string LoginID { get; set; }
/// <summary>
/// 登录密码
/// </summary>
public string PassWord { get; set; }
/// <summary>
/// 用户姓名
/// </summary>
public string UserTrueName { get; set; }
/// <summary>
/// 手机号码
/// </summary>
public string MobileNumber { get; set; }
/// <summary>
/// 所属部门
/// </summary>
public string DepartmentName { get; set; }
/// <summary>
/// 用户角色
/// </summary>
public string RoleName { get; set; }
}
}
(3)AccountController接口代码
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using APIDemo.Models;
using APIModel;
using APIBLL;
namespace APIDemo.Controllers
{
/// <summary>
/// 登录账户信息
/// </summary>
[RoutePrefix("api/Account")]
public class AccountController : ApiController
{
private readonly AccountInfoAdapter accountInfoAdapter;
public AccountController()
{
accountInfoAdapter = new AccountInfoAdapter();
}
/// <summary>
/// 保存用户信息
/// </summary>
/// <param name="accountSaveRequest"></param>
/// <returns></returns>
[HttpPost,Route("SaveAccount")]
public ApiResult SaveAccount([FromBody] AccountSaveRequest accountSaveRequest)
{
ApiResult apiResult = new ApiResult();
if (accountSaveRequest != null)
{
var model = accountInfoAdapter.GetByID(accountSaveRequest.ID);
if (model == null)
{
model = new AccountInfo();
model.CreatedBy = "";
}
else
{
model.ModifyTime = DateTime.Now;
}
model.LoginID = accountSaveRequest.LoginID;
model.PassWord = accountSaveRequest.PassWord;
model.UserTrueName = accountSaveRequest.UserTrueName;
model.MobileNumber = accountSaveRequest.MobileNumber;
model.DepartmentName = accountSaveRequest.DepartmentName;
model.RoleName = accountSaveRequest.RoleName;
if (accountInfoAdapter.Save(model))
{
apiResult.success = true;
apiResult.message = "保存成功";
}
}
else
{
apiResult.success = false;
apiResult.message = "参数错误";
}
return apiResult;
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <returns></returns>
[HttpGet,Route("GetAccountList")]
public ApiResult<List<AccountInfo>> GetAccountList()
{
ApiResult<List<AccountInfo>> apiResult = new ApiResult<List<AccountInfo>>();
var list = accountInfoAdapter.GetListByWhere(new Hashtable());
apiResult.data = list;
return apiResult;
}
}
}
以上六步完成后,启动项目,使用PostMan调用接口,并保存数据,最终的数据结果为