Data-Access APIs

SQL Server owes its continued growth in part to its easy and efficient data access for legacy applications and the latest object frameworks. The APIs I list here are the gateways that let SQL Server function as a data source both for traditional, 2-tiered client/server applications and as a data source for Web and n-tiered applications.

7. ESQL/C (Embedded SQL for C)

One of the original SQL Server data-access technologies, ESQL/C uses embedded SQL statements in C code to access SQL Server databases. A preprocessor for C converts the SQL statements into DB-Library function calls that are incorporated into the executable program.

6. DB-Library

The DB-Library is primarily a C implementation, but SQL Server also provides a .bas file that lets you use this library with Visual Basic (VB). Although legacy applications still use the DB-Library, it doesn't support the latest SQL Server 7.0 enhancements, and Microsoft won't enhance DB-Library in the future.

5. ODBC

ODBC provides a vendor-independent database-access API. The ODBC API has been wildly successful as a database-access standard. Virtually all products that require database access support it, and the most recent ODBC driver supports the SQL Server 7.0 enhancements. You use a call-level interface (CLI) to implement the ODBC API.

4. Data Access Object (DAO)

Microsoft developed DAO to provide access to the Microsoft JET database, which Access uses. DAO is a COM-based object framework that Microsoft later expanded to address ODBC connectivity. DAO is much easier to use than its CLI-based predecessors, but its JET orientation made it less than optimal for connecting to ODBC data sources such as SQL Server. About two years ago, Microsoft introduced ODBC-Direct, a better ODBC-based extension to DAO. But by then, developers had widely adopted Remote Data Objects (RDO).

3. RDO

The RDO object framework combines the best of DAO and ODBC. Like DAO, RDO is COM-based. Unlike DAO, RDO was developed to work with ODBC. RDO is easy to use and provides good performance. However, although RDO is good at relational database access, that task is all it does. You can't use it for nonrelational data access. Microsoft phased out RDO in recent releases of Access and VB.

2. OLE DB

Positioned as the successor to ODBC, OLE DB provides access to relational and nonrelational data sources. OLE DB is the cornerstone of Microsoft's Universal Data Access strategy, and you can use OLE DB to access any data source that can be represented by a row-and-column format. But despite OLE DB's COM foundation, to directly use the OLE DB API, you need a language that supports pointers—in other words, C++.

1. ADO

ADO's position as my number-one data-access API shouldn't surprise you. Like DAO and RDO, ADO is a COM-based object framework. Unlike its predecessors, ADO is an object framework over OLE DB. So, you can use ADO and OLE DB in development tools such as VB, VBScript, Java, JScript, and ASP. If you develop new database applications, say ADO to those old data-access technologies.