如何创建具有多个数据库连接的 API

Question

我需要在.NET中创建一个API，以连接和验证多个数据库引擎(MySQl，PostgreSQL，SQL Server(中的一些数据。 我可以建立一种连接，但我不明白如何建立多个连接。

这是我的代码：

启动.cs

public void ConfigureServices(IServiceCollection services)
{
#region MSSqlServer
var connection = Configuration.GetConnectionString("SQLSRV");
services
.AddDbContext<SQLSRVDBContext>
(options => options.UseSqlServer(connection));
services
.AddTransient<IService, Service>();
#endregion
services.AddMvc();
}

appsettings.json

"ConnectionStrings": {
"SQLSRV": "Server=localhost;Database= dbName;User Id=dbUser;Password=dbPassword;MultipleActiveResultSets=true",
},

接口

namespace VerificaUsuarios.Interfaces
{
public interface IService
{
bool GetUsuarioSG(string userName, string password);
}
}

实现

namespace VerificaUsuarios.Services
{
using VerificaUsuarios.Interfaces;
using VerificaUsuarios.Models;
using VerificaUsuarios.Persistence;
using System.Linq;
using global::ADWS;
public class Service : IService
{
private readonly SQLSRVDBContext _sQLSRVDBContext;
public Service(SQLSRVDBContext sQLSRVDBContext)
{
_sQLSRVDBContext = sQLSRVDBContext;
}

public bool GetUsuarioSG(string userName, string password)
{
var result = new UsuariosSG();
var activeDirectory = new AD_WSClient();
try
{
bool isUsuario = activeDirectory.LoginAsync(userName, password).Result;

if(isUsuario)
{
try
{
result = _sQLSRVDBContext
.Usuarios
.Where(u => u.UsrLogin.Trim() == userName.Trim())
.First();
}
catch (System.Exception ex)
{
return false;
}
return true;
}
else
{
return false;
}

}
catch(System.Exception excep)
{
return false;
}
}
}
}

和数据库上下文

namespace VerificaUsuarios.Persistence
{
using Microsoft.EntityFrameworkCore;
using VerificaUsuarios.Models;
public partial class SQLSRVDBContext : DbContext
{
public SQLSRVDBContext()
{
}
public virtual DbSet<UsuariosSG>  Usuarios{ get; set; }
public SQLSRVDBContext(DbContextOptions<SQLSRVDBContext> options)
: base(options)
{ }
}
}

Answer 1

连接到不同电机的示例，并针对活动目录进行验证

1( 在 VS 中安装不同的 EF Core 数据库提供程序

实体框架核心使用提供程序模型来访问许多不同的数据库。EF Core 包含提供程序作为需要安装的 NuGet 包。

下面列出了数据库提供程序和 EF 核心的 NuGet 包(NuGet 包(。

SQL Server  Microsoft.EntityFrameworkCore.SqlServer
MySQL   MySql.Data.EntityFrameworkCore
PostgreSQL  Npgsql.EntityFrameworkCore.PostgreSQL

2(对要在不同引擎中使用的 bd 和表执行基架-数据库上下文。 PostgreSQL

Scaffold-DbContext "Host=myserver;Database=mydatabase;Username=myuser;Password=mypassword" Npgsql.EntityFrameworkCore.PostgreSQL -o Models -Table MyTablePSQL

MySql

Scaffold-DbContext "server=myserver;port=3306;user=myuser;password=mypass;database=mydb" MySql.Data.EntityFrameworkCore -OutputDir Models -f -Table MyTableMySQL

SqlServer

Scaffold-DbContext "Server=myserver;Database=mydb;User Id=myuser;Password=mypassword;MultipleActiveResultSets=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Table MyTableSQL

3(在应用设置中添加不同的连接字符串.json

"ConnectionStrings": {
"SQLSRV": "Server=myserver;Database= mydb;User Id=myuser;Password=myPassword;MultipleActiveResultSets=true",
"MySql": "server=myserver;user id=myuser;password=mypassword;port=3306;database=mydb;",
"PSQL": "Host=myserver;Database=mydb;Username=myuser;Password=mypassword"
},

4( 修改由 Scaffold-DbContext 生成的 DbContext

SQLSRVDBContext

namespace MyProject.Persistence
{
using Microsoft.EntityFrameworkCore;
using MyProject.Models;
public partial class SQLSRVDBContext : DbContext
{
public SQLSRVDBContext()
{
}
public virtual DbSet<MyTableSQL>  Users{ get; set; }
public SQLSRVDBContext(DbContextOptions<SQLSRVDBContext> options)
: base(options)
{ }
}
}

MySQLDBContext

namespace MyProject.Persistence
{
using Microsoft.EntityFrameworkCore;
using MyProject.Models;
public partial class MySQLDBContext : DbContext
{
public MySQLDBContext()
{
}
public virtual DbSet<MyTableMySQL> Users { get; set; }
public MySQLDBContext(DbContextOptions<MySQLDBContext> options)
: base(options)
{ }
}
}

PostgreSQL

namespace MyProject.Models
{
using Microsoft.EntityFrameworkCore;
public partial class PostgreSQLDBContext : DbContext
{
public PostgreSQLDBContext()
{
}
public virtual DbSet<MyTablePSQL> Users { get; set; }
public PostgreSQLDBContext(DbContextOptions<PostgreSQLDBContext> options)
: base(options)
{
}
}
}

5(创建接口

SQLSRV

namespace MyProject.Interfaces
{
public interface IService
{
bool GetUserSQLSRV(string userName, string password);
}
}

MySQL

namespace MyProject.Interfaces
{
public interface IServiceMySQL
{
bool GetUserMySQL(string userName, string password);
}
}

PostgreSQL

namespace MyProject.Interfaces
{
public interface IServicePSQL
{
bool GetUserPSQL(string userName, string password);
}
}

6(创建服务

SQLSRV(SQLSRVDBContext(

namespace MyProject.Services
{
using MyProject.Interfaces;
using MyProject.Models;
using MyProject.Persistence;
using System.Linq;
using global::ADWS;
public class Service : IService
{
private readonly SQLSRVDBContext _sQLSRVDBContext;
public Service(SQLSRVDBContext sQLSRVDBContext)
{
_sQLSRVDBContext = sQLSRVDBContext;
}
public bool GetUserSQLSRV(string userName, string password)
{
var result = new MyTableSQL();
var activeDirectory = new AD_WSClient();
try
{
bool isUser = activeDirectory.LoginAsync(userName, password).Result;
if(isUser)
{
try
{
result = _sQLSRVDBContext
.Users
.Where(u => u.UsrLogin.Trim() == userName.Trim())
.First();
}
catch (System.Exception ex)
{
return false;
}
return true;
}
else
{
return false;
}
}
catch(System.Exception excep)
{
return false;
}
}
}
}

MySQL(MySQLDBContext(

namespace MyProject.Services
{
using MyProject.Interfaces;
using MyProject.Models;
using MyProject.Persistence;
using System.Linq;
using global::ADWS;
public class ServiceMySQL : IServiceMySQL
{
private readonly MySQLDBContext _mySQLDBContext;
public ServiceMySQL(MySQLDBContext mySQLDBContext)
{
_mySQLDBContext = mySQLDBContext;
}
public bool GetUserMySQL(string userName, string password)
{
var result = new MyTableMySQL();
var activeDirectory = new AD_WSClient();
try
{
bool isUser = activeDirectory.LoginAsync(userName, password).Result;
if(isUser)
{
try
{
result = _mySQLDBContext
.Users
.Where(u => u.UsrLogin.Trim() == userName.Trim())
.First();
}
catch (System.Exception ex)
{
return false;
}
return true;
}
else
{
return false;
}
}
catch(System.Exception excep)
{
return false;
}
}
}
}

PostgreSQL(PostgreSQLDBContext(

namespace MyProject.Services
{
using MyProject.Interfaces;
using MyProject.Models;
using MyProject.Persistence;
using System.Linq;
using global::ADWS;
public class ServicePSQL : IServicePSQL
{
private readonly PostgreSQLDBContext _postgreSQLDBContext;
public ServicePSQL(PostgreSQLDBContext postgreSQLDBContext)
{
_postgreSQLDBContext = postgreSQLDBContext;
}
public bool GetUserPSQL(string userName, string password)
{
var result = new MyTablePSQL();
var activeDirectory = new AD_WSClient();
try
{
bool isUser = activeDirectory.LoginAsync(userName, password).Result;
if(isUser)
{
try
{
result = _postgreSQLDBContext
.Users
.Where(u => u.UsrLogin.Trim() == userName.Trim())
.First();
}
catch (System.Exception ex)
{
return false;
}
return true;
}
else
{
return false;
}
}
catch(System.Exception excep)
{
return false;
}
}
}
}

7( 在启动中配置不同的服务.cs

public void ConfigureServices(IServiceCollection services)
{
#region SQLSRV
var connection = Configuration.GetConnectionString("SQLSRV");
services
.AddDbContext<SQLSRVDBContext>
(options => options.UseSqlServer(connection));
services
.AddTransient<IService, Service>();
#endregion
#region MySql
var connectionMySql = Configuration.GetConnectionString("MySQL");
services
.AddDbContext<MySQLDBContext>
(options => options.UseMySQL(connectionMySql));
services
.AddTransient<IServiceMySQL, ServiceMySQL>();
#endregion
#region PostgreSQL
var connectionPSQL = Configuration.GetConnectionString("PSQL");
services
.AddDbContext<PostgreSQLDBContext>
(options => options.UseNpgsql(connectionPSQL));
services.AddTransient<IServicePSQL, ServicePSQL>();
#endregion
services.AddMvc();
}

8(创建不同的控制器

SQLSRV

namespace MyProject.Controllers
{
using Microsoft.AspNetCore.Mvc;
using MyProject.Interfaces;
[Route("api/GET/[controller]")]
public class UserSQLSRVController : Controller
{
private readonly IService _userSQLSRVService;
public UserSQLSRVController(IService userSQLSRVService)
{
_userSQLSRVService = userSQLSRVService;
}
[HttpGet]
public IActionResult GetUserSQLSRV(string userName, string password)
{
return Ok(
_userSQLSRVService.GetUserSQLSRV(userName, password));
}
}
}

MySQL

namespace MyProject.Controllers
{
using Microsoft.AspNetCore.Mvc;
using MyProject.Interfaces;
[Route("api/GET/[controller]")]
public class UserMySqlController : Controller
{
private readonly IServiceMySQL _userMySqlService;
public UserMySqlController(IServiceMySQL userMySqlService)
{
_userMySqlService = userMySqlService;
}
[HttpGet]
public IActionResult GetUserMySQL(string userName, string password)
{
return Ok(
_userMySqlService.GetUserMySQL(userName, password));
}
}
}

PSQL

namespace MyProject.Controllers
{
using Microsoft.AspNetCore.Mvc;
using MyProject.Interfaces;
[Route("api/GET/[controller]")]
public class UserPSQLController : Controller
{
private readonly IServicePSQL _userPSQLService;
public UserPSQLController(IServicePSQL userPSQLService)
{
_userPSQLService = userPSQLService;
}
[HttpGet]
public IActionResult GetUserPSQL(string userName, string password)
{
return Ok(
_userPSQLService.GetUserPSQL(userName, password));
}
}
}