VistaDB.Net

VistaDB is an embedded SQL database engine that delivers full database management support in a serverless design. It allows software developers using Visual Studio 2008 / 2010 and other .NET development tools to build robust WinForms and ASP.NET database applications using TSQL commands or VistaDB's own high-speed Direct Data Access (DDA) data objects. XCopy deployment makes distributing your VistaDB embedded applications a breeze with zero configuration and no runtime costs.

The VistaDB embedded database engine processes SQL commands and performs low-level reading and writing of data in VistaDB databases. The entire VistaDB engine is contained in a single less than 1 MB fully managed assembly which may be freely deployed with your applications.

The engine (i.e. assembly) does not require any special registration or configuration on the end-user machine (zero config). It can be placed in the same directory as your applications or it can be installed to the GAC. Another way to deploy the VistaDB is to fully embed it into your EXE using Microsoft ILMerge. We call this ZeroClick deployment since there is no additional files or assemblies to deploy with your application.

Features

• Less than 1 MB single DLL assembly
• Robust single-user and multi-user support*
• Fully multi-thread compatible.  Note that ADO.NET objects are not all thread safe.
• Row level and table level locking
• Royalty free distribution of your product
• High-speed SQL query processor
• Read committed pessimistic isolation level
• Transaction Processing*
• SureCommit fault-tolerant data management
• 100% embeddable using Microsoft ILMerge utility
• No CPU or RAM usage limits imposed by the engine
• Native 64 bit support on Windows x64 platforms
• Network multi user access from shared drives*
• TSQL Procs and UDF support*
• CLR Procedures and Trigger supported with SQL Server compatibility modes

NOTE: Not all editions support all features.  See the VistaDB Edition Matrix for more information.

Single-User and Multi-User

Applications built using VistaDB can access data on local drives and shared network drives (e.g. F:\MyDatabase.VDB4) in both single and multi-user mode. That means multiple users can access the same database and tables concurrently. This type of multi-user data access is often referred to as a "file server" or "peer-to-peer" configuration.

Developers benefit from VistaDB's table and row locking so that applications can lock rows for editing, or lock tables for general file maintenance. The built-in Read comitted isolation level Transaction Processing helps ensure good data integrity and repeatable read scenarios for multi user access. Developers using VistaDB's DDA technology can manage table and row locks manually. Either way, data can be simultaneously accessed by multiple users.

In a shared network scenario, Windows workstations require additional resources to manage the sharing of the database file, or any file for that matter. Each VistaDB client application running on each Windows workstation must open the entire .VDB4 database file on the shared network drive, which will cause network traffic. The Windows file sharing sub-system is in control of how the file (i.e. database) is open.

The key point to understand is that the first user that accesses the VistaDB database on the shared drive will experience very good performance relative to local desktop access. Windows recognizes that they are the only user of this file and Windows is able to open the database in a type of exclusive mode (The VistaDB open mode settings have no affect on this Windows setting. This is internal to Windows). However, the moment a second user accesses the same database, Windows drops its exclusive mode file access down to a shared mode file access which is magnitudes slower.  Windows Server Operating Systems perform better in this scenario, but still degrade multi user on a single file.

Even after the 2nd user closes his connection to the database, and the first user is back to being the only user working on the database, this first users's Windows workstation does not resume exclusive mode file access until the file (i.e. database) is closed and re-opened. The solution is to not keep connections open in this type of environment, open them for the minimum amount of time required (always a good programming practice).

Write a small service to answer DB queries

A good solution in these cases is to create a small Windows Service (SOAP, and WCF services work just as well) to answer database queries.  This will allow the local service to open the database in exclusive mode, and each client only has to ask questions to the service.  This can result in much faster operation than sharing the file on a network drive.