Wanted to execute Stored Procedure in Generic Repository ?

If you are familiar with Repository and Generic repository pattern then you must know the philosophy behind those patterns.

Yes, they create abstraction on top of data access mechanism and helps on CRUD for particular model. If it is generic Repository the one class serves CRUD functionality for all Entity. Now the question is , What If we want to execute stored procedure in Generic Repository. In my experience I never seen any function to execute stored procedure in Repository pattern, may be “People don’t want to fire query on top of Context directly”

That is the reason to create abstraction over DB, which we call as Repository but I think, this is not much harmful to access SP from generic repository. The choice of implementation is yours. Here is very simple was to implement this in generic repository.

Here is partial code to generic repository class. Have a look that SQLQuery function is ready to take sql query and it will return DbRawSqlQuery<T> object.

class GenericRepository<T> : IGenericRepository<T> where T : class

    {

        private ProjectDbContext entities = null;

        DbSet<T> _objectSet;

        public GenericRepository(ProjectDbContext _entities)

        {

            entities = _entities;

            _objectSet = entities.Set<T>();

        }

        public DbRawSqlQuery<T> SQLQuery<T>(string sql, params object[] parameters)

        {

            return entities.Database.SqlQuery<T>(sql, parameters);

        }

}

Location of DbRawSqlQuery is  System.Data.Entity.Infrastructure;

Here is Controller to Execute SQLQuery function.

public class CompanyController : Controller

    {

        private GenericUnitOfWork Uow;

        public CompanyController()

        {

            this.Uow = new GenericUnitOfWork();

        }

        public void ExecuteSP()

        {

Uow.Repository<Company>().SQLQuery<Company>("EXEC mySP @p1", new SqlParameter("@p1","value"));

        }

}