Working with the OLEDB Data Provider

This next section gives you a brief introduction to using the OLEDB data provider. One of the main goals of data providers is to provide a standardized, unified method of access to differing types of data. As a result, the way you use the OLEDB data provider is extremely similar to the way in which you use any other valid data provider for the .NET Framework. As a result, this section focuses mainly on the differences between the two providers, rather than how they are alike.

Overview of the OLEDB Data Provider

The OLEDB provider is a set of classes that provides a managed wrapper around an OLEDB data source. Unlike the SQL Server Data Provider, which only connects to a single type of server, the OLEDB provider can connect to any OLEDB data source. These data sources can include everything from Microsoft Access databases, to MS Office documents such as Excel, and even SQL Server using the OLEDB drivers. As a result, some functionality that is exposed by the provider might not be available depending on the type of data source being used, and some functionality may behave differently. For a complete list of what functionality is and is not available for a particular OLEDB data source, consult the manufacturer's documentation for its OLEDB driver.

Using the OleDbConnection

OleDbConnection provides a connection to a data source that is exposed through an OLE DB server. OLE (Object Linking and Embedding) is the pre-cursor to COM and OLEDB data sources are exposed through a standardized set of binary interfaces. Any server that implements these interfaces can expose its data to OLEDB, making a large number of varying and diverse types of data available to the same client API.

The connection string is the key property of the OleDbConnection class. Like the SQL connection string, it is divided into name-value pairs that are separated by semicolons. The most important name-value pair is the keyword Provider. This defines the type of OLEDB data source to which the class instance will be connecting. The Data Source parameter defines the source of that data. For some providers, this indicates a filename, whereas other providers use this parameter to specify a server name or network address.

Using the OleDbCommand

The OleDbCommand class works in an almost identical fashion to that of the SqlCommand class, except that the underlying data source is an OLEDB provider instead of SQL Server. It enables you to execute stored procedures (if the underlying provider supports it) or execute SQL-syntax queries. Parameters can also be input or output, just as with the SQL command class.

The tricky part about the OLEDB command is that, even though the API is the same regardless of the underlying OLEDB provider, not all providers support stored procedures. So, if you attempt to execute a stored procedure using one of those OLEDB providers, you will get an exception.

Using the OleDbDataReader

The OleDbDataReader is a class that provides a read-only, forward-only cursor model for traversing data that was returned as a result from an OleDbCommand. If you know how to use one provider's data reader, you should have no trouble using a data reader for a different provider. The OleDbDataReader list of exposed methods and properties doesn't differ from that of the SQL data reader.

Using the OleDbDataAdapter

The OleDbDataAdapter is, just like all other data adapters, designed to adapt data between the original data source and a DataSet or DataTable. Using the Fill and Update methods, you can establish a bi-directional, dynamic binding that was never possible using any of ADO.NET's ADO predecessors. You will see how to accomplish this later in the chapter. Take a look at the code in Listing 27.3 illustrating the use of the Microsoft Access OLEDB provider.

Listing 27.3. A Sample Use of an OLEDB Provider to Query an Access Database

 using System; using System.Data; using System.Data.OleDb; namespace OleDbClient {   class Class1   {     /// <summary>     /// The main entry point for the application.     /// </summary>     [STAThread]     static void Main(string[] args)     {       OleDbConnection conn = new OleDbConnection(         @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=..\..\testdatabase.mdb");       conn.Open();       OleDbCommand cmd = conn.CreateCommand();       cmd.CommandText = "SELECT * FROM SampleTable ORDER BY Description";       cmd.CommandType = CommandType.Text;       // store in a DataSet       DataSet ds = new DataSet();       OleDbDataAdapter da = new OleDbDataAdapter( cmd );       da.Fill(ds);       foreach (DataRow row in ds.Tables[0].Rows)       {         Console.WriteLine("{0}\t{1}", row["ID"], row["Description"]);       }       cmd.Dispose();       conn.Close();       Console.ReadLine();     }   } }