ADO.NET

Accessing a Text File using ADO.NET


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.gif

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.gif

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.jpg

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.jpg

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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.
    Membership not found