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.