Application Programming Interfaces
Microsoft® SQL Server™ supports several database application programming interfaces (APIs): Active Data Object (ADO), OLE DB, Open Database Connectivity (ODBC), Remote Data Object (RDO), Data Access Object (DAO), the Microsoft Foundation Class (MFC) Database Classes, Embedded SQL, and DB-Library. SQL Server supports these APIs usually in the form of a dynamic-link library (DLL) called a provider or driver. The provider or driver translates the calls made by the application to the database API into commands sent to an instance of SQL Server.
To work with a database, an API:
- Opens a connection to the database.
- Sets options that control certain behaviors, such as whether cursors will be used, what type of cursor will be used, and whether updates are allowed.
- Executes a Transact-SQL statement. Optionally, the application may also use program variables to supply parameter values for the executed statement. An application may execute only one statement at a time on each connection, or it may execute several simultaneously.
- Moves the data values of return codes, output parameters, and result sets into program variables, where they can be used by the application logic. If the statement returns its result set in the form of an XML document, the application can stream that to a component that consumes XML documents.
- Disconnects when finished working in the database.
The application programmer must understand both Transact-SQL and the proper use of the database API.