The previous blog created the UA project, inserted the required SQLite bits and set up the XAML user interface. This part covers the functional code.


Now for some coding dexterity:

Each of the sensor buttons’ content (the displayed text on the button)  is of the format:

<Sensor Name> <Space> <Action: Insert|Update|Delete>

To that end, all buttons use the same event handler,button_Click. The handler extracts the sensor name and action and calls the downstream handler for the action passing the sensor name as a parameter. The other UI buttons are specific Table and Database actions and so the content is used to directly to decipher which method to call.


  • Create the project as per the previous blog: Win 10 IoT- Universal App – SQLite Database- Part 2 “A Universal App”
  • Copy each of the code snippets into MainPage.cs within the MainPage class.
  • Build and test the app on a Windows 10 desktop (x86/64)
    Try it on an x86 Win 10 IoT device if you have one.
  • Set the target to ARM and build and test on a Win 10 IoT RPI2.
  • ToDo: Modify the XAML so the app will fit on a Win 10 phone (Next blog)

The code for this project has been added to the IOT Samples Codeplex project: https://iotsampler.codeplex.com/


In what follows:

  • TextBox.Text.Text is the database filename.

1. The Button Click  Event Handler

        private void button_Click(object sender, RoutedEventArgs e)
        {
            Button button = (Button)sender;
            button.IsEnabled = false;
            string content = button.Content.ToString();
            string buttonContents = content.Split(new char { ' ' });
            switch (buttonContents[1])
            {
                //For these the verb is last
                case "Insert":
                    Insert(buttonContents[0]);
                    break;
                case "Update":
                    Update(buttonContents[0]);
                    break;
                case "Delete":
                    Delete(buttonContents[0]);
                    break;
                default:
                    // For these the verb is first
                    switch (content)
                    {
                        case "Query Database":
                            QueryDB();
                            break;
                        case "Add Table":
                            AddTable();
                            break;
                        case "Drop Table":
                            DropTable();
                            break;
                        case "New Database":
                            CreateDB();
                            break;
                        case "Open Database":
                            OpenDB();
                            break;
                        case "Drop Database":
                            DropDatabase();
                            break;
                    }
                    break;

            }
            button.IsEnabled = true;
        }

2. Sensor Record Insert/Update/Delete

2.1 Insert

        public async void Insert(string sensor)
        {
            
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
            var Sensor = new Sensor()
            {
                dateTime = DateTime.Now,
                Name = sensor,
                Value = GetRandomValue(sensor)
             };
            await connection.InsertAsync(Sensor);
        }

2.2 Update

        public async void Update(string sensor)
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
        var SensorQry = await connection.Table<Sensor>().Where(x => x.Name.StartsWith(sensor)).FirstOrDefaultAsync();

            if (SensorQry != null)
            {
                SensorQry.dateTime = DateTime.Now;
                SensorQry.Value = GetRandomValue(SensorQry.Name);
                await connection.UpdateAsync(SensorQry);
            }
        }

2.3 Delete

        public async void Delete(string sensor)
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);

        var SensorQry = await connection.Table<Sensor>().Where(x => x.Name.StartsWith(sensor)).FirstOrDefaultAsync();

            if (SensorQry != null)
            {
                await connection.DeleteAsync(SensorQry);
            }

        }

2.4 GetRandomValue

The randomly generated sensor values are +/- 50 about a sensor specific median vale.

        Random ran = new Random();
        int GetRandomValue(string sensor)
        {
            int val = ran.Next();
            val = val % 50;
            switch (sensor.Substring(0,4))
            {
                case "Temp":
                    val += 25;
                    break;
                case "Humi":
                    val += 50;
                    break;
                case "Pres":
                    val += 975;
                    break;
            }
            return val;

        }

3. Query

This gets all records whose sensor name begins with the string in the textbox textBoxQueryBeginsWith. If it is blank then all records are selected. The records are then parsed and sent to the Debug window. (We could do something more here).

        public async void QueryDB()
        {
            string qry = textBoxQueryBeginsWith.Text;
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
            var SensorQry = /*await*/ connection.Table<Sensor>().Where(x => x.Name.StartsWith(qry));

            var SensorQryLst = await SensorQry.ToListAsync();

            System.Diagnostics.Debug.WriteLine("Number of {0} records found: {1}", qry, SensorQryLst.Count);
            
            foreach (var item in SensorQryLst)
            {
                int id = item.id;
                string name = item.Name;
                int value = item.Value;
                DateTime dateTime = item.dateTime;
                if (item.Name == null)
                    name = "Unknown";
                //Do your stuff
                System.Diagnostics.Debug.WriteLine("{0} {1} {2} {3}", id,dateTime, name, value); ;
            }

4. Table Actions

Add and drop table actions work with the sensor class.

        public async void AddTable()
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
            await connection.CreateTableAsync<Sensor>();
        }

        public async void DropTable()
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
            await connection.DropTableAsync<Sensor>();
        }

5. Database Actions

5.1 Open an existing database and drop (delete) a database.

        public async void OpenDB()
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
        }

        public async void DropDatabase()
        {
            SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text);
            await connection.DropTableAsync<Sensor>();
        }

5.2 For a new database

Code to delete the database if it exists and create a new version. Also auto-creates the table.
Note that res is set to true regardless so a simple open is sufficient

        public async System.Threading.Tasks.Task<bool> CreateDB()
        {
            SQLite.SQLiteAsyncConnection connection;
            bool res = await DoesDbExist(textBox.Text);
            res = true;
            if (!res)
            {
                SQLite.SQLiteOpenFlags sqlflg = SQLite.SQLiteOpenFlags.Create;
                connection = new SQLite.SQLiteAsyncConnection(textBox.Text, sqlflg, false);
            }
            else
            {
                connection = new SQLite.SQLiteAsyncConnection(textBox.Text);
            }
            await connection.CreateTableAsync<Sensor>();

            return res;
        }

5.3 The database is in user storage

        public async System.Threading.Tasks.Task<bool> DoesDbExist(string DatabaseName)
        {
            bool dbexist = true;
            try
            {
Windows.Storage.StorageFile storageFile = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(DatabaseName);

                if (storageFile == null)
                    dbexist = false;
            }
            catch
            {
                dbexist = false;
            }

            return dbexist;
        }


Next:

Win 10 IoT- Universal App – Using the Visual State Manager to dynamically modify the UI

Previous: Win 10 IoT- Universal App – SQLite Database- Part 2 “A Universal App”