Tiny database C# library/code to handle multiple columns of data?

Question

I'm looking for a multi-column database, but even SQLite at over a megabyte seems like overkill (and it isn't available for C# unless you use something like this). I only want 100-500k max of code. What's the best option here?

The Generic Dictionary in C# is useful to an extent, but by default, it won't allow you to retrieve the key/s from a given value. Likewise, I might have more than two columns of data, and even if I used a BiDictionary with the value as a class/struct (for multiple values), I would need to find the key of an n-column value, then find the new o-column value of the key. So that's a bit inconvenient (plus that link is an incomplete implementation without even a 'set' command).

Here's a simple example of a table I might create (I might have a dozen columns or a thousand rows however):

name       type colour  length
orange     a    orange  5
apple      a    green   4
banana     c    yellow  7
strawberry b    red     3
blackberry b    black   2
gooseberry b    green   2

I'd like to retrieve/set any value/s in the table from any other value/s preferably using LINQ or an SQL-like query along with fast retrieval/writing and large-ish datasets if possible.

What's driving your resource limit of 100-200K? Without knowing that, it may well be hard to satisfy your *real* requirements. — Jon Skeet, May 27 '12 at 20:39
Fair question. My own program is only around 200k, so to have the database code 5x larger (for only a minor use of the program I might add) is a bit... strange (and affects the download size for users who might have a slow connection). And what I want seems in principle fairly simple. I don't want tons of fancy relational-type features; something small will be quicker to understand, in theory. Okay, if something out there exists that is 500k maximum, maybe that's not too bad. — Dan W, May 27 '12 at 20:45
its not really a fair comparison, your program is likely leveraging megabytes of libraries under the hood in the .net library and in windows itself. So adding another megabyte depedency only seems out of proportion. But, in general, don't worry about the size of your dependency libs unless you have a hard requirement for the software to remain small — Keith Nicholas, May 27 '12 at 20:49
Hi Keith, fair point. I'd still at least like the download to remain small for my users, and I find it hard to believe there's no middleground between the simple Dictionary and the giant feature-set of SQLite. — Dan W, May 27 '12 at 20:54

score 5 · Accepted Answer · answered May 27 '12 at 21:02

5

The DataSet class may be a good fit for what you are doing - you can load data into it directly from XML (ReadXml methods), which would translate into in memory tables.

Using the DataTable (or several and their relationships) representing your data you can query it using LINQ.

answered May 27 '12 at 21:02

Oded

489,969
99
883
1,009

Thanks! It's a little more verbose than I would have liked, but that's partially LINQ to blame there. Do you know the performance in comparison to SQLite? I've created an answer too with some sample code using the DataSet class. One question I have is can I treat the first column as the 'key' column (no duplicates) and write something like `table.Rows["strawberry"]["colour"]` (illegal) and it would return "red". That would utilize the simplicity of the Dictionary class as a bonus. – Dan W May 28 '12 at 02:04
@DanW - Can't comment about performance. It is _in memory_ in the same process space, so should be rather performant. As usual with such things, you will need to test both options with your own data. In regards to your other question: You can set a column to `AutoIncrement` as well as `Unique` - see [DataColumn](http://msdn.microsoft.com/en-us/library/system.data.datacolumn.aspx) on MSDN. – Oded May 28 '12 at 09:30

score 0 · Answer 2 · edited May 23 '17 at 11:52

Based on Oded's answer and this old question, I went about writing some sample code and it works a treat:

//////////////////////////////////////////////
//// Setting up the table is as simple as this: 
DataTable table = new DataTable("fruit");

table.Columns.Add("name", typeof(string) );
table.Columns.Add("type", typeof(char));
table.Columns.Add("colour", typeof(string));
table.Columns.Add("length", typeof(int));

//////////////  name, type, colour, length
table.Rows.Add("orange", 'a',"orange", 5);
table.Rows.Add("apple", 'a',"green", 4);
table.Rows.Add("banana", 'c',"yellow", 7);
table.Rows.Add("strawberry", 'b',"red", 3);
table.Rows.Add("blueberry", 'b',"black", 1);
table.Rows.Add("gooseberry", 'b',"green", 2);
//////////////////////////////////////////////

////// Accessing the data via Linq!
var results = table.AsEnumerable()
    .Where(row => row.Field<char>("type") == 'b')
    .OrderBy(row => row.Field<int>("length"))
    .Select(row => row.Field<string>("name") +": "+row.Field<string>("colour"));                
foreach(string s in results) Console.WriteLine(s);
///////////////////////////////

Output is:

blueberry: black
gooseberry: green
strawberry: red

score 0 · Answer 3 · answered Jun 04 '12 at 19:21

0

SQL Server compact edition is free with .NET: http://en.wikipedia.org/wiki/SQL_Server_Compact

It stores data using a flat .sdf file, and you can write SQL queries against it.

answered Jun 04 '12 at 19:21

C.M.

1,474
13
16

Tiny database C# library/code to handle multiple columns of data?

3 Answers3