SQL Server and ADO
Microsoft® ActiveX® Data Objects are a set of Automation objects that consume the OLE DB API and allow applications to consume data from OLE DB data sources. This includes data stored in many different formats, not only SQL databases. The ActiveX Data Object (ADO) API can be used from applications written in any automation-enabled language, such as Microsoft Visual Basic®, Microsoft Visual C++®, Microsoft Visual J++®, and Microsoft Visual FoxPro®.
ADO applications access data through OLE DB providers. Microsoft SQL Server™ 2000 includes a native Microsoft OLE DB Provider for SQL Server used by ADO applications to access the data in SQL Server. In SQL Server version 6.5 and earlier, ADO applications had to use the OLE DB Provider for ODBC layered over the Microsoft SQL Server ODBC driver. Although ADO applications can still use the OLE DB Provider for ODBC with the SQL Server ODBC driver, it is more efficient to only use the OLE DB Provider for SQL Server.
ADO is the API most recommended for general-purpose data access to SQL Server for these reasons:
- ADO is easy to learn and program.
- ADO has the feature set required by most general-purpose applications.
- ADO enables programmers to quickly produce robust applications.
The core capabilities of the OLE DB specification provide all the data access functionality needed by most applications. In addition, OLE DB allows individual providers to define provider-specific mechanisms to support additional features of the data engine accessed by the provider. ADO exposes the core capabilities of OLE DB, but does not expose provider-specific features. ADO applications cannot access a few SQL Server features exposed through provider-specific features of the OLE DB Provider for SQL Server, such as the IRowsetFastLoad bulk copy methods, SQL Server-specific extended diagnostic information, and auto-fetch cursors.
ADO also supports the XML functionality of SQL Server 2000. This provides an easy migration path for Internet applications coded to use ADO to retrieve a rowset and then convert the rowset into an XML document. The application can instead use ADO to execute an XPath query or a SELECT statement with a FOR XML clause, in which case the result set is built as an XML document on the server rather than having to be converted on the application computer.
ADO has evolved from the earlier, ODBC-based Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. RDO and DAO applications can be converted to ADO, and RDO and DAO application programmers quickly learn ADO. ADO is used extensively in Active Server Pages (ASP).