ADO.NET 3.5 Cookbook, 2nd Edition

10.22. Writing Provider- and Database-Independent Code

Problem

You need to create a solution that can work with more than one data provider. The solution needs to use both connected classes, including DataReader objects, and disconnected classes, including DataTable objects, to both retrieve and update data.

Solution

Two solutions are given—one for ADO.NET 2.0 and later, and one for ADO.NET 1.1.

The first solution targets ADO.NET 2.0 and later uses data provider factories. This solution has two methods that use data provider factories to retrieve data as both a DataReader and DataTable:

GetDataTable()

Takes provider invariant name, connection string, and select statement arguments and returns a DataTable.

GetDataReader()

Takes provider invariant name, connection string, and select statement arguments and returns a DataReader.

The solution uses these methods to retrieve data using both the SQL Server data provider and the OLE DB data provider. Results are output to the console.

You can easily extend this solution to update a data source by using data provider factories to create a DbDataAdapter as shown in the GetDataTable() method. Call the Update() method of the DbDataAdapter, passing in a DataTable or DataSet object as an argument to update changes to the data source.

The C# code in Program.cs in the project WriteProviderIndependentCode is shown in Example 10-34.

Example 10-34. File: Program.cs for WriteProviderIndependentCode solution

using System; using System.Data; using System.Data.Common; ...