SQL Operations on a Text File with ADO.NET

You can use a text file as a database with ADO.NET via the OleDb Provider. Here is how 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 (ISAM) drivers that are specified in the Extended Properties attribute of the connection string.

Text Files are supported with the text source database type as shown in the following connection string:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +System.Environment.CurrentDirectory + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";

Notice that only the folder where the text file resides is specified. The filename of the text file is specified in the SQL commands that access data in the file, similar to referencing a table name in a query.

The Extended Properties attribute can also specify whether tables include headers for field names in the first row using the HDR attribute.

It is not possible to define all the characteristics of a text file in the connection string, however. You can access files that use nonstandard delimiters and fixed-width lines by creating a Schema.ini text file that must reside in the same folder as the text file database. A sample schema for the "quotes.txt" file of famous quotations that is included in the downloadable sample is shown below:

[quotes.txt]
Format=Delimited(|)
ColNameHeader=True
MaxScanRows=0
Character=OEM

The above is saved as "Schema.ini" next to the quotes.txt text file database.

The Schema.ini file provides the 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 file name surrounded by square brackets.

The format specifier can be one of the following:

Format=CSVDelimited - Fields are delimited with commas. This is the default value.
Format=Delimited(Custom Character Here) - You can use any single character except the double quotation mark as a delimiter. In my sample file, I use the Pipe (|) symbol.
Format=FixedLength - If the ColumnName header option is true, the first line with the column names must be comma-delimited.
Format=TabDelimited - Fields are delimited with Tabs.

You can specify your fields in the text file in one of two ways:

1. Include the field names in the first row of the text file and set the ColNameHeader option to True.
2. Identify each column using the format ColN (where N is the 1-based column number) and specify the name, width, and data type of each column.

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

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

The syntax of the ColN entry is:

Col1=ID  Short Width 4
Col2=FirstName Text Width 100

The datatype can be any of Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single or Text.

The Character option specifies the character set and can be set to either ANSI or OEM.

Once this is all set up, you can issue select, update, delete and insert statments exactly the way you would with a "regular" database.

The code from my sample:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace TextFileADONET
{
    class Program
    {
         static void Main(string[] args)
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +System.Environment.CurrentDirectory + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [quotes.txt]", connectionString);
            DataTable dt = new DataTable();
             da.Fill(dt);
            foreach(DataRow row in dt.Rows)
                Console.WriteLine((string)row["ID"] + ": " + (string) row["LastName"] + (string) row["quotation"]);

           Console.WriteLine("---SELECT * FROM [quotes.txt] WHERE LastName='Einstein'---------");

            OleDbDataAdapter da2 = new OleDbDataAdapter("select TOP 1 * from [quotes.txt] WHERE LastName='Einstein'", connectionString);
            DataTable dt2 = new DataTable();
             da2.Fill(dt2);
            foreach (DataRow row in dt2.Rows)
           Console.WriteLine((string)row["ID"] + ": " + (string)row["LastName"] + " " + (string)row["quotation"]);
           Console.WriteLine("Any key to quit.");
           Console.ReadLine();
        }
    }
}

You can download the sample code here.

By Peter Bromberg   Popularity  (4890 Views)