Dapper and Repository Pattern in MVC

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

这里我个人写了一个Dapper.net 的Repository模式的底层基础框架。

涉及内容：

Dapper.net结合Repository的设计，可切换不同数据库及当前操作数据库的事务支持，依赖注入（工具：Autofac）。

项目可直接在此基础框架上开发。

该底层架构分层参考：

Nopcommerce：https://www.nopcommerce.com

以及自己累积的经验分层及设计

项目结构图：

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<T> where T : BaseEntity

    {

        /// <summary>

        /// 根据主键获取一条数据

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据</returns>

        T GetById(string sql, object param = null, bool buffered = true, int? commandTimeout = null,

            CommandType? commandType = null, bool useTransaction = false);

        /// <summary>

        /// 根据相关条件获取一条数据

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据</returns>

        T GetBy(string sql, object param = null, bool buffered = true, int? commandTimeout = null,

            CommandType? commandType = null, bool useTransaction = false);

        /// <summary>

        /// 获取数据列表（所有、部分或者分页获取）

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据列表</returns>

        IEnumerable<T> GetList(string sql, object param = null, bool buffered = true, int? commandTimeout = null,

            CommandType? commandType = null, bool useTransaction = false);

        /// <summary>

        /// 添加数据

        /// </summary>

        /// <param name="entity">要添加的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（一般为添加的Id）</returns>

        dynamic Insert(T entity, int? commandTimeout = null, bool useTransaction = false);

        /// <summary>

        /// 修改数据

        /// </summary>

        /// <param name="entity">要修改的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（true or false）</returns>

        bool Update(T entity, int? commandTimeout = null, bool useTransaction = false);

        /// <summary>

        /// 删除数据

        /// </summary>

        /// <param name="entity">要删除的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（true or false）</returns>

        bool Delete(T entity, int? commandTimeout = null, bool useTransaction = false);

        /// <summary>

        /// 执行对象sql语句（一般需要事务处理）

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行受影响的行数</returns>

        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("~");

        /// <summary>

        /// mssql 连接字符串

        /// </summary>

        private static string _mssqlConnectionString = Config.AppSettings.Settings["MssqlConnectionString"].Value;

        /// <summary>

        /// mysql 连接字符串

        /// </summary>

        private static string _mysqlConnectionString = Config.AppSettings.Settings["MysqlConnectionString"].Value;

        /// <summary>

        /// oracle 连接字符串

        /// </summary>

        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;

        }

        /// <summary>

        /// 创建数据库连接会话

        /// </summary>

        /// <returns></returns>

        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<T> where T : BaseEntity

    {

        protected virtual IDbSession DbSession

        {

            get { return SessionFactory.CreateSession(DataType); }

        }

        /// <summary>

        /// 数据库类型（MSSQL,MYSQL...）

        /// </summary>

        protected abstract DatabaseType DataType { get; }

        /// <summary>

        /// 根据主键获取一条数据

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据</returns>

        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<T>(sql, param, null, buffered, commandTimeout, commandType).SingleOrDefault();

            session.Dispose(); // 释放资源

            return result;

        }

        /// <summary>

        /// 根据相关条件获取一条数据

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据</returns>

        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<T>(sql, param, null, buffered, commandTimeout, commandType).FirstOrDefault();

            session.Dispose(); // 释放资源

            return result;

        }

        /// <summary>

        /// 获取数据列表（所有、部分或者分页获取）

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="buffered">是否缓冲查询数据，详细信息：https://dapper-tutorial.net/buffered </param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>当前查询数据列表</returns>

        public virtual IEnumerable<T> 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<T> results;

            IDbSession session = DbSession;

            if (useTransaction)

            {

                session.BeginTrans();

                results = session.Connection.Query<T>(sql, param, session.Transaction, buffered, commandTimeout, commandType).ToList();

                session.Commit();

            }

            else

            {

                results = session.Connection.Query<T>(sql, param, null, buffered, commandTimeout, commandType).ToList();

            }

            session.Dispose(); // 释放资源

            return results;

        }

        /// <summary>

        /// 添加数据

        /// </summary>

        /// <param name="entity">要添加的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（一般为添加的Id）</returns>

        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(); // 释放资源

            }

        }

        /// <summary>

        /// 修改数据

        /// </summary>

        /// <param name="entity">要修改的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（true or false）</returns>

        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(); // 释放资源

            }

        }

        /// <summary>

        /// 删除数据

        /// </summary>

        /// <param name="entity">要删除的实体对象</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行结果（true or false）</returns>

        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(); // 释放资源

            }

        }

        /// <summary>

        /// 执行对象sql语句（一般需要事务处理）

        /// </summary>

        /// <param name="sql">sql语句或者存储过程</param>

        /// <param name="param">语句参数</param>

        /// <param name="commandTimeout">执行超时时间</param>

        /// <param name="commandType">命令类型（sql语句或是存储过程）</param>

        /// <param name="useTransaction">是否开启事务</param>

        /// <returns>执行受影响的行数</returns>

        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<Customer>

    {

        #region Customer

        Customer GetCustomerById(int id);

        CustomerDtoModel GetCustomerBy(int id);

        IEnumerable<CustomerDtoModel> GetAllCustomers();

        int InsertCustomer(Customer customer, int roleId);

        int UpdateCustomer(Customer customer, int roleId);

        #endregion

        #region Customer Roles

        // IEnumerable<CustomerRole> 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<Customer>, 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<CustomerDtoModel, CustomerRole, CustomerDtoModel>(sql, (c, cr) =>

                {

                    c.CustomerRole = cr;

                    return c;

                }, new { id }).FirstOrDefault();

                return customers;

            }

        }

        public IEnumerable<CustomerDtoModel> 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<CustomerDtoModel, CustomerRole, CustomerDtoModel>(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);

        }

        /// <summary>

        /// 更新信息（事实上用户有可能具有多个角色，我这里为了演示方便就假设用户只有一个角色处理了）

        /// </summary>

        /// <param name="customer"></param>

        /// <param name="roleId">对应角色id</param>

        /// <returns></returns>

        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<CustomerRole> GetCustomerRoles()

        {

            const string sql = "SELECT Id,Name,SystemName FROM CustomerRole";

            IDbSession session = DbSession;

            try

            {

                using (IDbConnection conn = session.Connection)

                {

                    session.BeginTrans();

                    IEnumerable<CustomerRole> result = conn.Query<CustomerRole>(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<CustomerDtoModel> GetAllCustomers();

        int InsertCustomer(Customer customer, int roleId);

        int UpdateCustomer(Customer customer, int roleId);

        bool DeleteCustomer(Customer customer);

        #endregion

        #region CustomerRole

        //IEnumerable<CustomerRole> 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<CustomerDtoModel> 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<CustomerRole> 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<CustomerRepository>().As<ICustomerRepository>().InstancePerLifetimeScope();

            builder.RegisterType<CustomerRoleRepository>().As<ICustomerRoleRepository>().InstancePerLifetimeScope();

            // Services

            builder.RegisterType<CustomerService>().As<ICustomerService>().InstancePerLifetimeScope();

            builder.RegisterType<CustomerRoleService>().As<ICustomerRoleService>().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<CustomerDtoModel> 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<DapperRepository.Core.Domain.Customers.CustomerDtoModel>

@{

    ViewBag.Title = "Index";

}

<h2>Data List</h2>

<div class="content-header clearfix">

    <h5 class="pull-left">

        <a href="@Url.Action("Create")" class="btn btn-primary">Add</a>

    </h5>

</div>

<table class="table table-bordered">

    <tr>

        <th>Id</th>

        <th>Name</th>

        <th>Email</th>

        <th>Role</th>

        <th>Active</th>

        <th>CreationTime</th>

        <th>Action</th>

        @foreach (var item in Model)

        {

        <tr>

            <td>@item.Id</td>

            <td>@item.Username</td>

            <td>@item.Email</td>

            <td>@item.CustomerRole.Name</td>

            <td>@item.Active</td>

            <td>@item.CreationTime</td>

            <td>

                <a class="btn btn-default" href="@Url.Action("Edit", new {id = item.Id})">Edit</a>

                <a class="btn btn-default del" href="javascript:void(0)" data-id="@item.Id">Delete</a>

            </td>

        </tr>

        }

    </table>

    <script>

        $(function () {

            $('a.del').click(function () {

                if (confirm("Are you sure to delete the data?")) {

                    $.ajax({

                        url: "@Url.Action("Delete")",

                        type: "POST",

                        data: { id: $(this).data("id") }

                    }).done(function (data) {

                        if (data.status) {

                            location.reload();

                        } else {

                            console.log(data.msg);

                        }

                    }).error(function (xhr) {

                        console.log(xhr.message);

                    });

                }

            });

        })

    </script>

相关Create.cshtml及Edit.cshtml这里我就不给出了，大家可下载完整项目。

GitHub地址：https://github.com/Olek-HZQ/DapperRepositoryDemo

项目演示地址：http://dapperrepository.coolwecool.com

巴特西

Dapper and Repository Pattern in MVC

最新文章

热门文章