ADO.NET Cookbook

3.6. Combining Data in Tables from Heterogeneous Data Sources

Problem

You want to create a report that is based on data from tables in more than one data source.

Solution

Use ad-hoc connector names in SQL statements.

The sample code retrieves data from both a SQL Server table and a Microsoft Access table to create a single result set. Specifically, Northwind Order data is retrieved from SQL Server and Northwind Order Details data is retrieved from Access and joined to the Order information.

The C# code is shown in Example 3-6.

Example 3-6. File: CombiningDataFromMultipleDatabasesForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//  . . . 

// Fill the table with data from SQL Server and MS Access.
String sqlSelect = "SELECT o.OrderID, o.CustomerID, o.OrderDate, " +
    "od.ProductId, od.UnitPrice, od.Quantity, od.Discount " + 
    "FROM Orders o INNER JOIN " + 
    "OPENROWSET('Microsoft.Jet.OLEDB.4.0','" +
    ConfigurationSettings.AppSettings["MsAccess_Database_Filename"] +
    "';'admin';'',[Order Details]) " +
    "AS od ON o.OrderID = od.OrderID " +
    "ORDER BY o.OrderID, od.ProductID";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable dt = new DataTable( );
da.Fill(dt);

// Set up and bind a view with data from both tables.
DataView dv = dt.DefaultView;
dv.AllowDelete = false;
dv.AllowEdit = false;
dv.AllowNew = false;
dataGrid.DataSource = dv;

Discussion ...