Use stored procedures for multi-tenant specific database #11758

Currently our application is a multi tenant with single host database. But now we are switching to multi database i.e. tenant specific database. We were using stored procedures to perform task. I have a StoredProcedureCalling.cs class. So my doubt is, what changes are required to be made in my StoredProcedureCalling.cs for multi-database so that during execution it will use the tenant specific database.

Below is the definition of StoredProcedureCalling.cs

using Abp.Data; using Abp.EntityFrameworkCore; using Microsoft.Data.SqlClient; using System.Data; using System.Data.Common; using CDP.EntityFrameworkCore.Repositories; using CDP.EntityFrameworkCore; using Microsoft.EntityFrameworkCore; using Abp.Domain.Entities; using System.Threading.Tasks;

namespace CDP.StoredProcedures { public class StoredProcedureCalling<TEntity, TPrimaryKey> : CDPRepositoryBase<TEntity, TPrimaryKey>, IStoredProcedureCalling<TEntity, TPrimaryKey> where TEntity : class, IEntity<TPrimaryKey> { private readonly IActiveTransactionProvider _transactionProvider;

    public StoredProcedureCalling(IDbContextProvider<CDPDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
        : base(dbContextProvider)
    {
        _transactionProvider = transactionProvider;
    }

    public DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
    {
        var command = Context.Database.GetDbConnection().CreateCommand();

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

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

        return command;
    }

    public void EnsureConnectionOpen()
    {
        var connection = Context.Database.GetDbConnection();

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

    public DbTransaction GetActiveTransaction()
    {
        return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
        {
            {"ContextType", typeof(CDPDbContext) },
            {"MultiTenancySide", MultiTenancySide }
        });
    }

}

}

And I have ActionItemAppService where I am calling the procedure

public class ActionItemsAppService : CDPAppServiceBase, IActionItemsAppService { private readonly IStoredProcedureCalling<Tenant, int> _storedProcedureCalling; public ActionItemsAppService(IDbContextProvider<CDPDbContext> dbContextProvider,IActiveTransactionProvider transactionProvider) { _storedProcedureCalling = new StoredProcedureCalling<Tenant, int>(dbContextProvider, transactionProvider); }

 public async Task<List<GetActionItemsForViewDto>> GetAllActionItemsList(GetAllActionItemsInput input)
    {

        List<GetActionItemsForViewDto> ActionItemList = new List<GetActionItemsForViewDto>();

        _storedProcedureCalling.EnsureConnectionOpen();
        List<IDataRecord> outputPostDataReader = new List<IDataRecord>();

        SqlParameter[] parameters = new SqlParameter[]
        {
             new SqlParameter(){ Direction=ParameterDirection.Input, SqlDbType =SqlDbType.BigInt,ParameterName="@TenantID",Value=AbpSession.TenantId},
         };

        try
        {

            using (var command = _storedProcedureCalling.CreateCommand("GetAllActionItems", CommandType.StoredProcedure, parameters))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {
                    if (dataReader.HasRows)
                    {
                        outputPostDataReader = dataReader.Cast<IDataRecord>().ToList();
                    }

                }

                foreach (var user in outputPostDataReader)
                {
                    var ActionItem = new GetActionItemsForViewDto
                    {
                        Id = user.GetInt64(user.GetOrdinal("Id")),
                        Title = user.GetString(user.GetOrdinal("Title")),
                    };
                    ActionItemList.Add(ActionItem);
                }

            }
        }
        catch (Exception ex)
        {
            throw new UserFriendlyException(ex.Message + " " + ex.StackTrace);
        }

        return ActionItemList;
    }

}