Working with Data—Connecting to Access using OLE DB

Jump to: navigation, search
Visual C# Tutorials
Database Tutorials

Working with Data

© 2006 Jeffery Suddeth

Connecting to Access using OLE DB

In this section, we will use the Northwind Trader database that installs (optionally) with Microsoft Access. The program is a Console Application that simply loads a DataSet from the Customers table of the database. Then, we loop through the records, printing the CustomerName field to the screen.

Listing 15.2

// Example15_2.cs
using System;
using System.Data;
using System.Data.OleDb;
 
namespace csbook.ch15 {
 
   class Example15_2 {
 
      static void Main(string[] args) {
         string conString =
              @"Provider=Microsoft.JET.OLEDB.4.0;"
            + @"data source=c:\data\Northwind.mdb";
 
            
         // create an open the connection          
         OleDbConnection conn = new OleDbConnection(conString);
         conn.Open();
 
         // create the DataSet
         DataSet ds = new DataSet();
 
         // create the adapter and fill the DataSet
         OleDbDataAdapter adapter = 
            new OleDbDataAdapter("Select * from Customers", conn);
         adapter.Fill(ds);
 
         // close the connection
         conn.Close();
 
         DataTable dt = ds.Tables[0];
         foreach (DataRow dr in dt.Rows) {
            Console.WriteLine(dr["CompanyName"].ToString());
            }
         }
      }
   }

The listing first creates a connection string that provides information to the OleDbConnection class. Specifically, the connection string contains the provider for the database engine that we want to connect to and the data source, which in this case is a Microsoft Access file. If the file were password protected, we would also specify a User ID and Password in the connection string. After creating the connection string, the program creates an OleDbConnection object, passing the connection string as the argument.

Next, the listing opens the database connection by calling the connection’s Open method. It also creates an empty DataSet that will later be filled from the database.

The OleDbAdapter class encapsulates the command to fill the DataSet. We create the adapter by passing a SQL query string and the database connection to its constructor. This query string will select all fields of all rows from the Customers table. The adapter’s Fill method will execute the query through the connection and load the results into the DataSet that we pass to it. The results include the data from the query as well as the metadata, defining its structure. When the call is complete, the DataSet will contain a table with all the Customer records from the database. At that point, we can close the database connection so that it can be used by some other application.

The tables within the DataSet are represented by the DataTable class. Each DataTable has a collection of DataRow objects storing the rows from the query. The foreach loop iterates through the DataRows, displaying the CompanyName field. The DataRow object uses the string "CompanyName" as an index to find the field of that name and retrieve its value. The value is returned as an object type. Calling the virtual ToString method will result in displaying the string value of the field.


prevpp.png  nextpp.png
C# Online.NET