Using Generic Repository and Stored Procedures

The purpose of the Repository Pattern is to abstract away the storage & retrieval of data to protect your client code e.g. business layer (service layer in your case) from needing to know anything about how data is persisted. SQL statements, for example, would only exist inside your Repository classes, and not ripple throughout your code.

If you expose SQL, Stored Procedure names and parameters to your client code your are not getting much benefit from the Repository Pattern, and if fact you can't really call it a Repository at all. You lose the benefit of being able to mock the repository and test your business layer independently of your data access layer. This means integration tests (requiring a full database instance) are required to verify business logic.

Consider re-factoring so that you have a CountryRepository class which has a GetCountry(int CountryId, DateTime fromDate, DateTime toDate) method that returns a Country entity, or similar. I think you'll agree the readability of your code will be much improved compared to the code in your question.

public class CountryRepository
{
  public Country GetCountry(int CountryId, DateTime fromDate, DateTime toDate)
  {
    // EF or ADO.NET code here
  }
}

Client code would then be e.g.

var c = unitOfWork.CountryRepository.GetCountry(1, DateTime.Now.AddYears(-1), DateTime.Now);

See also this SO question


If you have an entity with those fields you can call SqlQuery method as you show above, if not, then I suggest creating a new class to map the result:

public class Result
{
    public int CountyId { get; set; }

    public DateTime FromDateTime { get; set; }

    public DateTime ToDateTime { get; set; }
}

I don't know how is implemented the UnitOfWork pattern in your case, but I assume that you have access to your Context. In your UnitOfWork class you could create a generic method like this:

public class UnitOfWork 
{
    private YourContext Context { get; set; }

    public DbRawSqlQuery<T> SQLQuery<T>(string sql, params object[] parameters)
    {
       return Context.Database.SqlQuery<T>(sql, parameters);
    }
}

This way, you can execute your store procedures as I show below:

var result= _unitOfWork.SqlQuery<Result>("sp_Get @FromDateTime, @ToDateTime, @CountyId",
                     new SqlParameter("FromDateTime", SqlDbType.DateTime) { Value = Request.FromDateTime },
                     new SqlParameter("ToDateTime", SqlDbType.DateTime) { Value = Request.TripToDateTime },
                     new SqlParameter("CountyId", SqlDbType.Int) { Value = Convert.ToInt32(Request.County) }
       ).ToList();