Dapper and Repository Pattern in MVC-白红宇

Dapper and Repository Pattern in MVC

阅读量：5789 次

发布时间：2019-06-18

本文共 28595 字，大约阅读时间需要 95 分钟。

大家好，首先原谅我标题是英文的，因为我想不出好的中文标题。

这里我个人写了一个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; } ///  /// 创建数据库连接会话 /// 
 /// 
      public static IDbSession CreateSession(DatabaseType databaseType) { IDbConnection conn = CreateConnection(databaseType); IDbSession session = new DbSession(conn); return session; } } }

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 /// 
          public int UpdateCustomer(Customer customer, int roleId) { StringBuilder builder = new StringBuilder(50); builder.Append("UPDATE [dbo].[Customer] SET [Username] = @Username,[Email] = @Email,[Active] = @Active WHERE [Id] = @Id;"); builder.Append("UPDATE [dbo].[Customer_CustomerRole_Mapping] SET [CustomerRoleId] = @CustomerRoleId WHERE [CustomerId] = @CustomerId;"); return Execute(builder.ToString(), new { customer.Username, customer.Email, customer.Active, customer.Id, @CustomerRoleId = roleId, @CustomerId = customer.Id }, commandType: CommandType.Text); } #region Customer Roles /* public IEnumerable
         
           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
       
        Add 
 
      
 
      
 @foreach (var item in Model) { 
 } 
                          Id           Name           Email           Role           Active           CreationTime           Action         
                  @item.Id           @item.Username           @item.Email           @item.CustomerRole.Name           @item.Active           @item.CreationTime            Edit Delete