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.
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: 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.
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 = _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'" );
Example 2: Create a FilterExpressionGroup
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:
= |
|
Equal |
<> |
|
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 |
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 = _db.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 = _db.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 ~.
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.
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 (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 } );
_db.AddRecord( record ); |
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.

|