How to Use Ingres ODBC with .Net

From Ingres Community Wiki

Ingres ODBC and .NET Framework

.NET applications can access Ingres through the Microsoft ODBC .NET Data Provider, using the Ingres ODBC driver. The .NET classes in the .NET System.Data.Odbc namespace (for example, OdbcConnection, OdbcCommand, and so on) can be used to access the Ingres ODBC DSN and ODBC driver.

Like all other Ingres ODBC applications, .NET applications using the Microsoft ODBC .NET Data Provider with the Ingres ODBC Driver require the Ingres Net Client to be installed on each client application. This is necessary to pick up the ODBC driver, Ingres API, Communications Server, and other Ingres components that the Ingres ODBC requires.

More detail on the Microsoft ODBC .NET Data Provider can be found in the MSDN documentation at http://msdn2.microsoft.com/en-us/library/system.data.odbc.aspx.

If the application requires use of the .NET System.Transactions.TransactionScope, you must register the Ingres ODBC Driver to Windows.

Prerequisites

It is recommended to use an ODBC DSN (data source definition) to connect to the target database. If you already have an ODBC DSN configured, or are using a connection string, proceed to section Sample Program Using MS ODBC .Net Data Provider Access.

A data source configuration is a collection of information that identifies the database you want to access using the ODBC driver. You must configure a data source before connecting to a database through ODBC.

To configure a data source, follow these steps:

1. Open the ODBC Data Source Administrator:

You can define one or more data sources for each installed driver. The data source name should provide a unique description of the data; for example, Payroll or Accounts Payable.

2. Select the User DSN or the System DSN tab, depending on your requirements. System-level applications are seen by all logins and services on your local machine. User-level definitions are seen only by users logged in using the current login name.

Note: A system DSN pointing to a public server definition is required for Microsoft Internet Information Server (IIS) and Microsoft Transaction Server (MTS).

3. Click Add.

The Create New Data Source dialog appears, which lists all the ODBC drivers installed on your system.

4. Click the driver named Ingres, then click Finish. The Ingres ODBC Administrator main page appears:

The minimum entries required for connection to the database are:

• The ODBC DSN definition name.
• The vnode name. The default "(local)" setting should be accepted if the connection is to a local database.
• The database name.

Note that the entry boxes for the database and vnode include scroll menus to show the vnodes available in your installation and the databases for each vnode.

The "Data Source" page represents the fundamental connection information. You may wish to click on the "Advanced" tab to view advanced configuration options:

6. If you click on the Data Source tab, you will be returned to the Ingres ODBC Administrator main page. Click the Apply button if you are satisfied with the connection information.

7. Verify that you can connect successfully. This step is necessary to ensure that all parameters are correct and to cache the information about the specific connection. Caching connection information improves connection response times when the connection is used by ODBC applications. Click on the "Test" button. The following display indicates a successful connection:

7. Click OK to return to the ODBC Data Source Administrator main page.

You are returned to the ODBC Data Source Administrator, where your newly defined data source appears in the Data Sources list. You are now ready to use your ODBC Data Source in ODBC applications.

More information on using the Ingres ODBC Driver can be found at Understanding ODBC Connectivity.

The ODBC DSN Configuration Table describes each ODBC configuration option.

Sample Program Using MS ODBC .NET Data Provider Access

The following is a sample .NET program that accesses Ingres through the Microsoft ODBC .NET Data Provider.

using System;
using System.Configuration;
using System.Data;
using System.IO;
class App
{
static public void Main()
{
       ConnectionStringSettingsCollection connectionSettings =
               ConfigurationManager.ConnectionStrings;
       if (connectionSettings.Count == 0)
               throw new InvalidOperationException(
                       "No connection information specified in application configuration file.");
       ConnectionStringSettings connectionSetting = connectionSettings["myODBCConnectionString"];
       // get the System.Data.Odbc provider name
       string invariantName      = connectionSetting.ProviderName;
       // get the ODBC connection string such as
       //    "DSN=mydsn;UID=myuserid;PWD=mypass
       string myConnectionString = connectionSetting.ConnectionString;
       DbProviderFactory factory = GetFactory(invariantName);
       DbConnection conn =
               factory.CreateConnection();
       conn.ConnectionString = myConnectionString;
       conn.Open();   // open the Ingres connection
       string cmdtext =
               "select table_owner, table_name, " +
               " create_date from iitables " +
               " where table_type in ('T','V') and " +
               " table_name not like 'ii%' and" +
               " table_name not like 'II%'";
       DbCommand cmd = conn.CreateCommand();
       cmd.CommandText = cmdtext;
       //          read the data using the DataReader method
       DbDataReader   datareader = cmd.ExecuteReader();
       //          write header labels
       Console.WriteLine(datareader.GetName(0).PadRight(18) +
       datareader.GetName(1).PadRight(34) +
       datareader.GetName(2).PadRight(34));
       int i = 0;
       while (i++ < 10  &&  datareader.Read())
       // read and write out a few data rows
      {     // write out the three columns to the console
               Console.WriteLine(
               datareader.GetString(0).Substring(0,16).PadRight(18) +
               datareader.GetString(1).PadRight(34) +
                       datareader.GetString(2));
       }
       datareader.Close();
       DataSet  ds  = new DataSet("my_list_of_tables");
       //          read the data using the DataAdapter method
       DbDataAdapter adapter = factory.CreateDataAdapter();
       DbCommand adapterCmd = conn.CreateCommand();
       adapterCmd.CommandText = cmdtext;
       adapter.SelectCommand = adapterCmd;
       adapter.Fill(ds);  // fill the dataset
       //          write the dataset to an XML file
       ds.WriteXml("c:/temp/temp.xml");

       conn.Close();   // close the connection
}  // end Main()
}  // end class App

Sample .NET Configuration for the Sample Program

A .NET configuration XML file can be used to define connection parameters rather than hard-coding them into the application source. Different configuration files can be deployed with the same application executable to direct the application to a different data provider, database host, or with different user credentials.

The following is an example for the above sample program:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <connectionStrings>
               <clear />
                <add name="myODBCConnectionString" 
                                   providerName="System.Data.Odbc" 
                                   connectionString="DSN= mydsn;"/>
     </connectionStrings>
</configuration>