CODEWITHSUNDEEP
asp.net-coreentity-framework-core
Braian Mellor
Braian Mellor

Reputation: 1954

How to create an API with severals DB connections

I need to create an API in .NET to connect and verify some data in several database engines (MySQl, PostgreSQL, SQL Server). I can make one connection, but I don't understand how to make more than one.

Here's my code:

Startup.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",
    },

Interface

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

Implementation

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;
            }
        }
    }
}

And the db context

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)
        { }
    }
}

Upvotes: 0

Views: 828

Answers (1)

Lucas M. Martinez
Lucas M. Martinez

Reputation: 36

example of connection to different motors with validation against active directory

1) install the different EF Core Database Providers in VS

Entity Framework Core uses a provider model to access many different databases. EF Core includes providers as NuGet packages which you need to install.

The following lists database providers and NuGet packages for EF Core (NuGet package). 

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

2)Perform the Scaffold-DbContext to the bd and tables that you want to use in the different engines. 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)add the different connection string in the appsettings.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) modify the DbContext generated by the Scaffold-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)create Interfaces

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)create the Services

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) configure the different services in Startup.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)creation of the different Controller

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));
        }
    }
}

Upvotes: 2

Related Questions