MS SQL Server Connectivity Knowledge

The top part of the stack is the API or object library layer. The application is connected to the Microsoft®sql Server through API functions or interfaces exposed by the object library. Examples of APIs used to access SQL Server include ODBC and db-library. Examples of object libraries for accessing SQL Server include OLE DB, ADO, and Ado.net. Because ADO eventually communicates with the server using OLE db, Windows applications actually use only two common object libraries, that is, OLE DB and Ado.net, when communicating with SQL Server. Because connecting through ADO or ado.net is generally more common than connecting through ODBC (but SQL Server Query Analyzer and Enterprise Manager are still connected through ODBC), this article will introduce SQL Se from the Ado/ole DB and ado.net perspective RVer the client that connects the architecture. Today, most applications connect to SQL Server through object libraries rather than ODBC or similar APIs.

ADO and OLE DB

The OLE DB client (also known as the consumer) communicates with the server and other back-end programs through a client-side provider. This provider is a set of COM components (one or more) that are used to convert application requests to network interprocess communication (IPC) requests. When using SQL Server, the most commonly used OLE DB provider is SQLOLEDB, which is the OLE DB provider provided by Microsoft for SQL Server. SQLOLEDB is attached to SQL Server and is installed as part of the Microsoft Data Access Component (MDAC) library.

To communicate with SQL Server using ADO, the application first uses the Connection object to establish a connection to the server. The Connection object of ADO accepts a connection string that specifies the OLE DB provider to use and the parameters passed to it. If an application connects to SQL Server using the SQLOLEDB provider, the string will display "SQLOLEDB".

ADO applications can also connect to SQL Server through ODBC. To do this, the application will use an OLE DB provider that is appropriate for ODBC and specify the ODBC data source that references the destination SQL Server in its connection string. In this case, the application communicates with OLE DB, and the ODBC OLE DB provider invokes the appropriate ODBC API for a session with SQL Server.