ADO.NET 3.5 Cookbook, 2nd Edition

3.29. Retrieving Data from a Text File

Problem

You want to use ADO.NET to access data stored in a text file.

Solution

Use the Microsoft Access Database Engine OLE DB provider to access data in a text file.

The solution creates an OleDbDataAdapter that loads the text file Category.txt, shown in Example 3-41, from the directory containing the solution file ReadTextFileData.sln into a DataTable and outputs the contents of the DataTable to the console.

Example 3-41. File: Category.txt

"CategoryID","CategoryName","Description"
1,"Beverages","Soft drinks, coffees, teas, beers, and ales"
2,"Condiments","Sweet and savory sauces, relishes, spreads, and seasonings"
3,"Confections","Desserts, candies, and sweet breads"
4,"Dairy Products","Cheeses"
5,"Grains/Cereals","Breads, crackers, pasta, and cereal"
6,"Meat/Poultry","Prepared meats"
7,"Produce","Dried fruit and bean curd"
8,"Seafood","Seaweed and fish"

The C# code in Program.cs in the project ReadTextFileData is shown in Example 3-42.

Example 3-42. File: Program.cs for ReadTextFileData solution

using System; using System.Data; using System.Data.OleDb; namespace ReadTextFileData { class Program { static void Main(string[] args) { string sqlSelect = "SELECT * FROM [Category.txt]"; string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=..\..\..\;" + "Extended Properties=\"text;HDR=yes;FMT=Delimited\";"; // Create and fill a DataTable. OleDbDataAdapter da = new OleDbDataAdapter(sqlSelect, connectString); DataTable dt = new DataTable("Categories"); ...