Open Closed

Possible to connect using two different database management systems in single solution #3579

User avatar
0
AlderCove created

Hi,

Is it possible to connect using two different database management systems within the same solution?

I want to use two different DBs; one SQL Server and one MySQL.

I've been testing an approach but before I go too far down that path, can you tell me if it's even possible?

If so, I'll share the approach I started taking and the issues.

Thanks Jamie

Go to accepted answer
12 Answer(s)
  • User Avatar
    0
    EngincanV created
    Support Team .NET Developer

    Hi, you can set different connection strings for each modules. Please see the Connection Strings docs for that.

    You need to add two infrastructure projects (one for SQL server and the other for MySQL in your case) and implement the repository interfaces and make the related service registrations. Then you should be able to use multiple database providers, without any problem.

  • User Avatar
    0
    AlderCove created

    Ok Great.

    I've taken that approach and when I run the application, the last referenced DependsOn module with a type of EntityFrameworkCoreModule in the Host project seems to determine which database driver to use.

    When the SqlServer module is last, the application starts fine and I don't get any errors until I invoke the application service (from an Application project that references both Domain projects) that uses the MySql repository. The error indicates that a SQL server driver is used when connecting to the MySql database:

    2022-08-18 09:10:48.677 -07:00 [ERR] Keyword not supported: 'port'. System.ArgumentException: Keyword not supported: 'port'. at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword) Here's what I've done so far and the issue I am experiencing:

    When I swap these around and the MySql module is last, the application doesn't start because it attempts to connect to the SqlServer repositories used by the framework using a MySql connection:

    2022-08-18 09:13:24.639 -07:00 [ERR] An exception was thrown while activating Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext -> λ:Microsoft.EntityFrameworkCore.DbContextOptions1[[Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext, Volo.Abp.BackgroundJobs.EntityFrameworkCore, Version=5.1.4.0, Culture=neutral, PublicKeyToken=null]]. Autofac.Core.DependencyResolutionException: An exception was thrown while activating Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext -> λ:Microsoft.EntityFrameworkCore.DbContextOptions1[[Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext, Volo.Abp.BackgroundJobs.EntityFrameworkCore, Version=5.1.4.0, Culture=neutral, PublicKeyToken=null]]. ---> System.ArgumentException: Option 'trusted_connection' not supported. at MySqlConnector.MySqlConnectionStringOption.GetOptionForKey(String key) in /_/src/MySqlConnector/MySqlConnectionStringBuilder.cs:line 927

    In my Host project, I have the connection strings defined:

    In the MySql Domain, Domain.Shared and EntityFrameworkCore projects, I have removed everything except for the minimum package references:

    I am not sure how I can resolve this issue.

    Thanks

  • User Avatar
    0
    AlderCove created

    Any help with this would be greatly appreciated.

    Thanks!

  • User Avatar
    0
    AlderCove created

    Just checking that someone is looking at this for me?

    Thanks

  • User Avatar
    0
    AlderCove created

    Can someone please help me with this?

    It's been more than a week and there's been no further support for this issue.

    It's an urgent one for me now.

    Thanks!

  • User Avatar
    0
    enisn created
    Support Team .NET Developer

    Does the following article help you? https://community.abp.io/posts/configuring-multiple-dbcontexts-in-an-abp-framework-project-uoz5is3o

  • User Avatar
    0
    AlderCove created

    No, I still have the issue.

    The key difference is that the example uses two different database providers (MongoDB and EFCore). Whereas I am trying to use the same database provider (EF Core) with two different database management systems (SQL Server & MySQL).

    My core solution is SQL Server and everything works with that. I am trying to add a MySQL readonly repository to read in data.

    To simplify things, I created a brand new EFCore project in the current solution:

    The module is configured to use MySql:

    using Acs.Cts.MySql.Candidates;
using Acs.Cts.MySql.EntityFrameworkCore.Candidates;
using Microsoft.Extensions.DependencyInjection;
using Volo.Abp.EntityFrameworkCore;
using Volo.Abp.EntityFrameworkCore.MySQL;
using Volo.Abp.Modularity;

namespace Acs.Cts.MySql.EntityFrameworkCore;

[DependsOn(
    typeof(AbpEntityFrameworkCoreMySQLModule))]
public class MySqlEntityFrameworkCoreModule : AbpModule
{
    public override void PreConfigureServices(ServiceConfigurationContext context)
    {
        MySqlEfCoreEntityExtensionMappings.Configure();
    }

    public override void ConfigureServices(ServiceConfigurationContext context)
    {
        context.Services.AddAbpDbContext<MySqlDbContext>(options =>
        {
            /* Remove "includeAllEntities: true" to create
             * default repositories only for aggregate roots */
            options.AddDefaultRepositories(includeAllEntities: true);

            options.AddRepository<TestCandidate, EfCoreTestCandidateRepository>();
        });

        Configure<AbpDbContextOptions>(options =>
        {
            /* The main point to change your DBMS.
             * See also PortalDbContextFactory for EF Core tooling. */
**            options.UseMySQL();**
        });
    }
}

    The MySqlDbContext has a "MySql" Connection string:

    using Acs.Cts.MySql.Candidates;
using Microsoft.EntityFrameworkCore;
using Volo.Abp.Data;
using Volo.Abp.EntityFrameworkCore;

namespace Acs.Cts.MySql.EntityFrameworkCore;

**[ConnectionStringName("MySql")]**
public class MySqlDbContext : AbpDbContext<MySqlDbContext>, IMySqlDbContext
{
    /* Add DbSet properties for your Aggregate Roots / Entities here. */

    public MySqlDbContext(DbContextOptions<MySqlDbContext> options)
        : base(options)
    {

    }

    public DbSet<TestCandidate> TestCandidates { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        /* Include modules to your migration db context */

        /* Configure your own tables/entities inside here */

        /* Include modules to your migration db context */
        builder.ConfigureMySql();
    }
}

    The MySql connection string is defined in the HttpApi.Host project appsettings.json:

    In this project, I have a view defined on the MySQL database:

    using Acs.Cts.MySql.Candidates;
using Microsoft.EntityFrameworkCore;
using Volo.Abp;

namespace Acs.Cts.MySql.EntityFrameworkCore;

public static class MySqlDbContextModelCreatingExtensions
{
    public static void ConfigureMySql(
        this ModelBuilder builder)
    {
        Check.NotNull(builder, nameof(builder));

        /* Configure all entities here. */

        if (builder.IsHostDatabase())
        {
            builder.Entity<TestCandidate>(b =>
            {
                b.ToView("vwTestCandidates");
            });
        }
    }
}

    The view returns a single row:

    The project references my core domain project, which contains the TestCandidate entity:

    The module in the HttpApi.Host project has references the MySqlEntityFrameworkCoreModule before (its the first DependsOn) the original PortalEntityFrameworkCoreModule:

    The application service exists to get the list of TestCandidates:

    When I invoke the service from Swagger, I get an error that indicates that a SQL Server driver is being used to try and connect to the MySQL database:

    Log File:

    2022-08-31 08:48:18.473 -07:00 [INF] Executing endpoint 'Acs.Cts.MySql.Candidates.TestCandidatesAppService.GetListAsync (Acs.Cts.Portal.Application)'
2022-08-31 08:48:18.490 -07:00 [INF] Route matched with {action = "GetList", controller = "TestCandidates", area = "", page = ""}. Executing controller action with signature System.Threading.Tasks.Task`1[Volo.Abp.Application.Dtos.PagedResultDto`1[Acs.Cts.MySql.Candidates.TestCandidateDto]] GetListAsync(Acs.Cts.MySql.Candidates.GetTestCandidateInput) on controller Acs.Cts.MySql.Candidates.TestCandidatesAppService (Acs.Cts.Portal.Application).
2022-08-31 08:48:18.949 -07:00 [DBG] Added 0 entity changes to the current audit log
2022-08-31 08:48:21.520 -07:00 [ERR] ---------- RemoteServiceErrorInfo ----------
{
  "code": null,
  "message": "An internal error occurred during your request!",
  "details": null,
  "data": {},
  "validationErrors": null
}

**2022-08-31 08:48:21.520 -07:00 [ERR] Keyword not supported: 'port'.
System.ArgumentException: Keyword not supported: 'port'.
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
   at Microsoft.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.<>c.**<get_IsMultipleActiveResultSetsEnabled>b__7_0(String cs)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerCompiledQueryCacheKeyGenerator.GenerateCacheKey(Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.LongCountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Acs.Cts.MySql.EntityFrameworkCore.Candidates.EfCoreTestCandidateRepository.GetCountAsync(Nullable`1 id, CancellationToken cancellationToken) in C:\Users\Jamie\Repo\CTSPortal\apps\acs-portal\aspnet-core\src\Acs.Cts.MySql.EntityFrameworkCore\MySql\Candidates\EfCoreTestCandidateRepository.cs:line 38
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Acs.Cts.MySql.Candidates.TestCandidatesAppService.GetListAsync(GetTestCandidateInput input) in C:\Users\Jamie\Repo\CTSPortal\apps\acs-portal\aspnet-core\src\Acs.Cts.Portal.Application\MySql\TestCandidateAppService.cs:line 24
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Authorization.AuthorizationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.GlobalFeatures.GlobalFeatureInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Validation.ValidationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Auditing.AuditingInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)
   at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue`1.ProceedAsync()
   at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)
   at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter`1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func`3 proceed)
   at lambda_method2691(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.&lt;InvokeNextActionFilterAsync&gt;g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.&lt;InvokeNextExceptionFilterAsync&gt;g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

    If I swap the order of the DependsOn in the HttpApiHost module, I get an error with my original EFCore, which should be connecting to SQL Server, trying to connect using MySQL:

    Log file:

    022-08-31 08:53:17.476 -07:00 [ERR] An exception was thrown while activating Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext -> λ:Microsoft.EntityFrameworkCore.DbContextOptions`1[[Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext, Volo.Abp.BackgroundJobs.EntityFrameworkCore, Version=5.1.4.0, Culture=neutral, PublicKeyToken=null]].
Autofac.Core.DependencyResolutionException: An exception was thrown while activating Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext -> λ:Microsoft.EntityFrameworkCore.DbContextOptions`1[[Volo.Abp.BackgroundJobs.EntityFrameworkCore.BackgroundJobsDbContext, Volo.Abp.BackgroundJobs.EntityFrameworkCore, Version=5.1.4.0, Culture=neutral, PublicKeyToken=null]].
 ---> System.ArgumentException: Option 'trusted_connection' not supported.
   at MySqlConnector.MySqlConnectionStringOption.GetOptionForKey(String key) in /_/src/MySqlConnector/MySqlConnectionStringBuilder.cs:line 927
   at MySqlConnector.MySqlConnectionStringBuilder.set_Item(String key, Object value) in /_/src/MySqlConnector/MySqlConnectionStringBuilder.cs:line 808
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Microsoft.EntityFrameworkCore.ServerVersion.AutoDetect(String connectionString)
   at Volo.Abp.EntityFrameworkCore.AbpDbContextConfigurationContextMySQLExtensions.UseMySQL(AbpDbContextConfigurationContext context, Action`1 mySQLOptionsAction)

    But the MySQL service now works:

  • User Avatar
    0
    AlderCove created
    • UI framework: MVC
    • Mobile: None
    • Database provider: Entity Framework Core
    • Database management system: SQLServer
    • Public website: No
    • Separate tenant schema: No
    • Tiered: No
    • Preview version: No

    Hi

    I have completed some additional testing in a simpler solution and have the same issues.

    1. Created a new 5.1.4 application template using abp suite called EfCoreMultiContextApp:
    2. Generated a test entity called TestSqlServerEntity
    3. Added and configured new project to the solution for My SQL
    4. Created test entity called TestMySqlEntity
    5. Created and ran migrations - both the databases have the correct entities created
    6. Added references in .Web app to the MySql EF Core project

    I have the same issues where either MySql drivers are used or Sql Server drivers are used depending on the order of the DependsOn references.

    I have uploaded the code to github: ** removed **

    It's a very simple example and it demonstrates the same issues I am having with my more complex application.

    Appreciate your assistance with this!

    Thanks

  • User Avatar
    0
    gterdem created
    Senior .NET Developer

    I have removed the link, please don't share your commercial application in public repositories. Please change your repository visibility to private.

    Here is a community post about how to configure multiple databases in a single application: https://community.abp.io/posts/configuring-multiple-dbcontexts-in-an-abp-framework-project-uoz5is3o

  • User Avatar
    0
    AlderCove created

    I have deleted the commercial github repo and created a new open source repo. https://github.com/jtasko/MultipleDbContextDemo

    This is based on the community post you shared and that I have already reviewed.

    I removed the MongoDb project and added a new MySql EF Core Project (MultipleDbContextDemo.MySql.EntityFrameworkCore). This project has DBContext for the MySql database.

    I updated the solution to contain two entities; TestSqlServerEntity and TestMySqlEntity.

    I added connection strings for the MySql Db Context.

    I created migrations for each EFCore project and updated the databases. Each database has the appropriate tables.

    I have the same issue with this solution and cannot connect to both the MySql and SQL Server databases. The order of the DependsOn results in the application using either the Sql Server or MySql connections. One or the other, but not both.

    There could very well be something trivial that I have missed but I really, honestly, could use some help here.

    Thanks

  • User Avatar
    2
    gterdem created
    Senior .NET Developer

    I sent a PR. You can examine it.

    You had missing module dependencies, extra db-migrator configurations and MySql db configuration for the specific dbcontext:

    Configure<AbpDbContextOptions>(options =>
        {
            /* The main point to change your DBMS.
             * See also MultipleDbContextDemoDbContextFactory for EF Core tooling. */
            options.Configure<MySqlAppDbContext>(opt =>
            {
                opt.UseMySQL();
            });
        });

    Check https://docs.abp.io/en/abp/latest/Entity-Framework-Core#abpdbcontextoptions for more information.

    There is also docker-compose file to run the mysql in containers and the appsettings configurations are done based on that. So, you can also just run that.

  • User Avatar
    0
    AlderCove created

    Thank you so much!

    It was trivial in the end and seems obvious now I see the solution.

    The migrations weren't an issue for me in my production solution but thank you for updating those too and the docker setup.

Assignee
No one
Labels
None yet
Made with ❤️ on ABP v9.1.0-preview. Updated on November 18, 2024, 05:54