ADO.NET Cookbook

1.1. Connecting to an ODBC Data Source

Problem

You want to access your data source using an ODBC provider from your .NET application.

Solution

Use the ODBC .NET data provider to access data exposed through an ODBC driver.

The sample code contains a single event handler:

Connect Button.Click

Creates an OdbcDataAdapter and uses it to fill a DataTable with the Category table from the Northwind sample database. The default view of the table is bound to a data grid on the form.

The C# code is shown in Example 1-1.

Example 1-1. File: OdbcConnectForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    // Create the DataAdapter.
    String sqlSelect = "SELECT CategoryID, CategoryName, Description " +
        "FROM Categories";
    OdbcDataAdapter da = new OdbcDataAdapter(sqlSelect,
        ConfigurationSettings.AppSettings["Odbc_ConnectString"]);

    // Create the table, fill it, and bind the default view to the grid.
    DataTable dt = new DataTable( );
    da.Fill(dt);
    dataGrid.DataSource = dt.DefaultView;
}

Discussion

The ODBC .NET data provider communicates with native ODBC drivers through COM interop (.NET’s interoperability layer for COM). The following ODBC providers are guaranteed to be compatible with the ODBC.NET data provider:

• Microsoft SQL Server ODBC Driver

• Microsoft ODBC Driver for Oracle

• Microsoft Access (Jet) ODBC Driver

The .NET data provider for ODBC connects to ...