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.

Nardi

Answer 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

Alesiaalessandra

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

Answer 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);
}

Limassol

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? – Liturgist

Answer 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.

Imalda

Answer 4

This is a nice aproach, i didn't tested, but i will soon...

http://www.c-sharpcorner.com/uploadfile/rfederico/xbaseenginerfv12022005011623am/xbaseenginerfv.aspx

Animal

Answer 5

I have offered many answers on working with database files (more specifically VFP, but the Microsoft VFP OleDb provider will recognize older dbase files. You can do a search to find more of these links via:

user:74195[vfp][oledb]

First, I would start with getting the Microsoft VFP OleDb Provider download.

Next, if you already have some dbf files you are trying to connect to for testing, you need to establish a connection. The connection must point to the PATH where the files are located, not the specific .dbf file. So, if you have a folder with 20 tables in it, once you connect to the PATH, you can query from any/all the tables via standard VFP-SQL Syntax (common with many sql the overall structure, but different based on some functions like string, date and number manipulations).

Learn about PARAMETERIZING your queries. With VFP OleDb, parameters are done with the "?" character as a place-holder, so the parameters need to be added in the exact same sequence as they appear in the query. The "?" can appear as field values, join conditions, where criteria, etc.

The following are a FEW to get you started to HOPEFULLY get you started with a valid connection, query, then insert/update/delete with parameters.

1. Sample showing a connection string and simple query from a table

2. Shows a parameterized sql-insertbut in this case gets the data from another data source, such as sql-server and creating a VFP/dbf style table from it. It goes through cycling through records and pulling values for each parameter and inserting.

3. and another showing parameterized SQL-update

Good luck, and there are plenty of others who answer on VFP and OleDb Access, these are just some that I have specifically participated in and show functional implementations that may have something you may otherwise may have missed.

Tramroad