Reading Data from Remote Sources at the Middle Tier

Let's go back for a moment to using ADO.NET. To read data from remote sources from a middle-tier component, you need to open a Connection object to each of the different data sources that you want to communicate with and query each one of them independently for the data that you want to extract.

As Figure 8-1 shows, when taking this approach, the middle-tier code has to deal with querying each of the different data sources using each data source's access API and merging the collected result sets into a single result set.

Figure 8-1. Architectural model for reading data from remote sources at the middle tier.

Reading Data from Remote Data Sources Using ADO.NET at the Middle Tier

Your middle-tier application must connect to each of the different data sources using the appropriate data access provider. For example:

• To connect to an Oracle database using ADO.NET:

  'Connect to pacific sales Dim oracleConn As OracleConnection = New OracleConnection() oracleConn.ConnectionString = "Data Source=MyOracleDB;Integrated Security=yes" Dim oracleDA As New OracleDataAdapter("SELECT * FROM PacificSales", oracleConn)

• To connect to an Excel file using ADO.NET:

  'Connect to central sales Dim excelConn As New OleDbConnection() excelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &_     "Data Source=C:\CentralSales.xls;Extended Properties=""Excel 8.0""" Dim excelDA As New OleDbDataAdapter("SELECT * FROM [Sales$]", excelConn)

• To connect to a SQL Server database using ADO.NET:

  'Connect to atlantic sales Dim sqlConn As New SqlConnection() sqlConn.ConnectionString = _     "Data Source=MySQLServer; Initial Catalog=MySQLDB; Integrated Security=SSPI" Dim sqlDA As New SqlDataAdapter("SELECT * FROM AtlanticSales", sqlConn)

Then your middle-tier application must use a DataSet object to hold all the data coming from the different data sources, as in the following code snippet. (The code in this section is included in the sample files as MiddleTier.vb.txt.)

Dim salesData as New DataSet() oracleDA.Fill(salesData) excelDA.Fill(salesData) sqlDA.Fill(salesData)

To summarize, by implementing the remote access at the middle tier, every time the sales data is required, the application needs to:

1.	Open a different connection to each of the remote data sources.
2.	Bring the data to the middle tier and merge all of the results into a single result.

The middle tier deals with the fact that the data is distributed in different physical locations.

Sometimes merging the data in the middle tier is not as easy as shown in this example because it might come in different formats and representations. The code needed to manipulate heterogeneous data sources is not always the same in .NET Framework programming. For example, if you need to retrieve data from a text file, you probably would use the classes inside the System.IO namespace, but if you need to retrieve data from Active Directory, you probably would use the classes inside the System.DirectoryServices namespace. The programming model for the classes inside these two namespaces is very different.