SQL Server connection Basics

The top of the stack is the API or object library layer. Applications connect to Microsoft SQL Server through APIS or interfaces exposed by the object library. Examples of APIs used to access SQLServer include ODBC and DB-Library. Examples of object libraries used to access SQLServer include OLEDB, ADO, and ADO. NET. Since ADO eventually uses OLED

The top of the stack is the API or object library layer. Applications connect to Microsoft SQL Server through APIS or interfaces exposed by the object library. The APIs used to access SQL Server include ODBC and DB-Library. Examples of object libraries used to access SQL Server include OLE DB, ADO, and ADO. NET. Since ADO eventually uses OLE D

The top of the stack is the API or object library layer. Applications connect to Microsoft through APIS or interfaces exposed by the object library®SQL Server. The APIs used to access SQL Server include ODBC and DB-Library. Examples of object libraries used to access SQL Server include OLE DB, ADO, and ADO. NET. Since ADO eventually uses ole db to communicate with the Server, Windows applications only use two common object libraries, ole db and ADO. NET, to communicate with SQL Server. Due to the use of ADO or ADO. NET connections are more common than ODBC connections (however, SQL Server's query analyzer and Enterprise Manager are still connected through ODBC). Therefore, this article will refer to ADO/OLE DB and ADO.. NET. Today, most applications connect to SQL Server through an object library instead of ODBC or similar APIs.

ADO and OLE DB

The ole db client (also called a user) communicates with the server and other backend programs through the client provider. This provider is a group of COM components (one or more) used to convert application requests to inter-network process Communication (IPC) requests. When SQL Server is used, the most common ole db Provider is SQLOLEDB, which is an ole db Provider provided by Microsoft for SQL Server. SQLOLEDB is attached to SQL Server and installed as part of the Microsoft Data Access Component (MDAC) Library.

To use ADO to communicate with SQL Server, the application first uses the Connection object to establish a Connection with the Server. The Connection object of ADO accepts a Connection string, which specifies the ole db provider to be used and the parameters passed to it. If the application uses the SQLOLEDB provider to connect to SQL Server, "SQLOLEDB" is displayed in this string ".

The ADO application can also connect to SQL Server through ODBC. To this end, the application uses the ole db provider for ODBC and specifies to reference the ODBC Data Source of the target SQL Server in its connection string. In this case, the application communicates with ole db, while the ole db provider of ODBC calls the corresponding odbc api for session with SQL Server.

ADO. NET

ADO. NET applications usually use. NET Framework Data Provider for SQL Server to connect to SQL Server. The local provider enables the ADO. NET object to communicate directly with SQL Server. Generally, an application uses the SqlConnection object to establish a connection, then uses the SqlCommand object to send commands to the server, and receives the results returned by the server. The SqlDataAdapter and SqlDataReader classes are usually used with SqlCommand to interact with SQL Server through hosted code applications.

Through the OleDbConnection class, ADO. NET applications can also use the sqloledb ole db provider to interact with SQL Server. In addition, they can use ODBC to access SQL Server through the OdbcConnection class. Therefore, only by hosting code, you have three different methods to access SQL Server from the application. From the perspective of troubleshooting, it is very useful to understand these methods, because it helps you to resolve connection-related problems to a specific data access layer or database.