How to read/write dBase III files using C#/.NET ODBC or OLE?

Question

I have searched for various techniques on how to read/write dBase III (dbf) files using OLEDB or ODBC with C#/.NET. I have tried almost all of the tecniques posted, but without success. Can someone point me in the right direction?

Thanks for your time.

Solution 1

Something like ... ?

 ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\dBase;Extended Properties=dBase III"
Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(ConnectionString )
dBaseConnection.Open()

From: http://bytes.com/forum/thread112085.html

Solution 2

I realize this is an old thread, but in case someone gets here by google (like I have few days ago).. As I wrote here, the elegant solution is to use LINQ to VFP to read from and write to DBF files. I tested it with some dBase III files. It goes like this:

You define your table to match the DBF definition like this:

public partial class MyTable 
{
    public System.Int32 ID { get; set; }
    public System.Decimal Field1 { get; set; }
    public System.String Field2 { get; set; }
    public System.String Field3 { get; set; }
}

You define the context like this:

public partial class Context : DbEntityContextBase 
{
    public Context(string connectionString)
        : this(connectionString, typeof(ContextAttributes).FullName) 
    {
    }

    public Context(string connectionString, string mappingId)
        : this(VfpQueryProvider.Create(connectionString, mappingId)) 
    {
    }

    public Context(VfpQueryProvider provider)
        : base(provider) 
    {
    }

    public virtual IEntityTable<MyTable> MyTables 
    {
        get { return this.GetTable<MyTable>(); }
    }
}

You define context attributes like this:

public partial class ContextAttributes : Context 
{
    public ContextAttributes(string connectionString)
        : base(connectionString) {
    }

    [Table(Name="mytable")]
    [Column(Member="ID", IsPrimaryKey=true)]
    [Column(Member="Field1")]
    [Column(Member="Field2")]
    [Column(Member="Field3")]
    public override IEntityTable<MyTable> MyTables 
    {
        get { return base.MyTables; }
    }
}

You also need a connection string, you can define it in app.config like this (Data\ relative path is used as the source of DBF files in this case):

<connectionStrings>
  <add name="VfpData" providerName="System.Data.OleDb"
    connectionString="Provider=VFPOLEDB.1;Data Source=Data\;"/>
</connectionStrings>

And finally, you can perform reading and writing to and from DBF files as simple as:

// Construct a new context
var context = new Context(ConfigurationManager.ConnectionStrings["VfpData"].ConnectionString);

// Write to MyTable.dbf
var my = new MyTable
{
    ID = 1,
    Field1 = 10,
    Field2 = "foo",
    Field3 = "bar"
}
context.MyTables.Insert(my);

// Read from MyTable.dbf
Console.WriteLine("Count:  " + context.MyTables.Count());
foreach (var o in context.MyTables)
{
    Console.WriteLine(o.Field2 + " " + o.Field3);
}

Solution 3

FoxPro 2.0 files were exactly the same as dBase III files with an extra bit for any field that was of type "memo" (not sure the exact name, it's been a while). That means that if you just use a FoxPro 2.x method for accessing the files, it should work.

Comments

[runako]

You can also create an ODBC DSN for the connection in Control Panel and then connect to that in your code.

[th1rdey3]

i tried running your code but it gives Could not load type 'LinqToVfp.VfpQueryProvider' from assembly 'LinqToVFP, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'. this error. any ideas how to solve this?