This article has been
excerpted from book "A Programmer's Guide to ADO.NET in C#".
You can access a text file using the ODBC data provider. There are two ways to
access text files. Either you can create a DSN from the ODBC Data Source
Administrator or you access the text file directly in your application. To
create a data source for a text file, you go to the ODBC Source Admin, click the
New button (or the Add button if you're using Windows XP), and select the
Microsoft Text Driver (*.txt,*.csv) option (see Figure 11-29).
Figure 11-29: Selecting the Microsoft Text Driver (*.txt, *.csv) option
You define the DSN name and description in the ODBC Text Setup dialog box.
Uncheck the Use Current Directory option to enable the Select Directory button
and click on Option to see more options (see Figure 11-30).
Figure 11-30: Setting the DSN name and description
After that you select your DSN name. You can select any directory you want to
use. An entire text file is used as a database table (see Figure 11-31).
Figure 11-31: Selecting directory and file types
You can even define different formats by using the Define Format button. As you
can see from figure 11-32, all files are treated as a database table. From the
Format drop-down box, you can select the type of format you want, such as comma-
delimited or tab-delimited. The Guess button guesses the column names for you.
If it doesn't find a proper format file, it creates F1 . . . Fn columns for you.
You can also add, modify, and remove columns and their types.
Figure 11-32: Defining a text file format and column settings
After creating a DSN, you can use DSN as a connection source for your
connection:
OdbcConnection conn = new
OdbcConnection("DSN=TxtDSN");
Another way to access text files is directly using the text ODBC driver in the
connection string. For example, ConnectionString in the following code defines a
connection having the Microsoft Text Driver and source directory as C:\.
// Connection string
for a Text file
string ConnectionString = @"Driver={Microsoft
Text Driver (*.txt; *.csv)};DBQ=c:\";
Every text or .csv file in the C:\directory will be treated as a database table,
which you pass in your SQL string:
OdbcConnection conn = new
OdbcConnection(ConnectionString);
OdbcDataAdapter da = new
OdbcDataAdapter
("Select * FROM Employees.txt",
conn)
To test this code, I created a Windows application, dropped a DataGrid control
on the form, and used the code shown in listing 11-3 on the Form_load event.
Listing 11-3: Accessing the TextDB.txt file
private
void Form1_load(object
sender System.EventArgs e)
{
// connection string
for a text file
string ConnectionString =
@"Driver={Microsoft Text Driver (*.txt,
*.csv)};DBQ=c:\";
// Query the
Employees.txt file as a table
OdbcConnection conn = new
OdbcConnection(ConnectionString);
conn.Open();
OdbcDataAdapter da = new
OdbcDataAdapter
("Select * FROM Employees.txt",
conn);
DataSet ds =
new DataSet();
da.Fill(ds, "TextDB");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close the
connection
conn.Close();
}
Note: Don't forget to add a reference to the Microsoft.Data.Odbc
namespace.
Now compile and run the application, and you should see data in the DataGrid.
Conclusion
Hope this article would have helped you in understanding
Accessing a Text File using ADO.NET. See my other articles on the website on ADO.NET.
![adobook.jpg]()
|
This essential guide
to Microsoft's ADO.NET overviews C#, then leads you toward deeper
understanding of ADO.NET. |