Repository for Dapper.net

Hi all,

I've been using Nop + dapper.net for months in replace of entity framework. I share the repository here and hope someone could improve it if you're interested in.


public partial interface IRepository
{

    T GetById<T>(object id) where T : BaseEntity, new();      
    IEnumerable<T> GetWhere<T>(object filters) where T : BaseEntity, new();

    IQueryable<T> Table<T>() where T : BaseEntity, new();

    IEnumerable<TElement> SqlQuery<TElement>(string sql, DynamicParameters parameters = null);      
    void Insert<T>(T instance) where T : BaseEntity, new();
    void Update<T>(T instance) where T : BaseEntity, new();
    void Delete<T>(T instance) where T : BaseEntity, new();

    void ExecuteSqlCommand(string sql, DynamicParameters parameters = null, bool doNotEnsureTransaction = false, int? timeout = null);
    IEnumerable<T> ExecuteStoredProcedureList<T>(string commandText, DynamicParameters parameters = null) where T : BaseEntity, new();

Tuple<IEnumerable<T1>, IEnumerable<T2>> MultiResults<T1, T2>(string sql, DynamicParameters parameters = null);  
}

///////////////////////////////////////////////////////////////////////////////////////////////////////

  public class DataConnection : IDisposable
    {
        #region Properties

        /// <summary>
        ///
        /// </summary>
        private IDbConnection _connection;

        /// <summary>
        ///
        /// </summary>
        protected IDbConnection Connection
        {
            get
            {
                if (_connection.State != ConnectionState.Open && _connection.State != ConnectionState.Connecting)
                    _connection.Open();

                return _connection;
            }
        }

        #endregion

        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        public DataConnection(IDbConnection connection)
        {
            _connection = connection;
        }

        /// <summary>
        /// Close the connection if this is open
        /// </summary>
        public void Dispose()
        {
            if (_connection != null && _connection.State != ConnectionState.Closed)
                _connection.Close();
        }
    }

for auto sql generator, i use https://github.com/Yoinbol/MicroOrm.Pocos.SqlGenerator, so my implementing repo class
becomes:


  public class DapperRepository : DataConnection, IRepository
{

    public DapperRepository(IDbConnection connection)
        : base(connection)
    {
    }


    public IEnumerable<T> GetWhere<T>(object filters) where T : BaseEntity, new()
    {
        //Creates the sql generator
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();

        //Creates the query
        var query = sqlGenerator.GetSelect(filters);

        //Execute the query
        return Connection.Query<T>(query, filters);
    }


    public IQueryable<T> Table<T>() where T : BaseEntity, new()
    {
        //Creates the sql generator
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();

        //Creates the query
        var query = sqlGenerator.GetSelectAll();

        //Execute the query
        return Connection.Query<T>(query).AsQueryable();
    }      

    public void ExecuteSqlCommand(string sql, DynamicParameters parameters=null, bool doNotEnsureTransaction = false, int? timeout = null)
    {
        using (var transaction = Connection.BeginTransaction())
        {
            Connection.Execute(sql, parameters, transaction);
        }

    }
    public IEnumerable<TElement> SqlQuery<TElement>(string sql, DynamicParameters parameters=null)
    {
        return Connection.Query<TElement>(sql, parameters);
    }
    public IEnumerable<T> ExecuteStoredProcedureList<T>(string commandText, DynamicParameters parameters=null) where T : BaseEntity, new()
    {                        
        var result = this.Connection.Query<T>(commandText, parameters, commandType: CommandType.StoredProcedure).ToList();

        return result;
    }

    public T GetById<T>(object filters) where T : BaseEntity, new()
    {
        //Creates the sql generator
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();

        //Creates the query
        var query = sqlGenerator.GetSelect(filters);

        //Execute the query
        return Connection.Query<T>(query, filters).FirstOrDefault();
    }    
    public void Insert<T>(T instance) where T : BaseEntity, new()
    {
        bool added = false;
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();
        var sql = sqlGenerator.GetInsert();

        if (sqlGenerator.IsIdentity)
        {
            var newId = Connection.Query<decimal>(sql, instance).Single();
            added = newId > 0;

            if (added)
            {
                var newParsedId = Convert.ChangeType(newId, sqlGenerator.IdentityProperty.PropertyInfo.PropertyType);
                sqlGenerator.IdentityProperty.PropertyInfo.SetValue(instance, newParsedId);
            }
        }
        else
        {
            added = Connection.Execute(sql, instance) > 0;
        }

        //return added;

    }

    public void Update<T>(T instance) where T : BaseEntity, new()
    {
        //Creates the sql generator
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();

        //Creates the query
        var query = sqlGenerator.GetUpdate();

        //Execute the query
        Connection.Execute(query, instance);
    }
    public void Delete<T>(T instance) where T : BaseEntity, new()
    {
        //Creates the sql generator
        var sqlGenerator = EngineContext.Current.Resolve<ISqlGenerator<T>>();

        //Creates the query
        var query = sqlGenerator.GetDelete();

        //Execute the query
        Connection.Execute(query, instance);
    }
public Tuple<IEnumerable<T1>, IEnumerable<T2>> MultiResults<T1, T2>(string sql, DynamicParameters parameters = null)
        {
            using (var multi = Connection.QueryMultiple(sql, parameters))
            {
                return new Tuple<IEnumerable<T1>, IEnumerable<T2>>(
                    multi.Read<T1>(),
                    multi.Read<T2>());
            }
        }
}


I still don't know how to implement Multi Mapping queries to the repo.

Thank you. Is this code ready for production ?

Thanks a lot for this contribution! Although there are no plans to move to Dapper in the near time please find and vote for this work item here

I f you want a professionnel solution that really supports high traffic

you need to do all data acces with with stored proc
call them with ado.net or micro orm like Dapper and then map them to entities

riaadh wrote:

And i recoomend to have a IRepository/Repository for each entity

Alse do Insert list elements with Table value parameters in sql not a foreach

Personnaly I can do that and share it to the community but i don"t know where to start ? and who should i contact from the developper team

As nop developer stated numerous times they dont have any interest in near future to use something else apart from entity framework so this changes will be only community based , the only thing is to open an experimental branch on github and do changes there so who is interest can contribute to the project or fork the project. Generally the interest is very low to use something different from entity framework and it makes sense why.

In nop 3.6, I think Nop team has done a great job to boost the performance in terms of speed and memory usage, so just be patient for what comes next in EF 7 and vNext .net. They should be faster than ever.

Thing that concerns me most right now is the database engine, Should we stick to a pricey database engine or move on to the current trend, Nosql which are Mongodb, Cassandra, or PostgreSQL. They're fast and free.

http://db-engines.com/en/ranking is an interesting ranking table, showing how fast NoSQL mongodb, postgre... moving up to close the gap in popularity rank against big guns Oracle, SQL Server...

With .net vNext running every where, imagine Nopcommerce running on Linux server with Mongodb, it's just so cool.

@congthanhgiong Were you able to complete conversion to Dapper?

congthanhgiong wrote:

Thank you for sharing your solution. I have finished my data layer. However, I have a question. How do you deal with the problem of lazyload? Thank you.

string sql = "SELECT * FROM Invoice AS A INNER JOIN InvoiceDetail AS B ON A.InvoiceID = B.InvoiceID;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
            sql,
            (invoice, invoiceDetail) =>
            {
                invoice.InvoiceDetail = invoiceDetail;
                return invoice;
            },
            splitOn: "InvoiceID")
        .Distinct()
        .ToList();
}