ASP.NET Core Web API - implement multiple databases using Sybase and SQL Server

Question

For some days now I've been trying to implement multiple databases in ASP.NET Core-6 Web API using Sybase and SQL Server. I have never done this before.

I have this connection:

  "ConnectionStrings": {
    "MssqlDbConnection": "Server=nikatic;Database=mymssql;Trusted_Connection=True; MultipleActiveResultSets=true;",
    "SybaseDbConnection": "Server=nxppawe;Database=mysybase;Port=6645;Min Pool Size=100;Max Pool Size=1000;User ID=acme;Password=lennon;charset=cp850;"
  },

I have three (3) tables:

1. Employees ---> From SQL Server
2. EmployeeProject --> Sybase
3. Projects --> Sybase

I want to query the 3 tables from the two different databases and display the result.

using System.Data;
using System.Data.SqlClient;

var sql = @"select e.EmployeeId, e.Name, e.StaffNo, e.Gender, e.DateOfBirth, p.ProjectId, p.Name, p.Duration
            from Employees e
            inner join
            EmployeeProject ep on ep.EmployeeId = e.EmployeeId
            inner join 
            Projects p on p.ProjectId = ep.ProjectId;";

How do I achieve this?

Thank you

Answer 1

Here's a link for you to create a linked server from SQL Server to ASE 12

https://www.cdata.com/kb/tech/sybase-odbc-linked-server.rst

General doc on how to create a linked server in SSMS

https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15

Once you created it, you will be able to access any of your Sybase tables from SQL Server. Note the 4-name notation for remote tables vs 3-name notation for local tables.

select 
  e.EmployeeId, e.Name, e.StaffNo, e.Gender, e.DateOfBirth, p.ProjectId, p.Name, p.Duration 
from 
  nxppawe.mysybase.acme.Projects p
join nxppawe.mysybase.acme.EmployeeProject ep 
  on p.ProjectId = ep.ProjectId
join mymssql.dbo.Employees e 
  on ep.EmployeeId = e.EmployeeId
;