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);
    }
}

這里定義相關數據操作（增刪查改）的基礎方法?；蛟S有些開發者會問為何分頁，執行存儲過程的基礎方法都沒有。這里我個人說明下，因為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

posted on 2019-02-28 17:10 zrSoldier 閱讀(1022) 評論(0) 收藏舉報

刷新頁面返回頂部

公告

導航