SqlClient Internals

for RuBoard

The SqlClient .NET Data Provider is what I've termed a specific or targeted provider because it's able to communicate with only a single data store; in this case, only SQL Server version 7.0, 2000, and later versions such as the release code-named Yukon. As you learned on Day 8, "Understanding .NET Data Providers," the reason you would use specific providers is that they offer two main advantages over the general providers such as OleDb and the ODBC provider.

First, specific providers can expose classes, methods , properties, fields, and events that aren't found in the base provider architecture. These additional constructs enable developers to work with the database server more closely because they expose features that are difficult or impossible to access using a generic provider. One example of this is the ExecuteXmlReader method we discussed on Day 10, "Using Commands."

Note

Both the ODBC and OLE DB architectures rely on a fundamental software development design pattern known as the adapter pattern. Simply put, the adapter pattern allows clients to work with multiple disparate pieces of software by abstracting their functionality into a well-known interface that the clients understand. In this pattern, the differences were abstracted to a lower level (the ODBC driver or OLE DB provider), resulting in wider access to data. The cost, of course, is performance as well as access to features that are very specialized.

Second, a specific provider eliminates a layer of abstraction sitting between the client code and the data store. When this extra layer is eliminated, performance naturally increases . Although much of this week has been devoted to showing the programmatic differences between SqlClient and OleDb, the differences in the underlying communication haven't been addressed. This section will explore the second of these benefits of a specific provider.

Using Tabular Data Stream

Under the covers, every client that communicates with SQL Server does so using the Tabular Data Stream (TDS) wire protocol through a SQL Server client Net-Library. Figure 13.1 depicts this architecture.

Figure 13.1. SQL Server client architecture. This diagram depicts the communication path from a client to a SQL Server.

The definition of the components shown in Figure 13.1 is as follows :

• TDS . TDS is the application-level protocol specific to SQL Server. It is a low-level protocol that specifies both commands and data in a specific arrangement. A TDS packet encapsulates the protocol and is configurable on the client. By default, the packet size is set to 4KB and should typically be left alone.

• Net-Library . This piece of software (unmanaged DLL) on the client machine receives TDS packets and encapsulates them into packets suitable for the network protocol being used. As a result, there are seven different Net-Library DLLs that install with MDAC 2.7, including Named Pipes, TCP/IP, Multiprotocol, NWLink IPX/SPX, AppleTalk, Banyan Vines, and VIA. They can be configured using the SQL Server Client Network Utility. On the server, the TDS packets are received by an analogous Net-Library (which ones the server listens on can also be configured).

• Relational Database Engine . This is the piece of software that does all the work you think of related to a database. The TDS packets are received from the server Net-Library and parsed, executed (including access to the data files), and the results are formulated as TDS packets. The return trip follows the same path in reverse with the server Net-Library encapsulating the TDS packets for transport to the client, where they are unpacked and processed .

In the past, on the client side, the ODBC driver and SQLOLEDB OLE DB provider were responsible for creating TDS packets. The SqlClient provider, however, now assumes that role by creating TDS packets directly in managed code and passing them off to the appropriate Net-Library. As you can see from Figure 13.1, different clients use different versions of TDS. This is the reason the SqlClient provider can't support all versions of the SQL Server; namely, it can create packets only for TDS 7.0 or higher, which older versions of SQL Server couldn't understand.

Note

As you no doubt will notice, the communication between SqlClient and SQL Server can be heavily influenced by the connection attributes we discussed on Day 9, "Using Connections and Transactions" ”particularly the Network Library and Packet Size attributes.

Because SqlClient creates TDS packets directly, performance is increased. In fact, Microsoft's internal tests have indicated that using the SqlClient provider in .NET applications is even faster than writing a managed Visual C++ application that accesses the SQLOLEDB OLE DB provider directly using COM. In addition, Microsoft's tests indicate that when you scale up and move from a single-processor server to a quad-processor server, performance increases by a factor of three. Rest assured, performance using SqlClient isn't an issue when developing .NET applications.

for RuBoard