Database engine for a desktop application in C#

N

13

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?

Nobe answered 14/9, 2009 at 13:42 Comment(1)

Yes, SQL Server Compact edition is "free" (c.f. the link I posted earlier). – Egarton 14/9, 2009 at 14:21

G

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.

Godforsaken answered 14/9, 2009 at 13:44 Comment(5)

Sql Server compact is pretty easy to get deployed when you use clickonce, too. – Donelson 14/9, 2009 at 13:49

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. – Kinakinabalu 14/9, 2009 at 13:50

+1 for SQLite ! As a bonus, there is a top-notch open-source ADO.NET provider available sqlite.phxsoftware.com, with Entity Framework support – Drover 14/9, 2009 at 13:55

I have use SqlCe with great results. – Furred 14/9, 2009 at 13:56

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 – Istanbul 14/11, 2010 at 11:57

V

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.

Virescence answered 14/9, 2009 at 13:47 Comment(0)

V

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.

Vonvona answered 14/9, 2009 at 13:49 Comment(1)

SqlCe is very good. I have done some amazing stuff with that little database. – Furred 14/9, 2009 at 13:55

T

4

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

Thenar answered 20/10, 2009 at 21:31 Comment(0)

S

2

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

Sherylsheryle answered 14/9, 2009 at 13:43 Comment(0)

E

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.

Egarton answered 14/9, 2009 at 13:46 Comment(1)

SqlCe is very easy to deploy. – Furred 14/9, 2009 at 13:55

I

2

What about MS SQL Server Compact?

Immitigable answered 14/9, 2009 at 13:47 Comment(0)

D

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.

Donelson answered 14/9, 2009 at 13:53 Comment(2)

But that's not free software, is it? – Nobe 14/9, 2009 at 15:28

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. – Onomastics 20/10, 2009 at 4:35

I

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

Indihar answered 14/9, 2009 at 15:41 Comment(2)

Could you explain this issue a bit longer? – Nobe 14/9, 2009 at 15:42

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 – Indihar 21/9, 2009 at 15:35

S

1

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

Sheasheaf answered 14/9, 2009 at 13:44 Comment(0)

M

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.

Minna answered 14/9, 2009 at 14:33 Comment(0)

T

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.

Troat answered 14/9, 2009 at 15:33 Comment(1)

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. – Nobe 14/9, 2009 at 15:41

J

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.

Jilljillana answered 14/9, 2009 at 15:41 Comment(0)

Recommended topics

Hot tags