How to connection multiples databases MySQL with C#?

Question

I am building an ASP.NET web API application, and I'm stuck in a problem... :/

I have appsettings.json to configure mysql connection:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "CoinConnection": "server=127.0.0.1;user id=root;password=root;port=3306;database=coin_db;Allow User Variables=True;",
    "UserConnection": "server=127.0.0.1;user id=root;password=root;port=3306;database=user_db;Allow User Variables=True;"
  }
}

I have Startup.cs to execute the service to connect mysql:

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace WebApplication2
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddTransient<AppDb>(_ => new AppDb(Configuration["ConnectionStrings:CoinConnection"]));
            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

I have AppDb.cs to receive a string and connect in database coin_db in mysql:

using System;
using MySqlConnector;

namespace WebApplication2
{
    public class AppDb : IDisposable
    {
        public MySqlConnection Connection { get; }

        public AppDb(string connectionString)
        {
            Connection = new MySqlConnection(connectionString);
        }

        public void Dispose() => Connection.Dispose();
    }
}

I don't know how I can do to connect the second database (user_db)... I've tried everything and I don't know how to do it.

You can simply have two classes, each connecting to a different database. I would definitely dispose of the connection immediately after each use instead of when the class is disposed. — insane_developer, Oct 11 '20 at 15:45

p-vogt · Answer 1 · 2020-10-11T16:14:12.113

Have you tried using a different AppDb implementation for each db? Then you can access them via dependency injection in your routes.

public class UserDb : AppDb 
{
   public UserDb(string connectionString) 
   : base(connectionString)
   {}
   // ...
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<AppDb>(_ => new AppDb(Configuration["ConnectionStrings:CoinConnection"])); // TODO: use CoinDb class and make AppDb class abstract
    services.AddTransient<UserDb>(_ => new UserDb(Configuration["ConnectionStrings:UserConnection"]));
    services.AddControllers();
}

score 1 · Answer 2 · answered Oct 11 '20 at 15:45

You don't need a second connection, if it is the same server.

Simply add the database name before the table name

SELECT a.*,b.* FROM coin_db.table1 a INNER JOIN user_db.tabl2 b ON a.id P= b.id

Mysql can identify so, whichtable you mean.

two connection is possible see here Choose one of many Internet connections for an application

How to connection multiples databases MySQL with C#?

2 Answers2