CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
Table 21-2. Microsoft ADO.NET Data Providers
Data Provider
Namespace
Assembly
OLE DB
System.Data.OleDb System.Data.dll
Microsoft SQL Server
System.Data.SqlClient
System.Data.dll
Microsoft SQL Server Mobile
System.Data.SqlServerCe
System.Data.SqlServerCe.dll
ODBC
System.Data.Odbc
System.Data.dll
Note There is no specific data provider that maps directly to the Jet engine (and, therefore, Microsoft Access).
If you want to interact with an Access data file, you can do so using the OLE DB or ODBC data provider.
The OLE DB data provider, which is composed of the types defined in the System.Data.OleDb
namespace, allows you to access data located in any data store that supports the classic COM-based OLE
DB protocol. You can use this provider to communicate with any OLE DB–compliant database simply by
tweaking the Provider segment of your connection string.
However, the OLE DB provider interacts with various COM objects behind the scenes, which can
affect the performance of your application. By and large, the OLE DB data provider is only useful if you
interact with a DBMS that does not define a specific .NET data provider. However, given the fact that
these days any DBMS worth its salt should have a custom ADO.NET data provider for download, you
should consider System.Data.OleDb a legacy namespace that has little use in the .NET 4.5 world (this is
even more the case with the advent of the data provider factory model introduced under .NET 2.0, which
you will learn about shortly).
Note There is one case in which using the types of System.Data.OleDb is necessary: when you need to
communicate with Microsoft SQL Server version 6.5 or earlier. The System.Data.SqlClient namespace can only
communicate with Microsoft SQL Server version 7.0 or higher.
The Microsoft SQL Server data provider offers direct access to Microsoft SQL Server data stores—
and only SQL Server data stores (version 7.0 and greater). The System.Data.SqlClient namespace
contains the types used by the SQL Server provider and offers the same basic functionality as the OLE
DB provider. The key difference is that the SQL Server provider bypasses the OLE DB layer and gives
numerous performance benefits. The Microsoft SQL Server data provider also allows you to gain access
to the unique features of this particular DBMS.
The remaining Microsoft-supplied provide