Best Embedded RDBMS Databases for .NET Developers

Black Falcon Software develops products for professional .NET developers and like any other MicroISV, the need for developer products to have embedded databases is often a necessary component for such work. The problem is that over the years, this area of necessary database support has deteriorated to the point where few products are available any longer that are worthy of being used in commercial development.

For .NET developers, the original database of choice was the Access MDB coming out of the transition from Classic VB. Though still available, this database has been long known for issues in the community and is now regarded as a non-starter for most professionals.

As the .NET environment grew in popularity, the database of choice for many was VistaDB, which was specifically designed for embedded database development. VistaDB came and went over the ensuing years but as the open-source database offerings became mainstream, it finally went where it is now owned by Gibralter Software, which sells it at the hefty price of $1500.00 for a “site” license. However, if you qualify as a “MicroISV” with the company, they will offer a reduced price to help you with your business; though no estimate is provided.

VistaDB is offered as the .NET developer’s alternative to both SQLite and SQL Server CE but with both the latter available at no cost, this qualification is a non-starter unless VistaDB has an exact feature that is required for any developer’s work.

To this end, there are quite a number of what may be called 3^rd-Tier database tools that can be used for embedded development, qualified simply is such from a lack of name recognition and corresponding probabilistic market-share. Many of these tools do not appear to have kept pace with the changing .NET Framework environments leaving them still supporting the 3.5 and 4.0 versions of the framework while integrations are correspondingly offered for Visual Studio 2005 and 2008. It is doubtful that any serious professional would want to take advantage of such tools. In this vein, “ScimoreDB” has appeared to be the most sophisticated of this group but it is stuck with its offering only supporting up to .NET Framework 4.0; at least at the last recent review of their business site.

Many other such tools look as if they are no longer supported or have been simply forgotten by time altogether.

Many who read this article may promote the use of the “Firebird Embedded Database Engine” as a viable alternative and to its credit, this offshoot of the still selling Interbase engine does have its merits. The Firebird Database is a rather well tuned piece of work and is more popular in Europe than in the States. The embedded version of its engine has a small footprint and only requires four DLLs for operation. And there is quite a community of support for this database. The problem with this software, which has always been its problem, is that the documentation for these products is in a word, terrible. Not only is it often outdated but highly inconsistent along with its support community for which a listing of their tools is often found to be out of date as well. Unlike the MySQL and PostgreSQL communities, which have complete and current reference manuals offered as freely as their products, the Firebird Team has nothing in corresponding quality except for Helen Borrie’s books, which must be purchased.

The Firebird Site still complains that their software is not widely known among the general developer community but take one look at the documentation and you will get a better understanding as to why.

For $64.00 USD, developers would be better off purchasing Embarcadero’s Embedded Interbase Engine, which has somewhat more consistent documentation. However, .NET developers will have to use either the supplied ODBC driver or the ADO.NET 2.0 provider to develop .NET applications against it. One may want to try the latest Firebird ADO.NET Connector to see if this provides a more recent version of this type of tool to in order to work with the later .NET Frameworks.

Leaving this area of discussion, we then have the entire list of the newer “NoSQL” database engines to note. To begin with, there are several very good offerings of these types of database with MongoDB and RavenDB being at the top of most lists. However, “NoSQL” databases are not “set” oriented, which many applications still require for convenience of data retrieval. And on another note, there is nothing new about the “NoSQL” phenomenon. They, like their RDBMS siblings, are all based upon B+Tree internals. They simply do not have a “SQL” language overlay to their access technologies.

Nonetheless, quite a number of these tools do offer embedded capabilities such as with the PerstDB and VelocityDB products. And if document style storage is what an application requires than these databases offer superior capabilities to the corresponding RDBMS engines. However, this advantage may be coming to an end as the people at PostgreSQL are now considering implementing a similar document storage capability to their popular RDBMS.

Despite the growth of the popularity in “NoSQL” database engines, recent reports demonstrate that most developers still prefer the maturity and the ease-of-use with RDBMS engines.

With all this said, it is obvious that the embedded database engine arena has not experienced any consistency and quality for quite some time if it ever had. However, two venerable engines still standout as the best of the entire lot with one still growing in popularity as it is still being actively supported, while the other is still a rather good alternative though no longer supported.

SQLite and Microsoft’s SQL Server CE (Compact Edition) are still the most viable choices for applications that require embedded databases. However, neither is without its problems.

SQLite has always been a database developed by and for C/C++ professionals. It has had two primary projects in the past that have supported it’s use with .NET application development; Finsair and the Phoenix projects, both of which are now defunct. Luckily, the work by the software engineer at the Phoenix project has been taken over by the SQLite Foundation and is keeping relative pace with the later .NET Frameworks, which include 4.5., 4.5.1, and 4.6.

The .NET SQLite packages can be downloaded for just about any .NET application requirement, inclusive of integration with Visual Studio 2012, 2013, and 2015. They can be found here at the following link… http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

The packages come with enough good documentation to get one started but if you are new to ADO.NET, there will not be enough to teach one the basics unless one has a bit of perseverance. These packages are not for “newbies”. However, due to the popularity of this database engine, there is more than enough support in terms of documentation and third-party tools across the popular technical sites for .NET development on the Internet. One just has to do a little research. In addition, Black Falcon Software will be eventually releasing its own SQLite “Data Access Layer” based on the base 4.5 package, which will make it much easier for people new to ADO.NET to incorporate it into their own applications.

SQL Server CE, as a complete package, was the perfect embedded database engine for .NET applications. The past tense is used here because Microsoft a while back announced that it will no longer be supporting this excellent software for such databases. It was replaced by the spin-off from their SQL Server Express databases, “LocalDB”.

Whatever “LocalDB” is, which has never been made very clear through the available documentation, it is not much of replacement for SQL Server CE as it takes a minimum of around 14 megabytes to set up as compared to 2.5 for CE. And nor is it embedded with an application’s infrastructure. Like its bigger brother, it sits as an external service to applications on a machine.

Many in the Microsoft development Community have been dismayed by this decision from Microsoft since it seems to have been for no other reason than to introduce a new replacement product.

Nonetheless, SQL Server CE proponents are still taking advantage of this software’s continuing availability that has been upgraded to work with Visual Studio 2013 and .NET Framework 4.5. This latest package can be downloaded from the following link… https://www.microsoft.com/en-us/download/details.aspx?id=30709

The SQL Server CE package contains everything a developer requires, including complete documentation, to implement an embedded SQL Server database within an application and comes with both 32bit and 64bit implementations. Note that the package installation will install all of the assembly references into the GAC. However, to distribute the database engine with an application you have to make provide for a “Private” implementation with the application. All this means is that you must copy the SQL Server CE DLLs into the application’s infrastructure and provide a local reference to the primary SQL Server CE assembly. Understanding the “Private” setup is the only drawback to this software as the documentation could have been made much clearer in this case.

This is a great package for developers new to ADO.NET development since all of the calls are the same for SQL Server in general.  However, since this is an embedded database it does have its limitations such as not having any stored procedure capability.

Unfortunately, with the release of Visual Studio 2015 and .NET Framework 4.6, this database engine will most likely pass into the cyber-space ether, leaving SQLite as the only remaining, viable solution for such work.