This database solution has a number of benefits including:
· Store C# objects (and objects of any .NET-language) without the need for any interface
or adapter.
· Store Dynamic objects with any fields/properties, and mapping them into objects
of any type.
· SQL-like queries. If you can write SQL, you can use this database right away.
· LINQ support
· Returns objects in their original state (as enumeration or as a single object)
· Arrays and lists – parameters support. Arrays could be either jagged or multidimensional
– query syntax remains the same.
· Functions and expressions in the queries, they can also be used for ORDER BY.
· Standard and non-standard ORDER BY.
· Regular expressions in the queries.
· Indexing and query optimizer to speed up queries.
· Bulk insert and update for objects.
· Generic objects support.
· Restoring read-only (init-only) fields and properties.
· Inheritance in queries (SELECT ParentClass would return both ParentClass and ChildClass).
· Queries for a required type only (SELECT ONLY ParentClass will return only ParentClass,
but will not return ChildClass)
· Capable of partial object restoration (for example if the required object is ForumTopic,
then it is not necessary to drag all the referenced TopicMessages)
· Restoration depth specification – return only A or A and A.B.C, or all referenced
objects?
· Client-server architecture - very important if you use it for a web site. Connect
to server from anywhere.
· Simultaneous support of multiple users (queries executed in parallel and independently).
· Authentication via Windows accounts (or uses the account of the active user) or
using the Eloquera DB integrated authentication.
· x86 and x64 builds available.
· Unique identifiers for each object— convenient for working in stateless environments
and ASP.NET.
· Culture support (for example, WHERE dates BETWEEN['en-US'] @d1 and @d2
— will be interpreted in US format, even if the current Windows Language is French)
· Approximate search using ALMOST
· Commercial use is FREE. Of course, there are paid support options.
There are numerous additional features including backup, fixing corrupt database files and much more. If you have an interest, study the documentation.
I've written a number of times about various NoSQL databases and stores, including
MongoDb and BPlusTree. Eloquera Database is simply a logical extension of that
quest. One thing I found was that the level of support is exceptionally good,
even for the "free" user. When I installed the Eloquera Database, the
service started, but there was nothing listening on the default port of 43962.
Consequently I was only able to use the database in embedded Desktop mode. After
exhausting all possible search results, I went on to the Eloquera forums and
posted my problem. Dmytro of Eloquera responded within 10 minutes, and walked
me through a very structured set of troubleshooting steps, and within an hour,
we got the issue resolved. This is quality customer service! You can look at
the conversation here. One important lesson I learned from this exchange is that if you start a service
from the command line with NET START <ServiceName> you can get a lot more
information in the Application Event Log than if you start it from the Services
Service Control Manager Control Panel applet.
Let's take a look at some typical usage:
Connect:
DB db = new DB("server=localhost;options=none;");
To connect to a remote machine running Eloquera as a service, replace localhost with
it's IP address.
Create a new database:
db.CreateDatabase("MyDatabaseName");
Note that CreateDatabase does not open the database.
Open existing database:
db.OpenDatabase("MyDatabaseName");
Store (or update) object:
db.Store(new Book());
Sample Query Syntax:
SELECT [SKIP count][TOP count] TypeName
[WHERE {Expression [AND|OR] }[..n]]
[ORDER BY {Expression}[,] [ASC|DESC]}[..n]]
var books = db.ExecuteQuery("SELECT UserGroup WHERE Name = 'Swimmers'");
foreach(var book in books)
{
…
}
Delete object:
var book = db.ExecuteScalar("SELECT UserGroup WHERE Name = 'Swimmers'");
db.Delete(book);
INSERT:
// Create the object we would like to work with.
Cinema cinema = new Cinema() {
Location = "Sydney",
OpenDates = new DateTime[] { new DateTime(2003, 12, 10), new DateTime(2003,
10, 3) }
};
// Store the object - no conversion required.
db.Store(cinema);
To UPDATE the existing object we simply call the same db.Store function.
This method stores an object in the database. If the object is present in the database,
it will be updated, otherwise it will be inserted.
For example:
//Get the object we would like to work with.
Parameters param = db.CreateParameters();
param["location"] = "Sydney";
Cinema cinema = (Cinema)db.ExecuteScalar("SELECT Cinema WHERE Location = @location",
0, param);
//Change that object
cinema.Location = "Melbourne";
//Store it back
//Object will be updated
db.Store(cinema);
To DELETE the object we can use db.Delete or db.DeleteAll functions
Delete - deletes an object that was retrieved from the database.
DeleteAll - deletes the object and its dependent objects are involved
Example of delete:
IEnumerable listOfMovies = db.ExecuteQuery("SELECT Movie WHERE Genre >= 5");
foreach (Movie mov in listOfMovies)
{
// Delete - deletes an object that was retrieved from the database.
db.Delete(mov);
}
A common query may look like this:
//Create parameters
Parameters param = db.CreateParameters();
//Simple type parameter
param["genre"] = 5;
Movie m = (Movie)db.ExecuteScalar("SELECT Movie WHERE Genre >= @genre",
param);
Or more sophisticated queries:
db.ExecuteQuery("SELECT Movie WHERE Genre = 1");
db.ExecuteQuery("SELECT Cinema WHERE Movies.Title = 'Die Hard 4'");
db.ExecuteQuery("SELECT Cinema WHERE Movies.Studios.Titles CONTAINS '20th
Century Fox'");
db.ExecuteQuery("SELECT Cinema WHERE ALL Movies.Studios.Titles CONTAINS '20th
Century Fox'");
db.ExecuteQuery("SELECT Cinema WHERE '20th Century Fox' IN Movies.Studios.Titles");
db.ExecuteQuery("SELECT Cinema WHERE OpenDates BETWEEN['en-US'] '10/1/2006'
AND '9/17/2009'");
db.ExecuteQuery("SELECT SKIP 6 JoinClassA, JoinClassB FROM JoinClassA JOIN JoinClassB
ON JoinClassA.id = JoinClassB.id INNER JOIN JoinClassC ON JoinClassA.id = JoinClassC.id");
COMPLEX OBJECT
A Complex object contains other objects or arrays.
For example:
public class BasicUser
{
public Location HomeLocation;
public Location CurrentLocation;
public string Name;
public DateTime BirthDate;
public string Interests;
public BasicUser[] Friends;
public WorkPlace UserWorkPlace;
public School UserSchool;
public string[] Emails;
}
To access the field during the query, you should write it after the ‘.’
For example, to access a field in BasicUser, a complex object would use:
var res = db.ExecuteQuery("SELECT BasicUser WHERE School.Name = 'Thornlegh
School'");
In this query, a search is performed in the database for any BasicUser that has School
with the name ‘Thornleigh School.’
You can also access other fields, as follows:
var res = db.ExecuteQuery("SELECT BasicUser WHERE Interests = 'reading'");
LINQ:
Traditional LINQ example:
var res = from Movie m in db where m.Year > x select m;
LINQ can also be used like this:
var movies = from m in db.Query<Movie>() where m.Title == "Joe" &&
m.Year > 1950 orderby -m.Year select m;
From version 4.1 Eloquera supports native type evolution.
Eloquera.config contains TypeUpdateAllowed tag that determines whether type changes
shall be auto detected and applied.
<SmartRuntime Smart="true" TypeUpdateAllowed ="true" />
If TypeUpdateAllowed is turned on, while storing a new object, database will auto-detect
· Changes in field’s type. Already stored objects' fields will be converted
to a new type.
· Newly added fields. Old objects will be converted to a new type, with new
fields assigned a default value.
· Removed fields.
A function to rename type is also available:
void RenameType(Type type, string oldTypeName);
Of course, performance is always an issue. Here I was pleasantly surprised:
OPERATION ELAPSED MILLISECONDS
10,000 Connect-Open-Close 3053
10,000 inserts 5158
10,000 Bulk Insert 1878
SELECT 2
10,000 Updates 10,338
10,000 Select 347 Ticks
The above are for a connection to the same machine. If connecting to a remote instance,
times will be longer.
With the options=inmemory,persist; in the connection string, times will be even faster. "inmemory,persist"
saves everything to the database file when db.Close() is called. Also, notice
in the source code for my demo, I've placed the {Index] attribute on the
ID of the Person Class. This speeds up updates and selects by a large factor.
You can place [Index] on more than one property.
I've included a Console App test harness illustrating the above. Of course you'll
need to download and install the Eloquera Database first.
Here is the sample code:
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Eloquera.Client;
namespace EloqueraTest
{
class Program
{
static void Main(string[] args)
{
// Connect
DB db = new DB("server=localhost;options=none;");
// Clear away any old work
db.DeleteDatabase("Test", true);
//Create the database
db.CreateDatabase("Test");
//Open existing database
db.OpenDatabase("Test");
Stopwatch sw= Stopwatch.StartNew();
//INSERT
for (int i = 0; i < 10000; i++)
{
List<Address> addresses = new List<Address>();
Address addr = new Address(i, i.ToString() + " My Street", "Nanuet", "NY", "10931", "Business");
Address addr2 = new Address(i, i.ToString() + " My Street", "Nanuet", "NY", "10931", "Personal");
addresses.Add(addr);
addresses.Add(addr2);
Person p = new Person(i, "Mr.", "Joe", "Blow" + i.ToString(), "joe@blow.com", "3867435454", addresses);
db.Store(p);
}
sw.Stop();
Console.WriteLine("INSERT: "+sw.ElapsedMilliseconds.ToString());
// BULK INSERT
Stopwatch sw4 = Stopwatch.StartNew();
List<Address> addresses2 = new List<Address>();
List<Person> persons2 = new List<Person>();
for (int i = 9999; i < 20000; i++)
{
Address addr = new Address(i, i.ToString() + " My Street", "Nanuet", "NY", "10931", "Business");
Address addr2 = new Address(i, i.ToString() + " My Street", "Nanuet", "NY", "10931", "Personal");
addresses2.Add(addr);
addresses2.Add(addr2);
Person p = new Person(i, "Mr.", "Joe", "Blow" + i.ToString(), "joe@blow.com", "3867435454", addresses2);
persons2.Add(p);
}
db.Store(persons2);
sw4.Stop();
Console.WriteLine("BULK INSERT: " + sw4.ElapsedMilliseconds.ToString());
// SELECT VIA LINQ
Stopwatch sw3 = Stopwatch.StartNew();
var persons = from p in db.Query<Person>() where p.LastName == "Blow2300" select p;
sw3.Stop();
Console.WriteLine("SELECT: " + sw3.ElapsedMilliseconds.ToString() + ": " + persons.FirstOrDefault().LastName);
// SELECT VIA SQL(10,000)
Stopwatch sw5 = Stopwatch.StartNew();
var people = db.ExecuteQuery("SELECT TOP 10000 Person WHERE Id >5000");
sw5.Stop();
Console.WriteLine("10000 SELECT: " + sw5.ElapsedTicks.ToString() + " Ticks.");
// UPDATE 10,000
Stopwatch sw2 = Stopwatch.StartNew();
for(int j=0;j<10000;j++)
{
Person p = (Person) db.ExecuteScalar("SELECT TOP 1000 Person WHERE Id=" + j.ToString());
p.LastName += "OK";
db.Store(p);
}
sw2.Stop();
Console.WriteLine("10000 UPDATES: " + sw2.ElapsedMilliseconds.ToString());
db.Close();
Console.WriteLine("Any Key to Quit.");
Console.ReadKey();
}
}
}
You can download the Visual Studio 2010 Solution here.