I am developing a Windows Runtime Component (C#) to be used in an HTML/JS fronte

Question

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?

Explanation / Answer

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.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.