What is a good choice of database for a small .NET application? [closed]

Asked 19/7, 2011 at 15:16 Answered 25/10, 2012 at 22:44

Solved mongodb sqlite vistadb eloquera database

189

I'm developing a small application with C# in .NET and I want to have a small light weight database which does not use much resources.

Could you please list some of the best known light weight database softwares.

Nathalienathan answered 19/7, 2011 at 15:16 Comment(1)

linqdb – Caridadcarie 16/9, 2016 at 13:36

204

14/06/2016 Yep... still getting upvotes :-/

17/03/2014 I'm still receiving upvotes for this, be mindful of the date this was originally answered. Though the main three items listed are still entirely viable, the list will tend towards becoming stale. There are further database technologies available that are not listed.

You have a couple of immediately recognisable and free options:

The SQL Server Compact download comes with the ADO.NET provider that you will need to reference in code. The SQLite download might not have it so here is a link:

http://sqlite.phxsoftware.com/

All three use SQL, though likely with a few limitations / quirks. Management Studio works with Compact and LocalDB, whereas with SQLite you will need another UI tool such as SQLite Administrator:

http://sqliteadmin.orbmu2k.de/

There are NoSQL alternatives, such as:

Personally I would avoid using MS Access in the face of other free options. You cannot go wrong with LocalDB, Compact, or SQLite. They are all lovely small databases that run relatively quickly in little RAM - personal preference as to the religious aspects about liking a Microsoft product I suppose :-)

I use Sterling for Windows Phone programming as it is built to use Isolated Storage. I have only seen articles on RavenDb, but I can tell you that it is a JSON based document storage framework.

Not to confuse the situation (go with SQLite, SQL Server Express LocalDB, or SQL Server Compact Edition), but there are other embedded / local databases out there, some are relational others are object-oriented:

Not all of these are free. SQL / LINQ / in-proc support differs across them all. This list is just for curiosity.

There is now also Karvonite, however the code gallery link is broken. When it's live again I'll be looking into this one for WP7 development.

Gizela answered 19/7, 2011 at 15:18 Comment(12)

+1 For SQL Server CE. Rather than installing an SQL server alongside your app, you can just use the SQL Server CE dlls and have a database file in your application's working directory. Simple. – Cinder 19/7, 2011 at 19:47

RavenDB is indeed a document database but it's API is much more powerfull than simple name and value pairs. Problem is that it's not free for commercial projects in case you were looking for free alternatives. – Mewl 20/7, 2011 at 1:36

@Mewl I'll admit I was generalising a bit with the summary, but I doubt anyone would take what I've said as gospel. – Gizela 20/7, 2011 at 7:45

@Brendan Long, there isn't a SQL implementation out there that you can say its 100% standard. – Softball 21/7, 2011 at 11:25

@Adam Houldsworth: Your answer was very good and complete. I just wanted to prevent future readers from confusing RavenDB with a key/value database, that's all. Cheers. – Mewl 21/7, 2011 at 21:23

@Cinder but what about stored procedures and function of sql? we can't use that in sql compact version ? – Lassitude 14/11, 2013 at 15:42

@AdamHouldsworth Hi! Which one of those options would work in a scenario, that 1) it should be a file (no installation necessary) 2) it could reside on a network share 3) could be accessible by multiple users from different pcs concurrently? – Fiorenza 27/11, 2013 at 17:56

@Fiorenza For the first and second choices, probably SQL CE, as LocalDb requires an install. Some other may fit this bill, but I don't have direct experience. As for 3, if you want that then simply use a database server. If you don't want to pay for it, use a Linux / PostgreSQL stack. – Gizela 27/11, 2013 at 20:52

Instead of leaving the small hard to read message at the top of your answer that explains that the information is outdated. It might be easier just to update and/or append to your answer and include some examples of options available today. – Lactic 9/1, 2015 at 19:39

@JamesShaw Or you could, or the community could. I cannot promise the commitment to an answer this old when I don't even need to refer to it myself. I can at least change the font size of that message. Simply this is why these kinds of questions get closed off, because the information is prone to going stale. – Gizela 10/1, 2015 at 11:25

LiteDb is also a good choice – Salable 30/11, 2019 at 4:28

LiteDb is a very light weight NoSQL database, it's available as a NuGet package and is very easy and simple to use for storing data. – Fi 2/1, 2021 at 15:54

I'd recommend SQLite. We are using it for almost all of the apps we develop where I work.

It's small and compact. It does require a DLL to be in the app directory, but you don't have to have other software installed like Access or SQL Server. Also, as stated by danielkza below, "SQLite is public domain, so you don't have to worry at all about licensing." That can really make a big difference.

You can use System.Data.SQLite or csharp-sqlite to access it in a C# app using the same methods as that of SQL or OleDB.

You will also need an application to edit/manage the database. The best one in my opinion is SQLite Studio. Here are a couple more:
SQLite Admin
SQLite 2009 Pro (bottom of the page)
Update - 7/25/11 - More SQLite apps (question here on SO)

Here's more on SQLite:
SQLite on Wikipedia
Companies that use SQLite

Custom Functions: As an addition, if you are looking in the SQLite Core Functions and don't see one you like, you can create your own custom functions. Here are a couple of examples:
From SO
Anoter example

Allout answered 19/7, 2011 at 15:22 Comment(3)

One addition: SQLite is public domain, so you don't have to worry at all about licensing. – Plumbaginaceous 19/7, 2011 at 22:41

@MikeWebb Will Sqlite work with ClickOnce applications without need to install any additional service or something? Will it work if it is on a network share for multiple concurrent users? – Fiorenza 27/11, 2013 at 18:1

@Fiorenza - All SQLite needs to run is the DLL file corresponding to the language you are using in your program (C++, C#, etc). There are no other services or programs required. Also, it should work on a network with concurrent users. We have a copy up and running on a server where I work and it works great. You can look more into concurrency here: sqlite.org/lockingv3.html – Allout 3/1, 2014 at 16:53

Firebird embedded can be a good choice

The embedded version is an amazing variation of the server. It is a fully featured Firebird server packed in just a few files. It is very easy to deploy, since there is no need to install the server.

There is some very good dot net drivers

Honeyhoneybee answered 19/7, 2011 at 18:41 Comment(0)

SQL Server Compact if you want to use the official microsoft solution. This has the advantage of being able to use replication with SQL server if you need that sort of thing.

SQLite if you want to somthing very simple small and free. This is what android uses for it's internal databases so it is very well supported and there are very good .NET bindings available.

One distinct advantage of SQLite is that it is cross-platform. So if you wanted to port your application to Mono.NET then you wouldn't have any modifications to make to the database implementation.

I do not like MS Access for this solution but a lot of people have included it in their answer. It is limited due to the proprietary format and platform dependence. It does have it's advantages though. You can manipulate data easily if you have a copy of MS Access, you can build queries graphically and create macros. You can easily integrate it with the rest of MS Office.

Out of all these SQLite would be my recommendation due to it being so compact, well documented and supported by a growing army of fellow developers regardless of platform.

EDIT

I realized that there is another option that everybody here forgot to mention

So long as you don't need relational tables you could use CSV file read as a dataset via ADO.NET. (More of a lulz suggestion than anything else but would be suitable in some cases and doesn't require additional libraries for a MS deployment.

Haze answered 19/7, 2011 at 22:16 Comment(0)

If you are building a project with .NET 4.0.2 or greater and want embedded database support, consider SQL Server Express LocalDB.

It's a relatively new addition to the Express family that features a smaller installation footprint and reduced management overhead (when compared to other editions of Express), yet it maintains the programmability aspects of SQL Server. That is, unlike Compact Edition, LocalDB does not require the installation of a separate ADO.NET provider to communicate with SQL.

See the following for more details:

SQL Express v LocalDB v SQL Compact Edition (MSDN Blogs)
SQL LocalDB vs SQL Server CE (Stack Overflow)

Mert answered 25/10, 2012 at 22:44 Comment(0)

what about http://en.wikipedia.org/wiki/NoSQL_(RDBMS) ?

in particular MongoDB for .Net

http://www.mongodb.org/display/DOCS/Home

Glee answered 19/7, 2011 at 15:19 Comment(3)

Or specifically for .NET you might want to look at RavenDB – Niggard 19/7, 2011 at 16:7

Mongo, while lovely, doesn't run embedded - you have to start a separate server process, which may be a little heavy for the OP's purposes. – Pegasus 20/7, 2011 at 11:40

Mongodb does not support sql. the asker wants "where I can save and retrieve records by Sql queries". And what's more, mongodb is memory hungry. I don't recommend it for this use case. – Laurin 24/7, 2011 at 8:17

You could use Sql Server Express Edition as its free and is equally powerful until and unless you want functionality like mirroring etc. Have a look at this.

Dorado answered 19/7, 2011 at 15:21 Comment(1)

The question states "I don't need anything powerful". SQL Server Express is NOT a light version of SQL Server. It is a database size limited version and has a big impact on starting the users PC as the services start. It is overkill for this question. SQL Server Compact is more suitable. – Haze 20/7, 2011 at 17:31

I've used db4o with success.
File based, large community, simple to use.

http://www.db4o.com/

Licensing Details

Free General Public License db4o is licensed under the GPL by default.

The GPL license is ideal if you plan to use db4o in house or you plan to develop and distribute your own derivative work as free software under the GPL as well.

Commercial License A commercial license is required if you want to embed db4o in a commercial non-GPL product. Commercial licensees gain access to premium services and support.

Holmic answered 19/7, 2011 at 18:54 Comment(1)

I used db4o for 6 months in 2012. If there was some way to avoid issues with being not being able to access old data after the object model changes, I would upvote this answer. As far as I know, its difficult to avoid losing old data if you change your objects. Bottom line is always keep a backup copy of your data elsewhere in a different, non-db4o format (which is a pretty generic rule anyway). – Erective 29/6, 2014 at 12:7

An alternative that hasn't been mentioned if you don't require it to be a free database is VistaDB. It's all managed code, provides tons of features for an embedded database, and offers pretty good performance. A pretty good bridge between SQL CE and SQL Server Express since the majority of your VistaDB stored procedures will run without modification in SQL Server Express. I've been pretty pleased with customer service at this point as well. I've been using it at work. No services to start. An empty DB is around 1MB and the DLL is pretty lightweight too. Has ADO.NET providers and such things. I liked it a good bit.

VistaDB

Paramo answered 20/7, 2011 at 2:42 Comment(5)

Hi! Does it work in multi user scenario? For example can I store this VistaDB on a network share and access it concurrently from multiple users? – Fiorenza 27/11, 2013 at 18:2

It does some limited abilities in this respect. Obviously it can't replace a true database running with a local db engine on that box but it does offer a few transactional options. We were planning to use it in that scenario as it seemed as though it would handle it better than any of the free options we encountered and our customers would not likely have the expertise to setup sql server or whatever. It offers more features than sqlce but not quite as much as full sql server. Its a real quality piece of software. – Paramo 29/11, 2013 at 16:56

Thank you for information! And their new expected version 5 sounds very promising. Have You tried Entity Framework with VistaDB? Maybe even created VistaDB file with EF Code first? – Fiorenza 29/11, 2013 at 18:15

I did but this was about 3-4 years ago. At that point I found it to work well and that was my first EF experience. I assume its better now. The only lacking it had at the time was certain segments of the documentation was sparse but that was during transition after it was purchased by their now owners. – Paramo 29/11, 2013 at 21:34

PS: At least at the time I used it they were more than willing to provide a demo license for use. I'd highly recommend you try it before committing to the license cost. – Paramo 30/11, 2013 at 18:58

SQL server Compact Edition best as it is free and Light in Size and integrates Well

Peduncle answered 19/7, 2011 at 20:7 Comment(0)

SQL Server Express or MS Access

Horsehide answered 19/7, 2011 at 15:18 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags