ADO.NET 3.5 Cookbook, 2nd Edition

4.2. Combining Data from Heterogeneous Data Sources

Problem

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

Solution

Use ad hoc connector names in SQL statements.

The solution retrieves data from both a SQL Server table and a Microsoft Access table to create a single result set. Specifically, Sales.SalesOrderHeader data is retrieved from AdventureWorks in SQL Server and joined to Sales.SalesOrderDetail data retrieved from Microsoft Access.

You must enable OPENROWSET support in SQL Server to run this example. To do this, open SQL Server Surface Area Configuration tool from the SQL Server Configuration Tools folder. Select Surface Area Configuration for Features. Select the Ad Hoc Remote Queries component and check the "Enable OPENROWSET and OPENDATASOURCE support" checkbox as shown in Figure 4-2.

Figure 4-2. Surface Area Configuration for Features dialog

Click the OK button to apply the changes. Close the Surface Area Configuration main window.

You also need to create a Microsoft Access file (named AdventureWorks.accdb in this example) and import the Sales.SalesOrderDetail table into it from AdventureWorks in SQL Server. Set the filename variable accessFileName in the example to match the location and name of your Microsoft Access file. Ensure that the Microsoft Access database is closed before running the example.

The C# code in Program.cs in the project CombineHeterogeneousData is shown in Example 4-2.

Example 4-2. File: Program.cs for CombineHeterogeneousData solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace CombineHeterogeneousData
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string accessFileName =
                @"C:\Documents and Settings\bill\My Documents\AdventureWorks.accdb";

            string sqlSelect =
                "SELECT TOP 20 h.SalesOrderID, h.CustomerID, h.OrderDate, " +
                "d.ProductID, d.OrderQty, d.LineTotal " +
                "FROM Sales.SalesOrderHeader h INNER JOIN " +
                "OPENROWSET('Microsoft.ACE.OLEDB.12.0','" + accessFileName +
                "';'admin';'',Sales_SalesOrderDetail) AS d " +
                "ON h.SalesOrderID = d.SalesOrderID " +
                "ORDER BY h.SalesOrderID, d.ProductID";

            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable();
            da.Fill(dt);

            Console.WriteLine("OrderID\tCustID\tOrderDate\t\tProdID\t" +
                "Qty\tLineTotal");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}",
                    row["SalesOrderID"], row["CustomerID"], row["OrderDate"],
                    row["ProductID"], row["OrderQty"], row["LineTotal"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-3.

Discussion

Microsoft SQL Server 2000 and later supports two methods to access data from heterogeneous data sources through OLE DB: ad hoc connector names and linked servers.

Figure 4-3. Output for CombineHeterogeneousData solution

Ad hoc connector names allow data from heterogeneous data sources to be accessed without setting up linked servers by providing the information required to connect to each data source in the SQL statement. This is done using either the OPENROWSET or the OPENDATASOURCE function to open the row set from the OLE DB data source. Both functions take arguments containing all connection information required to access the data source. The functions allow the row sets to be subsequently referenced like any other table in SQL statements.

For more information about OPENROWSET and OPENDATASOURCE functions, see Microsoft SQL Server Books Online.

Add linked servers using the Server Objects → Linked Servers node in Object Explorer in SQL Server Management Studio or using system stored procedures. Refer to linked servers in SQL statements using a four-part name comprised of the names of the linked server, the catalog, the schema within the catalog, and the data object, separated using periods. Access to linked servers has better performance than access using ad hoc connector names. For more information about using linked servers, see Microsoft SQL Server Books Online.