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.
The code for this project has been added to the IOT Samples Codeplex project: https://iotsampler.codeplex.com/
In what follows:
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; }
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); }
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); } }
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); } }
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; }
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); ; }
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>(); }
public async void OpenDB() { SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text); } public async void DropDatabase() { SQLiteAsyncConnection connection = new SQLiteAsyncConnection(textBox.Text); await connection.DropTableAsync<Sensor>(); }
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; }
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”