How to implement multiple DB (Sybase and MSSQL) using ASP.NET Core Web API

Question

I am used to ASP.NET Core Web API using EntityFramework, but I have a task with me.

I want to combine both MSSQL and Sybase. Fetch data from the two DBs and relate them.

1. I want to relate and display data from Sybase: employee and department tables; MSSQL: customer and locations tables
2. Then create user authentication and authorization in MSSQL

How do I achieve this?

Answer 1

as suggested above, you can run a query on each dbcontext (database), then join the requests. this is a in-memory join in the application. so for performance you will need a good strategy to limit the query sizes.

you don't say what data links the two systems. it may also be practical to load the employees/department data into memory (at least the keys).

you could also use SQLServer linked server support to access Sybase tables via the linked server. again not always the best performance for cross server joins.

Answer 2

Hi @MICHAEL ,

For the MSSQL database, as you said, you can use EntityFramework (EF) or EntityFramework Core (EF core) to access the database, and then use a dbcontext to access the data table and get the data.

For the Sybase database, I'm not sure whether you can use EF or EF core to access it or not, because from the EF core Database Providers, it doesn't contain the provider for the Sybase database. You might need to create custom provider and use it. Here is an article about How to connect to Sybase database from .net core, it will use the ADO.NET method to query the database.

So, you could use two dbcontext (if you can access the Sybase via the EF/EF core) or use one dbcontext for MS SQL Server database and use ADO.NET method for the Sybase database.

Then create user authentication and authorization in MSSQL

Agree with AgaveJoe, you can create JWT Authentication in Web API.

After fetching data from two databases respectively (using two dbcontext or one dbcontext and one Ado.net query result), you can use LINQ Query Operations to query the data and check whether the login user is a valid user or not, Then, generate a JWT token to do the authentication and authorization.

You can refer the following articles:

JWT Authentication In ASP.NET Core

ASP.NET Core Web API 5.0 Authentication Using JWT(JSON BASE TOKEN)

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion