Read Data from Delimited File using JET OLEDB and with out using JET OLEDB

Summary

This article discusses how to retrieve the data from Text Delimited file using Microsoft JET OLE DB driver / Provider's Text IISAM Driver. To study briefly about delimited files refer to the link http://en.wikipedia.org/wiki/Delimiter-separated_values




How to Read the data from the Delimited File?


Before starting to read the file we should know how to read the file. What are the various ways to get the delimited file information. 
The process is very simple process which is similar as executing the query from the application coding. The file existing folder is considered as a Database by the JET provider. The file will be treated as a Table. Other than the regular process we should know about the connection string other than this regular process (i.e, Open Database Connection, Read / Fetch the data by executing the query, Store the information into local memory as Data Table or Data Set).

Connection String Information

The following are the various connection string providers are available for reading the Delimited file data. 


OleDb Providers:
1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

ODBC Providers:

1. Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

Specify the Delimiter Information


The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
"Format" = "TabDelimited" or "Format" = "Delimited(;)"
*The delimited character is comma(,).






Read data from the file from C#


DataSet myReadData = new DataSet();
string myQuery = "select * from sampletestfile1.txt";
OleDbConnection myDBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestFolder;Extended Properties="text;HDR=Yes;FMT=Delimited"");
OleDbDataAdapter myDBAdapter = new OleDbDataAdapter(myQuery, myDBConnection);
myDBConnection.Open();
myDBAdapter.Fill(myReadData, "DelimitedTable");
myDBAdapter.Dispose();
myDBConnection.Close();


This will fetch the data from the delimited text file into data table. 






Read Data from the file through VB





Dim myReadData As DataSet = New DataSet()
Dim myQuery As String = "select * from sampletestfile1.txt"
Dim myDBConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\TestFolder;Extended Properties=" + Chr(34) + "text;HDR=Yes;FMT=Delimited" + Chr(34))
Dim myDBAdapter As OleDbDataAdapter = New OleDbDataAdapter(myQuery, myDBConnection)
myDBConnection.Open()
myDBAdapter.Fill(myReadData, "DelimitedTable")
myDBAdapter.Dispose()
myDBConnection.Close()



Read data from the file with out using Microsoft JET in VB.Net

Other than the Microsoft JET provider, Microsoft is also provided a class to read / parse the Delimited and Fixed-Length files which is named as "TextFieldParser". This class is available under Microsoft.VisualBasic.FileIO namespace which is new in the .Net Framework Version 2.0. The TextFieldParser class provides a way to easily and efficiently parse structured text files. The following example is used to parse a comma delimited file.

        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TestFolder\test.txt")
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(",")
            MyReader.CommentTokens() = New String() {"//"}
            MyReader.HasFieldsEnclosedInQuotes = True
            MyReader.TrimWhiteSpace = True


            Dim currentRow As String()
            While Not MyReader.EndOfData
                Try
                    currentRow = MyReader.ReadFields()
                    Dim currentField As String
                    For Each currentField In currentRow
                        MsgBox(currentField)
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using





The following conditions may cause an exception with this code:

• A row cannot be parsed using the specified format (MalformedLineException). The exception message specifies the line causing the exception, while the ErrorLine property is assigned the text contained in the line.
  
• The specified file does not exist (FileNotFoundException).
  
• A partial-trust situation in which the user does not have sufficient permissions to access the file. (SecurityException).
  
• The path is too long (PathTooLongException).
  
• The user does not have sufficient permissions to access the file (UnauthorizedAccessException).

Inheritance in C#

Inheritance in C#

This article discusses Inheritance concepts in the context of C# Before we understand Inheritance in C# it is important to understand the key players involved, viz Objects, Classes and Structs lasses and Structs are ‘blue-prints’ or templates from which we instantiate (create) objects Example a car may be created based on its blue print Car is the object and blue print is the class (or template)

What are types?

An object can be of the following types – Class or Struct There are many differences between the two ‘types’ The main difference between the two is the way in which they are stored in memory and the way they are accessed Classes are also called reference types Structs are known as value types Classes are stored in a memory space called ‘heap’ and Structs are stored in a memory space known as ‘stack’

Constructors:

In C#, (like other Objected Oriented languages) constructor is a method having the same name as the class The constructor is called when the object is being created It can have one or more parameters

Interfaces:

In the context of C#, an interface provides a contract A class that is derived from this interface will implement the functions specified by the interface

Inheritance:

C# supports two types of Inheritance mechanisms
1) Implementation Inheritance
2) Interface Inheritance

What is Implementation Inheritance?

- When a class (type) is derived from another class (type) such that it inherits all the members of the base type it is Implementation Inheritance

What is Interface Inheritance?

- When a type (class or a struct) inherits only the signatures of the functions from another type it is Interface Inheritance
In general Classes can be derived from another class, hence support Implementation inheritance At the same time Classes can also be derived from one or more interfaces Hence they support Interface inheritance Structs can derive from one more interface, hence support Interface Inheritance Structs cannot be derived from another class they are always derived from SystemValueType

Multiple Inheritance:

C# does not support multiple implementation inheritance A class cannot be derived from more than one class However, a class can be derived from multiple interfaces

Inheritance Usage Example:
Here is a syntax example for using Implementation Inheritance
Class derivedClass:baseClass
{
}
derivedClass is derived from baseClass

Interface Inheritance example:
private Class derivedClass:baseClass , InterfaceX , InterfaceY
{
}
derivedClass is now derived from interfaces – InterfaceX, InterfaceY. Similarly a struct can be derived from any number of interfaces.

private struct childStruct:InterfaceX, InterfaceY
{
}

Virtual Methods:
If a function or a property in the base class is declared as virtual it can be overridden in any derived classes
Usage Example:
class baseClass
{
    public virtual int fnCount()
    {
        return 10;
    }
}
class derivedClass :baseClass
{
    public override int fnCount()
    {
        return 100;
    }
}

This is useful because the compiler verifies that the ‘override’ function has the same signature as the virtual function

Hiding Methods:
Similar to the above scenario if the methods are declared in a child and base class with the same signature but without the key words virtual and override, the child class function is said to hide the base class function

class someBaseClass
{

}
class abcClass:someBaseClass
{
    public int fnAge()
    {
        return 99;
    }
}
class grandchildClass: abcClass
{
    public int fnAge()
    {
        return 10;
    }
}
In the example above the function fnAge in grandChildClass hides the function fnAge in its parent class ie abcClass

The C# compiler will generate a warning in this case The new keyword should be used when we intend to hide a method

Example:

class grandchildClass: abcClass
{
    public new int fnAge()
    {
        return 10;
    }
}

Calling Functions from the Base Class:

To call a function from the base class simply use the key word basefunctionname()
class basicMember
{
    public virtual float membershipFee()
    {
        return 100;
    }
}
class vipMember:basicMember
{
    public override float membershipFee()
    {
        return 200;
    }
    public float promoMembershipFee()
    {
        return basemembershipFee() + 60;
    }
}

What are Abstract Classes?

1) An abstract class cannot be instantiated
2) An abstract class can have one or more abstract functions
3) Abstract functions are virtual
4) They do not have any implementation
5) They need to be overridden and implemented in a derived non-abstract class

Abstract Classes and Functions example:
abstract class Car
{
    public int headlights = 2;
    public abstract price();
}

Sealed Classes:
If a class is declared as Sealed you cannot inherit from that class
Declaring a method as sealed prevents it from being overridden

sealed class LastManStanding
{
}

The compiler gives an error if any other class tries to derive from the above class

class bankManager
{
    public sealed override bool authorize()
    {
    }
}

The compiler gives an error if you override the above method

Constructors and Inheritance:

C# allocates a default zero parameter constructor to every class that does not have any explicit constructors defined If you instantiate a child class, all the constructors in the hierarchy are called The base call constructor is called first and then the next child class constructor This sequence continues until all the constructors are called If an explicit constructor is defined for a class anywhere in the hierarchy there is a possibility that the above chain is broken If this is the case the compiler raises an Error and the code will not compile

For instance, if you declare an explicit constructor with one or more parameters the above described sequence of calls to constructors in the class hierarchy which was being handled automatically is now broken This is because when you supply a constructor C# does not provide a default constructor In this case, we have to explicitly maintain the ‘chain’ Another scenario for this error is when you define an explicit constructor with zero parameters and mark it as private The compiler will raise an error in this case

Visibility Modifiers in C#:

public: Any types or members can be prefixed with this modifier If a member or type is prefixed with public it is visible to all the code

protected: It can be used for any member or a nested type This causes the member/nested type to be visible to any derived type

private: This can be used for any type or member and the member/type will be visible only inside the type where it was defined

internal: This causes the member/nested type to be visible within the assembly where it is defined

protected or internal: This causes the member/nested type to be visible within the assembly where it is defined and any derived type

Interfaces:

When a class derives from an Interface it implements the functions specified by the interface

Defining an Interface

We can define an interface as follows:

public interface IbankManager
{
    bool authorize();
}

Implementing an Interface

The above interface can be implemented as follows:

public class newManager:IbankManager
{
    public bool authorize()
    {
        //process
        return true;
    }
}
Deriving an Interface

Interfaces can be derived from other interfaces

Example:

public interface IbranchManager:IbankManager
{
    public string sendReports();
}

Summary:

In this article we discussed Inheritance as implemented in C# and NET We also reviewed the key players involved – Classes, Structs and Interfaces


Sources: Exforsys ,