Download includes the strong named  .NET Standard and .NET 4.5 DLLs and Database Explorer

We have developed FileDb as a simple cross-platform database solution for .NET and mobile applications which use Xamarin.  FileDb is a No-SQL database meant for use as a local data store for applications.  There are 2 compiled DLL versions: full Windows and Portable Class Library (PCL) for use in mobile phone app development (esp. with Xamarin).

*** FileDb is now 'Open Source' available at github.com/eztools-software/filedb ***

Here are some important points about FileDb:

• Stores one table per file, including its index
• Encryption ready - fully encrypt FileDb files
• Extremely small size DLL
• 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)
• PCL provides ideal cross-platform database for mobile phone development using Xamarin
• FileDb is VERY FAST
• FileDb is FREE to use in your applications
• Use with LINQ to Objects to achieve full relational capability
• Supports typed datasets, so you can use either the built-in Table or your own POCO objects (Plain Old Class Object).
• FULL SOURCE CODE AVAILABLE so you can compile for Android and IOS

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 using your Windows .NET application and it can be used in a mobile 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 phone applications where you need a simple, searchable, updatable local database.

Compiled versions for Standard.NET and and Framework.NET

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: Int, UInt, Int64, Decimal, Bool, String, Byte, Float, Double, DateTime, Guid, (and null) 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 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 = _db.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 = _db.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 = _db.SelectRecords( searchExp, new string[] { "ID", "LastName" } ); // Or you can simply pass the string filter directly table = _db.SelectRecords( "LastName = 'Peacock'", new string[] { "ID", "LastName" } ); foreach( Record record in table ) {     foreach( object value in record )     {         Debug.WriteLine( value );     } }

Or you can use the Expression Parser to create it for you, like this:

FilterExpression searchExp = FilterExpression.Parse( "LastName = 'Peacock'" );
 

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

// you can prefix fieldnames with ~ to specify a no-case comparison FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse( "(~FirstName = 'andrew' OR ~FirstName = 'nancy') AND LastName = 'Fuller'" ); Table table = _db.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 = _db.SelectRecords( allNamesGrp ); // or just pass the filter string directly table = _db.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 = _db.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.

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 = _db.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..." 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:

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

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

FileDb Explorer

If you're using FileDb then you need FileDb Explorer tool so you can visualize and edit your FileDb databases (this tool is not free).  FileDb Explorer implements a simple SQL parser so you can do simple queries using SELECT and ORDER BY.  You can edit and delete records directly in the Grid, which has a drag-drop data grouping ability - easily group on any column, and any number of columns.  You can edit array values (right-click on an array cell), and also import/export binary data in byte array cells.  You can purchase this tool via the Buy page.