Using LINQ to join data from multiple SQL Servers

By: Sherlee Dizon   |   Comments (6)   |   Related: > Language Integrated Query LINQ

Problem

In my project I need to retrieve and join data from two different SQL Server databases on two different SQL Server instances.  The data retrieval can only be done via the existing stored procedures that have been created in these databases. In this tip we look at how we can join two datasets from different servers using LINQ.

Solution

I am using LINQ to join the result sets in a C# .NET application from the two stored procedures. My solution is to convert my DataTable result set to a List<T> class. When it comes to performance, using the List<T> class is faster than datasets based on my testing.  List<T> classes are type safe and also easy to manipulate.  You will need to add the System.Collections.Generic namespace to be able to use the List<T> class and the System.Linq namespace to be able to use LINQ for querying.

For this simple example, let's say we have tip data and tip category data.  The tip category data is maintained on the back office server and the tip data is maintained on the front office server.

The code in this example uses Visual Studio 2010 ASP with C#.net and SQL Server 2008.

SQL Server sample objects

Here is the script for the tables used for the back-office SQL Server:

USE [MSSQLTIPS_BO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TipCategory](
 [TipCatID] [int] IDENTITY(1,1) NOT NULL,
 [TIPCategoryDesc] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Here is the sample stored procedure that we will call from the back-office SQL Server:

USE [MSSQLTIPS_BO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BO_GetTipCategory]
AS 
SELECT * FROM TipCategory
GO

Here is the script for the table used in the front-office SQL Server:

USE [MSSQLTIPS_FO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tips](
 [TipID] [int] NOT NULL,
 [TipTitle] [varchar](200) NULL,
 [TipCatID] [int] NULL,
 [SubmissionDate] [date] NOT NULL,
 [AuthorID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Here is the sample stored procedure that we will call from the front-office SQL Server:

USE [MSSQLTIPS_FO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FO_GetTips](@AuthorID int)
AS 
SELECT * FROM Tips
WHERE AuthorID = @AuthorID
ORDER BY SubmissionDate DESC
GO

LINQ Code Examples

TIPS.DTO is our data object dll.

The TipCategory class contains all the fields we need for the tip category data. Here we define and initialize our object data fields we need to use.

The Tips class contains all the fields we need for the tip data. Here we define and initialize our object data fields we need to use.

TIPS.Data is our object dll that will manage and handle data connections as well as defining and executing our stored procedures. Here you can see that we have defined a connection for the front-office connection and the back-office connection.

This is our object for defining our data objects for the public generic list collection for getting the list of tip categories. Here we define the stored procedure that will be used. Each record will be populated and assigned to our data object for TipCategory.

This is our object for our public generic list collection for getting the list of tips. Here we define the stored procedure that will be used. In addition, we are passing in an AuthorID parameter to limit the list of tips for a particular author.  Each record is then populated and assigned to our data object for Tips.

This is our core object that will manage the calling of our data objects. It will handle catching of exceptions and validations that are required. Our core object method will be the one to call the method from our data object which is the DATA.  

This is the web config file where we will define our database connections. TIPS_BO is the name of our connection string to the back-office database and TIPS_FO for the front-office database. Including the connection information in the config file will make our deployment easier to our development, testing and production servers.

This is our page load event where we will instantiate, use and call our data objects. We need to instantiate our core data object and our generic collection list. To be able to populate our gridview with the combined data we will use our GetTips method from tips and store the data to our generic list collection tipList. Then we will use our GetTipCategories method from tips and store the data to our generic collection list _TipCategoryList. By assigning the LINQ result to NewTipList we can use it as the Datasource to our GridView.

Let's say the records from TipCategoryList are:

and the records from TipList are:

Here is the sample output when the data was joined for authoridID = 2.

Next Steps

• I hope this tip is helpful to give you some suggestions on how to leverage LINQ in your applications.
• Now that you know how to join different result sets from different stored procedures you can now maximize the usage of your stored procedure result sets in LINQ.
• For your reference you can read and explore more about LINQ.
• Here is the complete Visual Studio Solution that you can download for your testing

About the author

Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips