locked
Ling to Sql OR stored procedures In Generics Repository for database interaction? RRS feed

  • Question

  • User1765474751 posted

    As generic repositories (Ling to Sql) can be use for Insert, Update, Delete etc. but it is limited only for simple Crud operations, if I want to use my generic repositories for some more complex insertion and searching like joining of tables and retrieve by stored procedures. And insert data to multiple tables using stored procedures so how will I handle it from generic repositories?

    1. Can I call stored procedures from my generic repository?

    2. Is it a good idea to use generic repositories + stored procedures?

    3. Will it compatible for criteria base complex search?

    Sunday, September 20, 2015 11:18 AM

All replies

  • User-219423983 posted

    Hi Riaz_Khan,

    About your needs, I think you could use the SP with generic repositories. As the second thread shows, you could create a function as below.

    public IEnumerable<T> ExecWithStoreProcedure(string query, params object[] parameters)
    {
            return _context.Database.SqlQuery<T>(query, parameters);
    }
    

    When you want to use, you could use the following code.

    IEnumerable<Products> products = 
                 _unitOfWork.ProductRepository.ExecWithStoreProcedure(
                 "spGetProducts @bigCategoryId",
                 new SqlParameter("bigCategoryId", SqlDbType.BigInt) { Value = categoryId } 
          );

    http://stackoverflow.com/questions/9333115/repository-pattern-and-stored-procedure

    http://stackoverflow.com/questions/18201646/repository-pattern-to-execute-a-stored-procedure-using-entity-framework

    I hope it’s useful to you.

    Best Regards,

    Weibo Zhang

    Monday, September 21, 2015 5:38 AM
  • User1765474751 posted

    Thanks for your Reply.
    But i don't want to Mention SP name in my controller. i want some generic functions for calling stored procedures as  we use in Generics Repository.
    <TEntity> GetAll<>
    so it can recognize the table name.
    I want jus like this.

    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);

    I want just like this.. But it is in Simple Repository . I want the same in Generics Repository.
    and 
    what will be my //EF Code if I provide SP name  here related to TEntity.? 
    as we simply do in generics repository as like this

    internal void Add<T>(T entity) 
    {
    dbSet.Add(entity);
    }

    it can recognize the insertion will be in table of TEntity.
    but If i want to call any Dynamic SP related to TEntity, so how will it possible.? 

    Thursday, October 1, 2015 5:30 AM