大家好，首先原谅我标题是英文的，因为我想不出好的中文标题。
这里我个人写了一个Dapper.net 的Repository模式的底层基础框架。
涉及内容：
Dapper.net结合Repository的设计，可切换不同数据库及当前操作数据库的事务支持，依赖注入（工具：Autofac）。
项目可直接在此基础框架上开发。
该底层架构分层参考：
Nopcommerce：
以及自己累积的经验分层及设计
项目结构图：
DapperRepository.Core: 放置相关数据接口和实体类
DapperRepository.Data:数据操作层，实现具体Repository和数据库连接及访问
DapperRepository.Services:业务逻辑层，处理相关业务逻辑
DapperRepository.Web:web端，客户端操作
以下简称Core、Data、Services、Web
数据库脚本：
创建数据库：
USE [master]GO/****** Object: Database [DapperRepositoryDb] Script Date: 2/28/2019 2:59:41 PM ******/ CREATE DATABASE [DapperRepositoryDb] CONTAINMENT = NONE ON PRIMARY ( NAME = N'DapperRepositoryDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DapperRepositoryDb.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DapperRepositoryDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DapperRepositoryDb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [DapperRepositoryDb] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [DapperRepositoryDb].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [DapperRepositoryDb] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [DapperRepositoryDb] SET ANSI_NULLS OFF GO ALTER DATABASE [DapperRepositoryDb] SET ANSI_PADDING OFF GO ALTER DATABASE [DapperRepositoryDb] SET ANSI_WARNINGS OFF GO ALTER DATABASE [DapperRepositoryDb] SET ARITHABORT OFF GO ALTER DATABASE [DapperRepositoryDb] SET AUTO_CLOSE OFF GO ALTER DATABASE [DapperRepositoryDb] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [DapperRepositoryDb] SET AUTO_SHRINK OFF GO ALTER DATABASE [DapperRepositoryDb] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [DapperRepositoryDb] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [DapperRepositoryDb] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [DapperRepositoryDb] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [DapperRepositoryDb] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [DapperRepositoryDb] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [DapperRepositoryDb] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [DapperRepositoryDb] SET DISABLE_BROKER GO ALTER DATABASE [DapperRepositoryDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [DapperRepositoryDb] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [DapperRepositoryDb] SET TRUSTWORTHY OFF GO ALTER DATABASE [DapperRepositoryDb] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [DapperRepositoryDb] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [DapperRepositoryDb] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [DapperRepositoryDb] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [DapperRepositoryDb] SET RECOVERY SIMPLE GO ALTER DATABASE [DapperRepositoryDb] SET MULTI_USER GO ALTER DATABASE [DapperRepositoryDb] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [DapperRepositoryDb] SET DB_CHAINING OFF GO ALTER DATABASE [DapperRepositoryDb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [DapperRepositoryDb] SET TARGET_RECOVERY_TIME = 0 SECONDS GO ALTER DATABASE [DapperRepositoryDb] SET READ_WRITE GO 创建表和演示数据：
USE [DapperRepositoryDb]GO/****** Object: Table [dbo].[Customer] Script Date: 2019/2/28 14:54:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer]( [Id] [INT] IDENTITY(1,1) NOT NULL, [Username] [NVARCHAR](32) NOT NULL, [Email] [NVARCHAR](128) NOT NULL, [Active] [BIT] NOT NULL, [CreationTime] [DATETIME] NOT NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[CustomerRole] Script Date: 2019/2/28 14:54:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CustomerRole]( [Id] [INT] IDENTITY(1,1) NOT NULL, [Name] [NVARCHAR](32) NOT NULL, [SystemName] [NVARCHAR](32) NOT NULL, [CreationTime] [DATETIME] NOT NULL, CONSTRAINT [PK_CustomerRole] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Customer_CustomerRole_Mapping] Script Date: 2019/2/28 14:54:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customer_CustomerRole_Mapping]( [CustomerId] [INT] NOT NULL, [CustomerRoleId] [INT] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Customer_CustomerRole_Mapping] WITH CHECK ADD CONSTRAINT [FK_Customer_CustomerRole_Mapping_Customer] FOREIGN KEY([CustomerId]) REFERENCES [dbo].[Customer] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Customer_CustomerRole_Mapping] CHECK CONSTRAINT [FK_Customer_CustomerRole_Mapping_Customer] GO ALTER TABLE [dbo].[Customer_CustomerRole_Mapping] WITH CHECK ADD CONSTRAINT [FK_Customer_CustomerRole_Mapping_CustomerRole] FOREIGN KEY([CustomerRoleId]) REFERENCES [dbo].[CustomerRole] ([Id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[Customer_CustomerRole_Mapping] CHECK CONSTRAINT [FK_Customer_CustomerRole_Mapping_CustomerRole] GO INSERT INTO [dbo].[CustomerRole] ([Name] ,[SystemName] ,[CreationTime]) VALUES ('Admin', 'Admin', GETDATE()) GO INSERT INTO [dbo].[CustomerRole] ([Name] ,[SystemName] ,[CreationTime]) VALUES ('Guest', 'Guest', GETDATE()) GO
接下俩详细分析：
实体基类BaseEntity:
namespace DapperRepository.Core{ public abstract class BaseEntity { public int Id { get; set; } } }
Core：
建立一个名为Data的文件夹放置IDbSession和IRepository：
IDbSession:
using System;using System.Data;namespace DapperRepository.Core.Data { public interface IDbSession : IDisposable { IDbConnection Connection { get; } IDbTransaction Transaction { get; } IDbTransaction BeginTrans(IsolationLevel isolation = IsolationLevel.ReadCommitted); void Commit(); void Rollback(); } } 这个接口定义数据数据连接对象属性和事务属性，以及相关事务性的操作方法。
IRepository:
using System.Data;using System.Collections.Generic;namespace DapperRepository.Core.Data { public interface IRepository where T : BaseEntity { /// /// 根据主键获取一条数据 /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据 T GetById(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false); /// /// 根据相关条件获取一条数据 /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据 T GetBy(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false); /// /// 获取数据列表（所有、部分或者分页获取） /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据列表 IEnumerable GetList(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false); /// /// 添加数据 /// /// 要添加的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（一般为添加的Id） dynamic Insert(T entity, int? commandTimeout = null, bool useTransaction = false); /// /// 修改数据 /// /// 要修改的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（true or false） bool Update(T entity, int? commandTimeout = null, bool useTransaction = false); /// /// 删除数据 /// /// 要删除的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（true or false） bool Delete(T entity, int? commandTimeout = null, bool useTransaction = false); /// /// 执行对象sql语句（一般需要事务处理） /// /// sql语句或者存储过程 /// 语句参数 /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 执行受影响的行数 int Execute(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = true); } } 这里定义相关数据操作（增删查改）的基础方法。或许有些开发者会问为何分页，执行存储过程的基础方法都没有。这里我个人说明下，因为dapper.net是精简orm，并不像EF那样集成了很多方法和扩展，dapper主要还是依赖于写的sql语句的处理逻辑。所以这里分页的话你写好sql语句或者存储过程并调用GetList方法即可。
创建一个名为Domain的文件夹放置相关实体：
DataBaseType枚举（数据库类型：MSSQL、MYSQL、ORACLE...）
namespace DapperRepository.Core.Domain{ public enum DatabaseType { Mssql, Mysql, Oracle }} 相关实体：
Customer：
using System;namespace DapperRepository.Core.Domain.Customers{ public class Customer : BaseEntity { public string Username { get; set; } public string Email { get; set; } public bool Active { get; set; } public DateTime CreationTime { get; set; } } } CustomerRole:
namespace DapperRepository.Core.Domain.Customers{ public class CustomerRole : BaseEntity { public string Name { get; set; } public string SystemName { get; set; } } } Dto实体CustomerDtoModel：
using System;namespace DapperRepository.Core.Domain.Customers{ public class CustomerDtoModel { public int Id { get; set; } public string Username { get; set; } public string Email { get; set; } public bool Active { get; set; } public DateTime CreationTime { get; set; } public virtual CustomerRole CustomerRole { get; set; } } }
Data：
新建一个类ConnConfig用于获取数据连接字符串：
using System.Configuration;using System.Web.Configuration;namespace DapperRepository.Data { public class ConnConfig { private readonly static Configuration Config = WebConfigurationManager.OpenWebConfiguration("~"); /// /// mssql 连接字符串 /// private static string _mssqlConnectionString = Config.AppSettings.Settings["MssqlConnectionString"].Value; /// /// mysql 连接字符串 /// private static string _mysqlConnectionString = Config.AppSettings.Settings["MysqlConnectionString"].Value; /// /// oracle 连接字符串 /// private static string _oracleConnectionString = Config.AppSettings.Settings["OracleConnectionString"].Value; public static string MssqlConnectionString { get { return _mssqlConnectionString; } set { _mssqlConnectionString = value; } } public static string MysqlConnectionString { get { return _mysqlConnectionString; } set { _mysqlConnectionString = value; } } public static string OracleConnectionString { get { return _oracleConnectionString; } set { _oracleConnectionString = value; } } } } 工厂类SessionFactory用于切换某个数据库以及创建数据库会话：
using System.Data;using System.Data.OracleClient;using System.Data.SqlClient; using DapperRepository.Core.Data; using DapperRepository.Core.Domain; using MySql.Data.MySqlClient; namespace DapperRepository.Data { public class SessionFactory { private static IDbConnection CreateConnection(DatabaseType dataType) { IDbConnection conn; switch (dataType) { case DatabaseType.Mssql: conn = new SqlConnection(ConnConfig.MssqlConnectionString); break; case DatabaseType.Mysql: conn = new MySqlConnection(ConnConfig.MysqlConnectionString); break; case DatabaseType.Oracle: conn = new OracleConnection(ConnConfig.OracleConnectionString); break; default: conn = new SqlConnection(ConnConfig.MssqlConnectionString); break; } conn.Open(); return conn; } /// /// 创建数据库连接会话 /// /// IDbSession的实现类DbSession：
using System;using System.Data;using DapperRepository.Core.Data; namespace DapperRepository.Data { public class DbSession : IDbSession { private IDbConnection _connection; private IDbTransaction _transaction; public DbSession(IDbConnection conn) { _connection = conn; } public IDbConnection Connection { get { return _connection; } } public IDbTransaction Transaction { get { return _transaction; } } public IDbTransaction BeginTrans(IsolationLevel isolation = IsolationLevel.ReadCommitted) { _transaction = _connection.BeginTransaction(isolation); return _transaction; } public void Commit() { _transaction.Commit(); } public void Rollback() { _transaction.Rollback(); } public void Dispose() { if (_transaction != null) { _transaction.Dispose(); _transaction = null; } if (_connection != null) { if (_connection.State == ConnectionState.Open) _connection.Close(); _connection.Dispose(); _connection = null; } GC.SuppressFinalize(this); } } } 抽象类RepositoryBase用于实现IRepository接口的方法：
using System;using System.Linq;using System.Data; using System.Collections.Generic; using Dapper; using DapperExtensions; using DapperRepository.Core; using DapperRepository.Core.Data; using DapperRepository.Core.Domain; namespace DapperRepository.Data { public abstract class RepositoryBase where T : BaseEntity { protected virtual IDbSession DbSession { get { return SessionFactory.CreateSession(DataType); } } /// /// 数据库类型（MSSQL,MYSQL...） /// protected abstract DatabaseType DataType { get; } /// /// 根据主键获取一条数据 /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据 public virtual T GetById(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false) { if (string.IsNullOrEmpty(sql)) return null; IDbSession session = DbSession; T result = session.Connection.Query (sql, param, null, buffered, commandTimeout, commandType).SingleOrDefault(); session.Dispose(); // 释放资源 return result; } /// /// 根据相关条件获取一条数据 /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据 public virtual T GetBy(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false) { if (string.IsNullOrEmpty(sql)) return null; IDbSession session = DbSession; T result = session.Connection.Query (sql, param, null, buffered, commandTimeout, commandType).FirstOrDefault(); session.Dispose(); // 释放资源 return result; } /// /// 获取数据列表（所有、部分或者分页获取） /// /// sql语句或者存储过程 /// 语句参数 /// 是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 当前查询数据列表 public virtual IEnumerable GetList(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = false) { if (string.IsNullOrEmpty(sql)) return null; IEnumerable results; IDbSession session = DbSession; if (useTransaction) { session.BeginTrans(); results = session.Connection.Query (sql, param, session.Transaction, buffered, commandTimeout, commandType).ToList(); session.Commit(); } else { results = session.Connection.Query (sql, param, null, buffered, commandTimeout, commandType).ToList(); } session.Dispose(); // 释放资源 return results; } /// /// 添加数据 /// /// 要添加的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（一般为添加的Id） public virtual dynamic Insert(T entity, int? commandTimeout = null, bool useTransaction = false) { IDbSession session = DbSession; try { if (useTransaction) { session.BeginTrans(); dynamic result = session.Connection.Insert(entity, session.Transaction, commandTimeout); session.Commit(); return result; } else { return session.Connection.Insert(entity, null, commandTimeout); } } catch (Exception) { if (useTransaction) { session.Rollback(); } return null; } finally { session.Dispose(); // 释放资源 } } /// /// 修改数据 /// /// 要修改的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（true or false） public virtual bool Update(T entity, int? commandTimeout = null, bool useTransaction = false) { IDbSession session = DbSession; try { if (useTransaction) { session.BeginTrans(); bool result = session.Connection.Update(entity, session.Transaction, commandTimeout); session.Commit(); return result; } else { return session.Connection.Update(entity, null, commandTimeout); } } catch (Exception) { if (useTransaction) { session.Rollback(); } return false; } finally { session.Dispose(); // 释放资源 } } /// /// 删除数据 /// /// 要删除的实体对象 /// 执行超时时间 /// 是否开启事务 /// 执行结果（true or false） public virtual bool Delete(T entity, int? commandTimeout = null, bool useTransaction = false) { IDbSession session = DbSession; try { if (useTransaction) { session.BeginTrans(); bool result = session.Connection.Delete(entity, session.Transaction, commandTimeout); session.Commit(); return result; } else { return session.Connection.Delete(entity, null, commandTimeout); } } catch (Exception) { if (useTransaction) { session.Rollback(); } return false; } finally { session.Dispose(); // 释放资源 } } /// /// 执行对象sql语句（一般需要事务处理） /// /// sql语句或者存储过程 /// 语句参数 /// 执行超时时间 /// 命令类型（sql语句或是存储过程） /// 是否开启事务 /// 执行受影响的行数 public virtual int Execute(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null, bool useTransaction = true) { if (string.IsNullOrEmpty(sql)) return 0; IDbSession session = DbSession; try { if (useTransaction) { session.BeginTrans(); int rowsAffected = session.Connection.Execute(sql, param, session.Transaction, commandTimeout, commandType); session.Commit(); return rowsAffected; } else { return session.Connection.Execute(sql, param, null, commandTimeout, commandType); } } catch (Exception) { if (useTransaction) { session.Rollback(); } return 0; } finally { session.Dispose(); // 释放资源 } } } } 新建接口ICustomerRepository：
using System.Collections.Generic;using DapperRepository.Core.Data;using DapperRepository.Core.Domain.Customers; namespace DapperRepository.Data.Repositories.Customers { public interface ICustomerRepository : IRepository { #region Customer Customer GetCustomerById(int id); CustomerDtoModel GetCustomerBy(int id); IEnumerable GetAllCustomers(); int InsertCustomer(Customer customer, int roleId); int UpdateCustomer(Customer customer, int roleId); #endregion #region Customer Roles // IEnumerable GetCustomerRoles(); #endregion } } 对应实现类CustomerRepository：
using System;using System.Text;using System.Data; using System.Collections.Generic; using System.Linq; using Dapper; using DapperRepository.Core.Data; using DapperRepository.Core.Domain; using DapperRepository.Core.Domain.Customers; namespace DapperRepository.Data.Repositories.Customers { public class CustomerRepository : RepositoryBase , ICustomerRepository { protected override DatabaseType DataType { get { return DatabaseType.Mssql; } } public Customer GetCustomerById(int id) { if (id == 0) return null; const string sql = "SELECT [Id],[Username],[Email],[Active],[CreationTime] FROM Customer WHERE Id=@id"; return GetById(sql, new { id }, commandType: CommandType.Text); } public CustomerDtoModel GetCustomerBy(int id) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT c.Id,c.Username,c.Email,c.Active,c.CreationTime,cr.Id,cr.Name,cr.SystemName FROM Customer c "); sb.Append("JOIN Customer_CustomerRole_Mapping crm ON c.Id = crm.CustomerId "); sb.Append("JOIN CustomerRole cr ON crm.CustomerRoleId = cr.Id WHERE c.Id = @id"); string sql = sb.ToString(); IDbSession session = DbSession; using (IDbConnection conn = session.Connection) { var customers = conn.Query (sql, (c, cr) => { c.CustomerRole = cr; return c; }, new { id }).FirstOrDefault(); return customers; } } public IEnumerable GetAllCustomers() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT c.Id,c.Username,c.Email,c.Active,c.CreationTime,cr.Id,cr.Name,cr.SystemName FROM Customer c "); sb.Append("JOIN Customer_CustomerRole_Mapping crm ON c.Id = crm.CustomerId "); sb.Append("JOIN CustomerRole cr ON crm.CustomerRoleId = cr.Id"); string sql = sb.ToString(); IDbSession session = DbSession; try { using (IDbConnection conn = session.Connection) { session.BeginTrans(); var customers = conn.Query (sql, (c, cr) => { c.CustomerRole = cr; return c; }, transaction: session.Transaction); session.Commit(); return customers; } } catch (Exception) { return null; } finally { session.Dispose(); } } public int InsertCustomer(Customer customer, int roleId) { StringBuilder builder = new StringBuilder(50); builder.Append("DECLARE @insertid INT;"); builder.Append("INSERT INTO dbo.Customer( Username,Email,Active,CreationTime ) VALUES ( @Username,@Email,@Active,@CreationTime );"); builder.Append("SET @insertid = SCOPE_IDENTITY();"); builder.Append("INSERT INTO [dbo].[Customer_CustomerRole_Mapping]( CustomerId,CustomerRoleId ) VALUES ( @insertid,@roleId );"); return Execute(builder.ToString(), new { customer.Username, customer.Email, customer.Active, customer.CreationTime, roleId }, commandType: CommandType.Text); } /// /// 更新信息（事实上用户有可能具有多个角色，我这里为了演示方便就假设用户只有一个角色处理了） /// /// /// 对应角色id /// GetCustomerRoles() { const string sql = "SELECT Id,Name,SystemName FROM CustomerRole"; IDbSession session = DbSession; try { using (IDbConnection conn = session.Connection) { session.BeginTrans(); IEnumerable result = conn.Query (sql, transaction: session.Transaction); session.Commit(); return result; } } catch (Exception) { return null; } finally { session.Dispose(); } } */ #endregion } }
Services：
接口ICustomerService：
using System.Collections.Generic;using DapperRepository.Core.Domain.Customers;namespace DapperRepository.Services.Customers { public interface ICustomerService { #region Customer Customer GetCustomerById(int customerId); CustomerDtoModel GetCustomerBy(int id); IEnumerable GetAllCustomers(); int InsertCustomer(Customer customer, int roleId); int UpdateCustomer(Customer customer, int roleId); bool DeleteCustomer(Customer customer); #endregion #region CustomerRole //IEnumerable GetCustomerRoles(); #endregion } } 对应实现类CustomerService：
using System;using System.Collections.Generic;using DapperRepository.Core.Domain.Customers; using DapperRepository.Data.Repositories.Customers; namespace DapperRepository.Services.Customers { public class CustomerService : ICustomerService { private readonly ICustomerRepository _repository; public CustomerService(ICustomerRepository repository) { _repository = repository; } #region Custoemr public Customer GetCustomerById(int customerId) { if (customerId == 0) return null; return _repository.GetCustomerById(customerId); } public CustomerDtoModel GetCustomerBy(int id) { if (id <= 0) return null; return _repository.GetCustomerBy(id); } public IEnumerable GetAllCustomers() { return _repository.GetAllCustomers(); } public int InsertCustomer(Customer customer, int roleId) { if (customer == null) throw new ArgumentNullException("customer"); return _repository.InsertCustomer(customer, roleId); } public int UpdateCustomer(Customer customer, int roleId) { if (customer == null) throw new ArgumentNullException("customer"); return _repository.UpdateCustomer(customer, roleId); } public bool DeleteCustomer(Customer customer) { return _repository.Delete(customer); } #endregion #region Customer Roles /* public IEnumerable GetCustomerRoles() { return _repository.GetCustomerRoles(); } */ #endregion } }
Web：
建立文件夹Infrastructure用于存放依赖注入的配置类Bootstrapper：
using System.Reflection;using System.Web.Mvc;using Autofac; using Autofac.Integration.Mvc; using DapperRepository.Data.Repositories.Customers; using DapperRepository.Services.Customers; namespace DapperRepository.Web.Infrastructure { public class Bootstrapper { public static void Run() { SetAutofacContainer(); } private static void SetAutofacContainer() { ContainerBuilder builder = new ContainerBuilder(); builder.RegisterControllers(Assembly.GetExecutingAssembly()); // Repositories builder.RegisterType ().As ().InstancePerLifetimeScope(); builder.RegisterType ().As ().InstancePerLifetimeScope(); // Services builder.RegisterType ().As ().InstancePerLifetimeScope(); builder.RegisterType ().As ().InstancePerLifetimeScope(); IContainer container = builder.Build(); DependencyResolver.SetResolver(new AutofacDependencyResolver(container)); } } } 添加控制器CustomerController：
using System;using System.Collections.Generic;using System.Linq; using System.Web.Mvc; using DapperRepository.Core.Domain.Customers; using DapperRepository.Services.Customers; using DapperRepository.Web.Models.Customers; namespace DapperRepository.Web.Controllers { public class CustomerController : Controller { private readonly ICustomerService _customerService; private readonly ICustomerRoleService _customerRoleService; public CustomerController(ICustomerService customerService, ICustomerRoleService customerRoleService) { _customerService = customerService; _customerRoleService = customerRoleService; } public ActionResult Index() { IEnumerable customers = _customerService.GetAllCustomers(); return View(customers); } public ActionResult Create() { var customerRoles = _customerRoleService.GetCustomerRoles().Select(x => new SelectListItem { Text = x.Name, Value = x.Id.ToString() }).ToList(); CustomerModel model = new CustomerModel { AvailableRoles = customerRoles }; return View(model); } [HttpPost] public ActionResult Create(CustomerModel model) { if (ModelState.IsValid) { Customer customer = new Customer { Username = model.Username, Email = model.Email, Active = model.Active, CreationTime = DateTime.Now }; _customerService.InsertCustomer(customer, model.RoleId); } return RedirectToAction("Index"); } public ActionResult Edit(int id) { CustomerDtoModel customer = _customerService.GetCustomerBy(id); if (customer == null) return RedirectToAction("Index"); var customerRoles = _customerRoleService.GetCustomerRoles().Select(x => new SelectListItem { Text = x.Name, Value = x.Id.ToString(), Selected = x.Id == customer.CustomerRole.Id }).ToList(); CustomerModel model = new CustomerModel { Id = customer.Id, Username = customer.Username, Email = customer.Email, Active = customer.Active, CreationTime = customer.CreationTime, RoleId = customer.CustomerRole.Id, AvailableRoles = customerRoles }; return View(model); } [HttpPost] public ActionResult Edit(CustomerModel model) { Customer customer = _customerService.GetCustomerById(model.Id); if (customer == null) return RedirectToAction("Index"); if (ModelState.IsValid) { customer.Username = model.Username; customer.Email = model.Email; customer.Active = model.Active; _customerService.UpdateCustomer(customer, model.RoleId); } return RedirectToAction("Index"); } [HttpPost] public ActionResult Delete(int id) { Customer customer = _customerService.GetCustomerById(id); if (customer == null) return Json(new { status = false, msg = "No customer found with the specified id" }); try { bool result = _customerService.DeleteCustomer(customer); return Json(new { status = result, msg = result ? "deleted successfully" : "deleted failed" }); } catch (Exception ex) { return Json(new { status = false, msg = ex.Message }); } } } } Index.cshtml：
@model IEnumerable@{ ViewBag.Title = "Index";} Data List
Id Name Role Active CreationTime Action @foreach (var item in Model) {} @item.Id @item.Username @item.Email @item.CustomerRole.Name @item.Active @item.CreationTime Edit Delete
相关Create.cshtml及Edit.cshtml这里我就不给出了，大家可下载完整项目。
GitHub地址：
项目演示地址：