Question about Custom Repository #7161

maharatha created 4 years ago

I have two database context , Context A and Comntext B The Context A is for my Abp Project and Context B is for a separete database. Both are in MS SQL Server.

I want to run stored procedure in the database for Context B. How do I run the stored procedure and get the result ?

I saw the repositorybase class under EntityFrameworkcore project and found the below comment :

//add your common methods for all repositories

Then I refered the link below

https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html

Do I still have to write the repsoitory class and tthe helper class mentioned in the link ?

I need some directions.

I am using .NET Core

8 Answer(s)

0
ryancyq created 4 years ago
Support Team
Hi @maharatha,

if you want to create new custom reposotory with new interface, the steps in the article you mentioned are required.

for example, if you want to call your custom method on repository, something like

class MyClass { private IMyCustomRepository _myCustom; public void MyAction() { _myCustom.MyCustomRepositortAction(); } }

however, if you want to change the implementation of some public methods, for example,

changing GetAll() to store procedure

class MyProjectRepositoryBase<TEntity> { public IQueryable<TEntity> GetAll() { return GetAllByStoreProcedure(); } // add your common methods for all repositories protected IQuerable<TEntity> GetAllByStoreProcedure() { return null; } }

and calling _myCustom.GetAll() now will be calling store procedure instead of ef core query.

the above is just an example of a possible use case of the common methods
0

maliming created 4 years ago
Support Team

https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html

The introduction in this article is a good suggestion, you can try it. If you encounter problems, you can feedback.
0

maharatha created 4 years ago

I am sorry but i am still not able to proceed far. I think I am unable to understand the concepts here. Let me put that into some perspective :

I have two DBContext Context A and Context B. Context A is Abp database and Context B is let's say Adventureworks database.

I need to call a stoired procedure in Adventureworks database.

The article written in the link talks about using repository pattern accessing a table and getting a defined result. What I plan to do is build an interface where users are going to declare the stored procedure name, and the parameters and I should be able to execute this in Adventureworks and get the result back.

I tried creating a AdventureRepository base but couldn't move forward. Can you please me further, i am kind of stuck here?
0

ryancyq created 4 years ago
Support Team

What I plan to do is build an interface where users are going to declare the stored procedure name, and the parameters and I should be able to execute this in Adventureworks and get the result back.

Repository base in ANZ was designed with a single and primary entity in mind. In you case (for handle any kind of entity), you can consider just implementing a service to handle these instead of repository

I tried creating a AdventureRepository base but couldn't move forward

also, can you share the issues/code snippets that are not working for you?

maharatha created 4 years ago

Below is the class I wrote in EFCore :

using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using Abp.Data;
using Abp.Dependency;
using Microsoft.AspNetCore.Hosting.Internal;
using Microsoft.EntityFrameworkCore;

namespace YYY.AAA.EntityFrameworkCore
{
    public class xxxxDBHelperMethods : ITransientDependency
    {
        private readonly IActiveTransactionProvider _transactionProvider;
        private readonly xxxxDbContext _xxxxDbContext;

        public xxxxDBHelperMethods(IActiveTransactionProvider transactionProvider, xxxxDbContext xxxxDbContext)
        {
            _transactionProvider = transactionProvider;
            _xxxxDbContext = xxxxDbContext;
        }

        public async Task<ArrayList> GetStoredProcedureResult(string storedProcedureName, SqlParameter[] parameters)
        {
            EnsureConnectionOpen();
            ArrayList rowList = new ArrayList();

            using (var command = CreateCommand(storedProcedureName, CommandType.StoredProcedure, parameters))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {


                    while (dataReader.Read())
                    {
                        object[] values = new object[dataReader.FieldCount];
                        dataReader.GetValues(values);
                        rowList.Add(values);
                    }

                    return rowList;
                }
            }
        }
        private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var command = _xxxxDbContext.Database.GetDbConnection().CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = GetActiveTransaction();

            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        private void EnsureConnectionOpen()
        {
            var connection = _xxxxDbContext.Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(xxxxDbContext) }

            });
        }
    }
}

Below is the class I wrote in my Application Project :

using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Threading.Tasks;
using YYY.AAA.EntityFrameworkCore;

namespace YYY.AAA.Reporting
{
    public class ReportingAppServices : AAAAppServiceBase, IReportingAppServices
    {
        private readonly xxxxDBHelperMethods _xxxxDbHelperMethods;

        public ReportingAppServices(xxxxDBHelperMethods xxxxDbHelperMethods)
        {
            _xxxxDbHelperMethods = xxxxDbHelperMethods;

        }

        public Task<ArrayList> GetReportDataFromxxxx(int id)
        {

            SqlParameter param = new SqlParameter { ParameterName = "@id", Value = id, };

            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = param;
            return _xxxxDbHelperMethods.GetStoredProcedureResult("[xxxx].[TestStoredProcedure]", parameters);
        }
    }
}

This is a test, and I am getting the below error :

2019-06-27 16:35:27,613 [6 ] Mvc.ExceptionHandling.AbpExceptionFilter - Can't create component 'CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext' as it has dependencies to be satisfied. 'CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext' is waiting for the following dependencies:

Service 'Microsoft.EntityFrameworkCore.DbContextOptions`1[[CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext, CASTANDCREW.FMS.EntityFrameworkCore, Version=6.4.0.0, Culture=neutral, PublicKeyToken=null]]' which was not registered. Castle.MicroKernel.Handlers.HandlerException: Can't create component 'CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext' as it has dependencies to be satisfied. 'CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext' is waiting for the following dependencies:
Service 'Microsoft.EntityFrameworkCore.DbContextOptions`1[[CASTANDCREW.FMS.EntityFrameworkCore.CAPSPayDbContext, CASTANDCREW.FMS.EntityFrameworkCore, Version=6.4.0.0, Culture=neutral, PublicKeyToken=null]]' which was not registered.

Is this the rightway to do this ? and why am I getting dependency injection error

0

ismcagdas created 4 years ago
Support Team

Hi @maharatha

Have you seen this sample ? https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/MultipleDbContextEfCoreDemo

This might help you.

maharatha created 4 years ago

Thank you @ismcagdas. But I am already using a secondary db context to get data. I figured the problem with the dependency Injection.

The last piece i am unable to figure out is the GetActiveTransaction() part.

       public class SecondaryDBDBHelperMethods : ITransientDependency
    {
        private readonly IActiveTransactionProvider _transactionProvider;
        private readonly IDbContextProvider<SecondaryDBDbContext> _SecondaryDBDbContext;

        public SecondaryDBDBHelperMethods(IActiveTransactionProvider transactionProvider, IDbContextProvider<SecondaryDBDbContext> SecondaryDBDbContext)
        {
            _transactionProvider = transactionProvider;
            _SecondaryDBDbContext = SecondaryDBDbContext;
        }

        public async Task<ArrayList> GetStoredProcedureResult(string storedProcedureName, SqlParameter[] parameters)
        {
            EnsureConnectionOpen();
            ArrayList rowList = new ArrayList();

            using (var command = CreateCommand(storedProcedureName, CommandType.StoredProcedure, parameters))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {


                    while (dataReader.Read())
                    {
                        object[] values = new object[dataReader.FieldCount];
                        dataReader.GetValues(values);
                        rowList.Add(values);
                    }

                    return rowList;
                }
            }
        }
        private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var command = _SecondaryDBDbContext.GetDbContext().Database.GetDbConnection().CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;
           // command.Transaction = GetActiveTransaction();

            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        private void EnsureConnectionOpen()
        {
            var connection = _SecondaryDBDbContext.GetDbContext().Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(SecondarybContext) },
                {"MultiTenancySide", MultiTenancySide }

            });
        }
    }

How can i get a ActiveTransaction from the secondary database ? Or do I need to change something

0

ismcagdas created 4 years ago
Support Team

Hi @maharatha

Is it possible to share a sample project with us ?

Have an answer to this question? Log in and write your answer.