O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required