ADO.NET 3.5 Cookbook, 2nd Edition

1.14. Connecting to a Text File

Problem

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

Solution

Use the OLE DB .NET data provider.

The solution creates and opens a connection to a text file using the OLE DB .NET data provider. Information about the connection is displayed.

The C# code in Program.cs in the project ConnectTextFile is shown in Example 1-15.

Example 1-15. File: Program.cs for ConnectTextFile solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectTextFile
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\..\..\;" +
                "Extended Properties=\"text;HDR=yes;FMT=Delimited\";";

            OleDb Connection connection = new OleDbConnection(connectString);
            connection.Open(  );

            // Output some connection properties to the console
            Console.WriteLine("Connection.String = {0}\n",
                connectString);
            Console.WriteLine("Connection.State = {0}",
                connection.State);
            Console.WriteLine("Connection.Provider = {0}",
                connection.Provider);
            Console.WriteLine("Connection.ServerVersion = {0}",
                connection.ServerVersion);

            connection.Close(  );

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in Figure 1-15.

Figure 1-15. Output for ConnectTextFile solution

Discussion

The .NET OLE DB provider can read records from and insert records into a text file data source using the Microsoft Access database engine (ACE) driver. The ACE driver can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Text files are supported with the text source database type as shown in the following example:

	"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\;
	Extended Properties="text;HDR=yes;FMT=Delimited";

Notice that only the directory for the text file is specified in the connection string. The filename of the text file is specified in the T-SQL commands that access data in the text file, similar to a table name in a database.

The Extended Properties attribute can, in addition to the ISAM version property, specify whether tables include headers as field names in the first row of a range using an HDR attribute.

It is not possible to define all characteristics of a text file through the connection string. You can access files that use nonstandard text delimiters and fixed-width text files by creating a schema.ini file in the same directory as the text file. A text file, Category.txt, is shown in Figure 1-16.

Figure 1-16. Text file Category.Txt

A possible schema.ini file for the Category.txt file is:

	[Category.txt]
	Format=CSVDelimited
	ColNameHeader=True
	MaxScanRows=0
	Character=OEM
	Col1=CategoryID Long Width 4
	Col2=CategoryName Text Width 15
	Col3=Description Text Width 100

The schema.ini file provides this schema information about the data in the text file:

• Filename

• File format

• Field names, widths, and data types

• Character set

• Special data type conversions

The first entry in the schema.ini file is the text filename enclosed in square brackets. For example:

	[Category.txt]

The Format option specifies the text file format. Table 1-2 describes the options.

Table 1-2. Schema.ini format options

Format	Description
CSV Delimited	Fields are delimited with commas: Format=CSVDelimited This is the default value.
Custom Delimited	Fields are delimited with a custom character. You can use any single character except the double quotation mark (") as a delimiter: Format=Delimited(customCharacter)
Fixed Length	Fields are fixed length: Format=FixedLength If the ColumnNameHeader option is True, the first line containing the column names must be comma-delimited.
Tab Delimited	Fields are delimited with tabs: Format=TabDelimited

You can specify the fields in the text file in two ways:

• Include the field names in the first row of the text file and set the ColNameHeader option to True.

• Identify each column using the format ColN(where N is the one-based column number) and specify the name, width, and data type for each column.

The MaxScanRows option indicates how many rows should be scanned to automatically determine column type. A value of 0 indicates that all rows should be scanned.

The ColN entries specify the name, width, and data type for each column. This entry is required for fixed-length formats and optional for character-delimited formats. The syntax of the ColN entry is:

	ColN=columnName dataType [Width n]

The parameters in the entry are:

columnName

The name of the column. If the column name contains spaces, it must be enclosed in double quotation marks.

dataType

The data type of the column. This value can be Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single, or Text.

DateTime values must be in one of the following formats: dd-mmm-yy, mm-dd-yy, mmm-dd-yy, yyyy-mm-dd, or yyyy-mmm-dd, where mm is the month number and mmm are the characters specifying the month.

Width n

The literal value Width followed by the integer value specifying the column width.

The Character option specifies the character set; you can set it to either ANSI or OEM.