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.
