Geek Answers Handbook

LINQ + lightweight database: which db should I choose?

I am launching a new web application. It will be hosted on a service that charges an additional fee for SQL Server, and to be honest, I don’t think the site needs this much of the database. Now the data model is 5 tables. And I will be amazed if the largest table is ever out of 10 thousand records.

So I would like to facilitate db. SQLite aroused my interest initially, because I need to learn it for Android. But the lack of foreign keys makes me cringe. I’m sure that you can implement something similar to foreign key restrictions, but it just feels non-relational. Firebird seems to be the only lightweight (and free) db that supports FK.

In addition, I would really like my feet to be wet in LINQ with this project. So far, I have found dbLINQ , which allows you to use SQLite or Firebird with LINQ. It is currently located at v0.18, so it is far from prime time. I ran tests for SQLite using dbLinq and they pass me what I need.

There was another LINQ implementation for SQLite, but all the links I found for it turned out to be in the 404s.

So what are my options for lightweight databases that are compatible with LINQ? Any of the compact editions of SQL Server is missing, unless XCOPY cannot be installed without installing the agent / service ? I can’t ask the host to install new software, since I doubt that they will do this, and I want the application to be very portable (regarding hosting).

The list so far:

  • Sqlite
  • Firebird
  • SQL Server Compact
  • VistaDB

Update: I tried all versions and wrote my impressions here . Short version: SQLite wins hands. This is the only one that has a good graphical interface, no place to install and is free.

+4
source share
6 answers

SQLite It has a good graphical interface (with automatic freakin-complete at least), has no place to install, is free and will work regardless of where I host the site. I know that I am answering my question, but no one answered only SQLite.

It is important . SQLite will require a web host that uses full trust mode if you want to run it on shared hosting.

+3
source

You can use LINQ to SQL, as in an existing database, as long as you can create a standard IDbConnection object.

Here is some code for using LINQ in a Firebird database.

 DbProviderFactory dbProvider = DbProviderFactories .GetFactory("FirebirdSql.Data.FirebirdClient"); DbConnection connection = dbProvider.CreateConnection(); connection.ConnectionString = "some connection string"; DataContext linqContext = new DataContext(connection); var query = from something in linqContext.GetTable<SomeType>() select something.someproperty;
+4
source

SQL Server Compact Edition ( http://en.wikipedia.org/wiki/SQLCE ) supports LINQ and all other VS tools, is built into VS 2008, supports FK, and XCOPY is deployed with a flat file for the database. Be careful, though not without its reservations, many things, such as views or subqueries, fall, and this can be pretty bloated if you start getting the database size pretty large (i.e. 50 MB +).

SQLite is also much better if you use the SQLite.NET provider here ( http://sqlite.phxsoftware.com/ ), also works with LINQ, and has basic VS support.

+3
source

I would advise you to take a look at VistaDB . It will do exactly what you are looking for , with the added benefit of SQLCE and SQLite, which supports views, stored procedures, and triggers. It actually supports writing procedures and triggers in TSQL as well as .NET so that you can use your SQL Server and your .NET knowledge.

+3
source

SQL Server Express

+2
source

VistaDB is the only alternative if you intend to run your site on shared hosting (almost all of them will not allow you to run your sites in full trust mode), and also if you need a simple website for deployment using x-copy.

+2
source

More articles:


All Articles