SQL Server Connectivity Basics

Introduction

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.

Ado.net

Ado.net applications typically connect to SQL Server using the. NET Framework Data Provider for SQL Server. This native provider enables the Ado.net object to communicate directly with SQL Server. Typically, the application uses the SqlConnection object to establish a connection and then uses the SqlCommand object to send commands to the server and receive the results returned by the server. The SqlDataAdapter and SqlDataReader classes are typically used with SqlCommand to interact with SQL Server through managed code applications.

With the OleDbConnection class, the Ado.net application can also interact with SQL Server using the SQLOLEDB OLE DB provider. In addition, they can use ODBC to access SQL Server through the OdbcConnection class. Therefore, with managed code alone, you have three different ways to access SQL Server from your application. From the perspective of troubleshooting, it is useful to understand these methods because it helps you to boil down the connection-related issues you encounter to a specific data access layer or library.

Client Net-library

The next layer in the stack is net-library. Net-library provides a channel between an API or object library (where the application uses it to communicate with SQL Server) and a network protocol (used to exchange data with the network). SQL Server provides net-library for all major network protocols. These libraries transparently send client-issued requests to SQL Server and return the server-issued response to the client. You can use the Client Network Utility of SQL Server to configure the net-library for a specific client. The supported client protocols include TCP/IP, Named pipes, NWLink, Multi-Protocol (RPC), and some other protocols.

Particularly noteworthy is the Net-library shared memory net-library. As the name suggests, the net-library uses the shared Memory feature of Windows to communicate between the SQL Server client and the server. Obviously, this means that the client and the server must be on the same physical computer.