Exploring the System.Data.SqlClient Namespace

The SqlClient namespace is responsible for all Microsoft SQL Server^[3]specific operations. As I'll illustrate later, the SqlClient namespace is specifically designed for (and only for) Microsoft SQL Server Version 7.0 and later. The .NET Framework supports several similar classes used to interface to other specific data sources like Oracle (System.Data.OracleClient) and generic data sources like ODBC (System.Data.Odbc) and OLE DB (System.Data.OleDb), as shown in the Object Browser dump in Figure 8.13. Over the years, these providers have moved from place to place, but they have settled down in their current homes, so addressing them is no longer an issue. I don't expect them to move around again, but, then again, no one expected that it would take three years to get SQL Server 2005 on the streets.

^[3] No, the SqlClient .NET Data Provider does not work with Sybase SQL Server. You'll need to use the OLE DB or (better yet) the ODBC .NET Data Providersunless you can find a Sybase-specific .NET Data Provider.

Figure 8.13. The Provider-specific namespaces in the .NET Framework.

Note that the System.Data.SqlClient namespace is derived from the classes in System.Data.Common. Remember the Class Diagram in Figure 8.5? Is this important to know? Yes, if you need to create a generic application that needs to address more than one backend database. Using 2.0 Framework "factory" classes, you can generate appropriate SQL Serverspecific (SqlClient) classes as well as Oracle-specific (OracleClient), OLE DB (OleDb), or ODBC-specific (Odbc) classes, as needed. I'm going to defer further discussion of this topiclook for another EBook on this feature.

Choosing the Right ProviderWhen it's Not SQL Server

When choosing the "right" data access provider, be sure to pick a "native" interface first. That is, if there is a .NET Data Provider designed to interface to your specific data source (like SQL Server or Oracle), use it. The same is true for the new SQL Server Everywhere. It uses the SqlCE namespace that must be referenced manually. If the .NET Framework does not include a suitable native provider, look for one elsewhere. There are a wealth of other providers out there sold by (or simply given away by) other vendors, like Sybase, Oracle, IBM (DB2), and others. There are also several third-party companies, like Data Direct^[4] and Cor Lab^[5], to name just two^[6].

^[4] www.datadirect.com/products/net/index.ssp

^[5] www.crlab.com/products.html

^[6] See www.sqlsummit.com/DataProv.htm for a list of .NET Data Providers from a host of vendors.

One big difference between providers is their dependence on COM-based components. If a provider says that it's "managed," there's a good chance that it's not COM-dependent. However, even Microsoft calls its System.Data.OleDb .NET Data Provider "managed," when it's really dependent on COM-based OLE DB providers. Perhaps the distinction is "100% managed" code, as touted in the DataDirect providers. In any case, if the provider depends on COM, it's going to be slower and more trouble-prone. That's because any references to COM components from a .NET application have to traverse a layer of plumbing code called "COM interop," which exposes COM objects so they look like .NET objects, and vice versa. While it's relatively fast, it's far from free of performance and interface-translation issues. It's also fraught with other (shall I say) "issues" that make it tough to trust. All too often. I hear about problems caused by COM interop and how it did not emulate the COM component in the same way as it executed in a COM-based application.

If you must access a data source that does not support a native managed interface, you'll have to use one of the one-size-fits-all (OSFA) .NET Data Providers like System.Data.Odbc or (as a last resort) System.Data.OleDb. The Odbc provider uses the same ODBC drivers you've been using for a decadewith all of the benefits and issues that have been discussed in earlier editions of the Hitchhiker's Guide. In my tests, the Odbc .NET Data Provider (which is not COM-dependent) is about 20% faster than OleDb. No, Odbc is not a good choice for situations where you need to access a JET^[7] database, as the Odbc drivers don't expose all of the functionality you might need. This means you're stuck with OleDb when accessing JET databases. However, for any data source that doesn't have a native provider, Odbc is your best choice.

^[7] JET: Remember that Access defaults to use of the JET database engine to manage its database file.

IMHO

Don't rely on my (or anyone's) testsespecially if they have the words "Sales" or "Marketing" in their title. They might not match your configuration or hardware setup. Create and run your own tests on your own hardware.