Building an embedded database engine in C#

void CreateDobIndex()
{
  var sortOrder = 
    new DbfDotNet.SortOrder<Individual>(
    /*unique*/false);
    sortOrder.AddField("DOB");
    mDobIndex = mIndividuals.GetIndex(
    "DOB.NDX", sortOrder);
}

void CreateDobIndex()
{
 using (DbCommand cmd =
   _cnn.CreateCommand())
 {
  cmd.CommandText =
   string.Format(
   "CREATE INDEX DOB_IDX ON 
    INDIVIDUAL (DOB)");
  cmd.ExecuteNonQuery();
 }
}

Getting individuals sorted by Age 

IEnumerable<Individual>
  IndividualsByAge()
{
  foreach (Individual indiv
    in mDobIndex)
  {
    yield return indiv;
  }
}

IEnumerable<Individual> 
  IndividualsByAge()
{
 using (DbCommand cmd =
   _cnn.CreateCommand())
 {
  cmd.CommandText = string.Format(
    "SELECT * FROM INDIVIDUAL
    ORDER BY DOB");
  var reader = cmd.ExecuteReader();
  try
  {
   while (reader.Read())
   {
    yield return 
      GetNewIndividual(reader);
   }
  }
  finally
  {
   reader.Close();
  }
 }
}

As you can see the code is generally much shorter with DbfDotNet. 

I tried to drive away from having to provide a commands in a string.  

On the contrary I tried to make it use type safe members and overall more object oriented. 

High Level Interface 

I have been asked how I compare to other SQL databases.

Again DbfDotNet is not a SQL engine.

It is rather an object persistence framework, like the Microsoft Entity Framework or NHibernate.

The difference is that it doesn't translate object manipulations into SQL requests because it speaks directly to the database layer.

I would love to write a proper Dbf to Linq interface, if you want to help me on this please volunteer. 

The difference  

Using the code 

Warning: This project is at its infancy, it has not been tested thoroughly. 

You can try it but please don't use it in a live environment. 

If you want speed however and are ready to either report or fix issues that might arrise: 

1. Create a C# project
2. Reference DbfDotNet.dll in your project
3. Create a record class
4. Write some code manipulate the records

Point 3 and 4 are expanded below.

The DbfRecord class

The DbfRecord class represent one row in your table.

You can can the column attribute to change DBF specific parameters.

class Individual : DbfDotNet.DbfRecord
{
    [Column(Width = 20)]        public string FIRSTNAME;
    [Column(Width = 20)]        public string MIDDLENAME;
    [Column(Width = 20)]        public string LASTNAME;
    public DateTime DOB;
    [Column(Width = 20)]        public string STATE;
}

The system automatically chooses the DbfField most appropriate for your datatype.

The DbfTable class

In order to store your records somewhere you need to create a Table:

individuals = new DbfTable<Individual>(
     @"individuals.dbf",
     Encoding.ASCII,
     DbfVersion.dBaseIV);

Note that this using a type safe template. Every record in the table are individual's.

Record Manipulation

You can add new lines in the table by using the NewRecord

var newIndiv = individuals.NewRecord();

Then you simply use the fields in your record

newIndiv.LASTNAME = "GANAYE";

Optionally you can make a call to SaveChanges to immediately save your changes.
If you don't the data will be saved when your individual is garbage collected.

newIndiv.SaveChanges();

Index support

This is still very basic. First you define your sort order:

var sortOrder = new SortOrder<Individual>(/* unique */ false);
sortOrder.AddField("LASTNAME");

Then you can get your index:

mIndex = individuals.GetIndex("lastname.ndx", sortOrder);

You can then, In a type safe way, retrieve any individual from your index.

individual = mIndex.GetRecord(rowNo);

In order to maximize speed, the index emit its own type safe code for :

• reading the index fields from the DBF record
• reading and writing index entries
• comparing index entries

Inner architecture

DbfDotNet main class is the ClusteredFile

The ClusteredFile is a wrapper around stream that provide paging and caching support.

The ClusteredFile is the base class for DbfFile and NdxFile. It will also be the base class for memo files when I write them.

The ClusteredFile uses a class called QuickSerializer to serialize the record content to a byte array.

QuickSerializer parse the Record fields and generate a bit of IL code for every fields to allow reading, saving and comparison.

NdxFile implements a B+Tree index

Roadmap

My plan is to keep this library extremelly small. It is not my intention to implement any transaction or multi-threading support.

I will implement :

• support for every DBF fields types
• memo fields (VARCHAR type)
• multiple indexes files (*.mdx)
• Proper documentation
• LINQ (in a separate dll)

If you want to help me on this project please contact me.

Points of Interest

In order to maximize speed I forced myself to not use any thread synchronization locking.

Each set of Dbf + Indexes must be called from a given thread.
In other word each dbf file and its index can be used by only one thread.

I encountered a problem though when the Garbage Collector finalize a record, this is done in the Garbage Collector thread. I did not want to lock a resource and ended up writing this code:

class Record
{
   private RecordHolder mHolder;

   ~Record()
   {
      try
      {
         ...
      }
      finally   
      {
         mHolder.RecordFinalized.Set();
      }
   }
}

Each record has a RecordHolder that store a ReadBuffer and potentially a WriteBuffer.

When the record finalize it signal the RecordHolder that the record has been finalized. This instruction is not blocking, it raises a flag that can be used in other threads.

class ClusteredFile
{
   internal virtual protected Record InternalGetRecord(UInt32 recordNo)
   {
      RecordHolder holder = null;
      if (!mRecordsByRecordNo.TryGetValue(recordNo, out holder)) {...}
      
      record = holder.mRecordWeakRef.Target;
      if (record==null)
      {
         // the object is not accessible it has finalized a while ago or is being finalized 
         if (holder.RecordFinalized.WaitOne())
         {
            //Now it has finalized we will create a new record
            holder.RecordFinalized.Reset();
            holder.Record = OnCreateNewRecord(/*isnew*/false, recordNo);
         }
      }
      return holder.Record;
   }
}

And then when the table thread try to get the record while it is disposing we use the method : holder.RecordFinalized.WaitOne() to make sure the finalization has completed first. Most of the time this method won't be blocking your DBF thread as the record has been finalized some time ago. 

History

2009 June 4th : Added samples and ADO.Net comparison
2009 June 1st : First DbfDotNet (C#) release. 

2000 May 21st : I wrote my first database engine, it is called tDbf and works on Delphi.