How to join two tables from two different sql server databases in a simple but complete c# project?

Question

I have two Sql Server databases d1 and d2 which are in the same physical server. d1 has a table address, d2 has a table telephone. I want to join them in a C# project, I googled and tried many ways, but all failed. If anybody can provide a simple but "COMPLETE and RUNNABLE" C# example project, that would be greatly appreciated.

[d1].[dbo].[address]:
id    name    address
1     Adam    add1
2     Bob     add2
3     Pete    add3
4     Hans    add4

[d2].[dbo].[telephone]:
id    addr_id    phoneNumber
1     2          632532
2     1          233257
3     4          343439
4     3          798111

addr_id is a foreign key with the reference to id of table address in database d1.

ConnectionString_d1 = "Data Source=ServerName;" + "Initial Catalog=d1;" + "User id=ui;" + "Password=pd;";
ConnectionString_d2 = "Data Source=ServerName;" + "Initial Catalog=d2;" + "User id=ui;" + "Password=pd;";

Certainly, you need change the two connection strings according to your own databases for the test.

SELECT t1.name, t1.address, t2.phoneNumber
FROM [d1].[dbo].[address] t1
INNER JOIN [d2].[dbo].[address] t2
ON t1.id = t2.addr_id;

Please don't just tell me the T-Sql statement mentioned above that only works in MSSQL Management Studio, doesn't work in a C# project. I need the simple but the COMPLETE Implementation of a C# console application that can be built and run, thanks in advance!

You can join the two tables from different Databases within SQL Server ([source](https://stackoverflow.com/questions/11426929/can-we-use-join-for-two-different-database-tables)) . Create a stored procedure within one of your databases, and have C# call the stored procedure and return it to a `DataTable`. — Jaskier, Mar 01 '19 at 15:21
haha you changed the answer so fast, I have to edit my reply accordingly. I'll look into the link, thanks — adorp, Mar 01 '19 at 15:23
You dont need 2 connections strings for this. you need a linked DB, and the connection string to the primary db. with that, you can run your query as is, without needing to change connection strings — Stormhashe, Mar 01 '19 at 15:24
If there are 5 tables from 3 different databases and they all need joined, you would create either a `view` or `stored procedure` within your `SQL Server` and only use C# to return the result of the `stored procedure`. Using C# to join all of them manually would be highly inefficient. It would be best to make the `SQL Server` do what it's supposed to. — Jaskier, Mar 01 '19 at 15:25
OK, Symon, I am new to stored procedure and its being called in C#, anyhow I'll give it a try, thanks — adorp, Mar 01 '19 at 15:26
@Symon, a simple but concrete example is more powerful than 10 thousands words :) Thanks — adorp, Mar 01 '19 at 15:29
If both the databases are in same physical server all you need to do is create a user/ give access to user who can connect to both databases and your query should run fine in c# code. — Krishna, Mar 01 '19 at 15:42
@Krishna , Can't think of a reason for the query to be ran *within* the C#. Would be better only to have C# call it from the SQL Server and return the result. Let the SQL Server do what it is meant to — Jaskier, Mar 01 '19 at 15:46
@Symon Well its all in the perspective of what is required. We can do it both ways. Which ever works for adorp. we been doing in C# for ages. So just my thought. — Krishna, Mar 01 '19 at 15:49
@Krishna , fair point. All references and sources I've run into have always stated to run the SQL from the server to avoid `SQL injection` and inefficient memory usage. — Jaskier, Mar 01 '19 at 15:57

Jaskier · Accepted Answer · 2019-03-11T19:28:22.350

SQL Server can join separate tables from different databases fairly easily. If your query requirements become more complex, then you just alter the query to return the data you're needing (i.e 5 tables from 3 databases). The SQL you provided works just fine for returning data from multiple tables from different databases:

SELECT t1.name, t1.address, t2.phoneNumber
FROM [d1].[dbo].[address] t1
INNER JOIN [d2].[dbo].[address] t2
ON t1.id = t2.addr_id;

If you are wanting to filter down the results more, then you should add a WHERE clause as well.

You can create this to a Stored Procedure like so:

--To be ran on your SQL Server and not from C# program
USE [d1]
GO

CREATE PROCEDURE [d1].[dbo].[MyProcedureName]
--add parameters if needed
AS
BEGIN
    SET NOCOUNT ON

    SELECT t1.name, 
        t1.address, 
        t2.phoneNumber

    FROM [d1].[dbo].[address] t1 
        INNER JOIN [d2].[dbo].[address] t2 ON t1.id = t2.addr_id;

END

With the Stored Procedure created, you can then use C# to call it, and return the result. Here is a C# method I have that I only use to call a stored procedure and return it as a DataTable:

//using System.Data.SqlClient;
public DataTable SqlToDT (string connectionString, string procName)
{
    //receives connection string and stored procedure name
    //then returns the populated data table

    DataTable table = new DataTable();

    using (var connection = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(procName, connection))
    using (var adapter = new SqlDataAdapter(cmd))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        da.Fill(table);
    } //the using statements will dispose the connection safely for you

    return table;
}

You can use this method like so:

//using System.Data;

//Got rid of the needless string concatenation 
string connectionStringd1 = "Data Source=ServerName;Initial Catalog=d1;User id=ui;Password=pd;";
string procName = "MyProcedureName";

DataTable myTable = new DataTable();
myTable = SqlToDT(connectionStringd1, procName);

//continue on from here

fantastic solution, my intuition tells me it ought to work. Later I'll try it, thanks! — adorp, Mar 01 '19 at 18:55
@adorp , not a problem! If it doesn't work or you have anymore question, feel free to comment and I can edit my answer accordingly. — Jaskier, Mar 01 '19 at 19:22

How to join two tables from two different sql server databases in a simple but complete c# project?

1 Answers1