If you start to explore Windows 8 system folders, you will quickly find a set of the same files presented in various locations:

What are these files? They are generated by Extensible Storage Engine (ESE), indexed sequential access data storage technology. ESE runtime has been a part of Windows since version 2000, empowering such products as Exchange, Active Directory and Desktop Search.

Windows 8 is not an exception. ESE is used by Zune Music, Zune Video, app repository and is available as a native API for all Windows Store app developers.

How to start

While ESE APIs can be used directly, C# developers may find using of a managed wrapper called ManagedEsent more convenient. All code samples in this post use ManagedEsent.

ManagedEsent supports Windows 8 starting from version 1.8. At this moment binaries for v1.8 are not available for download and you will need to compile sources by yourself or download assembly compiled by me to start using library .

The following code samples demonstrate how to create DatabaseRepository class that allows to instantiate ESE database, put, get and delete data. The data are represented by Event class:

public class Event
{
   public Guid Id { get; set; }
   public string Description { get; set; }
   public double Price { get; set; }
   public DateTime StartTime { get; set; }
}

Database

Creation of database includes two steps: creation of ESE instance and creation of database itself. Instances can include up to six databases and provide shared transaction log for all attached databases.

Create and configure instance

private Instance _instance;
private string _instancePath;
private string _databasePath;
private const string DatabaseName = "Database";

public void CreateInstance()
{
   _instancePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DatabaseName);
   _databasePath = Path.Combine(_instancePath, "database.edb");
   _instance = new Instance(_databasePath);

   // configure instance
   _instance.Parameters.CreatePathIfNotExist = true;
   _instance.Parameters.TempDirectory = Path.Combine(_instancePath, "temp");
   _instance.Parameters.SystemDirectory = Path.Combine(_instancePath, "system");
   _instance.Parameters.LogFileDirectory = Path.Combine(_instancePath, "logs");
   _instance.Parameters.Recovery = true;
   _instance.Parameters.CircularLog = true;

   _instance.Init();
}

Create database

The following function creates database file and configures database schema to store Events.

public void CreateDatabase()
{
    using (var session = new Session(_instance))
    {
        // create database file
        JET_DBID database;
        Api.JetCreateDatabase(session, _databasePath, null, out database, CreateDatabaseGrbit.None);

        // create database schema
        using (var transaction = new Transaction(session))
        {
            JET_TABLEID tableid;
            Api.JetCreateTable(session, database, "Events", 1, 100, out tableid);

            // ID
            JET_COLUMNID columnid;
            Api.JetAddColumn(session, tableid, "Id",
                   new JET_COLUMNDEF
                   {
                      cbMax = 16,
                      coltyp = JET_coltyp.Binary,
                      grbit = ColumndefGrbit.ColumnFixed | ColumndefGrbit.ColumnNotNULL
                   }, null, 0, out columnid);
            // Description
            Api.JetAddColumn(session, tableid, "Description",
                   new JET_COLUMNDEF
                   {
                      coltyp = JET_coltyp.LongText,
                      cp = JET_CP.Unicode,
                      grbit = ColumndefGrbit.None
                   }, null, 0, out columnid);
            // Price
            Api.JetAddColumn(session, tableid, "Price",
                   new JET_COLUMNDEF
                   {
                      coltyp = JET_coltyp.IEEEDouble,
                      grbit = ColumndefGrbit.None
                   }, null, 0, out columnid);
            // StartTime
            Api.JetAddColumn(session, tableid, "StartTime",
                   new JET_COLUMNDEF
                   {
                      coltyp = JET_coltyp.Currency,
                      grbit = ColumndefGrbit.None
                   }, null, 0, out columnid);

            // Define table indices
            var indexDef = "+Id\0\0";
            Api.JetCreateIndex(session, tableid, "id_index",
                               CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);

            indexDef = "+Price\0\0";
            Api.JetCreateIndex(session, tableid, "price_index", 
                               CreateIndexGrbit.IndexDisallowNull, indexDef, indexDef.Length, 100);

            transaction.Commit(CommitTransactionGrbit.None);
        }

        Api.JetCloseDatabase(session, database, CloseDatabaseGrbit.None);
        Api.JetDetachDatabase(session, _databasePath);
    }
}

Data manipulation

Most of ESE data manipulations require opened session, started transaction and active table, so it makes sense to create a function to encapsulate all these activities:

private IList<Event> ExecuteInTransaction(Func<Session, Table, IList<Event>> dataFunc)
{
    IList<Event> results;
    using (var session = new Session(_instance))
    {
        JET_DBID dbid;
        Api.JetAttachDatabase(session, _databasePath, AttachDatabaseGrbit.None);
        Api.JetOpenDatabase(session, _databasePath, String.Empty, out dbid, OpenDatabaseGrbit.None);
        using (var transaction = new Transaction(session))
        {
            using (var table = new Table(session, dbid, "Events", OpenTableGrbit.None))
            {
                results = dataFunc(session, table);
            }

            transaction.Commit(CommitTransactionGrbit.None);
        }
    }

    return results;
}

Add event

public void AddEvent(Event ev)
{
    ExecuteInTransaction((session, table) =>
    {
        using (var updater = new Update(session, table, JET_prep.Insert))
        {
            var columnId = Api.GetTableColumnid(session, table, "Id");
            Api.SetColumn(session, table, columnId, ev.Id);

            var columnDesc = Api.GetTableColumnid(session, table, "Description");
            Api.SetColumn(session, table, columnDesc, ev.Description, Encoding.Unicode);

            var columnPrice = Api.GetTableColumnid(session, table, "Price");
            Api.SetColumn(session, table, columnPrice, ev.Price);

            var columnStartTime = Api.GetTableColumnid(session, table, "StartTime");
            Api.SetColumn(session, table, columnStartTime, DateTime.Now.Ticks);

            updater.Save();
        }
        return null;
    });
}

Delete event

public void Delete(Guid id)
{
    ExecuteInTransaction((session, table) =>
    {
        Api.JetSetCurrentIndex(session, table, null);
        Api.MakeKey(session, table, id, MakeKeyGrbit.NewKey);
        if (Api.TrySeek(session, table, SeekGrbit.SeekEQ))
        {
            Api.JetDelete(session, table);
        }
        return null;
    });
}

Get all events

public IList<Event> GetAllEvents()
{
    return ExecuteInTransaction((session, table) =>
    {
        var results = new List<Event>();
        if (Api.TryMoveFirst(session, table))
        {
            do
            {
                results.Add(GetEvent(session, table));
            }
            while (Api.TryMoveNext(session, table));
        }
        return results;
    });
}

private Event GetEvent(Session session, Table table)
{
    var ev = new Event();

    var columnId = Api.GetTableColumnid(session, table, "Id");
    ev.Id = Api.RetrieveColumnAsGuid(session, table, columnId) ?? Guid.Empty;

    var columnDesc = Api.GetTableColumnid(session, table, "Description");
    ev.Description = Api.RetrieveColumnAsString(session, table, columnDesc, Encoding.Unicode);

    var columnPrice = Api.GetTableColumnid(session, table, "Price");
    ev.Price = Api.RetrieveColumnAsDouble(session, table, columnPrice) ?? 0;

    var columnStartTime = Api.GetTableColumnid(session, table, "StartTime");
    var ticks = Api.RetrieveColumnAsInt64(session, table, columnStartTime);
    if (ticks.HasValue)
        ev.StartTime = new DateTime(ticks.Value);

    return ev;
}

Get event by ID

public IList<Event> GetEventsById(Guid id)
{
    return ExecuteInTransaction((session, table) =>
    {
        var results = new List<Event>();
        Api.JetSetCurrentIndex(session, table, null);
        Api.MakeKey(session, table, id, MakeKeyGrbit.NewKey);
        if (Api.TrySeek(session, table, SeekGrbit.SeekEQ))
        {
            results.Add(GetEvent(session, table));
        }
        return results;
    });
}

Get events for a price range

public IList<Event> GetEventsForPriceRange(double minPrice, double maxPrice)
{
    return ExecuteInTransaction((session, table) =>
    {
        var results = new List<Event>();

        Api.JetSetCurrentIndex(session, table, "price_index");
        Api.MakeKey(session, table, minPrice, MakeKeyGrbit.NewKey);

        if (Api.TrySeek(session, table, SeekGrbit.SeekGE))
        {
            Api.MakeKey(session, table, maxPrice, MakeKeyGrbit.NewKey);
            Api.JetSetIndexRange(session, table, 
                  SetIndexRangeGrbit.RangeUpperLimit | SetIndexRangeGrbit.RangeInclusive);

            do
            {
                results.Add(GetEvent(session, table));
            }
            while (Api.TryMoveNext(session, table));
        }
        return results;
    });
}

Conclusion

Extensible Storage Engine is a fast, transactional database optimized for sequential data access. It provides simple, native data storage mechanism for Windows 8 Store apps. The main disadvantages of ESE are the verbose API and limited querying capabilities. Additional information about Extensible Storage Engine is available on Wikipedia.

Download complete implementation of DataRepository class.