Universal Windows Platform – Tutorial Insider – Database

Standard

comentsys.wordpress.com today features a Universal Windows Platform tutorial showing how to use a Database with SQLite.

Details

The NuGet package Microsoft.Data.SQLite is used in the Project and used in the Library.cs – there’s a Item class that defines the object to be stored in the Database with “Id”, “Album”, “Artist” and “Genre” properties. In the Library Class there are some const defining the fields that are in the Database for parameterised queries, the “connection_string” for the database and the SQL instructions to Create the Database and to Insert, Update, Delete and Select data from the Database. There’s a Dialog method which takes an Item Object as a parameter for editing then defines a TextBox for each property of the Item Class plus a TextBlock to show the “Id” and returns the Item Object.

The AddItemAsync method takes in an Item Object then uses the SqliteConnection to connect to the Database then opens it with OpenAsync and then sets up an Insert with an SqliteCommand and sets the Parameters to be the string properties from the Item Class and then Executes the Command with ExecuteScalarAsync. EditItemAsync takes in an Item Object then uses the SqliteConnection to connect to the Database then Opens it with OpenAsync and then sets up an Update with an SqliteCommand and sets the Parameters to be all the properties from the Item Class and then Executes the Command with ExecuteScalarAsync.

The DeleteItemAsync method takes in an Item Object then uses the SqliteConnection to connect to the Database then Opens it with OpenAsync and then sets up a Delete with an SqliteCommand and sets the Parameters to be the “Id” from the Item Class and then Executes the Command with ExecuteNonQueryAsync. CreateAsync uses the SqliteConnection to connect to the Database then opens it with OpenAsync and then sets up the a Create with an SqliteCommand and then Executes the Command with ExecuteNonQueryAsync. ListAsync uses the SqliteConnection to connect to the Database then Opens it with OpenAsync and then sets up a Select with an SqliteCommand and then Executes the Command with ExecuteReaderAsync with a SqliteDataReader, then While it can Read from the results it creates an Item from the returned Columns and add them to the results – this is returned as a List of Item.

There’s an AddAsync method that uses Dialog to create a new Item and this is passed to the AddItemAsync method to be added to the Database, EditAsync uses the passed in AppBarButton which has an Item bound to the “Tag” Property of it then passes this into the Dialog method and then used with the EditItemAsync method to update the Database. DeleteAsync uses the passed in AppBarButton which has an Item bound to the “Tag” Property of it then passes this into the DeleteItemAsync method to remove it from the Database.

The MainPage has an AppBarButton for Add – this will show the Dialog to add a new Item into the Database. Then theres a ListBox that will have a list of Item set to it’s DataSource and has a DataTemplate that displays a list of Id, Album, Artist and Genre for each Item in the Database plus an AppBarButton for Edit and Delete.

Behind the Scenes

This was the first time using SQLite and also for an example a NuGet Package which allows external functionality to be easily added to a Project, although this is just a basic example of creating and using a Database it shows how straightforward it is to have data be persistable in an application – anything added will be recalled on subsequent runs of the Application unless deleted for example. This was the last example in this set of Tutorials and also brings back an example from an older set of Tutorials done for Visual Basic many years ago and here it is updated for today using SQLite.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s