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)
-
0
Bump! Halil - any insights you can offer here?
-
0
You have two options:
- 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.
- 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.
-
0
Hi there,
Can you be more specific?
-
0
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?
-
0
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)
