Introduction

I developed FileDb as a local database solution for .NET apps, but especially for Silverlight and Windows Phone platforms which have no database support at all. 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. FileDb is NoSql, so there is no concept of joining tables as in SQL. This sort of complexity is often not needed for local database needs. By keeping it simple, the size of the FileDb DLL is kept very small (< 50K) 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, Bool, String, Byte, Float, Double and DateTime 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 fast and it's free from EzTools Software (www.eztools-software.com).

Background

The .NET runtimes for Silverlight and Windows Phone lack support for databases - the System.Data namespace is not even there. So for local data storage, you must roll your own solution. Furthermore, regular .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 roll your own solution which is less than ideal.

SQL databases are inherently complex and large. Most local databases don't need all that power (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 NoSql 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 re-entrant for multithreading. Thread access must be synchronized by your application. Failure to synchronise access will result in database corruption.
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.

FileDb doesn't support the notion of NULL fields for the non-array type. Only array type fields can have NULL values. The non-array field values will always have a value, either zero or empty.

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 expresssions (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.GetMatchingRecords( searchExp, new string[] { "ID", "LastName"
}, false, null );

Example 2: Create a FilterExpressionGroup

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.GetMatchingRecords( 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.GetMatchingRecords( allNamesGrp, null, false, null );

FileDb supports these comparison operators:

=   Equal
~=   Case insensitive Equal (for strings only)
<>   Not Equal
!=   Not Equal
>=   Greater than or Equal
<=   Less than or Equal
LIKE   Use Regular Expression

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.GetMatchingRecords( 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.GetMatchingRecords( 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.GetAllRecords( new string[] { "ID", "Firstname", "LastName",
	"Age" }, false, new string[] { "~LastName", "~FirstName", "!Age" } );

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 NoSql, how can we use a 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 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

  • New submission
关键字 filedb,a simple nosql database for silverlight,windows phone and .net
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架