Fig 1.01 - Output to ListView from 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 column name header, the names of the columns are extracted from the first line of the file. This dialog also let's you choose if the file is tab delimited, comma delimited (csv), or custom delimited. In the custom delimited format you can choose your own delimiter 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 in this case is a file)
ADODataSetCommand AdoCmd = new ADODataSetCommand("SELECT * FROM
egistrations.txt", conn);
// fill the dataset with the registration.txt table
AdoCmd.FillDataSet(dataSet1, "registrations.txt");
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.