C# ADO.NET ODBC tutorial

Use the ODBC .NET Data Provider to access data from your C Sharp ADO.NET applications.

Contents

• .NET and data access
• What does this document show?
• A simple example
• Getting error information
• Updating data
• Multiple results

.NET and data access

ADO.NET is the data access model for .NET-based applications. This interface will be familiar to users of the older ActiveX Data Objects (ADO) data access interface. However, there are some important differences between the two.

A useful Microsoft document that details ADO.NET from an ADO programmer's perspective can be found at:

http://msdn.microsoft.com/en-us/library/ms973217.aspx

One of the key changes that ADO.NET introduces is the replacement of the ADO Recordset object with a combination of the DataTable, DataSet, DataAdapter, and DataReader objects. A DataTable represents a collection of rows from a single table, and in this respect is similar to the Recordset. A DataSet represents a collection of DataTable objects, together with the relationships and constraints that bind the various tables together.

One of the key characteristics of the DataSet is that it has no knowledge of the underlying data source that might have been used to populate it. The actual interface to the underlying data source is provided by using one of a number of .NET Data Providers. A .NET Data Provider comprises of four key objects (Connection, Command, DataReader and DataAdapter).

.NET Data Providers fall into two categories: bridge providers and native providers. Bridge providers, such as those supplied for OLE DB and ODBC, allow you to use data libraries designed for earlier data access technologies. Native providers are normally provided by the actual database supplier and provide interfaces to specific databases. For example, SQL Server has a native provider available from Microsoft.

What does this document show?

This document concentrates on the use of the ODBC .NET Data Provider to provide access to data though the use of ODBC drivers, which are available for most database systems. The remainder of this document will use Easysoft ODBC-ISAM Driver as the sample ODBC driver, this provides a connection through the Easysoft ODBC-ODBC Bridge to data stored in ISAM files. The examples are equally appropriate when using the ODBC-ODBC Bridge, the Easysoft Oracle ODBC driver, the Easysoft InterBase ODBC driver, the Easysoft RMS ODBC driver or any other Windows ODBC driver.

What do you need to recreate these examples?

You can download the ODBC drivers from the Easysoft web site. All the Microsoft .NET tools are available from Microsoft.

The sample ODBC data source

In this document, we will use the sample data provided by installing the Easysoft ISAM ODBC driver on the target machine, and the Easysoft ODBC-ODBC Bridge client on the local Windows machine.

A simple example

The following steps create an application that shows the basic operations used to interface ODBC and .NET.

1. Create a new Visual Studio Project. For this example, we will use a C# Console Application.
2. Import the System.data.Odbc namespace into the application, to allow us to use the ODBC .NET Data Provider:

   using System.Data.Odbc;

3. Create an OdbcConnection object and connect it to our sample data source.

   OdbcConnection DbConnection = new OdbcConnection("DSN=SAMPLE_ISAM");

   This example uses a DSN connection, but a DSN-less connection can be used by using the DRIVER={} format. Consult your ODBC driver documentation for more details.

4. Open that OdbcConnection object:

   DbConnection.Open();

5. Using the OdbcConnection, create an OdbcCommand:

   OdbcCommand DbCommand = DbConnection.CreateCommand();

6. Using that command, run a query and create an OdbcDataReader:

   DbCommand.CommandText = "SELECT * FROM NATION";
   OdbcDataReader DbReader = DbCommand.ExecuteReader();

7. Use the OdbcDataReader to find the number and names of the columns in the result set, and display them on the console.

   int fCount = DbReader.FieldCount;
   Console.Write(":");
   for ( int i = 0; i < fCount; i ++ )
   {
          String fName = DbReader.GetName(i);
          Console.Write( fName + ":");
   }
   Console.WriteLine();

8. Read each row from the result set, and read each column in that row and display its value.

   while( DbReader.Read()) 
   {
            Console.Write( ":" );
            for (int i = 0; i < fCount; i++)
            {
                    String col = DbReader.GetString(i);
                           
                    Console.Write(col + ":");
            }
            Console.WriteLine();
    }

9. Once OdbcDataReader.Read() returns false, every row in the result set has been read. Clean up the objects.

   DbReader.Close();
   DbCommand.Dispose();
   DbConnection.Close();

This C# ADO.NET application connects to an ODBC data source, issues a query, and then displays the results from that query.

The complete program source for this section is CSharpODBCExample1.cs

Getting error information

CSharpODBCExample1.cs does not contain any error handling code. Unfortunately in the real world, we need to handle unexpected situations. So, we will now add some extra code to our sample that allows us to handle errors.

First, we will allow the connection string to be passed in on the command line of the application.

if (args.Length != 1 )
{
    Console.WriteLine("usage: sample connection-string");
    return;
}
String connStr = args[0];
OdbcConnection DbConnection = new OdbcConnection( connStr );

Now we can set the command line properties to the ODBC data source used earlier.

"DSN=SAMPLE_ISAM"

The program can be run as before. However, if the user enters an invalid connection string, the program will fail when the OdbcConnection.Open() method is called. The program will throw an exception at this point. To handle this, we can change our code to catch this exception and display information from the ODBC layer to the user.

The exception thrown will be an OdbcException, and we can use this to extract the error string that the ODBC Driver Manager returns:

try
{
  DbConnection.Open();
}
catch (OdbcException ex)
{
  Console.WriteLine("connection to the DSN '" + connStr + "' failed.");
  Console.WriteLine("The OdbcConnection returned the following message");
  Console.WriteLine(ex.Message);
  return;
}

We will also allow the user to enter a SQL query:

Console.Write("SQL> ");
String query = Console.ReadLine();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = query;

But now the SQL may not be valid, so the OdbcCommand.ExecuteReader() may also throw an exception, which we can catch and display.

try
{
  OdbcDataReader DbReader = DbCommand.ExecuteReader();
  // rest of the code to process the result set
}
catch (OdbcException ex)
{
  Console.WriteLine("Executing the query '" + query + "' failed."); 
  Console.WriteLine("The OdbcCommand returned the following message");
  Console.WriteLine(ex.Message);
  return;
}

The complete program source for this section is in CSharpODBCExample2.cs

Updating data

Now that we can enter a query at the command line, our application must handle queries that do more than just create result sets. At the moment, if we try to create a new table using the application, the OdbcDataReader will be created with no columns. We can use the OdbcDataReader to decide how to process the query.

int fCount = DbReader.FieldCount;
if (fCount > 0)
{
        // process result set
}
else 
{
        // process non result set
}

In the case of a non-result-set generating statement, we can use the OdbcDataReader.RecordsAffected property to find out how many rows have been altered by an UPDATE or DELETE or INSERT statement.

else
{
        int row_count =  DbReader.RecordsAffected;
        Console.WriteLine( "Query affected " + row_count + " row(s)");
}

Our sample can now query and update the database.

The complete program source for this section is in CSharpODBCExample3.cs.

Multiple results

Most databases can now process commands like this:

select * from table1; select * from table2

This creates more than one result set, so we will now add code that allows each result set to be processed in turn. The OdbcDataReader object has a method NextResult() that moves the OdbcDataReader to the next result from the query. So, all we need to do is wrap the query in a loop, processing each result in turn until NextResult() returns false:

OdbcDataReader DbReader = DbCommand.ExecuteReader();
do
{
        // process result
}
while (DbReader.NextResult());

Our final program is in CSharpODBCExample4.cs.

• About Easysoft

• Contact us
• About us
• Clients
• Blog
• Careers

• Products

• ODBC drivers
• JDBC drivers
• Bridges and gateways
• In development

• Services

• Consultancy
• Training
• Custom development

• Licensing

• Product licenses
• Prices

• Support

• Support home
• Solution wizard
• Getting Started Guides
• User Guides
• Knowledge Base

• Search

• Easysoft.com
• Documentation
• Knowledge Base

• Popular Resources

• Developer area
• Client applications

• Legal

• Privacy statement

© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.

• 
• 
•