WPF+Mvvm项目入门完整教程-基于SqlSugar的数据库实例（三）

数据库实现

创建数据库类库

新建项目，选择类库，命名为 DataBaseLib，如下所示:
划分数据库三层结构目录，数据模型 Entities，数据访问层 DAL，业务逻辑层 BLL,分别创建三层目录文件夹，如下所示：
创建一个基类数据模型 BaseModel，用来提取数据模型的公共部分,其他数据模型继承这个基类数据模型，如下所示：
SqlSugar 安装，在 DataBaseLib 类库右键，管理NuGet包，搜索 SqlSugar 安装最新版本。
BaseModel 代码实现如下：

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace DataBaseLib.Entities.BaseEntity
{public class BaseModel{public BaseModel(){CreateTime = DateTime.Now;}/// <summary>/// 自增主键/// </summary>[SugarColumn(IsPrimaryKey =true,IsIdentity =true,ColumnDescription ="自增主键")]public int Id {  get; set; }/// <summary>/// 创建时间/// </summary>[SugarColumn(ColumnDescription = "创建时间")]public DateTime CreateTime {  get; set; }/// <summary>/// 更新时间 可空/// </summary>[SugarColumn(IsNullable =true,ColumnDescription = "更新时间")]public DateTime UpdateTime {  get; set; }}
}

创建一个测用户信息表 UserInfo，表结构代码如下

using DataBaseLib.Entities.BaseEntity;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace DataBaseLib.Entities
{[SugarTable("UserInfo")]//设置数据库名称，当类库名称和数据库不一致是可以通过别名统一public class UserInfo : BaseModel{/// <summary>/// 编号/// </summary>[SugarColumn(ColumnDataType = "Nvarchar(100)", ColumnDescription = "编号")]public string Code { get; set; }/// <summary>/// 姓名/// </summary>[SugarColumn(ColumnDataType = "Nvarchar(100)", ColumnDescription = "姓名")]public string Name { get; set; }/// <summary>/// 密码/// </summary>[SugarColumn(ColumnDataType = "Nvarchar(50)", ColumnDescription = "密码")]public string Password { get; set; }/// <summary>/// 性别/// </summary>[SugarColumn(ColumnDescription = "性别")]public int Gender { get; set; }/// <summary>/// 角色Id/// </summary>[SugarColumn(ColumnDescription = "角色Id")]public int RoleId { get; set; }}
}

6、DAL层创建一个基础的泛型实现类 BaseDAL.cs，代码实现如下：

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.Remoting;
using System.Text;
using System.Threading.Tasks;namespace DataBaseLib.DAL
{public class BaseDAL<T> where T : class, new(){protected MyDbContext db;protected IAdo ado{get{return db.Client.Ado;}}public SimpleClient<T> CurrentDb{get{return db.Client.GetSimpleClient<T>();}}public BaseDAL(){db = new MyDbContext();}/// <summary>/// 返回第一行数据/// </summary>/// <returns></returns>public T GetFirstModel(){try{return CurrentDb.GetList().FirstOrDefault();}catch (Exception ex){throw ex;}}/// <summary>/// 获取所有/// </summary>/// <returns></returns>public virtual List<T> GetList(){return CurrentDb.GetList();}/// <summary>/// 根据表达式查询/// </summary>/// <returns></returns>public virtual List<T> GetList(Expression<Func<T, bool>> whereExpression){return CurrentDb.GetList(whereExpression);}/// <summary>/// 根据表达式查询分页/// </summary>/// <returns></returns>public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel){return CurrentDb.GetPageList(whereExpression, pageModel);}/// <summary>/// 根据表达式查询分页并排序/// </summary>/// <param name="whereExpression">it</param>/// <param name="pageModel"></param>/// <param name="orderByExpression">it=>it.id或者it=>new{it.id,it.name}</param>/// <param name="orderByType">OrderByType.Desc</param>/// <returns></returns>public virtual List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel pageModel, Expression<Func<T, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc){return CurrentDb.GetPageList(whereExpression, pageModel, orderByExpression, orderByType);}/// <summary>/// 根据主键查询/// </summary>/// <returns></returns>public virtual List<T> GetById(dynamic id){return CurrentDb.GetById(id);}/// <summary>/// 根据主键删除/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Delete(dynamic id){return CurrentDb.DeleteById(id);}/// <summary>/// 根据实体删除/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Delete(T data){if (data == null){Console.WriteLine(string.Format("要删除的实体对象不能为空值！"));}return CurrentDb.Delete(data);}/// <summary>/// 根据主键删除/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Delete(dynamic[] ids){if (ids.Count() <= 0){Console.WriteLine(string.Format("要删除的主键ids不能为空值！"));}return CurrentDb.AsDeleteable().In(ids).ExecuteCommand() > 0;}/// <summary>/// 根据表达式删除/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Delete(Expression<Func<T, bool>> whereExpression){return CurrentDb.Delete(whereExpression);}/// <summary>/// 根据实体更新，实体需要有主键/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Update(T obj){if (obj == null){Console.WriteLine(string.Format("要更新的实体不能为空，必须带上主键！"));}return CurrentDb.Update(obj);}/// <summary>///批量更新/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Update(List<T> objs){if (objs.Count <= 0){Console.WriteLine(string.Format("要批量更新的实体不能为空，必须带上主键！"));}return CurrentDb.UpdateRange(objs);}/// <summary>/// 插入/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Insert(T obj){return CurrentDb.Insert(obj);}/// <summary>/// 批量/// </summary>/// <param name="id"></param>/// <returns></returns>public virtual bool Insert(List<T> objs){return CurrentDb.InsertRange(objs);}}
}

继续实现UserInfoDAL.cs 的代码

using DataBaseLib.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace DataBaseLib.DAL
{public class UserInfoDAL : BaseDAL<UserInfo>{}
}

7、BLL层实现，业务逻辑实现，实现UserInfoBLL.cs,代码如下：

using DataBaseLib.DAL;
using DataBaseLib.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace DataBaseLib.BLL
{public class UserInfoBLL{public UserInfoDAL userInfoDAL = new UserInfoDAL();/// <summary>/// 获取全部信息/// </summary>/// <returns></returns>public List<UserInfo> GetAllUserList(){return userInfoDAL.GetList();}public bool DelUserById(int id){return userInfoDAL.Delete(id);}}
}