Building an embedded database engine in C#

• Download DbfDotNet_version_1.0_Demo_Only - 35.4 KB (Executable)
• Download DbfDotNet_version_1.0_Source - 65.33 KB (Demo source included)

Introduction

This article present a standalone fully managed database/entity engine which implements fixed width record tables and BTree indexes. 

The latest source is available in CodePlex: http://dbfdotnet.codeplex.com/ 

I am welcoming anyone wanting to contribute to this project. 

Why an embedded database 

Although most of us will use a SQL Server to store and retrieve data sets.
There are several situation where an embedded database make sense.

• When you don't have a SQL Server available
• When you want your footprint as small as possible and can't afford SQL Express
• When you want to manipulate or cache SQL data
• When you need to write highly procedural data manipulation routines
• When you want maximum speed

Features

Despite its small size DbfDotNet provides a number of features that you might find useful

• Type safe

In DbfDotNet you manipulate classes with native field types. All data conversion plumbing is done automatically.

• Very simple entity framework

Creating a record and accessing its propery is only what you need.

• Very small memory footprint

Last time I checked the dbfDotNet dll was 50Kb. Other databases are 1Mb to 10Mb.

I would appreciate if someone could do some memory usage comparison (I will insert it here).

• Fast

DbfDotNet was conceived for speed.

DbfDotNet do not use PInvoke, Threading locks, and do not implement any transaction system.
Those 3 technologies have a performance cost that it won't have to pay.

In contrast it is using TypeSafe records (without boxing/unboxing) and type safe emitted code. The code is emitted only once per table.

It has therefore I believe the potential to be the fastest embedded .Net database there is.

I would appreciate if someone could do some speed comparison (I will insert it here).

• Very small runtime memory usage

When you use in Memory DataTable or SQL requests that return DataSets, the entire result sets is in memory.

DbfDotNet works conjointly with the garbage collector. As soon as you're finished modifying an entity the garbage collector will mark the record buffer to be saved to disk and released from memory.

Why Dbf

By default the files are compatible with dBase and can therefore be open in Excel and many other packages.

I have been asked : Why Dbf ? Dbf is an old format.

The answer is a bit long but simple.

As I said earlier DbfDotNet is designed to be as fast as possible.

In order to get the database started and get some interest I need two things:

• A good product
• A good user base

I know by experience that the DBF format will appeal to some of you for several reason:

• You can easily backup DBF files (and leave index files)
• You can check DBF content using Excel and many other tools
• DBF is well known and simple to implement
• It can be extended to modern types (and has been by clipper and fox pro)

Most importantly for me, implementing the .DBF rather that my own custom format has no impact on runtime speed.

How does it compare to ADO.Net, SQL, SqlLite, SharpSQL ... 

I did some speed test against another database (which I won't name) 

The results are quite encouraging. 

Opening DbfDotNetDatabase: 185 ms
Insert 1000 individuals: 39 ms
Read individuals sequentially: 5 ms
Read individual randomly: 3 ms
Modifying individuals: 21 ms
Create DateOfBirth index: 77 ms
     Michael Simmons 22/07/1909
     Mark Adams 21/09/1909
     Charles Edwards 28/09/1909
     ... total 1000 records
Enumerate Individuals by age: 36 ms
Closing DbfDotNetDatabase: 44 ms

Opening ADO.Net Database: 459 ms
Insert 1000 individuals: 80601 ms
Read individuals sequentially: 1655 ms
Read individual randomly: 1666 ms
Modifying individuals: 75574 ms
Create DateOfBirth index: 80 ms
     Michael Simmons 22/07/1909
     Mark Adams 21/09/1909
     Charles Edwards 28/09/1909
     ... total 1000 records
Enumerate Individuals by age: 29 ms
Closing ADO.Net Database: 0 ms

In this test Dbf.Net runs nearly 400 times faster. This is quite unfair however. Dbf.Net does not have transactions and is not ACID. 

Lets not focus to much on speed but more on code differences: 

Creating a Table 

Creating the table is quite different. Dbf.Net requires a type safe record upfront to create a table.  In ADO.Net you provide a string. 

DbfTable<dbfdotnetindividual> mIndividuals;

void CreateIndividualTable()
{
  mIndividuals = 
    new DbfTable<dbfdotnetindividual>(
      @"individuals.dbf", 
      Encoding.ASCII, 
      DbfDotNet.DbfVersion.dBaseIV);
}


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

Connection _cnn = null;


void ITestDatabase.CreateIndividualTable()
{
  _cnn = new System.Data.Connection(
"Data Source=adoNetTest.db");
  _cnn.Open();
  using (DbCommand cmd = _cnn.CreateCommand())
 {
   cmd.CommandText = "CREATE TABLE 
     INDIVIDUAL (ID int primary key, 
     FIRSTNAME VARCHAR(20), 
     MIDDLENAME VARCHAR(20), 
     LASTNAME VARCHAR(20), 
     DOB DATE, 
     STATE VARCHAR(20))";

    cmd.ExecuteNonQuery();
  }
}

Inserting new entries in a table: 

Inserting entries differ again, in ADO you have to build a command string. In DbfDotNet you simply call the NewRecord() method and set the fields. Dbf.Net automatically uses the class you have provided to create the table. Calling the SaveChanges() is not mandatory but useful if you want your controls to refresh instantly. 

void InsertNewIndividual(
   int id, 
   string firstname,
   string middlename,
   string lastname,
   DateTime dob,
   string state)
{
  var indiv = mIndividuals.NewRecord();
  indiv.FIRSTNAME = firstname;
  indiv.MIDDLENAME = middlename;
  indiv.LASTNAME = lastname;
  indiv.DOB = dob;
  indiv.STATE = state;
  indiv.SaveChanges();
}

void InsertNewIndividual(
  int id, 
  string firstname, 
  string middlename, 
  string lastname,
  DateTime dob, 
  string state)
{
 using (DbCommand cmd =
   _cnn.CreateCommand())
 {
  cmd.CommandText = string.Format(
   "INSERT INTO INDIVIDUAL (ID,
    FIRSTNAME, MIDDLENAME, LASTNAME, 
    DOB, STATE) VALUES({0},
    '{1}', '{2}', '{3}', 
    '{4}', '{5}');",
   id, firstname, middlename,
   lastname,
   dob.ToString("yyyy-MM-dd HH:mm:ss"),
   state);
  cmd.ExecuteNonQuery();
 }
}

Getting an individual by record ID  

Getting a Individual record differs again, in ADO.Net you have to build a command string. In Dbf.Net you call a method. Also Dbf.Net automatically uses the class you have provided to create the table. Are you seeing a pattern emerging here? 

IIndividual GetIndividualById(int id)
{
  DbfDotNetIndividual result =
    mIndividuals.GetRecord(id);
    return result;
}

IIndividual GetIndividualById(int id)
{
 using (DbCommand cmd =
   _cnn.CreateCommand())
 {
  cmd.CommandText = string.Format(
    "SELECT * FROM INDIVIDUAL
     WHERE ID=" + id);
  var reader = cmd.ExecuteReader();
  try
  {
   if (reader.Read())
    return GetNewIndividual(reader);
   else return null;
  }
  finally
  {
   reader.Close();
  }
 }
}

Individual GetNewIndividual(
DbDataReader reader)
{
 var res = new Individual();
 res.ID = reader.GetInt32(0);
 res.FirstName = reader.GetString(1); 
 res.MiddleName = reader.GetString(2);
 res.LastName = reader.GetString(3);
 res.Dob = reader.GetDateTime(4);
 res.State = reader.GetString(5);
 return res;
}

 class Individual : IIndividual
 {
  public int ID { get; set; }
  public string FirstName { get; set; }
  public string MiddleName { get; set; }
  public string LastName { get; set; }
  public DateTime Dob { get; set; }
  public string State { get; set; }
 }

Saving a modified individual back to the database.

In Dbf.Net you don't have to write any code, if you don't want to wait for the garbage collector to collect your individual you can call SaveChanges. 

void SaveIndividual(
  Individual individual)
{
  individual.SaveChanges();
}

void SaveIndividual(
  IIndividual individual)
{
 using (DbCommand cmd =
   _cnn.CreateCommand())
 {
  cmd.CommandText = string.Format(
    "UPDATE INDIVIDUAL
 SET DOB='{1}' WHERE ID={0};",
 individual.ID,
 individual.Dob.ToString(
   "yyyy-MM-dd HH:mm:ss"));
  cmd.ExecuteNonQuery();
 }
}

Creating an Index 

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

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

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

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

    var newIndiv = individuals.NewRecord();

    newIndiv.LASTNAME = "GANAYE";

    newIndiv.SaveChanges();

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

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

   individual = mIndex.GetRecord(rowNo);

class Record
{
   private RecordHolder mHolder;

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

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