Want to make use of a DB without installing a DB engine in .NET

Question

There is a small enhancement I'm adding to an application. It would be nice to provide the user with the ability to filter and sort without having to write a lot of code that's already implemented in a database engine. However, I cannot justify installing a full database environment for this small piece of functionality.

I did have an implementation that was using the Jet engine against a CSV file. That provided me the query capabilities I needed. However, that (the Jet engine) does not work on Vista 64. I have read that there are workarounds, but implementing workarounds increases the likelihood that you'll be less portable and I want to avoid that.

If I want the full query capabilities of a database engine, even if I'm querying against a CSV file, am I stuck with installing it on every user's machine? The application is currently xcopyable and doesn't leave a footprint after it's deleted and I'd like to keep it that way.

As I was writing this, and XML file and XPath came to mind, but I don't think that will be the most elegant solution for this problem. XPath is slow, the file would get to be huge, and I'd like to have multiple concurrency handled for me. I ask so much.

Answer 1

Have you looked at SQLite?

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

Answer 2

I would suggest looking at System.Data.SQLite. It doesn't query CSV files directly, but it's easy enough to generate one when needed.

Answer 3

SQLite can handle importing from CSV files. It's easy to set up, only requires one DLL, and can offer all of the database/SQL capabilities you seem to need. You can hold the DB in memory after importing a CSV as well, so you should easily be able to meet your minimal footprint requirement.

Answer 4

If all you're looking for is sorting and filtering, then just use a DataSet and create a DataView referring to the table you've filled in. You can then use the RowFilter and Sort properties of the DataView, and bind your controls to the DataView.