FluentData简单介绍和应用
一 介绍
官方介绍
A simple to use Micro ORM with a great fluent API that makes it simple to select, insert, update and delete data in a database. Supported databases: Microsoft SQL Server, MS SQL Server Compact, MS SQL Azure, Oracle and MySQL.
FluentData是一个简单易用的微型ORM框架,可以对数据库执行插入,更新和删除等操作,目前支持的数据库有:Microsoft SQL Server,MS SQL Server Compact,MS SQL Azure,Oracle和MySQL等。
二 基本使用
新建一个控制台项目,通过Nuget添加FluentData的引用,并通过文件夹创建项目结构,Model对应数据库表,DTO代表数据传输对象,Service代表业务层,DataAccess中的文件代表数据库访问层,如图
在配置文件中新增配置,如下
<?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> <connectionStrings> <add name="demo" connectionString="Server=127.0.0.1\SQLEXPRESS;Database=demo;User ID=sa;Password=sa;Trusted_Connection=False;"/> </connectionStrings> </configuration>
在Model文件夹中新建User类文件,Model文件夹下的类型对应数据库表结构
namespace Learn_FluentData.Model { public class User { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } } }
在DTO中新建UserDTO类,代表UI和DataAccess之间传输的对象,并重写ToString()方法
namespace Learn_FluentData.DTO { public class UserDTO { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public override string ToString() { return $"User [ ID:{this.Id} Name:{this.Name} Age:{this.Age} ]"; } } }
在DataAccess中编写数据访问层代码,与数据库交互
using FluentData; using Learn_FluentData.Model; using System.Collections.Generic; namespace Learn_FluentData.DataAccess { public class DataAccess { private IDbContext DbContext; /// <summary> /// 构造函数创建IDbContext对象,所有对数据库的操作都基于该类型 /// </summary> /// <param name="connName"></param> public DataAccess(string connName = "demo") { DbContext = new DbContext().ConnectionStringName(connName, new SqlServerProvider()); } #region 数据库操作 /// <summary> /// 根据用户Id查询用户信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public User GetUserById(int id) { string sqlText = "SELECT * FROM USERS WHERE id =@id"; var user = this.DbContext.Sql(sqlText) .Parameter("id", id) .QuerySingle<User>(MapperData); return user; } /// <summary> /// 查询所有用户 /// </summary> /// <returns></returns> public List<User> GetAllUser() { string sqlText = "SELECT * FROM USERS"; var users = this.DbContext.Sql(sqlText) .QueryMany<User>(MapperData); return users; } /// <summary> /// 新增用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public bool InsertUser(User user) { string sqlText = "INSERT INTO USERS(NAME,AGE) VALUES(@NAME,@AGE)"; var result = this.DbContext.Sql(sqlText) .Parameter("NAME", user.Name) .Parameter("AGE", user.Age) .Execute(); return result > 0; } /// <summary> /// 更新用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public bool UpdateUser(User user) { string sqlText = "UPDATE USERS SET NAME=@name WHERE ID=@id"; var result = this.DbContext.Sql(sqlText) .Parameter("name", user.Name) .Parameter("id", user.Id) .Execute(); return result > 0; } /// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteByUserId(int id) { string sqlText = "DELETE FROM USERS WHERE ID =@id"; var result = this.DbContext.Sql(sqlText) .Parameter("id", id) .Execute(); return result > 0; } #endregion #region 私有方法 /// <summary> /// 映射数据库与实体 /// </summary> /// <param name="user"></param> /// <param name="dataReader"></param> private void MapperData(User user, IDataReader dataReader) { user.Id = dataReader.GetInt32("id"); user.Name = dataReader.GetString("Name"); user.Age = dataReader.GetInt32("Age"); } #endregion } }
编写UserService代码,代表业务处理层
using System.Collections.Generic; using Learn_FluentData.DTO; using Learn_FluentData.Model; namespace Learn_FluentData.Service { public class UserService { private Learn_FluentData.DataAccess.DataAccess dataAccess; public UserService() { this.dataAccess = new Learn_FluentData.DataAccess.DataAccess(); } /// <summary> /// 查询某一个用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public UserDTO GetUserInfoById(int id) { User user = this.dataAccess.GetUserById(id); return AutoMapper.Mapper.Map<UserDTO>(user); } /// <summary> /// 查询所有用户 /// </summary> /// <returns></returns> public List<UserDTO> GetAllUser() { var users = this.dataAccess.GetAllUser(); return AutoMapper.Mapper.Map<List<UserDTO>>(users); } /// <summary> /// 新增用户 /// </summary> /// <param name="userDto"></param> /// <returns></returns> public bool AddUser(UserDTO userDto) { var user = AutoMapper.Mapper.Map<User>(userDto); return this.dataAccess.InsertUser(user); } /// <summary> /// 更新用户 /// </summary> /// <param name="userDto"></param> /// <returns></returns> public bool UpdateUser(UserDTO userDto) { var user = AutoMapper.Mapper.Map<User>(userDto); return this.dataAccess.UpdateUser(user); } /// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteUserInfoById(int id) { return this.dataAccess.DeleteByUserId(id); } } }
注册对象映射,此处使用AutoMapper
using AutoMapper; using Learn_FluentData.DTO; using Learn_FluentData.Model; namespace Learn_FluentData.Common { public class AutoMapperRegister { public static void Register() { AutoMapper.Mapper.Initialize(it => { it.AddProfile<MapperProfile>(); }); } } public class MapperProfile : Profile { public MapperProfile() { this.CreateMap<User, UserDTO>(); this.CreateMap<UserDTO, User>(); } } }
编写Main方法,代表UI层逻辑
using Learn_FluentData.Common; using Learn_FluentData.DTO; using Learn_FluentData.Service; using System; namespace Learn_FluentData { class Program { static void Main(string[] args) { AutoMapperRegister.Register(); UserService userService = new UserService(); Console.WriteLine("======GetUserInfoById======"); var userInfo = userService.GetUserInfoById(1); Console.WriteLine(userInfo.ToString()); Console.WriteLine("======GetAllUser======"); var userInfos = userService.GetAllUser(); userInfos.ForEach(userinfo => { Console.WriteLine(userinfo.ToString()); }); UserDTO userDto = new UserDTO { Name = "new add", Age = 89 }; bool insertResult = userService.AddUser(userDto); if (!insertResult) { Console.WriteLine("Insert Failed !!!"); } else { Console.WriteLine("======afterAdduserInfos======"); var afterAdduserInfos = userService.GetAllUser(); afterAdduserInfos.ForEach(userinfo => { Console.WriteLine(userinfo.ToString()); }); } userDto.Id = 3; userDto.Name = "update"; bool updateResult = userService.UpdateUser(userDto); if (!updateResult) { Console.WriteLine("Update Failed !!!"); } else { Console.WriteLine("======afterUpdateuserInfos======"); var afterUpdateuserInfos = userService.GetAllUser(); afterUpdateuserInfos.ForEach(userinfo => { Console.WriteLine(userinfo.ToString()); }); } bool deleteResult = userService.DeleteUserInfoById(3); if (!updateResult) { Console.WriteLine("Delete Failed !!!"); } else { Console.WriteLine("======afterDeleteuserInfos======"); var afterDeleteuserInfos = userService.GetAllUser(); afterDeleteuserInfos.ForEach(userinfo => { Console.WriteLine(userinfo.ToString()); }); } Console.ReadKey(); } } }
运行程序,结果如下,可以看到最简单数据增删改查操作
三 总结
无论是微软的EF,还是此处FluentData,以及其他的ORM框架,其目的都是为剪坏数据库操作而生,所以无所谓孰好孰坏,一切依据实际业务而定
源代码地址:https://github.com/qyjwork/Learn-FluentData