Determining Which Provider to Use

Microsoft® Host Integration Server offers two different types of providers with which to access data: a traditional COM-based OLE DB provider, and an ADO .NET provider. Both providers have their advantages, which you should consider carefully when determining which provider to use.

Reasons to Use the .NET Provider

The reasons to use the .NET Providers will be familiar to many who have programmed with .NET technologies: using the .NET providers improves your ability to manage and reuse your code base, access distributed and disconnected systems, and to increase security.

  • Improved support for the disconnected business model

    ADO provides limited support for disconnected access using the Recordset. ADO.NET introduces a new object, the DataSet, which serves as a common, in-memory representation of relational data. The DataSet is, by design, disconnected at all times. Because it holds no persistent connection to outside resources, the DataSet is ideal for packaging, exchanging, caching, persisting, and loading data.

  • Provider support

    Host Integration Server 2004 supports .NET database access using two optimized providers: the Managed Provider for DB2, and the OLE DB Provider for DB2 using ADO.NET. In addition, ADO.NET includes general provider support for SQL, OLE DB, and ODBC. Therefore, a .NET solution may work better for you than a COM-based solution if you are accessing these types of databases.

  • XML integration

    In addition to persisting and loading both data and its relational structure as XML, ADO.NET relies on XML for remoting data between tiers or clients. The generic XML representation that ADO.NET uses provides a convenient method for transmitting data across any network, including those with restrictive security perimeters. For example, ADO.NET XML data objects are easier to transmit through firewalls, whereas most firewalls are set up to block the COM marshalling commands used by an OLE DB provider. ADO.NET also uses XML tools to perform validation, hierarchical queries, and data transformations on relational data.

  • Consistent programming interface

    ADO constructs, such as the Recordset, do not employ familiar programming constructs but instead are modeled to be database-oriented. For example, cursors, which are used to navigate and retrieve data, function differently than other data constructs such as arrays and collections. In ADO .NET, however, in-memory data can be exposed through common .NET Framework structures, including arrays and collections, providing you with common access methods when working with relational data.

  • Improved UI controls

    .NET data controls, such as the DataGrid, Datalist, and DropDownList controls, are generally easier to use and maintain the code base for than their predecessors. Such controls can change the face of your application, improving the look and feel, and increasing overall developer productivity.

  • Increased native support

    Recent products by Microsoft are incorporating native support for the .NET Framework. For example, Microsoft BizTalk® Server has native orchestration and messaging support for the .NET framework, and therefore has been optimized to work efficiently with .NET data providers.

  • Explicit control of data access behavior

    The design of ADO includes implicit behaviors that may not always be required in an application and that may therefore limit performance. ADO.NET provides well-defined, factored components with predictable behavior, performance, and semantics that enable you to address common scenarios in a highly optimized manner.

Reasons to Use the COM Provider

As with the .NET providers, the main reasons to use the COM providers will be familiar to many who have examined the issue for other technologies: COM providers have been integrated with current and legacy technologies, use less system resources than the .NET equivalent, and do not require conversions or upgrades in order to implement.

  • Speed and efficiency

    Like most COM-based technologies, the OLE DB provider tends to run with a smaller footprint and at greater speeds while using less network resources than the equivalent .NET provider does. While this is often countered with the increased ease in which .NET applications can be implemented and maintained, the OLE DB providers are more effective in situations where speed and bandwidth efficiency is a prime consideration.

  • Scalability

    As a consequence of the previous point, an optimized OLE DB provider will scale more efficiently than the equivalent .NET provider will. Therefore, in a large-scale enterprise dealing with thousands of transactions per second over a wide-area network, you will most likely be better served using a COM-based connection.

  • Architecture restrictions

    ADO .NET is optimized for a disconnected business model, where data is stored locally in discrete chunks. However, architectures designed with uninterrupted connections in mind will have little reason to take advantage of this feature.

    For example, an enterprise whose primary clients use server-side cursors to access data on a mainframe over a landline would not need to use the disconnected business features built into ADO.NET.

  • Provider and driver support

    Host Integration Server 2004 supports several different options for data access. The OLE DB Provider for DB2 allows you to access a DB2 database; the OLE DB Provider for Host Files allows you to access the file system. While a .NET solution would grant you access to DB2, there is no equivalent .NET provider for accessing the file system.

    In addition, Host Integration Server 2004 supports an ODBC driver for DB2. In contrast, ADO.NET supports a general-purpose ODBC provider. Like a COM-based provider, the ODBC driver for DB2 is faster and has a smaller footprint than the equivalent .NET provider has. While not technically a COM-based solution, the ODBC driver is mentioned here as another possible reason for using a technology other than .NET.

  • Product compatibility

    Some commonly used products are compatible only COM-based technologies and not with the .NET Framework. Specifically, SQL Server 2000 consumers utilize OLE DB and not ADO or ADO .NET. Similarly, Office XP consumers utilize ODBC and ADO. Therefore, when writing applications that interface with these products, you should also use the OLE DB Provider.

  • Native support

    Many applications natively support COM access, and are therefore more efficient when accessing data through OLE DB than the .NET equivalent. Specifically, BizTalk Server Orchestration has native support for COM, which more effectively supports writing custom Receive Functions and AICs using the OLE DB Provider for DB2.

  • Conversion overhead

    You may be working in an environment in which you have an existing code base that is not completely compatible with the .NET Framework. In order to use the .NET providers, you would have to translate your application into a language compatible with .NET, such as Microsoft Visual Basic® .NET. As doing so is a non-trivial task, it may pose a greater resource drain than you are willing to accept at this time.

To download updated Host Integration Server 2004 Help from www.microsoft.com, go to http://go.microsoft.com/fwlink/?linkid=29507.

Copyright © 2004 Microsoft Corporation.
All rights reserved.
Show: