MVC 6 How can I include a BaseRepository in my controller class

asp.net-core asp.net-core-mvc asp.net-mvc dapper

Question

I am using an ORM to connect to the database it is called dapper. The issue with it is that it's database calls are synchronous and I recently found a way to make it asynchronous by following this short tutorial http://www.joesauve.com/async-dapper-and-async-sql-connection-management/ . My question is how can I bring this BaseRepository into my Controller class ? This is the code on that website and it's the same one I have

BaseRepository- by the way there is no issue in this code

 public abstract class BaseRepository 
   {

private readonly string _ConnectionString;

protected BaseRepository(string connectionString) 
{
    _ConnectionString = connectionString;
}

protected async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData) 
{
    try {
        using (var connection = new SqlConnection(_ConnectionString)) {
            await connection.OpenAsync(); // Asynchronously open a connection to the database
            return await getData(connection); // Asynchronously execute getData, which has been passed in as a Func<IDBConnection, Task<T>>
        }
    }
    catch (TimeoutException ex) {
        throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
    }
    catch (SqlException ex) {
        throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
    }
}

}

and now he brings it in like this

public class PersonRepository : BaseRepository
{
    public PersonRepository(string connectionString): base (connectionString) { }

    public async Task<Person> GetPersonById(Guid Id)
    {
        return await WithConnection(async c => {

            // Here's all the same data access code,
            // albeit now it's async, and nicely wrapped
            // in this handy WithConnection() call.
            var p = new DynamicParameters();
            p.Add("Id", Id, DbType.Guid);
            var people = await c.QueryAsync<Person>(
                sql: "sp_Person_GetById", 
                param: p, 
                commandType: CommandType.StoredProcedure);
            return people.FirstOrDefault();

        });
    }
}

The part I am having a problem with is this public class PersonRepository : BaseRepository because Asp.Net Controllers start with public class HomeController: Controller , I need access to the WithConnection method to get this working. My controller looks like this

 public class HomeController : Controller
    {

        public class ConnectionRepository : BaseRepository
        {
            public ConnectionRepository(string connectionString) : base(connectionString) { }

        }
      public async Task<ActionResult> topfive()
            {
    // I get Error on WithConnection as it can't see the BaseRepository
                return await WithConnection(async c =>
                {


                    var topfive = await c.QueryAsync<Streams>("select * from streams ").ToList();
                return View(topfive);
                });

 }
            }

I obviously can not cover my ActionResult method with the BaseRepository because it gives all types of errors any suggestions ?

Accepted Answer

Why are you using inheritance instead of composition? What about something like:

public class PersonRepository : BaseRepository
{
    public PersonRepository(string connectionString): base (connectionString) { }

    public async Task<Person> GetPersonById(Guid Id)
    {
        return await WithConnection(async c => {

            // Here's all the same data access code,
            // albeit now it's async, and nicely wrapped
            // in this handy WithConnection() call.
            var p = new DynamicParameters();
            p.Add("Id", Id, DbType.Guid);
            var people = await c.QueryAsync<Person>(
                sql: "sp_Person_GetById", 
                param: p, 
                commandType: CommandType.StoredProcedure);
            return people.FirstOrDefault();

        });
    }
    }

        public class ConnectionRepository : BaseRepository
        {
            public ConnectionRepository(string connectionString) : base(connectionString) { }

        }
      public async Task<List<TopFileClass>> topfive()
            {
    // I get Error on WithConnection as it can't see the BaseRepository
                return await WithConnection(async c =>
                {


                    var topfive = await c.QueryAsync<Streams>("select * from streams ").ToList();
                return topfive;
                });

 }

public class HomeController : Controller
{
   private readonly PersonRepository _repo;

   public HomeController(PersonRepository repo)
   {
       _repo = repo;
   }

   public async Task<ActionResult> TopFive() 
   {
       var top5 = await _repo.topfive();
       return View(top5);
   }
}

If you are not familiar how to make the repository automatically get injected into the constructor, read up on dependency injection in MVC 6.


Popular Answer

you have to intehirt the "BaseRepository" from "Controller". i think that will work for you. then just go with below code:

public abstract class BaseRepository : Controller 
{ 
// do you work
}


public class PersonRepository : BaseRepository
{
public PersonRepository(string connectionString): base (connectionString) { }

public async Task<Person> GetPersonById(Guid Id)
{
    return await WithConnection(async c => {

        // Here's all the same data access code,
        // albeit now it's async, and nicely wrapped
        // in this handy WithConnection() call.
        var p = new DynamicParameters();
        p.Add("Id", Id, DbType.Guid);
        var people = await c.QueryAsync<Person>(
            sql: "sp_Person_GetById", 
            param: p, 
            commandType: CommandType.StoredProcedure);
        return people.FirstOrDefault();

    });
   }
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why