一、环境准备
VS2017 、.net core 2.1
二、功能实现
1、创建.net core 项目
2、项目中创建数据model实体类库(.net core 类库),创建完成后,从Nuget搜索安装Npgsql.EntityFrameworkCore.PostgreSQL
3、创建数据库上下文 Dbcontext
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Bosch.Rtns.Repository.Domain;
namespace Bosch.Rtns.Repository
{
public class BoschRtnsDBContext:DbContext
{
public BoschRtnsDBContext(DbContextOptions<BoschRtnsDBContext> options) : base(options)
{
}
#region dbSet
public virtual DbSet<OnlineUsers> OnlineUsers { get; set; }
public virtual DbSet<EquipmentFault> EquipmentFault { get; set; }
public virtual DbSet<AppClass> AppClass { get; set; }
public virtual DbSet<RingstoneSetting> RingstoneSetting { get; set; }
public virtual DbSet<FaultLevel> FaultLevel { get; set; }
public virtual DbSet<FaultSolution> FaultSolution { get; set; }
public virtual DbSet<SolutionAttachFiles> SolutionAttachFiles { get; set; }
public virtual DbSet<SolutionComment> SolutionComment { get; set; }
#endregion
}
}
4、封装数据库通用方法类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
namespace Bosch.Rtns.Repository.Interface
{
public interface IRepository<T> where T : class
{
/// <summary>
/// 新增
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
bool Add(T entity);
/// <summary>
/// 批量添加
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
bool BatchAdd(T[] entities);
/// <summary>
/// 修改
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
bool Update(T entity);
/// <summary>
/// 根据ID获取单个数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
T GetEntiyByID(string id);
/// <summary>
/// 根据查询条件获取单个数据
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
T GetEntityByWhere(Expression<Func<T, bool>> expression);
/// <summary>
/// 根据ID删除
/// </summary>
/// <param name="id"></param>
/// <param name="IsDelete">是否删除</param>
/// <returns></returns>
bool Delete(string id, bool IsDelete = false);
/// <summary>
/// 删除
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
bool Delete(T entity);
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
bool IsExist(Expression<Func<T, bool>> expression);
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="orderExp">排序条件</param>
/// <param name="expression">查询条件</param>
/// <param name="orderBy">排序方式</param>
/// <returns></returns>
List<T> GetList(Expression<Func<T, dynamic>> orderExp, Expression<Func<T, bool>> expression = null, string orderBy = "desc");
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="expression">查询条件</param>
/// <returns></returns>
List<T> GetList(Expression<Func<T, bool>> expression);
/// <summary>
/// 获取分页数据列表
/// </summary>
/// <param name="expression">查询条件</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">页码</param>
/// <param name="orderExp">排序条件</param>
/// <param name="orderBy">排序方式</param>
/// <param name=""></param>
/// <returns></returns>
List<T> GetPageList(Expression<Func<T, bool>> expression, int pageSize, int pageIndex, out int totalCount, Expression<Func<T, dynamic>> orderExp, string orderBy = "desc");
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Bosch.Rtns.Repository.Domain;
using Bosch.Rtns.Repository.Interface;
using Bosch.Rtns.Infrastructure;
namespace Bosch.Rtns.Repository
{
/// <summary>
/// 数据操作通用方法
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseRepository<T> : IRepository<T> where T :BaseEntity
{
protected BoschRtnsDBContext boschRtnsDBContext;
public BaseRepository(BoschRtnsDBContext _boschRtnsDBContext)
{
boschRtnsDBContext = _boschRtnsDBContext;
}
/// <summary>
/// 新增
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Add(T entity)
{
try
{
boschRtnsDBContext.Entry(entity).State = EntityState.Added;
int rowsAffected = boschRtnsDBContext.SaveChanges();
boschRtnsDBContext.Entry(entity).State = EntityState.Detached;
return rowsAffected > 0 ? true : false;
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("数据新增失败:"+ex.Message, GetType());
return false;
}
}
/// <summary>
/// 批量添加
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public bool BatchAdd(T[] entities)
{
try
{
boschRtnsDBContext.Set<T>().AddRange(entities);
int rowsAffected = boschRtnsDBContext.SaveChanges();
return rowsAffected == entities.Length ? true : false;
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("批量添加数据失败:" + ex.Message, GetType());
return false;
}
}
/// <summary>
/// 修改
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Update(T entity)
{
try
{
boschRtnsDBContext.Set<T>().Attach(entity);
boschRtnsDBContext.Entry(entity).State = EntityState.Modified;
int rowsAffected = boschRtnsDBContext.SaveChanges();
return rowsAffected > 0 ? true : false;
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("修改数据失败:" + ex.Message, GetType());
return false;
}
}
/// <summary>
/// 根据ID获取单个数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T GetEntiyByID(string id)
{
try
{
Expression<Func<T, bool>> where = x => x.Id.Equals(id);
var modelList = boschRtnsDBContext.Set<T>().Where(where).AsQueryable().ToList();
return modelList.Any() ? modelList.FirstOrDefault() : null;
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("根据ID获取单个数据失败:" + ex.Message, GetType());
return null;
}
}
/// <summary>
/// 根据查询条件获取单个数据
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public T GetEntityByWhere(Expression<Func<T, bool>> expression)
{
try
{
return boschRtnsDBContext.Set<T>().FirstOrDefault(expression);
}
catch(Exception ex)
{
LogNetManager.WriteErrorLog("根据查询条件获取单个数据失败:" + ex.Message, GetType());
return null;
}
}
/// <summary>
/// 根据ID删除
/// </summary>
/// <param name="id"></param>
/// <param name="IsDelete">是否删除</param>
/// <returns></returns>
public bool Delete(string id,bool IsDelete=false)
{
var model = GetEntiyByID(id);
if (model != null)
{
try
{
if (IsDelete)
{
boschRtnsDBContext.Set<T>().Attach(model);
boschRtnsDBContext.Entry(model).State = EntityState.Deleted;
int rowsAffected = boschRtnsDBContext.SaveChanges();
return rowsAffected > 0 ? true : false;
}
else
{
model.IsDelete = 1;
return Update(model);
}
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("根据ID删除数据失败:" + ex.Message, GetType());
return false;
}
}
else
{
return false;
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Delete(T entity)
{
try
{
boschRtnsDBContext.Set<T>().Remove(entity);
int rowsAffected = boschRtnsDBContext.SaveChanges();
return rowsAffected > 0 ? true : false;
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("删除数据失败:" + ex.Message, GetType());
return false;
}
}
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public bool IsExist(Expression<Func<T, bool>> expression)
{
try
{
return boschRtnsDBContext.Set<T>().Any(expression);
}
catch(Exception ex)
{
LogNetManager.WriteErrorLog("判断数据是否存在失败:" + ex.Message, GetType());
return true;
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="orderExp">排序条件</param>
/// <param name="expression">查询条件</param>
/// <param name="orderBy">排序方式</param>
/// <returns></returns>
public List<T> GetList(Expression<Func<T, dynamic>> orderExp, Expression<Func<T, bool>> expression = null, string orderBy = "desc")
{
try
{
IQueryable<T> quary;
if (expression == null)
{
quary = boschRtnsDBContext.Set<T>().AsNoTracking().AsQueryable();
}
else
{
quary = boschRtnsDBContext.Set<T>().AsNoTracking().AsQueryable().Where(expression);
}
return orderBy == "desc" ? quary.OrderByDescending(orderExp).ToList() : quary.OrderBy(orderExp).ToList();
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("获取数据列表失败:" + ex.Message, GetType());
return null;
}
}
/// <summary>
/// 获取数据列表
/// </summary>
/// <param name="expression">查询条件</param>
/// <returns></returns>
public List<T> GetList( Expression<Func<T, bool>> expression)
{
try
{
IQueryable<T> quary= boschRtnsDBContext.Set<T>().AsNoTracking().AsQueryable().Where(expression);
return quary.ToList();
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("获取数据列表失败:" + ex.Message, GetType());
return null;
}
}
/// <summary>
/// 获取分页数据列表
/// </summary>
/// <param name="expression">查询条件</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">页码</param>
/// <param name="orderExp">排序条件</param>
/// <param name="orderBy">排序方式</param>
/// <param name=""></param>
/// <returns></returns>
public List<T> GetPageList(Expression<Func<T, bool>> expression, int pageSize, int pageIndex, out int totalCount, Expression<Func<T, dynamic>> orderExp, string orderBy = "desc")
{
try
{
if (pageIndex < 1)
{
pageIndex = 1;
}
int skipCount = (pageIndex - 1) * pageSize;
totalCount = boschRtnsDBContext.Set<T>().Where(expression).Count();
IQueryable<T> quary = boschRtnsDBContext.Set<T>().AsNoTracking().AsQueryable().Where(expression);
return orderBy == "desc" ? quary.OrderByDescending(orderExp).Skip(skipCount).Take(pageSize).ToList() : quary.OrderBy(orderExp).Skip(skipCount).Take(pageSize).ToList();
}
catch (Exception ex)
{
LogNetManager.WriteErrorLog("获取分页数据列表失败:" + ex.Message, GetType());
totalCount = 0;
return null;
}
}
}
}
5、配置数据库连接
appsettings中添加数据库的配置
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"BoschRtnsDBContext": "Server=localhost;Port=5432;Database=boschRtns;User Id=postgres;Password=123456"
}
}
start up ConfigureServices中设置数据库连接
string dbCoonectionstring = Configuration.GetConnectionString("BoschRtnsDBContext");//获取数据库连接
services.AddDbContext<BoschRtnsDBContext>(options => options.UseNpgsql(dbCoonectionstring));//数据库连接
6、数据业务层调用(示例)
using System;
using Bosch.Rtns.Repository.Interface;
using Bosch.Rtns.Repository.Domain;
namespace Bosch.Rtns.App
{
public class BaseApp<T> where T: BaseEntity
{
protected IUnitWork UnitWork;
protected IRepository<T> Repository;
/// <summary>
///
/// </summary>
/// <param name="unitWork"></param>
/// <param name="repository"></param>
public BaseApp(IUnitWork unitWork, IRepository<T> repository)
{
UnitWork = unitWork;
Repository = repository;
}
/// <summary>
/// 根据id获取信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T GetEntityByID(string id)
{
return Repository.GetEntiyByID(id);
}
/// <summary>
/// 按id批量删除
/// </summary>
/// <param name="ids"></param>
public bool DeleteById(string ids)
{
int okCount = 0;
var idArrary = ids.Split(',', StringSplitOptions.RemoveEmptyEntries);
foreach(var id in idArrary)
{
var result=Repository.Delete(id);
if (result)
{
okCount++;
}
}
return (okCount == idArrary.Length) ? true : false;
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq.Expressions;
using Bosch.Rtns.Repository.Domain;
using Bosch.Rtns.Repository.Request;
using Bosch.Rtns.Repository.Response;
using Bosch.Rtns.Repository.Interface;
using Bosch.Rtns.Infrastructure;
namespace Bosch.Rtns.App
{
/// <summary>
/// 在线用户业务层
/// </summary>
public class OnlineUsersApp:BaseApp<OnlineUsers>
{
public OnlineUsersApp(IUnitWork unitWork, IRepository<OnlineUsers> repository) : base(unitWork, repository)
{
}
/// <summary>
/// 新增
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool Add(OnlineUsers item)
{
item.Id = Guid.NewGuid().ToString();
item.CreateTime = DateTime.Now;
return Repository.Add(item);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool Update(OnlineUsers item)
{
var model = Repository.GetEntiyByID(item.Id);
if (model != null)
{
model.DeviceId = item.DeviceId;
model.DeviceBattery = item.DeviceBattery;
model.LoginName = item.LoginName;
model.IsOnline = item.IsOnline;
model.ModifyTime = DateTime.Now;
}
return Repository.Update(model);
}
/// <summary>
/// 根据设备ID保存数据
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public bool Save(OnlineUsers item)
{
var model = Repository.GetEntityByWhere(x=>x.DeviceId.Equals(item.DeviceId));
if (model == null)
{
item.Id = Guid.NewGuid().ToString();
item.CreateTime = DateTime.Now;
return Repository.Add(item);
}
else
{
model.DeviceId = item.DeviceId;
model.DeviceBattery = item.DeviceBattery;
model.LoginName = item.LoginName;
model.IsOnline = item.IsOnline;
model.ModifyTime = DateTime.Now;
return Repository.Update(model);
}
}
/// <summary>
/// 根据设备id更新用户在线状态
/// </summary>
/// <param name="deviceId"></param>
/// <param name="isOnline"></param>
/// <returns></returns>
public bool UpdateByDeviceId(string deviceId,int isOnline)
{
var model = Repository.GetEntityByWhere(x=>x.DeviceId.Equals(deviceId));
if (model != null)
{
model.IsOnline = isOnline;
model.ModifyTime = DateTime.Now;
}
return Repository.Update(model);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public ResponseData GetPageList(OnlineUserSearchRequest request)
{
Expression<Func<OnlineUsers, dynamic>> predicteSort = x => x.CreateTime;//排序字段
Expression<Func<OnlineUsers, bool>> predicate = x =>x.IsDelete==0&&x.IsOnline==1;
if (!string.IsNullOrEmpty(request.LoginName))
{
predicate = predicate.And(x => x.LoginName.Contains(request.LoginName));
}
int total = 0;
var dataList = Repository.GetPageList(predicate,request.PageSize, request.PageIndex,out total, predicteSort);
var result = new ResponseData();
result.DataResult = dataList;
result.TotalCount = total;
if (dataList == null)
{
result.Code = 500;
result.Message = "服务器错误";
}
return result;
}
}
}