Working with .NET Data Providers

The System.Data.SqlClient and System.Data.OleDb namespaces provide classes that are optimized to use a specific database access API. Database access is accomplished through one of the .NET data providers. .NET data providers are the Common Language Runtime (CLR) equivalent of the OleDb providers that were used with classic ADO for the Win32/COM platform.

Your first step is to determine which one of the .NET data providers (and which namespace) is appropriate for the database you are using. The .NET data providers are as follows :

• System.Data.SqlClient

• System.Data.OleDb

• System.Data.Odbc

• Any .NET data providers from a third party

If your application targets Microsoft SQL Server 7, SQL Server 2000, or later versions, you can use classes in the System.Data.SqlClient namespace. These are optimized to provide the best performance by using SQL Server’s native Tabular Data Stream (TDS) protocol.

If your application must support older versions of Microsoft SQL Server, Microsoft Access databases, Oracle, or others, then you must use the System.Data.OleDb classes.

The Open DataBase Connectivity (ODBC) data provider is not installed as part of the Visual Studio .NET package but can be downloaded from the Microsoft website. You will need the classes in this library if you are supporting legacy systems that cannot be accessed with the OleDb data provider.

It is expected that as the .NET development platform grows in popularity, third-party database software vendors will create custom data providers for their own products.

If you are working in Visual Studio .NET, a reference will automatically be set to the System.Data.dll for most project types. If not, you must add this reference manually. The System.Data.dll assembly supports both System.Data.SqlClient and System.Data.OleDb. If you install the ODBC data provider or any third-party providers, you will have to set references to the appropriate assemblies.