FileDb is a simple database designed as a simple local database solution for Silverlight, Windows Phone 7 and .NET applications.  FileDb is a No-SQL database meant for use as a local data store for applications.  Here are some important points about FileDb:

• Stores one table per file, including its index
• Extremely Lightweight - less than 50K
• Supports field types Int, UInt, Bool, String, Byte, Float, Double and DateTime and also arrays of the same types
• Index supports a single Primary Key field (optional)
• Compiled versions for Windows Phone 7, Silverlight and .NET
• FileDb is VERY FAST
• FileDb is FREE to use in your applications
• Use with LINQ to Objects to achieve full relational capability

FileDb was specifically designed to use only native .NET data types so there would no need to translate between database storage and the CLR data types.  So you can just as easily read/write a String[] field as you would an Int field.  Another feature is that a database file created on any .NET platform will work on any other.  So you can create a database file on your Windows machine and it can be used in a Silverlight or Windows Phone app.

LINQ + FileDb gives you full relational database capability

Even though FileDb is a "flat-file" database, using LINQ it becomes fully relational!  LINQ to Objects allows you to join Tables together just as you would do in SQL. All of the power of LINQ is available to you: Joins, Grouping, Sum - the lot.  (See the examples below.)

FileDb also has a built-in query filter parser so you can write SQL-like filter expressions to make filtering data easy, like this:

string filter = "FirstName IN ('Cindy', 'John') AND Age > 32"

Use FileDb in your .NET and mobile applications where you need a searchable, updatable local database.

Use FileDb with Xamarin cross-platform app development

Most phone apps only require a simple database.  By purchasing a source code license you can compile FileDb into your IOS, Android and Windows Phone projects and use the same exact data layer code for them all.  This is much easier than using Sqlite wrappers.  With FileDb's built in encryption you can have everything you need to have a secure data layer.

FileDb Database Overview

FileDb is a simple database designed for use on any .NET platform such as Windows Phone and Silverlight, but its also great for any .NET app where simple local database storage is needed. For example, instead of using XML config files you could use a FileDb database to store and retrieve application data much more efficiently. FileDb allows only a single table per database file, so when we talk about a FileDb database we really mean a single table with an index. The index is stored in the file with the data, and allows an optional Primary Key.

FileDb is NOT a relational database - it is NO-SLQ, meaning you can't directly issue SQL commands for querying, adding or updating. However, you CAN use LINQ with FileDb to get full relational query capabilities.   And FileDb does include an Expression Parser which parses SQL-like filter expressions, which makes searching, updating and deleting very easy - see below for an example.

And FileDb supports using powerful Regular Expressions for filtering.

FileDb supports AES encryption at the record level. This means the database schema is not encrypted (field names, etc.), but each record is entirely encrypted. Encryption is "all or nothing", meaning it expects that either all records are encrypted or all records are not encrypted. You turn encryption on by passing an encryption key when opening the database.

FileDb is thread-safe for multithreading environments, so it can be accessed from multiple threads at the same time without worrying about database corruption.

FileDb databases can only be opened by a single application. Any attempt to open the file when already open will fail.  This makes sense since its meant for use by a single application at a time (FileDb is not meant as a multi-user database, such as SQL Server Express).
 

FileDb Classes

The main FileDb classes are: FileDb, Table, Field and Record.

Database Fields

Fields (or Columns) can be of several common types: String, Int, UInt, Bool, Byte, Float, Double and DateTime, or can also be an array of any of these types.

Int Fields can be AutoIncrementing, and you can optionally specify one field to be Primary Key (it must be of type Int or String).

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.

FileDb Records

FileDb supports two methods of data retrieval.  You can say the "default" way is with the built-in Record and Records classes.  Think of Record as the .NET DataRow class, and think of Table as a DataTable.  Table is a list of Records, and a Record holds the actual values. You access Field values using indexing just as you would a DataRow, like this:

FileDb employeesDb = new FileDb(); employeesDb.Open( Employees.fdb" ); Table employees = employeesDb.SelectAllRecords(); Record record = employees[0]; int id = (int) record["EmployeeId"]; // or id = (int) record[0];

To use a Table with LINQ, you do this:

var recs = from e in employees            where (string) e["FirstName"] == "John"            select e;

Notice we have to cast the record value to a string.  This is because, just like with the DataRow, Record values are all type object.

Records and Custom Objects

Records are great because they require no additional programming and they work with LINQ, albeit with some casting.  But you can use your own custom classes if you want because FileDb has template (generic) overloads for each of the SelectRecords methods.  You only need to create a class with public properties which match the names of the fields you want to use.  Here's an example using the Employees table.

public class Employee {     public int EmployeeID { get; set; }     public string LastName { get; set; }     public string FirstName { get; set; }     public string Title { get; set; }     public string TitleOfCourtesy { get; set; }     public DateTime BirthDate { get; set; }     public DateTime HireDate { get; set; }     public string Address { get; set; }     public string City { get; set; }     public string Region { get; set; }     public string PostalCode { get; set; }     public string Country { get; set; }     public string HomePhone { get; set; }     public string Extension { get; set; }     public Byte[] Photo { get; set; }     public string Notes { get; set; }     public int ReportsTo { get; set; } }

The templated SelectRecords versions return a IList<T> where T is your custom type.

IList<Employee> employees = employeesDb.SelectAllRecords<Employee>(); Employee employee = employees[0]; int id = Employee.EmployeeId; var emps = from e in employees            where e.FirstName == "John"            select e;

As you can see, this is much cleaner code.  And its actually more efficient since the Record class has more overhead because its not as simple.

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 (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. You can see it used in the examples below. It also recognizes LIKE, which translates to use Regular Expression. See the section on Regular Expressions below for more info. Field names prefixed with ~ specifies no-case comparison (for strings only).

Each time you use () around an expression, a new FilterExpressionGroup will be created. The inner-most expressions are evaluated first, just as in SQL.

// build an expression manually FilterExpression searchExp = new FilterExpression( "LastName", "Peacock", Equality.Equal ); // build the same expression using the parser searchExp = FilterExpression.Parse( "LastName = 'Peacock'" ); Table table = employeesDb.SelectRecords( searchExp, new string[] { "ID", "LastName" } ); // Or you can simply pass the string filter directly table = employeesDb.SelectRecords( "LastName = 'Peacock'", new string[] { "ID", "LastName" } ); foreach( Record record in table ) {     foreach( object value in record )     {         Debug.WriteLine( value );     } }

This example creates two identical FilterExpressionGroups, one using the Expression Parser and the other with code.

// For string fields there are 2 ways to specify no-case comparisons: you can prefix fieldnames with ~ or you can use ~= as demonstrated below // The first form is needed when using the IN operator, eg. FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse( "(FirstName ~= 'andrew' OR ~FirstName = 'nancy') AND LastName = 'Fuller'" ); Table table = employeesDb.SelectRecords( filterExpGrp ); // equivalent building it manually var fname1Exp = new FilterExpression( "FirstName", "andrew", Equality.Equal, MatchType.IgnoreCase ); var fname2Exp = new FilterExpression( "FirstName", "nancy", Equality.Equal, MatchType.IgnoreCase ); var lnameExp = new FilterExpression( "LastName", "Fuller", Equality.Equal ); // this constructor defaults to MatchType.UseCase 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 = employeesDb.SelectRecords( allNamesGrp ); // or just pass the filter string directly table = employeesDb.SelectRecords( "(FirstName ~= 'andrew' OR ~FirstName = 'nancy') AND LastName = 'Fuller'" );

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.  Internally, FileDb uses FilterExpressions to evaluate fields.  You don't need to use them because you can pass in filter strings and they'll be parsed into FilterExpressions/FilterExpressionGroups for you.  This is just to show you how can create them manually if you want to.  In the example below, both FilterExpressionGroups are identical.

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 no-case sort, prefix with ~. To get both reverse and no-case sort, use both ! and ~.

Example:

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

Selecting a Table from a Table

Another very powerful feature of FileDb is the ability to select a Table from another Table.  This would allow you to be able to select data from a Table after the database file has been closed, for example.

Example:

customersDb.Open( path + "Customers.fdb" ); // select all fields and records from the database table Table customers = customersDb.SelectAllRecords(); Table subCusts = customers.SelectRecords( "CustomerID <> 'ALFKI'", new string[] { "CustomerID", "CompanyName", "City" }, new string[] { "~City", "~CompanyName" } );

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. Its all or nothing. Likewise, you cannot add records with encryption and later add records without.  You must set the encryption key each time you add any records.

Persisting Tables

You can easily save a Table as a new database using Table.SaveToDb.  This method creates a new database file using the Fields in the Table then populates it using the Records in the Table.  For example, you can select subsets of your data, save it as a new database and send it over the Internet.

Table table = employeesDb.SelectAllRecords( new string[] { "ID", "Firstname", "LastName" } ); table.SaveToDb( "Names.fdb" );

You can also save a Table to a database from the FileDb Explorer. Just right-click on the Grid to show the context menu and select the "Create database from Table..." menu item.
 

Using LINQ to Objects with FileDb

Microsoft has done an amazing job with LINQ.  They have invested a huge amount of time, effort and $ in this technology which allows you to query just about any kind of data in a SQL-like way.  We use LINQ with FileDb to join Tables as we would using SQL.  The difference is that instead of doing it all in a single step with SQL, we must do it in two steps.  First we select the data Tables from the database files then we use LINQ to join them together.

LINQ to Objects produces a list of anonymous types as its result set.  This is good because we get strongly typed data objects which we can easily use in WPF/Silverlight apps.

Here is an example of doing a simple select using LINQ:

// Using the IN operator.  Notice the ~ prefix on the LastName field. This is how // you can specify case insensitive searches Table employees = employeesDb.SelectRecords( "~LastName IN ('Fuller', 'Peacock')" ); var query = from record in employees select new {     ID = record["EmployeeId"],     Name = record["FirstName"] + " " + record["LastName"],     Title = record["Title"] }; foreach( var rec in query ) {     Debug.WriteLine( rec.ToString() ); }

The only thing LINQ did for us in this example was gave us a typed list of anonymous objects.  Here's the same thing but with custom objects:

IList<Employee> employees = employeesDb.SelectRecords<Employee>( "~LastName IN ('Fuller', 'Peacock')" ); var query = from e in employees select e; foreach( var emp in query ) {     Debug.WriteLine( emp.ToString() ); }

Now lets tap into LINQ's real power to join tables together like a SQL inner join.  Notice in the following example we use the FilterExpression.CreateInExpressionFromTable method.  We do this to get only the records we are going to need with LINQ.  So using FileDb with LINQ is a two step process.  You first select the records you will need then use them in the LINQ query.  If your database files are large, you can filter the records like this.  Otherwise you can just select all records.