I am developing a Windows Runtime Component (C#) to be used in an HTML/JS frontend for a Windows Store App. Now, one of the requests is to have a database on the local system to store metadata about files that will be downloaded. After looking everywhere I have started using SQLite combined with the sqlite-net wrapper library.

This seems to be widely regarded as the best option but it is a serious hassle for me to get working. Perhaps my biggest problem with it is that it cannot map collection types to composite tables. This lead to me writing my own SQL queries for the entire database structure but this quickly turned out to be a very inconvenient solution.

To clarify, this is the situation I'm referring to:

class School {
    string Name { get; set; }
    List<Person> Students { get; set; }

class Person {
    int Id { get; set; }
    string Name { get; set; }

For such a small example you could surely write the SQL queries for the three tables yourself but in the actual situation I have an object that holds several collections which in turn also hold collections several levels down.

Maybe I am spoiled by Entity Framework but it is hard to believe that either everyone uses basic models that only hold the (very) limited set of types supported by sqlite-net or everybody writes his own wrapper around it to account for models with some more complexity.

How are these situations handled generally? Am I overlooking some very easy solution?

  • 5
    Yeah, you are spoiled on EF. I might have more sympathy for you, except that your use case (metadata about files that will be downloaded) appears to be relatively simple, even if you do have nested collections. How tough could it be to write a handful of SQL statements? Perhaps you could show us your proposed model? – Robert Harvey Jul 9 '14 at 17:15
  • Note that the description for SQLite.NET is very honest about it's modest capabilities. Also note that you could use EF with SQLite, if you really wanted to, or some other ORM with better capabilities. – Robert Harvey Jul 9 '14 at 17:19
  • @RobertHarvey: the actual model (with basic types left out) is along the lines of this. I could write out all the SQL queries myself but if I have to account for all CRUD actions and all other models that should be persisted, then that seems like a huge time allotment. Perhaps this is normal but I'd rather be sure about that beforehand. I'll take a look at System.Data.SQLite and see if it works on WinRT (although I doubt it considering this). – Jeroen Vannevel Jul 9 '14 at 17:36
  • Did you consider to use a Micro ORM like Dapper code.google.com/p/dapper-dot-net , which comes in a single file? – Doc Brown Jul 9 '14 at 18:03
  • 1
    Funnily enough this is the way you had to do data not more than 5 or so years ago. You kids are spoiled today. – Wyatt Barnett Jul 9 '14 at 19:13

If there is no ORM available, and writing all SQLs manually becomes too tedious, why not write a simple SQL code generator for the queries? This is simpler as it may look at a first glance. I know this for sure, since we did this around 10 years ago, when C# was new and there was no usable ORM available for .NET at that time.

Create a C# assembly with "naked" class definitions only, for the purpose of providing the relevant input for your code generator (so you don't have to create any kind of parser). Use reflection to grab the information from that assembly, to generate the real class definitions, including all CRUD code/SQL. Define some class attributes for any missing meta information (for example, about the relations between your classes and the needed composite tables). It may also turn helpful to have a consistent convention about technical primary keys (for example, let the primary keys be always of the form "class name" + ID).

Since you have only a medium size data model, you only have to support a limited list of data types and can design the code generator to follow the conventions of your project. This is a little bit like writing your own ORM, but with much less effort, since it does not have to work for many general cases and different DB systems, only for the handful cases of your project, and only for SqLite-net.

  • It looks like I'll be going this way indeed. Could you clarify what you mean with "real class definitions" as opposed to "naked ones" though? What will be the difference between both? – Jeroen Vannevel Jul 9 '14 at 19:17
  • @JeroenVannevel: in our approach, we took simple C# classes as input (which were not actually used in the application, except as input for the code generator) and generated equally named classes with some standard CRUD methods and other technical stuff as members (that was at the time of C# 1.1, without partial classes). Another approach, maybe better for your case, is not to add the SQLs code you need directly to the classes, but generate some factories or something similar. Then there is no need to make a distinction between the data classes you actually use and the ones for the code gen. – Doc Brown Jul 9 '14 at 20:50
  • The way I have it in mind right now for the create table queries is to use the sqlite-net attributes like [Primary Key], [Unique] etc and add my own [Composite] one. Then I'll just pass the type to some factory that will use reflection to create the query with all basic variables and use the PK of the current type and the type inside the collection of a [Composite] field to construct an extra table with those two PKs. Unless I am overlooking something this should be fairly straightforward. – Jeroen Vannevel Jul 9 '14 at 21:00

Well, lists of lists are handled by multiple select statements, then IDataReader.NextResult provides access to subsequent selects. You then build your lists of lists manually.

Another way to interpret the question is how to represent a composite object, and for that views are very handy, but they are read-only, can't insert or update a view.

Pseudocode inc, don't quote me.

sproc GetSchoolAndStudents(int schoolId) -- this is a stored procedure
    select ID, Name, Address, PhoneNumber, OtherSchoolData from School where ID = schoolId

    select ID, FirstName, LastName, DoB, Sex, HairColor, Grade from Student where SchoolID = schoolId

using(IDataReader reader = cmd.ExecuteReader())
    School school = null;
         school = /* ADO.NET jazzhands */
            school.Students.Add(new Student { /* ADO.NET jazzhands */ } );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.