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 );
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 string
s 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
FilterExpression searchExp = new FilterExpression
( "LastName", "Peacock", Equality.Equal, MatchType.Exact );
searchExp = FilterExpression.Parse( "LastName = 'Peacock'" );
Table table = _db.SelectRecords( 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.
FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
( "(FirstName ~= 'andrew' OR FirstName ~= 'nancy') AND LastName = 'Fuller'" );
Table table = _db.SelectRecords( srchExpGrp, null, false, null );
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 );
table = _db.SelectRecords( 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.
FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
( "(FirstName ~= 'steven' OR [FirstName] LIKE 'NANCY') AND LastName = 'Fuller'" );
Table table = _db.SelectRecords( srchExpGrp, null, false, null );
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" );
FilterExpression filterExp =
FilterExpression.Parse( "CustomerID IN( 'ALFKI', 'BONAP' )" );
FileDbNs.Table customers = customersDb.SelectRecords( filterExp );
filterExp = FilterExpression.CreateInExpressionFromTable
( "CustomerID", customers, "CustomerID" );
FileDbNs.Table orders = ordersDb.SelectRecords( filterExp );
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:
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 );
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.
string configFilename = Path.Combine( Application.StartupPath, "app.configdb" );
ConfigDb configDb = new ConfigDb();
configDb.Open( configFilename );
ConfigDbKey key = configDb.OpenKey( ConfigDbKey.CurrentUser, "Settings", false );
string value = configDb.GetValue( key, "CmdTimeout");
Int iValue = configDb.GetValueAsInt( key, "CmdTimeout");
configDb.SetValue( key, "CmdTimeout", ConfigDbDataType.String, 90 );
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