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:

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.

  • 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.
  • 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.

    Example 1: Create a FilterExpression

    // 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 );

    Example 2: Create a FilterExpressionGroup

    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:

    =   Equality
    ~=   No-case equality - strings only
    <>   Not Equal
    !=   Not Equal (same as <>)
    >=   Greater than or Equal
    <=   Less than or Equal
    Like and NotLike   Use Regular Expression
    In and NotIn   Creates a HashSet of values to use like SQL IN operator


    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.

    // Using the Expression Parser

    // You can use brackets around fieldnames if there are spaces in the name
    FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse( "(~FirstName = 'steven' OR [FirstName] LIKE 'NANCY') AND LastName = 'Fuller'" );
    Table table = employeesDb.SelectRecords( filterExpGrp );

    // we can manually build the same FilterExpressionGroup
    var fname1Exp = FilterExpression.Parse( "~FirstName = steven" );
    // LIKE specifies to use RegEx
    var fname2Exp = new FilterExpression( "FirstName", "NANCY", Equality.Like );
    var lnameExp = new FilterExpression( "LastName", "Fuller", Equality.Equal );
    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 );

    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 ~.


    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.


    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" } );


    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.

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

    customersDb.Open( "Customers.fdb" );
    ordersDb.Open( "Orders.fdb" );
    orderDetailsDb.Open( "OrderDetails.fdb" );
    productsDb.Open( "Products.fdb" );

    // get our target Customer records
    // Note that we should select only fields we need from each table, but to keep the code
    // simple for this example we just pass in 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 get only OrderDetails records for the target Order records

    filterExp = FilterExpression.CreateInExpressionFromTable( "OrderID", orders, "OrderID" );
    FileDbNs.Table orderDetails = orderDetailsDb.SelectRecords( filterExp );

    // now get only Product records for the target OrderDetails records

    filterExp = FilterExpression.CreateInExpressionFromTable( "ProductID", orderDetails, "ProductID" );
    FileDbNs.Table products = productsDb.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"]
        join orderDetailRec in orderDetails on orderRec["OrderID"] equals orderDetailRec["OrderID"]
        join productRec in products on orderDetailRec["ProductID"] equals productRec["ProductID"]
        select new
            ID = custRec["CustomerID"],
            CompanyName = custRec["CompanyName"],
            OrderID = orderRec["OrderID"],
            OrderDate = orderRec["OrderDate"],
            ProductName = productRec["ProductName"],
            UnitPrice = orderDetailRec["UnitPrice"],
            Quantity = orderDetailRec["Quantity"]

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

    Here's the same thing again using custom objects:

    // get our target Customer records

    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 get only OrderDetails records for the target Order records

    filterExp = FilterExpression.CreateInExpressionFromTable<Order>( "OrderID", orders, "OrderID" );
    IList<OrderDetail> orderDetails = orderDetailsDb.SelectRecords<OrderDetail>( filterExp );

    // now get only Product records for the target OrderDetails records

    filterExp = FilterExpression.CreateInExpressionFromTable<OrderDetail>( "ProductID", orderDetails, "ProductID" );
    IList<Product> products = productsDb.SelectRecords<Product>(( 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
        join orderDetailRec in orderDetails on orderRec.OrderID equals orderDetailRec.OrderID
        join productRec in products on orderDetailRec.ProductID equals productRec.ProductID
        select new
            ID = custRec.CustomerID,
            CompanyName = custRec.CompanyName,
            OrderID = orderRec.OrderID,
            OrderDate = orderRec.OrderDate,
            ProductName = productRec.ProductName,
            UnitPrice = orderDetailRec.UnitPrice,
            Quantity = orderDetailRec.Quantity

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


    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 );

    var myDb = new FileDb();

    myDb.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 (zero for numeric types, DateTime.MinValue, empty for String and NULL for array types).

    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 } );

    myDb.AddRecord( record );