Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using LINQ to join data from multiple SQL Servers

MSSQLTips author Sherlee Dizon By:   |   Read Comments (6)   |   Related Tips: More > 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.

linq code

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.

.net code sample

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.

linq .net code

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.

linq c# code

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.

.net c# code

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.  

linq c# code sample

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.

web config sample

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.

.net namespace example

Let's say the records from TipCategoryList are:

TipCatID

TIPCategoryDesc

1 Database Design
2 Database Development
3 Language Integrated Query LINQ
4 Stored Procedures

and the records from TipList are:

TipID

AuthorID

TipCatID

TipTitle

 SubmissionDate

1 1 4 Grant Execute to all SQL Server Stored Procedures 09/15/2010
2 3 Using Stored Procedures with LINQ to SQL 07/13/2010
3 2 3 Querying SQL Server databases using LINQ to SQL 08/15/2011

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

linq query output
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


Last Update: 10/14/2011


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, October 14, 2011 - 10:43:51 AM - KHALIF Read The Tip

INTERESTING POINT

THANKSYOU


Friday, October 14, 2011 - 12:13:16 PM - Steve Read The Tip

Check out http://www.codeproject.com/KB/database/CodeFirstStoredProcedures.aspx for a simpler way to interact with stored procedures in a LINQ and EF 4.1 (aka "Code First") friendly style.

 

 


Sunday, November 13, 2011 - 10:06:24 PM - Sherlee Read The Tip

Thanks to all.

 

Thanks Steve for the link.


Wednesday, December 26, 2012 - 1:18:44 AM - Harshad Read The Tip

smart code. well for me


Wednesday, January 16, 2013 - 7:14:22 AM - Milvette Calimag Read The Tip

Well done and with good explanation...

 

 


Wednesday, February 20, 2013 - 5:51:12 AM - Sherlee Read The Tip

 

Thanks  for the appreciation.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.