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