July 2, 2020
Hot Topics:

Managed Extensions: Using the .NET ODBC Classes to Read Text Data

  • By Tom Archer
  • Send Email »
  • More Articles »

Welcome to this week's installment of .NET Tips & Techniques! Each week, award-winning Architect and Lead Programmer Tom Archer demonstrates how to perform a practical .NET programming task using either C# or Managed C++ Extensions.

While data for professional-caliber applications is most typically stored in traditional database systems, sometimes the data your application must use is in text format. This includes situations where you are accessing a small amount of test data, as well as scenarios where another system is providing a text file for you to use and you have no control over its format. While most people naturally think of using streams to read and write text files, ODBC has for years provided a driver specifically for this purpose.

Why would you use the driver and incur the overhead of ODBC when you can easily stream the data? Well, for starters, ODBC provides a generic SQL-like interface to the data. Secondly, due to ODBC's generic interface, using it instead of directly accessing a file via a stream allows you to more easily use the same code to access data in other formats.

For example, let's say that the data your code will ultimately work with is stored in a traditional RDBMS (relational database management system), such as SQL Server or Oracle. However, you might want to test your application logic against a small amount of data that you can quickly enter into a text file via any editor (such as Notepad). Using ODBC, you would simply specify different DSNs or ODBC drivers (for a DSN-less connection) based on which file format you're using. That way, you wouldn't have to maintain two completely different code bases for accessing your data (one for streaming text files and one for reading from the RDMBS).

This article illustrates how easy it is to read text data using the .NET ODBC classes.

Reading Data from a DSN-less Text File

The ODBC Desktop Drivers include a driver for reading text called the Microsoft Text Driver. The easiest way to access text data is to simply use the ODBC Admin application (odbcad32.exe) and—specifying the text driver—create a DSN against the desired text file. However, I'll show you the basic steps for using the .NET ODBC classes to access a text file, such that you don't have to perform the extra step of creating a DSN. (The complete code—including basic error handling and clean-up—can be found in the dialog class of this article's demo application.)
  1. Create the Connection—Using the OdbcConnection class you can pass a connection string that allows you to specify the ODBC driver (the Microsoft Text Driver, in this case) and the path of the files. Note that I said files—plural. When you use the Microsoft Text Driver, you don't specify in the connection string the file the application will be accessing. Instead, you specify the path to the file and, optionally, the valid file extensions for any files in that path that can be opened. The reason for this is that the text driver logically treats the specified directory as a relational database and then the specific files that your application works with as tables within that database. This was a great idea by the folks at Redmond, as it more closely mimics how your code will access data from a true RDBMS. The following example creates a connection to the folder that contains the specified file:
    StringBuilder* connString = new StringBuilder();
    connString->Append(S"Driver={Microsoft Text Driver (*.txt; *.csv)};");
    OdbcConnection* connection = new OdbcConnection(connString->ToString());

    Note that when the Text Driver is used to make a connection to a given path (specified with the DBQ parameter), only files in that specific directory can be accessed—not files in any subdirectories.

  2. Create the Command—Once a connection is made, you can then create the desired command via the OdbcCommand class. This is where you would specify the file name. The following code snippet selects all rows from the specified file name, keeping in mind that the file must exist in the folder specified in the DBQ parameter of the connection string:
    CString strSelect;
    strSelect.Format(_T("SELECT * FROM [%s]"), 
    OdbcCommand* command = new OdbcCommand(strSelect, connection);
  3. Attach a Reader—Now that the command has been created, you can call the OdbcCommand::ExecuteReader method to execute the command and return a OdbcDataReader object that can be used to enumerate the returned data:
    OdbcDataReader* reader = command->ExecuteReader();
    while (reader->Read()) 
      for (int iCurrCol = 0; iCurrCol < reader->FieldCount; iCurrCol++) 
        // retrieve values via the various ODBCReader methods—such
        // as GetValue

Page 1 of 2

This article was originally published on September 14, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date