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.

Go to accepted answer
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)

Assignee
No one
Labels
None yet