FileDb - A Simple NoSql Database for Silverlight, Windows Phone and .NET

• Download source - 348.46 KB

Introduction

I developed FileDb as a simple local database solution for .NET, Silverlight and Windows Phone apps. This article demonstrates how to use FileDb as a simple local database solution. FileDb supports a single table per file with an index which can have an optional primary key. It can also store a single meta data value in the file (string or byte[]). FileDb is No-SQL, so there is no concept of joining tables as in SQL. This sort of complexity is often not needed for local database needs. However, where joining is needed, you can use LINQ directly against FileDb tables, so you don't really need SQL. By keeping it simple, the size of the FileDb DLL is kept very small (< 100K) - and less complexity means less opportunity for bugs.

• FileDb's searching is powerful. It supports Regular Expressions (RegEx) and compound expressions, e.g. FirstName = 'Bob' and (LastName = 'Smith' or LastName = 'Jones'). The filter expression parser will parse string expressions for you or you can create and populate FilterExpression objects yourself.
• FileDb databases are encryptable (uses AES). Encryption is done at the record level - the database schema is not encrypted.
• FileDb supports field types Int, UInt, Int64, Decimal, Bool, String, Byte, Float, Double, DateTime, Guid, (and null) and also arrays of the same types. These are the same types we work with in our .NET programs, so using FileDb is natural to .NET programmers.
• Int Fields can be AutoIncrementing, and you can optionally specify one field to be Primary Key, but it must be of type Int or String.
• FileDb is thread-safe for multithreading environments, so it can be accessed from multiple threads at the same time without worrying about database corruption.
• Supports typed datasets, so you can use either the built-in Table or your own POCO objects (Plain Old Class Object).
• FileDb is an Open Source project on Google Code (http://code.google.com/p/filedb-database) from EzTools Software (www.eztools-software.com).

Background

.NET applications which need a simple database for local storage quite often must either use overkill solutions (which often have their own negative issues) or a roll your own solution which is less than ideal. SQL databases are inherently complex and large. Most local databases don't need all that horsepower (and definitely not the footprint). For local database requirements, most applications only require single tables for storing data, such as lists and array values. What has been lacking is a simple No-SQL database for .NET which can fill this requirement.

Using the Code

As you might expect, you interact with FileDb databases using a FileDb class object. The main FileDb classes are: FileDb, Table, Field and Record. Here's a list of all of the classes:

• FileDb: Represents a database file. All database operations are initiated through this class.
  Table: Represents a two dimensional dataset returned from a query. A Table consists of Fields and Records.
• Field: Defines the properties of the table column, such as Name and DataType.
• Fields: A List of Field objects.
• Record: A list of data objects represents a single row in a Table. Implements IEnumerable and the Data property which is used for DataBinding.
• Records: A List of Record objects.
• FieldValues: A simple Name/Value pair Dictionary. Use this class when adding and updating records.
• FilterExpression: Used to filter records for query, update and delete.
  FilterExpressionGroup: Used to create compound expressions by grouping FilterExpressions and FilterExpressionGroups.

First, let us be clear that FileDb is NOT a multiuser database - FileDb databases can only be opened by a single application. Any attempt to open the file when already open will fail. This is as we should expect for a local database meant only for use by a single application.

OK, now let's see how to use FileDb.

Creating a Database

You create your database programmatically by defining Fields and adding them to an array, then calling FileDb.Create, similar to below. Notice we set the ID field to be AutoIncrementing and PrimaryKey. This code creates a database with every type of field.

Field field;
var fieldLst = new List<Field>( 20 );
field = new Field( "ID", DataType.Int );
field.AutoIncStart = 0;
field.IsPrimaryKey = true;
fields.Add( field );
field = new Field( "FirstName", DataType.String );
fields.Add( field );
field = new Field( "LastName", DataType.String );
fields.Add( field );
field = new Field( "BirthDate", DataType.DateTime );
fields.Add( field );
field = new Field( "IsCitizen", DataType.Bool );
fields.Add( field );
field = new Field( "DoubleField", DataType.Double );
fields.Add( field );
field = new Field( "ByteField", DataType.Byte );
fields.Add( field );

// array types
field = new Field( "StringArrayField", DataType.String );
field.IsArray = true;
fields.Add( field );
field = new Field( "ByteArrayField", DataType.Byte );
field.IsArray = true;
fields.Add( field );
field = new Field( "IntArrayField", DataType.Int );
field.IsArray = true;
fields.Add( field );
field = new Field( "DoubleArrayField", DataType.Double );
field.IsArray = true;
fields.Add( field );
field = new Field( "DateTimeArrayField", DataType.DateTime );
field.IsArray = true;
fields.Add( field );
field = new Field( "BoolArray", DataType.Bool );
field.IsArray = true;
fields.Add( field );
_db.Create( "MyDatabase.fdb", fieldLst.ToArray() );

Adding Records

You add records to a database by creating a FieldValues object and adding field values. You do not need to represent every field of the database. Fields that are missing will be initialized to the default value.

var record = new FieldValues();
record.Add( "FirstName", "Nancy" );
record.Add( "LastName", "Davolio" );
record.Add( "BirthDate", new DateTime( 1968, 12, 8 ) );
record.Add( "IsCitizen", true );
record.Add( "Double", 1.23 );
|record.Add( "Byte", 1 );
record.Add( "StringArray", new string[] { "s1", "s2", "s3" } );
record.Add( "ByteArray", new Byte[] { 1, 2, 3, 4 } );
record.Add( "IntArray", new int[] { 100, 200, 300, 400 } );
record.Add( "DoubleArray", new double[] { 1.2, 2.4, 3.6, 4.8 } );
record.Add( "DateTimeArray", new DateTime[] 
	{ DateTime.Now, DateTime.Now, DateTime.Now, DateTime.Now } );
record.Add( "BoolArray", new bool[] { true, false, true, false } );
_db.AddRecord( record );

Searching and Filtering

FileDb uses FilterExpressions and FilterExpressionGroups to filter records in queries and updates. We use FilterExpressions for simple queries which consist of a single field comparison (e.g. field = 'value') and we use FilterExpressionGroups for compound expressions, where multiple expressions and grouping are required. You can add either FilterExpressions or FilterExpressionGroups to a FilterExpressionGroup, thus creating complex expressions (FileDb processes FilterExpressionGroups recursively).

You can either create your own manually in code or use the built-in Expression Parser to create them for you. The Expression Parser recognizes standard SQL comparison operators, but also introduces ~=, which means NoCase comparison (for strings only). You can see it used in the examples below. It also recognizes LIKE, which translates to use Regular Expression (MatchType.RegEx). See the section on Regular Expressions below for more information.

In each example, we will show how to build a FilterExpression or FilterExpressionGroup manually and/or using the filter expression parser.

Example 1: Create a FilterExpression

// LastName = "Peacock"
FilterExpression searchExp = new FilterExpression
( "LastName", "Peacock", Equality.Equal, MatchType.Exact );
// using the expression parser
searchExp = FilterExpression.Parse( "LastName = 'Peacock'" ); 
Table table = _db.SelectRecords( searchExp, new string[] 
{ "ID", "LastName" }, false, null );

We use FilterExpressionGroups for compound expressions. This example creates two identical FilterExpressionGroups, one using the Expression Parser and the other with code. Each time you use () around an expression, a new FilterExpressionGroup will be created. The inner-most expressions are evaluated first, just as in SQL.

// the easy way, using the expression parser
FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
	( "(FirstName ~= 'andrew' OR FirstName ~= 'nancy') AND LastName = 'Fuller'" );
Table table = _db.SelectRecords( srchExpGrp, null, false, null );

// equivalent building it manually
var fname1Exp = new FilterExpression
( "FirstName", "andrew", Equality.Equal, MatchType.NoCase );
var fname2Exp = new FilterExpression
( "FirstName", "nancy", Equality.Equal, MatchType.NoCase );
var lnameExp = new FilterExpression
( "LastName", "Fuller", Equality.Equal, MatchType.Exact );
var fnamesGrp = new FilterExpressionGroup();
fnamesGrp.Add( BoolOp.Or, fname1Exp );
fnamesGrp.Add( BoolOp.Or, fname2Exp );
var allNamesGrp = new FilterExpressionGroup();
allNamesGrp.Add( BoolOp.And, lnameExp );
allNamesGrp.Add( BoolOp.And, fnamesGrp );
// should get the same records
table = _db.SelectRecords( allNamesGrp, null, false, null );

FileDb supports these comparison operators:

Regular Expressions in Searches and Filtering

FileDb supports using Regular Expressions. You can use any RegEx supported by .NET. The Expression Parser supports MatchType.RegEx using the LIKE operator. In the example below, both FilterExpressionGroups are identical.

// Using the Expression Parser

FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
    ( "(FirstName ~= 'steven' OR [FirstName] LIKE 'NANCY') AND LastName = 'Fuller'" );
Table table = _db.SelectRecords( srchExpGrp, null, false, null );

// we can manually build the same FilterExpressionGroup
var fname1Exp = FilterExpression.Parse( "FirstName ~= steven" );
var fname2Exp = new FilterExpression
( "FirstName", "NANCY", Equality.Equal, MatchType.RegEx );
var lnameExp = new FilterExpression
( "LastName", "Fuller", Equality.Equal, MatchType.Exact );
var fnamesGrp = new FilterExpressionGroup();
fnamesGrp.Add( BoolOp.Or, fname1Exp );
fnamesGrp.Add( BoolOp.Or, fname2Exp );
var allNamesGrp = new FilterExpressionGroup();
allNamesGrp.Add( BoolOp.And, lnameExp );
allNamesGrp.Add( BoolOp.And, fnamesGrp );
table = _db.SelectRecords( allNamesGrp, null, false, null );

Sort Ordering

Query methods allow for sorting the results by fields. To get a reverse sort, prefix the sort field list with !. To get a NoCase sort, prefix with ~. To get both reverse and NoCase sort, use both ! and ~.

Example

Table table = _db.SelectAllRecords( new string[] 
{ "ID", "Firstname", "LastName", "Age" }, false, new string[] 
{ "~LastName", "~FirstName", "!Age" } );

Using LINQ with FileDb

Once you have your selected records in your Table objects, you are ready to use LINQ to join the tables and to get the relations you need. Here's an example of joining the Northwind Customers, Orders, OrderDetails and Products tables and selecting anonymous objects. The thing to keep in mind in using LINQ with FileDb is that it's a two step process: First select the desired records using FileDb queries, then use LINQ on the resulting Tables to join them together in the relations you need.

This example uses the FileDb IN expression and FilterExpression.CreateInExpressionFromTable to get related records from other tables.

FileDb customersDb = new FileDb(),
ordersDb = new FileDb();

customersDb.Open( "Customers.fdb" );
ordersDb.Open( "Orders.fdb" );

// get our target Customer records
// Note that we should select only fields we need from each table, but to keep the code
// simple we just pass null for the field list

FilterExpression filterExp = 
	FilterExpression.Parse( "CustomerID IN( 'ALFKI', 'BONAP' )" );
FileDbNs.Table customers = customersDb.SelectRecords( filterExp );

// now get only Order records for the target Customer records
// CreateInExpressionFromTable will create an IN FilterExpression, which uses a HashSet 
// for high efficiency when filtering records

filterExp = FilterExpression.CreateInExpressionFromTable
( "CustomerID", customers, "CustomerID" );
FileDbNs.Table orders = ordersDb.SelectRecords( filterExp );

// now we're ready to do the join

var query =
from custRec in customers
join orderRec in orders on custRec["CustomerID"] equals orderRec["CustomerID"]
select new
{
ID = custRec["CustomerID"],
CompanyName = custRec["CompanyName"],
OrderID = orderRec["OrderID"],
OrderDate = orderRec["OrderDate"]
};

foreach( var rec in query )
{
Debug.WriteLine( rec.ToString() );
}

Using LINQ with FileDb, you get all the power that LINQ offers, including aggregation and hierarchical object graphs.

Using your own POCO objects (Plain Old Class Objects)

The FileDb Table object is fine, but when used with LINQ, the drawback is that you don't get Intellisense when constructing your LINQ queries. So FileDb has parallel generic/template versions of each "SelectRecords" method. So you can use your own class objects and FileDb will populate them in the query. The only requirement is that the class fields match the field names in the table. Here's the same code as above but using custom POCOs:

// Customer and Order classes are defined elsewhere

FilterExpression filterExp = FilterExpression.Parse
	( "CustomerID IN( 'ALFKI', 'BONAP' )" );
IList<Customer> customers = customersDb.SelectRecords<Customer>( filterExp );

filterExp = FilterExpression.CreateInExpressionFromTable<Customer>
( "CustomerID", customers, "CustomerID" );
IList<Order> orders = ordersDb.SelectRecords<Order>( filterExp );

// now we're ready to do the join

var query =
from custRec in customers
join orderRec in orders on custRec.CustomerID equals orderRec.CustomerID
select new
{
ID = custRec.CustomerID,
CompanyName = custRec.CompanyName,
OrderID = orderRec.OrderID,
OrderDate = orderRec.OrderDate,
};

foreach( var rec in query )
{
Debug.WriteLine( rec.ToString() );
}

Encryption

Using encryption with FileDb is simple. You only need to specify a string key when you open the database. After that, everything is automatic. The only caveat is you must set a key before you add any records. Once a single record has been added without a key set, you cannot later add records with a key. It's all or nothing. Likewise, you cannot add records with encryption and later add records without.

FileDb Explorer

Having a great database tool is good, but you may also need a tool to visualize and edit your data. For this, I have created the FileDb Explorer, shown just below. You may have noticed the SELECT statement and wondering why, if FileDb is No-SQL, how can we use an SQL statement to query the data. The answer is that I've implemented a small subset of SQL just for select statements (only in the Explorer, not the DLL). There is no support for UPDATE or DELETE. You can perform these operations by editing the data directly in the Grid. This tool is not free, but it is inexpensive. You can download FileDb Explorer from the EzTools website.

Points of Interest

I've always been interested in databases starting with DBase back in the 1980s. I've been disappointed by the lack of simple, small, efficient databases for storing local application data on the Windows platform. Writing this simple database which stores native .NET data types, and the expression parser was a lot of fun.

To demonstrate how to use FileDb I have also written ConfigDb, which implements a "configuration database". It works much like the Windows Registry. This is also a great alternative to using XML config files, which are not easily updatable. Often the Windows Registry can be locked on user's a machine. ConfigDb solves these problems. It's implemented in a single file which you include in your project (included in this download). Here's an example of using ConfigDb to get and set values.

// open the file
string configFilename = Path.Combine( Application.StartupPath, "app.configdb" );
ConfigDb configDb = new ConfigDb();
configDb.Open( configFilename );

// open the Key
ConfigDbKey key = configDb.OpenKey( ConfigDbKey.CurrentUser, "Settings", false );

// get the value as a String
string value = configDb.GetValue( key, "CmdTimeout");

// get the same value as an Int
Int iValue = configDb.GetValueAsInt( key, "CmdTimeout");

// set a value
configDb.SetValue( key, "CmdTimeout", ConfigDbDataType.String, 90 );

// set an array value
configDb.SetValue( key, "StringArray", ConfigDbDataType.String, 
new String[] { "s1", "s2", "s3" } );

You can see how easy it is to use ConfigDb. I have also created an Editor/Viewer which is much like the Windows RegEdit program (also free to use and distribute). You can download this tool from the EzTools website.

History

• Updated submission