3

I need a lightweight database engine for a desktop application. The application is not data centric, although it needs some persistent data. Which one would you use MS SQL Server express edition or SQLite?

EDIT

Is SQL Server Compact edition free? If it is the case, what about SQLite vs SQL Server Compact edition to develop this kind of application?

Improve this question
1
  • Yes, SQL Server Compact edition is "free" (c.f. the link I posted earlier). Commented Sep 14, 2009 at 14:21

13 Answers 13

Reset to default
10

Definitely NOT Sql Server Express. That's a server class engine. You want an in-process engine. In that regard, SQLite is fine. So is Sql Server Compact Edition. Both are free and either should be adequate for your needs.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Sql Server compact is pretty easy to get deployed when you use clickonce, too.
Although I do agree it is not particularly lightweight, there are a number of desktop apps that do use it. e.g. Act!, Microsoft Accounting.
+1 for SQLite ! As a bonus, there is a top-notch open-source ADO.NET provider available sqlite.phxsoftware.com, with Entity Framework support
I have use SqlCe with great results.
Have a very similar requirement. Problem is for .net4 the System.Data.Sqlite provider is quite unstable. This is probably going to shift me over to SqlCE
10

If you're working with C# and Visual Studio simply right-click on your project and select "Add Item..." and in the dialog select the "Local database", this will create a SQL CE (Compact Edition) database. It's got most of the functionality of SQL Express/Server as far as tables, data types, views but doesn't allow stored procs. It also works with Linq-to-SQL so it's a snap to integrate.

Improve this answer

Comments

4

Go with SQL Server Compact - you get to use LINQ that way, which itself is worth the effort.

While SQLite is an option, you'll need to get an ORM layer, and the performance gain will be minimal / non-existent compared to SQL Server.

Improve this answer

1 Comment

SqlCe is very good. I have done some amazing stuff with that little database.
4

Firebird is a well known database and support the latest Microsoft framework

Improve this answer

Comments

2

SQLite, especially if it's single-user. One data file and one DLL.

Improve this answer

Comments

2

If its a single user system you want to look at MS SQL Server compact edition not Express (see here: http://www.microsoft.com/sqlserver/2008/en/us/compact.aspx )

Compact Edition is an embedable database similar to SQLite.

Unfortunately I can't make a recommendation either way.

Improve this answer

1 Comment

SqlCe is very easy to deploy.
2

What about MS SQL Server Compact?

Improve this answer

Comments

2

Some love for one of the up and comers in the .NET embedded db world -- VistaDb. The license is a bit more restrictive than Sql Server Compact (free to single developers only), but its a 100% managed DB with xcopy deployment and has VS tool integration.

Improve this answer

2 Comments

But that's not free software, is it?
He never said it HAD to be free... And VistaDB is a great option to let you run on the desktop, then scale up to SQL Server with the same code. It has T-SQL Procs and other things that SQL CE is missing.
2

Be aware that if you use SQL CE along with EF, you will have to generate your own id key, because SQL CE does not support multiple query.

i.e: insert -> select key for the entity -> not good

generate and id key (GUID or something else) add it to your entity and then insert -> good

Improve this answer

2 Comments

Could you explain this issue a bit longer?
First of all sorry for the englih. What I mean by this, is that if you are using SQL server (not CE) with EF and you were to insert a new entity, 2 "operations" would happen: 1- insert of your entity 2- the id (if it is auto-increment of course) would be assigned to your entity. With SQL CE this is not possible the assignation of the ID will cause an Erroe therefore you will have to generate a GUID before inserting
1

Defo SQL lite, although it has some restrictions/features that you should be aware of

Improve this answer

Comments

0

if your need is data centric, what you really need is a file, if you need transaction (ACID) then your need is (rdbms) database.

Otherwise you'll have issues when up scaling.

Improve this answer

Comments

0

If it's not a lot of data, and you don't need to run complicated queries (although LINQ can), why not use an XML Dataset? That way it's platform independent (could run on Linux with Mono) and doesn't require the user to install extra resource hogging software.

I use XML Datasets extensively for applications that require persistent data storage of small amounts of information. If it's only a few variables, you can also use a settings file, and it will be stored either per application or per user depending on your choices.

Improve this answer

1 Comment

Thanks for the point. But, even thouhg the application is not data centric, the information it must persist constains also some logging of the activities. This is more convenient to be done with a database.
0

Think about overhead for your end users ... SQL Server Express still takes up resources, takes time to start up on a reboot and so on. For a small amount of single-user persistent data I would go with SQLite. Unless you're doing serious binding and so on you won't need an ORM between you and it.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.