Insert a job search

Implementation of Repository based on Dapper

public abstract class DapperDbContextBase : IDbContext
{
    #region Constructors
 
    /// <summary>
    ///Constructor
    /// </summary>
    ///< param name = "connectstring" > connection string < / param >
    protected DapperDbContextBase(string connectString)
    {
        ConnectString = connectString;
    }
 
    #endregion Constructors
 
    #region Properties
 
    /// <summary>
    ///Get whether to enable transaction commit
    /// </summary>
    public IDbTransaction CurrentTransaction { get; private set; }
 
    #endregion Properties
 
    #region Fields
 
    /// <summary>
    ///Current database connection
    /// </summary>
    public IDbConnection CurrentConnection =>
        TransactionEnabled ? CurrentTransaction.Connection : CreateConnection();
 
    /// <summary>
    ///Get whether to enable transaction commit
    /// </summary>
    public bool TransactionEnabled => CurrentTransaction != null;
 
    /// <summary>
    ///Connection string
    /// </summary>
    protected readonly string ConnectString;
 
    #endregion Fields
 
    #region Methods
 
    /// <summary>
    ///Open data context transaction explicitly
    /// </summary>
    ///< param name = "isolationlevel" > specifies the transaction locking behavior of the connection < / param >
    public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.Unspecified)
    {
        if (!TransactionEnabled) CurrentTransaction = CreateConnection().BeginTransaction(isolationLevel);
    }
 
    /// <summary>
    ///Commit transaction changes for the current context
    /// </summary>
    ///< exception CREF = "dataaccessexception" > exception occurred when submitting data update: "+ MSG < / exception >
    public void Commit()
    {
        if (TransactionEnabled)
            try
            {
                CurrentTransaction.Commit();
            }
            catch (Exception ex)
            {
                if (ex.InnerException?.InnerException is SqlException sqlEx)
                {
                    var msg = DataBaseHelper.GetSqlExceptionMessage(sqlEx.Number);
                    throw new DataAccessException("An exception occurred while submitting the data update:" + msg, sqlEx);
                }
 
                throw;
            }
    }
 
    /// <summary>
    ///Create record
    /// </summary>
    ///< param name = "entity" > entity class to be operated on < / param >
    ///Whether the operation is successful
    public bool Create<T>(T entity)
        where T : ModelBase
    {
        ValidateOperator.Begin().NotNull(entity, "Data records to be added");
        // insert single data always return 0 but the data is inserted in database successfully
        //https://github.com/StackExchange/Dapper/issues/587
        //List<T> data = new List<T>() { entity };
 
        return CurrentConnection.Insert(new List<T> {entity}, CurrentTransaction) > 0;
 
        #region test code
 
        //string sql = @"INSERT INTO [dbo].[EFSample]
        //      ([ID]
        //      ,[CreateTime]
        //      ,[ModifyTime]
        //      ,[Available]
        //      ,[UserName])
        //VALUES
        //      (@ID
        //      ,@CreateTime
        //      ,@ModifyTime
        //      ,@Available
        //      ,@UserName)";
 
        //return CurrentConnection.Execute(sql, entity) > 0;
 
        #endregion test code
    }
 
    /// <summary>
    ///Create database connection IDbConnection
    /// </summary>
    /// <returns></returns>
    public abstract IDbConnection CreateConnection();
 
    /// <summary>
    ///Delete record
    /// </summary>
    ///Whether the operation is successful
    ///< param name = "entity" > the entity class to be operated on. < / param >
    public bool Delete<T>(T entity)
        where T : ModelBase
    {
        ValidateOperator.Begin().NotNull(entity, "Data records to be deleted");
        return CurrentConnection.Delete(entity);
    }
 
    /// <summary>
    ///Performs tasks defined by the application associated with releasing or resetting unmanaged resources.
    /// </summary>
    public void Dispose()
    {
        if (CurrentTransaction != null)
        {
            CurrentTransaction.Dispose();
            CurrentTransaction = null;
        }
 
        CurrentConnection?.Dispose();
    }
 
    /// <summary>
    ///Condition judgment exists
    /// </summary>
    ///< returns > is there < / returns >
    ///< param name = "predicate" > judgment condition delegation < / param >
    public bool Exist<T>(Expression<Func<T, bool>> predicate = null)
        where T : ModelBase
    {
        var tableName = GetTableName<T>();
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
 
        var result =
            CurrentConnection.ExecuteScalar(queryResult.Sql, (object) queryResult.Param, CurrentTransaction);
        return result != null;
    }
 
    /// <summary>
    ///Get records based on id
    /// </summary>
    ///< returns > record < / returns >
    /// <param name="id">id.</param>
    public T GetByKeyId<T>(object id)
        where T : ModelBase
    {
        ValidateOperator.Begin().NotNull(id, "Id");
        return CurrentConnection.Get<T>(id, CurrentTransaction);
    }
 
    /// <summary>
    ///Conditional access record set
    /// </summary>
    ///< returns > set < / returns >
    ///< param name = "predict" > filter criteria. < / param >
    public List<T> GetList<T>(Expression<Func<T, bool>> predicate = null)
        where T : ModelBase
    {
        var tableName = GetTableName<T>();
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
 
        return CurrentConnection.Query<T>(queryResult.Sql, (object) queryResult.Param, CurrentTransaction).ToList();
    }
 
    /// <summary>
    ///Conditional access record first or default
    /// </summary>
    ///< returns > record < / returns >
    ///< param name = "predict" > filter criteria. < / param >
    public T GetFirstOrDefault<T>(Expression<Func<T, bool>> predicate = null)
        where T : ModelBase
    {
        var tableName = GetTableName<T>();
        var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
 
        return CurrentConnection.QueryFirst<T>(queryResult.Sql, (object) queryResult.Param, CurrentTransaction);
    }
 
    /// <summary>
    ///Condition query
    /// </summary>
    /// <returns>IQueryable</returns>
    ///< param name = "predict" > filter criteria. < / param >
    public IQueryable<T> Query<T>(Expression<Func<T, bool>> predicate = null)
        where T : ModelBase
    {
        throw new NotImplementedException();
    }
 
    /// <summary>
    ///Rollback transaction explicitly, only useful after transaction is opened explicitly
    /// </summary>
    public void Rollback()
    {
        if (TransactionEnabled) CurrentTransaction.Rollback();
    }
 
    /// <summary>
    ///Execute Sql script query
    /// </summary>
    ///< param name = "SQL" > sql statement < / param >
    ///< param name = "parameters" > parameters < / param >
    ///< returns > set < / returns >
    public IEnumerable<T> SqlQuery<T>(string sql, IDbDataParameter[] parameters)
    {
        ValidateOperator.Begin()
            .NotNullOrEmpty(sql, "Sql Sentence");
        var dataParameters = CreateParameter(parameters);
        return CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction);
    }
 
    /// <summary>
    ///According to the records
    /// </summary>
    ///Whether the operation is successful
    ///< param name = "entity" > entity class record. < / param >
    public bool Update<T>(T entity)
        where T : ModelBase
    {
        ValidateOperator.Begin().NotNull(entity, "Data records to be updated");
        return CurrentConnection.Update(entity, CurrentTransaction);
    }
 
    private DapperParameter CreateParameter(IDbDataParameter[] parameters)
    {
        if (!(parameters?.Any() ?? false)) return null;
 
        var dataParameters = new DapperParameter();
        foreach (var parameter in parameters) dataParameters.Add(parameter);
        return dataParameters;
    }
 
    private string GetTableName<T>()
        where T : ModelBase
    {
        var tableCfgInfo = AttributeHelper.Get<T, TableAttribute>();
        return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name;
    }
 
    #endregion Methods
}

Usage method

public class SampleService : ISampleService
{
    private readonly IDatabaseContextFactory _contextFactory;
 
    public SampleService(IDatabaseContextFactory contextFactory)
    {
        _contextFactory = contextFactory;
    }
 
    /// <summary>
    /// create
    /// </summary>
    /// <param name="sample">EFSample</param>
    /// <returns></returns>
    public bool Create(EfSample sample)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.Create(sample);
        }
    }
 
    /// <summary>
    ///Condition query
    /// </summary>
    /// <param name="predicate">The predicate.</param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public EfSample GetFirstOrDefault(Expression<Func<EfSample, bool>> predicate = null)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.GetFirstOrDefault(predicate);
        }
    }
 
    /// <summary>
    ///Query by primary key
    /// </summary>
    /// <param name="id">The identifier.</param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public EfSample GetByKeyId(Guid id)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.GetByKeyId<EfSample>(id);
        }
    }
 
    /// <summary>
    ///Condition query collection
    /// </summary>
    /// <param name="predicate">The predicate.</param>
    /// <returns></returns>
    public List<EfSample> GetList(Expression<Func<EfSample, bool>> predicate = null)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.GetList(predicate);
        }
    }
 
    /// <summary>
    ///Add to judge whether it exists
    /// </summary>
    /// <param name="predicate">The predicate.</param>
    /// <returns></returns>
    public bool Exist(Expression<Func<EfSample, bool>> predicate = null)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.Exist(predicate);
        }
    }
 
    /// <summary>
    ///Script query
    /// </summary>
    /// <param name="sql">The SQL.</param>
    /// <param name="parameter">DbParameter[]</param>
    /// <returns></returns>
    public List<EfSample> SqlQuery(string sql, DbParameter[] parameter)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.SqlQuery<EfSample>(sql, parameter)?.ToList();
        }
    }
 
    /// <summary>
    /// update
    /// </summary>
    /// <param name="sample">The sample.</param>
    /// <returns></returns>
    public bool Update(EfSample sample)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.Update(sample);
        }
    }
 
    /// <summary>
    //Affairs
    /// </summary>
    /// <param name="sample">The sample.</param>
    /// <param name="sample2">The sample2.</param>
    /// <returns></returns>
    public bool CreateWithTransaction(EfSample sample, EfSample sample2)
    {
        bool result;
        using (IDbContext context = _contextFactory.Create())
        {
            try
            {
                context.BeginTransaction();//Open transaction
                context.Create(sample);
                context.Create(sample2);
                context.Commit();
                result = true;
            }
            catch (Exception)
            {
                context.Rollback();
                result = false;
            }
        }
 
        return result;
    }
 
    /// <summary>
    /// delete
    /// </summary>
    /// <param name="sample"></param>
    /// <returns></returns>
    public bool Delete(EfSample sample)
    {
        using (IDbContext context = _contextFactory.Create())
        {
            return context.Delete(sample);
        }
    }
}

epilogue

1. Dapper and Entity Framework are implemented through IRepository, so you can switch through Ioc;
2. The unit test method of this article is consistent with that of the previous article;
3. Little brother is not talented, big man taps lightly;