Connecting to a Text file using ADO.NET

Fig 1.01. Output to ListView from a tab-delimited text file

After playing around with ADO.NET and getting an important question answered from the ADOGuy site. I was able to piece together how to use ODBC with a text file in .NET.

The first step is to create a Data Source Name (DSN)  in order to connect through ODBC.  This can be done by bringing up the ODBC Drivers Administration tool through the control panel

Fig 1.02. ODBC Data Sources under the control panel

You can then choose the Add button to create a new DSN on the User DSN tab.

Fig 1.03. Creating a new DSN for reading the text file

The Add Button brings up a wizard so that you can pick the driver you are interested in.  In our case, it's the text driver

Fig 1.04. Choosing a text ODBC driver

Choosing the text driver brings up the Text Setup Dialog.  Here you can set the name of the datasource, the directory it is located in, and the extension:

Fig 1.05. setting up the ODBC Text Data Source for our data

Clicking on the Define Format button gives you some important additional options:

Fig 1.06. Telling ODBC the particular format of our data

Each text file is treated as a table and if you check the column name header, the names of the columns are extracted from the first line of the file.  This dialog also lets you choose if the file is tab-delimited, comma-delimited (CSV), or custom delimited. In the custom delimited format, you can choose your own delimiters such as | or \.   You can also set your column names in this dialog or have Microsoft guess for you from the column header line.

Once you've set up your ODBC Data Source, you are ready to use it in C#.  Below is the code used to read 4 of the columns into a list view.  The Data Source driver names the tables after the file names (e.g. 'registrations.txt' or 'mar31, 2001.txt'.  The text database is considered the entire directory of text files.

The code shows how to read data from one of the tables, registrations.txt.

try
{
    // Create a new ADOConnection to the text file through ODBC and an existing Data Source
    ADOConnection conn = new ADOConnection("Provider=MSDASQL;DSN=registrations;");
    
    // Create a DataSet Command that selects all the records from the registration.txt table (which is a file in this case)
    ADODataSetCommand AdoCmd = new ADODataSetCommand("SELECT * FROM registrations.txt", conn);
    
    // Fill the dataset with the registration.txt table
    AdoCmd.FillDataSet(dataSet1, "registrations.txt");
    
    // Get the first table from the dataset
    DataTable ContactTable = dataSet1.Tables[0];
    
    int count = 0;
    
    // Loop through each row of the table and fill 15 rows of the listView
    foreach (DataRow dr in ContactTable.Rows)
    {
        listView3.ListItems[count].Text = dr["LastName"].ToString();
        listView3.ListItems[count].SetSubItem(0, dr["FirstName"].ToString());
        listView3.ListItems[count].SetSubItem(1, dr["Company"].ToString());
        listView3.ListItems[count].SetSubItem(2, dr["Address"].ToString());
        count++;
        
        if (count > 15)
        {
            break;
        }
    }
}
catch (ADOException ae)
{
    Console.WriteLine(ae.Message.ToString());
}

That's all there is to it.  This should also give you an idea of how to connect to databases through ODBC such as Oracle, Informix, Sybase, or Interbase. All you need to do is set up the appropriate Data Source through the Administration tools and use the code above to access your tables.