Local file-based SQL Server

Question

I've written a fairly large C# Winforms application for a client which uses SQL Server for its database. This was initially designed and developed for their internal use in-house. They've decided that they'd like to licence out a cutdown version of this to their clients, aswell as using the full version internally.

Whilst some of data that the cutdown version will store will be sent to our servers by calling a webservice, there is a requirement for most of the data to be stored locally.

Most of my database experience has been using an install of SQL Server. However, ideally in this case, it should be storing data in a local file-based database (without them having to install and configure a database server). Their clients aren't likely to be technically minded, and they will just want an setup.exe that will install the software, and it just work.

Most of my .NET code uses Linq-to-Sql to communicate with the database.

What are my best options for a local database in this case, which should allow me to still use the existing Linq code?

Answer 1

From my point-of-view, you do have at least two options here:

• Use Microsoft SQL Server Compact Edition (CE).
• Use VistaDB.

Both are databases for which you do not have to install anything on the target system but simply deploy one (or more) DLLs with your application.

The databases are file-based.

I have several real-world applications running VistaDB very well.

Here are my pros and cons:

Pro SQL CE:

• Comes from Microsoft
• No costs
• T-SQL syntax
• Seems to support Linq to SQL

Con SQL CE:

• No views
• Several DLLs to deploy

Pro VistaDB:

• SQL Server compatible syntax
• Views, Stored Procedures, etc.
• 100% Managed, no unmanaged code
• One single DLL to deploy
• Can be used by several users (locking)
• Seems to support Linq to SQL (never used it myself, though)

Con VistaDB:

• Costs money
• Less performance than e.g. Microsoft Access
• Somewhat slow when running from a Network drive