Building SQL Server Applications Overview

Application Programming Interfaces (APIs) are the mechanisms used by applications to access resources on the local computer or available through a network. Microsoft® SQL Server™ 2000 supports several classes of APIs that applications can use to access SQL Server resources:

* General database access APIs allow applications to work with the data in a relational database. The APIs present results to applications in one of two forms:
o Tabular result sets, which some APIs call rowsets.

o XML documents, which are the preferred way of representing data in Internet applications.
* SQL Server database services APIs allow applications to administer and configure the services included with the relational database engine, such as replication and Data Transformation Services (DTS).

* The Analysis Services API gives applications access to the OLAP and data mining facilities of Analysis Services. For more information, see Programming Analysis Services Applications.

* The Meta Data Services API gives applications access to the repository of SQL Server meta data stored in Meta Data Services. For more information, see Programming Meta Data Services Applications.

* The English Query API provides applications the ability to pass customer questions, written in English, about information in a database or OLAP cube to the English Query engine. The engine returns a Transact-SQL statement or MDX query that can be executed to answer the question. For more information, see Developing and Deploying English Query Applications.


For information about additional considerations regarding the use of the APIs supported by SQL Server 2000, see Application Development Architecture.
General Database Access APIs

Database applications generally deal with data in one of two formats:

* Tabular result sets, which are sometimes called rowsets. The application uses a database API to execute a Transact-SQL statement and process any result sets that may be returned. These APIs support result set processing: ADO, OLE DB, ODBC, Embedded SQL for C, and DB-Library.

* XML documents. The application uses an API or Universal Resource Locator (URL) to execute a Transact-SQL statement or XPath query. The application then retrieves any XML document that is returned. These access methods support XML documents: ADO, URLs, OLE DB.

While result set and XML processing is typically discussed in relation to retrieving the results of a command, result sets and XML documents can both be used as the source of data for modifications of database tables:

* An application using tabular result sets can open a cursor over a result set, and use data from the cursor to modify data in tables.

* An application using XML documents can use sp_xml_preparedocument to add a document to the database, and then use OPENXML to retrieve data from the document. The retrieved data can be used to modify data in tables.

Most of the general database APIs supported by SQL Server are of two types:

* An object database API uses an object model comprised of objects, properties, and interfaces an application uses to connect to a database, pass commands to the database, and retrieve results.

* A C database API is a set of C functions an application calls to connect to a database, pass commands to the database, and retrieve results.

In addition, SQL Server 2000 can be accessed from URLs in Internet applications. URLs are formatted strings, or stream objects, that Internet applications use to access resources available through the Internet or an enterprises intranet. SQL Server 2000 supports URLs that specify Transact-SQL statements, query templates, or XPath queries.

Any SQL commands sent to SQL Server 2000 through the database APIs or URLs must comply with the Transact-SQL language. Transact-SQL complies with the Entry Level of the SQL-92 standard, and in addition, supports powerful extensions to SQL-92.The SQL Server OLE DB provider and SQL Server ODBC driver also support the ODBC SQL specification. For more information, see Transact-SQL Overview.

These are the general database APIs supported by SQL Server 2000.

Programming ADO SQL Server Applications
(Microsoft ActiveX® Data Objects)
COM API recommended as the primary API for accessing data from general business applications, such as human resources, accounting, and marketing applications. ADO encapsulates the OLE DB API in a simplified object model that reduces application development and maintenance costs. The SQL Server OLE DB provider is the preferred provider to use in ADO applications that access SQL Server. ADO, similar to OLE DB, can access data from many sources, not just SQL databases. In SQL Server 2000, ADO supports XML document processing in addition to relational result set processing.

URL Access
Formatted strings or stream objects used by Internet applications to access resources available on the Internet or intranet. SQL Server 2000 supplies an ISAPI .dll that Internet Information Services (IIS) supports references to SQL Server 2000 from URLs.

OLE DB and SQL Server
Strategic, low-level, COM API for accessing data. OLE DB is recommended for developing tools, utilities, or low-level components that need high performance. The SQL Server OLE DB provider is a native, high performance provider that accesses the SQL Server TDS protocol directly. In SQL Server 2000, OLE DB supports XML document processing in addition to relational result set processing.

Programming ODBC SQL Server Applications
(Open Database Connectivity)
Open C API designed to access data in SQL databases. The SQL Server ODBC driver is a native, high-performance driver that directly accesses the SQL Server TDS protocol.

Programming Embedded SQL for C
Standard API defined for accessing SQL databases from C or COBOL applications.

Programming DB-Library for C
Legacy C API designed to work with SQL Server.

Warning While the DB-Library and Embedded SQL for C APIs are still supported in Microsoft SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will also be dropped in a future release. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server.

Through its support of ODBC, SQL Server 2000 also supports applications written to the Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. These are object APIs that encapsulate ODBC. They are not discussed further in SQL Server Books Online; programmers using RDO and DAO should refer to ODBC and SQL Server 2000 for implementation details for the SQL Server ODBC Driver.

SQL Server Books Online topics about ADO, OLE DB, and ODBC do not cover the full functionality of those APIs. The topics cover only the issues specific to those APIs when you are using the SQL Server OLE DB provider or the SQL Server ODBC driver. They assume that you are familiar with the general concepts for the API you are using, and that you have access to the documentation for the API. You can download the documentation for ADO, OLE DB, and ODBC at Microsoft Web site.

Microsoft Distributed Transaction Coordinator (MS DTC) is a component that allows applications to define distributed transactions. Distributed transactions protect the integrity of a series of updates made against multiple servers. SQL Server 2000 database applications can initiate distributed transactions themselves by calling the MS DTC API directly, but the SQL Server database engine can also call MS DTC to implement the functionality required by distributed Transact-SQL statements executed by applications. For more information, see MS DTC Distributed Transactions
SQL Server Database Services APIs

SQL Server 2000 supports APIs that allow applications to configure and administer the DTS and replication components of SQL Server. Applications can use the same administration and configuration API, SQL-DMO, which the SQL Server tools use when managing instances of SQL Server.

DTS Programming Reference
(Data Transformation Services)
Set of COM interfaces (based on OLE DB) for defining and executing complex data transformations between OLE DB data providers.

MS DTC Distributed Transactions
Component that allows applications to define distributed transactions that protect the integrity of a series of updates made against multiple servers. Applications use the transaction commands of an API or Transact-SQL, the API or SQL Server 2000 interface with MS DTC to implement the distributed transactions.

Programming Extended Stored Procedures
C API for writing SQL Server extended stored procedures.

Getting Started with Replication Programming
Set of COM interfaces for defining and managing replication between instances of SQL Server databases. You can also replicate data from heterogeneous third-party databases to SQL Server.

Developing SQL-DMO Applications
(SQL Distributed Management Objects)
Set of COM interfaces for managing and administering SQL Server 2000.

For information about additional considerations regarding the use of the APIs discussed, see Application Development Architecture.

Through its support of ODBC, SQL Server 2000 also supports applications written to the Remote Data Objects (RDO) and Data Access Objects (DAO) APIs. These are object APIs that encapsulate ODBC. They are not discussed further in SQL Server Books Online; programmers using RDO and DAO should refer to ODBC and SQL Server 2000 for implementation details for the SQL Server ODBC Driver.

SQL Server Books Online topics about ADO, OLE DB, and ODBC do not cover the full functionality of those APIs. The topics cover only the issues specific to those APIs when you are using the SQL Server OLE DB provider or the SQL Server ODBC driver. They assume that you are familiar with the general concepts for the API you are using, and that you have access to the documentation for the API. You can download the documentation for ADO, OLE DB, and ODBC at Microsoft Web site.

By : http://msdn.microsoft.com