Base solution for your next web application
Open Closed

Where to place access to Stored Procedures? #446


User avatar
0
theedge created

Hi,

  • Assume I have 3 tables named TableA, TableB, TableC
  • And a stored proc name StoredProc that makes use of all 3 tables with some form of JOIN

I would now like to access the StoredProc in my implementation of IApplicationService from the service layer.

So typically I have done:

public interface ITableARepository : IRepository<TableA, long>
 {
     //Internally uses DbContext to call the SP  
      void CallStoredProc();
 }

public interface IMyService : IApplicationService
 {
        void CallMyStoredProc();
}

public class MyService : IMyService
{   
  private  ITableARepository _TableArepository; 

  public MyService(ITableARepository aTableARepository)    
   {
         _TableARepository = aTableARepository;
   }

          public void CallMyStoredProc()
         {
                _TableARepository.CallStoredProc()
         }
}

However as the stored proc can access all the 3 tables invariably someone goes looking for it off TableBRepository or TableCRepository instead of TableARepository. As I don't want to pass a DBContext to my application service layer as that is a concrete and not an interface, what is a better way to do this?

Namely what is a "good" way to allow access to stored procedures without "tying" them arbitrarily to a repository interface? All the examples on ABP don't talk about stored procedures so I am looking for some guidance.

All pointers and suggestions appreciated.


5 Answer(s)
  • User Avatar
    0
    theedge created

    Bump! Halil - any insights you can offer here?

  • User Avatar
    0
    hikalkan created
    Support Team

    You have two options:

    1. Put your method into a repository. I suggest this. Select repository which is more appropriate. For example we have Person and Phone. And a SP named "GetPhonesIncludingPerson". So, this should go to PhoneRepository.
    2. You create a dedicated class inside your data layer (EF project) to call SPs. Inject IDbContextProvider<YourDbContext> dbContextProvider to this new class.

    I don't know a better way.

  • User Avatar
    0
    tarcisis created

    Hi there,

    Can you be more specific?

  • User Avatar
    0
    nobruds created

    I think he means like this: (i do like this)

    public interface ICustomRepository : IRepository<YourEntity>
    {
            void CustomMethodProcCall_1(int someParameter);
            void CustomMethodProcCall_2();
    }
    
    public class CustomRepository: NhRepositoryBase<YourEntity>, ICustomRepository 
    {
            private readonly string _procExecCommand01 = "Exec procName @Parameter = N'{0}' ";
            private readonly string _procExecCommand02 = "Exec procName02";
    
            public CustomRepository(ISessionProvider sessionProvider)
                : base(sessionProvider)
            {
            }
    
            public void CustomMethodProcCall_1(int someParameter)
            {
                    var sqlQuery = string.Format(_procExecCommand01, someParameter);
                    Session.CreateSQLQuery(sqlQuery).ExecuteUpdate();
            }
            public void CustomMethodProcCall_2()
            {
                    Session.CreateSQLQuery(_procExecCommand02).ExecuteUpdate();
            }
    
    }
    

    <cite>Tarcisis: </cite> Hi there,

    Can you be more specific?

  • User Avatar
    0
    daws created

    For Stored procedure in SQL Server, i use this nuget <a class="postlink" href="https://www.nuget.org/packages/EntityFrameworkExtras.EF6/">https://www.nuget.org/packages/EntityFr ... xtras.EF6/</a>

    and place my call to SP into my datalayer (custom repository)