Dapper and Repository Pattern in MVC

最新推荐文章于 2024-08-25 08:39:09 发布
最新推荐文章于 2024-08-25 08:39:09 发布
阅读量120 收藏
点赞数
文章标签： 测试 数据库 json
原文链接：http://www.cnblogs.com/zhangruisoldier/p/10451640.html
版权

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

这里我个人写了一个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

转载于:https://www.cnblogs.com/zhangruisoldier/p/10451640.html

weixin_33720078
关注
DapperRepository:MVC中的Dapper和存储库模式
05-05
MVC中的Dapper和存储库模式框架简介 1.目标框架 分行： 母版：.Net Framework 4.5.1 CoreDapperRepository：.Net Core 2.1 开发人员：Core 3.1 1.前言 该框架基于dapper.net存储库的基础框架，并遵循SOLID设计原则。 同时，它支持不同类型数据库（mssql，mysql，oracle ...）的多个数据库实例，以及与同一类型数据库共存的多个数据库。 只需修改配置文件即可随意移动它。 使用当前流行的工具（依赖注入：autofac，缓存：redis等），基础包封装了基本的CURD操作。 首先添加一个名为DapperRepositoryDb的新数据库，然后执行所需的sql脚本（mssql或mysql）。 2.项目分层 nopcommerce的参考： ://www.nopcommerce.com 各层的相关描述
Dapper结合Repository模式的应用
weixin_34204722的博客
06-13 985
Dapper结合Repository模式的应用，包括如何在数据访问层（DAL）使用Dapper组件。 Dapper在真实项目中使用，扩展IDbConnection的功能,支持Oracle、MS SQL Server 2005数据库 1）定义统一的IDbConnection访问入口   [csharp] view plain copy   public class...
Dapper封装实现仓储Repository
dawfwafaew的博客
03-28 938
仓储模式作为领域驱动设计（Domain-Driven Design，DDD）的一部分，在系统设计中的使用非常广泛。它主要用于解除业务逻辑层与数据访问层之间的耦合，使业务逻辑层在存储、访问数据库时无须关心数据的来源及存储方式，例如使用哪种类型的数据库，也无须关心对数据的操作，如数据库连接和命令等。所有这些直接对数据的操作均封装在具体的仓储实现中。本篇博客是主要介绍我封装个案例，如果想要学习对Dapper基本操作的话，请看其他博客！DapperRepository: Dapper封装仓储Repository。
DapperRepository:Dapper资料库
05-23
欢迎使用DapperRepository Wiki！ ADO.NET的简单SQL对象映射器 Dapper .NET源-https: Dapper扩展-https: 最新消息由Elyor Latipov修改-mailto： 我修复了此问题列表： - Type Mapping - Drop table - Exists table - Create table - Create Schema - Create Database - PK , UQ , NN impleted to every dialects 模型： public class TempModel { public TempModel() { LastModifiedDate = DateTime.Now; CreatedDate = DateTime.Now
Dapper-Repository
03-25
总结来说，"Dapper-Repository" 是一种使用 C# 语言和 Dapper 框架实现的数据访问层设计模式，它利用 Dapper 的高效和灵活，结合仓库模式提供了可扩展和可测试的数据库操作。在实际项目中，这样的设计可以帮助我们更...
asp.net MVC+Dapper高性能管理系统框架
09-03
开发WEB管理系统时，我们都希望有一套底层...后台代码.net mvc控制器调用service服务层，service层调用dao层，而dao层是封装dapper的一个通用类，可以轻松增删改查数据。本框架可以支持ie10、火狐、谷歌等主流浏览器。
Smooth.IoC.Dapper.Repository.UnitOfWork:使用带有存储库和UnitOfWork模式的Dapper Dapper.FastCRUD使IoC更加平滑
05-11
Smooth.IoC.Dapper.Repository.UnitOfWork 它创建了此程序包，以“修复”存储库和UnitOfWork模式背后的矛盾概念，并使用控制/依赖注入的反转。 我还想通过注入的工厂自动创建连接/开始事务，并在处理时断开/提交，...
一个简单使用dapper的三层mvc的小例子
06-02
在本示例中，我们将探讨如何在ASP.NET MVC项目中结合Dapper实现一个简单的三层架构。** **三层架构通常包括表现层（Presentation Layer）、业务逻辑层（Business Logic Layer）和数据访问层（Data Access Layer）。...
（Demo）Dapper+DapperExtensions+T4+Respository。VS2017开发的
07-04
开发工具VS2017。演示了Dapper+DapperExtensions+T4+Respository的使用方法。只需生成entity。就可以调用基类的Respository实现基本的增删查改的功能。
基于Dapper的数据访问层
09-30
本框架在Dapper的基础上再做封装，支持Lambda表达式树查询也支持纯Sql查询，相对来说比较灵活.实现单个实体的增删改查，可执行存储过程，可输出返回参数，查询结果集可根据实际情况返回DataTable、DataSet和强类型，同时支持不同类型数据库
在C#中如何使用Dapper详解（译）
12-26
前言： Dapper是一款轻量级ORM工具。如果你在小的项目中，使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀。你又觉得ORM省时省力，这时Dapper 将是你不二的选择。 对象关系映射（ORM）已经被使用了很长时间，以解决在编程过程中对象模型与数据模型在关系数据库中不匹配的问题。 Dapper是由Stack OverFlow团队开发的开源的，轻量级的ORM.相比于其他的ORM框架，Dapper速度非常快。 Dapper的设计考虑到了性能以及易用性。它支持使用事务，存储过程或数据批量插入的静态和动态对象绑定。 本文将给大家详细介绍关于
c# DB操作ORM框架
12-24
.NET ORM框架，基于Dapper效率高，包括根据条件Update,Delete有修改字段，各种单表查寻，操作简单。 支持分库，简单分页，暂不支持分表（想办法支持中，有头续的可以联系）。联系:else-love@qq.com
[开源]Dapper Repository 一种实现方式
baodieshe0711的博客
04-10 705
接着上篇[开源]Entity Framework 6 Repository 一种实现方式 由于Dapper 本身就是轻量级Orm特性，这里参考Creating a Data Repository using Dapper dynamic queries in dapper 代码，来解决实体类与Expression<Func<T, bool>> predicat...
Dapper Repositories 开源项目教程
最新发布
gitblog_00847的博客
08-25 553
Dapper Repositories 开源项目教程 dapper-repositoriesCRUD for Dapper项目地址:https://gitcode.com/gh_mirrors/da/dapper-repositories 项目介绍 Dapper Repositories 是一个基于 Dapper 的 CRUD 操作库，旨在简化数据库操作。Dapper 是一个简单且强大的微 OR...
Dapper+UOW+Repository示例代码
wujingang的专栏
10-15 1606
DataAccess IRepositories public interface IGenericRepository&lt;TEntity&gt; where TEntity : class { TEntity Get(int Id); IEnumerable&lt;TEntity&gt; GetAll(); void Add(T...
dapper框架_.net core 基于Dapper 的分库分表开源框架（core-data）
weixin_39519741的博客
11-27 688
一、前言感觉很久没写文章了，最近也比较忙，写的相对比较少，抽空分享基于Dapper 的分库分表开源框架core-data的强大功能，更好的提高开发过程中的效率； 在数据库的数据日积月累的积累下，业务数据库中的单表数据想必也越来越大，大到百万、千万、甚至上亿级别的数据，这个时候就很有必要进行数据库读写分离、以及单表分多表进行存储，提高性能，但是呢很多人不知道怎么去分库分表，也没有现成的分库分表的成熟...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值