Load XML to SQLITE database - Some ideas/help

Hi all,

New to Android and IOS device apps and Xamarin, but years of experience with C# and Windows Phone/desktop apps, but this has stumped me.

I have an application (Android to start with, then the IOS and Win versions later) that will be used for safety testing equipment in the field.

An XML gets downloaded to the tablet once a day with a Windows PC application (via USB/Wireless - there will be no webservices involved).

This XML I need to then "import" into a SQLite database on the tablet. The App will update the data in the database throughout the day. At the end of the day, the updated data gets extracted from the database and placed into an "export" xml file that gets re-imported into the PC app

With the apps I have written over the years, I just easily load the XML into a Dataset and use the Dataset. Obviously, cant do this under Android/IOS hence using a DB.

I have looked at code for creating the SQLite DB and to de-serialize the XML (haven't put anything together yet) but most of it seems doable but with a lot of code to get the desired result.

So, some questions.
1) Have others done this how did they accomplish it.
2) Is loading this into a SQLite database the best way (I hate json but if you suggest that and provide a sample then I will experiment).
3) I need to create the Database from scratch everyday as the Database will only contain data relevant for that days work.
4) Do I need to de-serialize the XML to get the fields/data from the XML file to update the DB, or can it be done a different way. The reason I ask is that I have looked at Xamarin's Working with Files document. In their example, they only seem to be using 1 field called Monkey in their LIST Type to de-serialise the XML into, BUT, my exported XMLs (there will be one per DB table) contain multiple fields and data and I am stumped at how can it be de-serialized if I don't know all the "fields" or have a "LIST Type" (well, I know them from the PC app side, but I was hoping I didn't have to hard code them).

I'm not after a full coded solution, just some ideas on how you would go about this. Maybe I am trying to make this too complicated (or more likely confused from all the different samples I have seen).

Thanks,
Rob

Posts

  • mikeyjmikeyj CAMember

    I don't know an awful lot of background here, and so; I'll just shoot this out there blindly.. =)

    I think what you're concerned about is perhaps a lack of ability to bind an SQLite source to ADO.NET DataSets etc. For example, load the DataSet with the contents of the XML file, and then use an adapter to push changes to the database, and the other way around...

    I'm sure there is a way, I just haven't worked with SQLite in that way. But you do have one thing going for you, it sounds as though the XML files used to ship data to the handheld is always generated originally from a desktop app, and that desktop app is already working.

    So it stands to reason that you could at least load a dataset on the device with that source XML file. Assuming that you have the XML file on the Android filesystem, shouldn't you just be able to create a dataset, and use the ReadXML method?

    I find that while working with SQLite, the schema is always very well known. So there's the manual step of defining what the SQLite database will be so your application can manage the data, sure, but that's not an extra step, youd be doing that anyways. What you're left with is moving data to and from the data sets (while reading or overwriting the xml files). So those datasets would only exist for the purposes of importing or exporting to or from the SQLite database. (That could easily be isolated with an activity from the rest of your app)

    I think you're doing yourself a disservice by not using a web service by the way, even if self-hosted within that desktop app, and only having them running during a sync process. I feel as though it would give you more control, have less code to maintain, and of course just perform better.. But that's a bold statement without any background to the app, sounds like you have a system the client's been happy with for a while..

    Hope that helps! I'll try and remember to check back up if you need me to try anything...

  • Hi Mike.

    Thanks for your email, very much appreciated.

    To fill in some blanks. I created the desktop app as it consumes an Access database. The PC App does all the grunt work of putting data into the access database (created by someone else) such as client information, job information and test results from the field. Luckily, there is only about 7 small tables, 5 tables will only be used for lookups. The "client" wants to on-sell this Application to his Franchisees as a stand-a-lone PC App with Smart Device App included to test the equipment out in the field.

    Yes, it is a disservice not using web services, but the "client" doesn't want this as mentioned above, each "franchisee" is meant to look after their own data and the main company doesn't need to know about it.

    What I did all day yesterday was to try some things out. What I came up with was this (yes, a bit of manual work/code) and it seems to work OK (only with one table tested at the moment).

    1) I export the XML file (1 per table) to the Device
    2) I created the DB on the device using Classes created for each table and with SQLite-net, send the commands:

    Note: dbConnection defined elsewhere as: public static SQLiteAsyncConnection dbConnection;
    dbPath defined as: public static string dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "fsm.db");

    dbConnection = new SQLiteAsyncConnection(dbPath); //Create tables dbConnection.CreateTableAsync<dbtblAlarm>(); dbConnection.CreateTableAsync<next table>(); (repeated for each "class/table")

    The class for one of the tables looks like this:
    public class dbtblAlarm { [PrimaryKey, AutoIncrement] public int Asset_Key { get; set; } public string Asset_ID { get; set; } public int Site { get; set; } public int Location { get; set; } public string Type { get; set; } public string Voltage { get; set; } public int Battery_10yr { get; set; } public string Make { get; set; } public string Model { get; set; } public string Serial_Number { get; set; } public string Manufacture_Date { get; set; } public int Test_Period { get; set; } public int In_Service { get; set; } public string Notes { get; set; } }

    3) I open each XML document up as an XDocument

    XDocument infodocument = XDocument.Load(xmlFile);

    4) I run a LINQ query on the XML to retrieve the data and load the class up. For example, using the class above, this is the code to read the XML and save to the class.

    var alarm = from r in infodocument.Descendants("Alarm") select new { _Asset_Key = r.Element("Asset_Key").Value, _Asset_ID = r.Element("Asset_ID").Value, _Site = r.Element("Site").Value, _Location = r.Element("Location").Value, _Type = r.Element("Type").Value, _Voltage = r.Element("Voltage").Value, _Battery_10yr = r.Element("Battery_10yr").Value, _Make = r.Element("Make").Value, _Model = r.Element("Model").Value, _Serial_Number = r.Element("Serial_Number").Value, _Manufacture_Date = r.Element("Manufacture_Date").Value, _Test_Period = r.Element("Test_Period").Value, _In_Service = r.Element("In_Service").Value, _Notes = r.Element("Notes").Value, };

              foreach (var r in alarm)
                {
                    dbtblalarm.Asset_Key = Convert.ToInt32(r._Asset_Key);
                    dbtblalarm.Asset_ID = r._Asset_ID;
                    dbtblalarm.Site = Convert.ToInt32(r._Site);
                    dbtblalarm.Location = Convert.ToInt32(r._Location);
                    dbtblalarm.Type = r._Type;
                    dbtblalarm.Voltage = r._Voltage;
                    dbtblalarm.Battery_10yr = Convert.ToInt32(r._Battery_10yr);
                    dbtblalarm.Make = r._Make;
                    dbtblalarm.Model = r._Model;
                    dbtblalarm.Serial_Number = r._Serial_Number;
                    dbtblalarm.Manufacture_Date = r._Manufacture_Date;
                    dbtblalarm.Test_Period = Convert.ToInt32(r._Test_Period);
                    dbtblalarm.In_Service = Convert.ToInt32(r._In_Service);
                    dbtblalarm.Notes = r._Notes;
    
                    dbInsertUpdateAlarmData(dbtblalarm);
                }
    

    5) The last command I use is to Update the database and I found that using the following code, I can just send the data and it will determine whether or not the data exists and needs updating, or inserting.

       public static async void dbInsertUpdateAlarmData(dbtblAlarm data)
        {
            try
            {
                int retval = await dbConnection.InsertAsync(data);
                if (retval != 0) await dbConnection.UpdateAsync(data);
            }
            catch (SQLiteException ex)
            {
            }
        }
    

    The only thing now is to see what the database and data look like, but I haven't got that far yet.

    Sure, the code isn't the prettiest (and if you see any glaring mistakes or better ways to do it, I would love to know). This code is run in a new thread (launched from the UI but not under the UI thread which is now causing other issues (like showing an alert on the UI if an update fails) but I will put in a different post later).

    Getting back to the Dataset thingy, I only thought there were datatables that were available to use, and from what I read, a lot highly recommend you DONT use them, hence why I thought the DB was the best way.

    So, that's as far as I got. Thanks for your input.

    Rob

  • mikeyjmikeyj CAMember

    Awesome, look I'm not here to judge specific code, lol

    Your approach is sound enough, read the Xml Document into the table. I guess in terms of whether to insert or update.. well if you can always assume the incoming XML file is up to date and has all records, I would clear the table before importing again.

    I would make this more complicated if you need 2-way merging... But since you're working on a copy to device and import , and export and then copy out of device, I suspect it doesn't need to be more complicated than that.

    Also, sure, if you want to see what that SQLite database looks like, now that you're importing stuffs into it... you should check out SQLiteBrowser. http://sqlitebrowser.org/ (It's free and pretty useful)

  • mikeyjmikeyj CAMember

    Also, when I responded this morning, I did so hastily, getting ready for a meeting. =) You asked if I saw anything that maybe you could do better, to let you know.

    I'm not sure if better is the right word for this suggestion, just something to keep in mind.

    Where you grab "Alarms" from the XML file, and you iterate through them to insert into the database. If you have any special rules in the table schema, such as required fields, or optional fields (In my experience it's the data that may or may not be there that would more likely cause an issue), consider guarding inputs.

    In .NET strings can be null of course, or in the case of pulling from an XML file, they can be empty as well. So when you have something like
    Convert.ToInt32, that's effectively a parse. And depending on how you have any supporting XSD's set up, you may be able to export that Xml file with some empty values (or potentially without even packing that element at all). It's very likely that you're not, but the Android app won't know anything about that.

    I consider it best practice (even if it doesn't always make sense, so use common sense I guess) to check before parsing.

    example:
    if (r == null) continue; //or log, or throw up (but don't bother trying to read fields off of it)
    ...
    dbtblalarm.Battery_10yr = string.IsNullOrWhitespace(r.Battery_10yr) ? null : Convert.ToInt32(r._Battery_10yr);
    ...

    Because if the table allows nulls, you'd rather insert a null value than the default parse result. Convert.To functions are really safe, so safe that a null value will insert a 0 instead of a null.

    Or on the other side of the fence, if the table doesn't allow nulls, or for some business reason has specific requirements on un-entered values, it allows you to gracefully handle that, instead of trying to make a catch all solve all your problems. And obviously, that could be modified to handle any other special case.

    Sometimes it also makes sense to make a table specifically for importing, that doesn't really have much in terms of expectations to the quality of the data at all. I like the term "staging" for those tables, the idea is that you get the data from the file, into the staging table, and then you implement the import using "transforms". It's a nice pattern for when you have really complicated business rules as to how you handle the data, especially if the data is going into environments that are heavily normalized.

    Anyways, my two cents. But honestly, those are pretty straight forward code snippets. Only so many ways to smash a rock! =)

  • Thanks for all that. Much appreciated.

    I haven't got to the error checking part, just threw something together to see if the concept would work.

    Thanks for the error check ideas. I usually do something similar.

    Thanks for the reference to the SQLite DB browser. I have downloaded it and looked at my SQLite DB and everything is AOK, so I will keep going down the path I have started.

    Thanks again for your input.

  • ariRaiariRai USMember
    edited June 2017

    Hello Robert,

    I am in the exact situation here. I have a requirement to create tables and load data from XML into a SQLite DB in C#. Your approach of using LINQ to XML and SQLite was exactly what i was thinking of. Can you please let me know if you were successful in this. Could you please share the code you used.

    Much appreciated!
    Thanks!

Sign In or Register to comment.