大家好,首先原谅我标题是英文的,因为我想不出好的中文标题。
这里我个人写了一个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这里我就不给出了,大家可下载完整项目。