How to call a SQL stored proc and UDF from an app service #3001

6 Answer(s)

• 

  0

  ismcagdas created 7 years ago

  Support Team

  Hi,

  In order to access DbContext you need to define a custom repository, see <a class="postlink" href="https://aspnetboilerplate.com/Pages/Documents/Repositories#custom-repositories">https://aspnetboilerplate.com/Pages/Doc ... positories</a>.

  Save Cancel
• 

  0

  sparkyjr created 7 years ago

  Hi ismcagdas,

  Thanks for your reply!

  I tried to understand from the documentation link you had in your reply. But, I do not want to add a repository for an entity. I want to call a SQL Stored Proc and UDF from App Service. The Stored Proc is not specific to a single entity or a SQL table.

  It would be great if you could provide me a good example which shows how exactly you call a SQL Stored Proc and UDF from App Service.

  Regards, SparkyJr

  Save Cancel
• 

  0

  alirizaadiyahsi created 7 years ago

  Hi,

  It is not possible to call it from AppService. Because you need to access to DbContext. You don't need to define CustomRepository that is releated an entity. If you want to know how to call a SP from a customRepository, we can provide an example.

  Save Cancel
• 

  0

  sparkyjr created 7 years ago

  Thanks alirizaadiyahsi for your reply!

  The Custom Repository has to be extended from either IRepository<TEntity> or IRepository<TEntity, TPrimaryKey>, am I right? What is TEntity in my case? Because Stored Proc doesn't depend on any specific Entity or SQL table. It will play with multiple SQL tables and return a result-set(s). How do I achieve this? Or am I missing something?

  Also, please send me your example for calling the Stored Proc from a Custom Repository. That may help!

  Regards, SparkyJr

  Save Cancel
• 

  0

  ismcagdas created 7 years ago

  Support Team

  Hi @sparkyjr,

  Sorry for the wrong information, I have told @alirizaadiyahsi to write this answer but I realized that you are right. In the current design, the only way to call a SP is to create a custom repository as far as I know.

  So, you need to choose one entity to define your custom repository, you can select the main entity or if there is an aggregate root entity, you can select that one to make it more meaningful.

  But you can return a custom type from your custom repository, it will not be a problem. You can define your custom repository like this.

  public interface IReportRepository : IRepository<User, long>
  {
      List<GetReportOutput> GetReport(GetReportInput input);
  }
  

  Then you can implement it like this.

  public class ReportRepository : YourRepositoryBase<User, long>, IReportRepository
  {
      public List<GetReportOutput> GetReport(GetReportInput missing_name)
      {
          return Context.Database.SqlQuery<List<YourCustomType>>("storedProcedureName",params);
      }
  
      public ReportRepository(IDbContextProvider<YourDbContext> dbContextProvider) : base(dbContextProvider)
      {
      }
  }
  

  Save Cancel
• 

  0

  sparkyjr created 7 years ago

  Thanks a lot for your solution!

  Save Cancel