Embedded Databases and all…

Been trying out different embedded databases for the past few days. And am not sure which one is the best. The background is this:

We have a desktop application in C#, and are sending data from desktop to the PHP backend on the server. Since the data is going to be large, we want to send only the changed data to the server. That too in the most optimized manner. We are using CSV file as data storage and doing a diff on it to create the delta that we can send on the server. We are even zipping the delta to make sure it’s good on the bandwidth.

And here’s the problem:

We want to pre-process the delta. So we convert the unified diff to CSV again. Now we want to push it to a temporary table, and update a few fields. E.g. we want to mark all new rows (+ in diff) as “I” (inserted), removed rows (- in diff) as “D” (deleted) and updated rows (- and + both in diff) as “U” (updated). To do this, we load the data into a DataTable and perform some queries on it.

But there’s a catch. The .Net DataTable can’t perform updates on CSV files. So we need a real database.

That’s where our search for embedded databases began.

We want something free and something that does not restrict redistribution in a commercial application.

The options were limited. SQL Server Express, Firebird and SQLite (didn’t want Access/MDB).

Frankly, it’s not been a simple ride so far. We are considering only Firebird and SQLite (open source etc). Neither had enough ready samples / documentation that could solve the problem for us readily. Firebird was more difficult – with a lot of confusion about setting it up and opening the fdb file from a GUI database administrator application. SQLite was easier.

We have to do INSERT queries on the table, as we do not know how many fields will be there in the CSV file, and we couldn’t figure out an easy way to load a CSV file into a new table.

At this stage, I am not sure which one to go for. We need something that will work fast. I know SQLite has an option to load CSV files directly into a table through command line, but we don’t want to do a system call.

Any suggestions?