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

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

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)};");
   connString->AppendFormat(S"DBQ={0};", 
                            Path::GetDirectoryName(strFileName)); 
   
   OdbcConnection* connection = new OdbcConnection(connString->ToString());
   connection->Open();
   

   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]"), 
                    Path::GetFileName(strFileName));
   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
       AfxMessageBox((CString)(reader->GetValue(iCurrCol)->ToString()));
     }
   }
   

Taking Control of the Process with the schema.ini File

By default, the text driver assumes that the data contains a column heading row. Therefore, if your data does not contain this row and you do not define a schema.ini file, you will find that the first row of data being ignored. For example, if your data looked like the following, the reader code above would display only the second and third records (leaving out your favorite author!):

Tom Archer,Archer Consulting Group
Bradley Jones,Jupitermedia
Bill Gates,Microsoft

data file

[data.txt]
ColNameHeader=FALSE

• You can include—as the first row in the text file—the column names and then specify the ColNameHeader attribute in the schema.ini file. (You can also omit the schema.ini file, as the text driver defaults the ColNameHeader value to TRUE).

  data file

  Name,Company
  Tom Archer,Archer Consulting Group
  Bradley Jones,Jupitermedia
  Bill Gates,Microsoft
  

  schema.ini file

  [data.txt]
  ColNameHeader=TRUE
  

• If the data doesn't include a column row, you can manually set the column names such that the two files look as follows:

  data file

  Tom Archer,Archer Consulting Group
  Bradley Jones,Jupitermedia
  Bill Gates,Microsoft
  

  schema.ini file

  [data.txt]
  ColNameHeader=FALSE
  Col1=Name Char Width 255
  Col2=Company Char Width 255
  

Download the Code

To download the accompanying source code for this article, click here.

About the Author

The founder of the Archer Consulting Group (ACG), Tom Archer has been the project lead on three award-winning applications and is a best-selling author of 10 programming books as well as countless magazine and online articles.