Xamarin/C# - Insert data into SQLite database programmatically for multiple tables?

Question

My Xamarin app pulls data from an API and inserts that data into a SQLite table. The API currently has 9 tables defined, and as such there are 9 classes in my app that match those tables. I used the code snippet from this question's accepted answer: Getting all types in a namespace via reflection

Below is my code, using the snippet from the answer and the foreach loop I'm trying to build that'll insert the data.

string nspace = "App.Tables";
            var q = from t in Assembly.GetExecutingAssembly().GetTypes()
                    where t.IsClass && t.Namespace == nspace
                    select t.Name; // Getting list of classes as IEnumerable
            var L = q.ToList(); // Converting to List

            foreach (var name in L) // Inserts data for every class found
            {
                var response = await httpClient.Value.GetStringAsync("http://website.com/api/" + name + "s"); // Substitutes each class name into API url
                var data = JsonConvert.DeserializeObject<List<TableName>>(response); // Deserializes into List
                using (SQLiteConnection conn = new SQLiteConnection(App.DatabaseLocation)) // Opens database connection
                {
                    conn.CreateTable<TableName>(); // Create table
                    conn.DeleteAll<TableName>(); // Delete all old data
                    conn.InsertAll(data); // Inserts new data
                }
            }

I don't know what TableName should be in order to get the correct class for each item on the list. For example: say the list contained the strings Table1, Table2, and Table3 - it got those strings from the App.Tables namespace which contains three separate classes called Table1, Table2, and Table3. When the first part of the code gets the list and stores it as the variable L, it should get the data for each "name" variable in L and then insert it into the matching table. How do I refer it to the table?

iSpain17 iSpain17 · Accepted Answer · 2019-10-15T09:15:18

Before I would give my answer, I would like to tell you that I do not recommend updating tables via reflection - tables should contain logically distinct entities, so batch deleting and updating them is kinda weird. This is one of those few occurrences where I would never work around typing the updates of the tables one by one. But those are just my two cents... Of course if I had a thousand tables my opinion would not stand.

This kind of reflection also makes your code hard to follow and trace - think about how you will search for usages of the CreateTable<ExampleClass>() method? You will never trace it back - or only via great efforts - that you called it in this piece of code.

So to answer your question...

You first get the method group, then create a generic version of it based on the type. I think converting from Type to string is unnecessary for the part you're looking for, since you need to convert back to Type

using (SQLiteConnection conn = new SQLiteConnection(App.DatabaseLocation)) {
    MethodInfo nonGenMethodCreate = typeof(SQLiteConnection).GetMethod("CreateTable");
    MethodInfo nonGenMethodDeleteAll = typeof(SQLiteConnection).GetMethod("DeleteAll");
    MethodInfo nonGenMethodInsertAll = typeof(SQLiteConnection).GetMethod("InsertAll");

    foreach(Type t in Assembly.GetExecutingAssembly.GetTypes()) {
        MethodInfo genMethodCreate = nonGenMethodCreate.MakeGenericMethod(t);
        MethodInfo genMethodDeleteAll = nonGenMethodDeleteAll.MakeGenericMethod(t);
        MethodInfo genMethodInsertAll = nonGenMethodInsertAll.MakeGenericMethod(t);

        genMethodCreate.Invoke(conn, null);
        genMethodDeleteAll.Invoke(conn, null);
        genMethodInsertAll.Invoke(conn, new[] { data });

    }
}

Xamarin/C# - Insert data into SQLite database programmatically for multiple tables?

1 Answers