Universal Windows Platform – Database

Toast Styles demonstrates how to display the different types of Toast notifications that are possible to be displayed in the Action Centre in Windows 10

Step 1

If not already, follow Setup and Start on how to Install and get Started with Visual Studio 2017 or in Windows 10 choose Start, and then from the Start Menu find and select Visual Studio 2017.

vs2017

Step 2

Once Visual Studio Community 2017 has started, from the Menu choose File, then New then Project…

vs2017-file-new-project

Step 3

From New Project choose Visual C# from Installed, Templates then choose Blank App (Universal Windows) and then type in a Name and select a Location and then select Ok to create the Project
vs2017-new-project-window

Step 4

Then in New Universal Windows Project you need to select the Target Version this should be at least the Windows 10, version 1803 (10.0; Build 17134) which is the April 2018 Update and the Minimum Version to be the same.

vs2017-target-platform

The Target Version will control what features your application can use in Windows 10 so by picking the most recent version you’ll be able to take advantage of those features. To make sure you always have the most recent version, in Visual Studio 2017 select Tools Extensions and Updates… then and then see if there are any Updates

Step 5

From the Menu choose Tools, then NuGet Package Manager and Manage NuGet Packages for Solution…

vs2017-tools-nuget-package-manager-manage-nuget-packages

Step 6

Then in NuGet select Browse and search for Microsoft.Data.SQLite as indicated and select Microsoft.Data.SQLite by Microsoft and then check the box under Project as indicated and select Install.

vs2017-nuget-database

Step 7

Then if Preview Changes is displayed, select Ok

vs2017-nuget-preview-changes-database

Step 8

License Acceptance will then be displayed, read through the terms displayed then to continue, select I Accept to Install the NuGet Package.

vs2017-nuget-license-database

Step 9

Once done select from the Menu, Project, then Add New Item…

vs2017-project-add-new-item

Step 10

From the Add New Item window select Visual C#, then Code from Installed then select Code File from the list, then type in the Name as Library.cs before selecting Add to add the file to the Project

vs2017-add-new-item-library

Step 11

Once in the Code View for Library.cs the following should be entered:

using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;

public class Item
{
    public int Id { get; set; }
    public string Album { get; set; }
    public string Artist { get; set; }
    public string Genre { get; set; }
}

public class Library
{
    private const string field_id = "@Id";
    private const string field_album = "@Album";
    private const string field_artist = "@Artist";
    private const string field_genre = "@Genre";
    private const string connection_string = "Filename=database.db";
    private const string table_create = "CREATE TABLE IF NOT EXISTS Items " +
        "(Id INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "Album NVARCHAR(255) NULL, Artist NVARCHAR(255) NULL, Genre NVARCHAR(255) NULL)";
    private const string table_insert = "INSERT INTO Items VALUES (NULL, @Album, @Artist, @Genre)";
    private const string table_update = "UPDATE Items SET Album = @Album, " +
        "Artist = @Artist, Genre = @Genre WHERE Id = @Id";
    private const string table_delete = "DELETE FROM Items WHERE Id = @Id";
    private const string table_select = "SELECT Id, Album, Artist, Genre FROM Items";

    private async Task<Item> Dialog(Item item)
    {
        Thickness margin = new Thickness(5);
        TextBlock id = new TextBlock()
        {
            Text = item.Id.ToString(),
            Margin = margin,
        };
        TextBox album = new TextBox()
        {
            Text = item.Album ?? string.Empty,
            Margin = margin,
            PlaceholderText = "Album"
        };
        TextBox artist = new TextBox()
        {
            Text = item.Artist ?? string.Empty,
            Margin = margin,
            PlaceholderText = "Artist"
        };
        TextBox genre = new TextBox()
        {
            Text = item.Genre ?? string.Empty,
            Margin = margin,
            PlaceholderText = "Genre"
        };
        StackPanel panel = new StackPanel()
        {
            Orientation = Orientation.Vertical
        };
        panel.Children.Add(id);
        panel.Children.Add(album);
        panel.Children.Add(artist);
        panel.Children.Add(genre);
        ContentDialog dialog = new ContentDialog()
        {
            Title = "Database",
            PrimaryButtonText = "Save",
            CloseButtonText = "Cancel",
            Content = panel
        };
        ContentDialogResult result = await dialog.ShowAsync();
        if (result == ContentDialogResult.Primary)
        {
            item.Album = album.Text;
            item.Artist = artist.Text;
            item.Genre = genre.Text;
            return item;
        }
        return null;
    }

    private async Task<bool> AddItemAsync(Item item)
    {
        bool result = false;
        using (SqliteConnection connection = new SqliteConnection(connection_string))
        {
            await connection.OpenAsync();
            SqliteCommand insert = new SqliteCommand()
            {
                Connection = connection,
                CommandText = table_insert
            };
            insert.Parameters.AddWithValue(field_album, item.Album);
            insert.Parameters.AddWithValue(field_artist, item.Artist);
            insert.Parameters.AddWithValue(field_genre, item.Genre);
            try
            {
                await insert.ExecuteScalarAsync();
                result = true;
            }
            catch (SqliteException)
            {
                result = false;
            }
            connection.Close();
        }
        return result;
    }

    private async Task<bool> EditItemAsync(Item item)
    {
        bool result = false;
        using (SqliteConnection connection = new SqliteConnection(connection_string))
        {
            await connection.OpenAsync();
            SqliteCommand insert = new SqliteCommand()
            {
                Connection = connection,
                CommandText = table_update
            };
            insert.Parameters.AddWithValue(field_id, item.Id);
            insert.Parameters.AddWithValue(field_album, item.Album);
            insert.Parameters.AddWithValue(field_artist, item.Artist);
            insert.Parameters.AddWithValue(field_genre, item.Genre);
            try
            {
                await insert.ExecuteScalarAsync();
                result = true;
            }
            catch (SqliteException)
            {
                result = false;
            }
            connection.Close();
        }
        return result;
    }

    private async Task<bool> DeleteItemAsync(Item item)
    {
        bool result = false;
        using (SqliteConnection connection = new SqliteConnection(connection_string))
        {
            await connection.OpenAsync();
            SqliteCommand delete = new SqliteCommand()
            {
                Connection = connection,
                CommandText = table_delete
            };
            delete.Parameters.AddWithValue(field_id, item.Id);
            try
            {
                await delete.ExecuteNonQueryAsync();
                result = true;
            }
            catch (SqliteException)
            {
                result = false;
            }
            connection.Close();
        }
        return result;
    }

    public async Task<bool> CreateAsync()
    {
        bool result = false;
        using (SqliteConnection connection = new SqliteConnection(connection_string))
        {
            await connection.OpenAsync();
            SqliteCommand create = new SqliteCommand(table_create, connection);
            try
            {
                await create.ExecuteNonQueryAsync();
                result = true;
            }
            catch (SqliteException)
            {
                result = false;
            }
            connection.Close();
        }
        return result;
    }

    public async Task<List<Item>> ListAsync()
    {
        List<Item> results = new List<Item>();
        using (SqliteConnection connection = new SqliteConnection(connection_string))
        {
            await connection.OpenAsync();
            SqliteCommand select = new SqliteCommand(table_select, connection);
            try
            {
                SqliteDataReader query = await select.ExecuteReaderAsync();
                while (query.Read())
                {
                    Item item = new Item()
                    {
                        Id = query.GetInt32(0),
                        Album = query.GetString(1),
                        Artist = query.GetString(2),
                        Genre = query.GetString(3)
                    };
                    results.Add(item);
                }
            }
            catch (SqliteException)
            {
                results = null;
            }
            connection.Close();
        }
        return results;
    }

    public async Task<bool> AddAsync()
    {
        Item item = await Dialog(new Item());
        if (item != null)
        {
            return await AddItemAsync(item);
        }
        return false;
    }

    public async Task<bool> EditAsync(AppBarButton button)
    {
        Item item = await Dialog((Item)button.Tag);
        if (item != null)
        {
            return await EditItemAsync(item);
        }
        return false;
    }

    public async Task<bool> DeleteAsync(AppBarButton button)
    {
        Item item = (Item)button.Tag;
        if (item != null)
        {
            return await DeleteItemAsync(item);
        }
        return false;
    }
}

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.

Step 12

In the Solution Explorer select MainPage.xaml

vs2017-mainpage-database

Step 13

From the Menu choose View and then Designer

vs2017-view-designer

Step 14

The Design View will be displayed along with the XAML View and in this between the Grid and /Grid elements, enter the following XAML:

<ListBox Margin="50" Name="Display">
	<ListBox.ItemContainerStyle>
		<Style TargetType="ListBoxItem">
			<Setter Property="HorizontalContentAlignment" Value="Stretch"/>
		</Style>
	</ListBox.ItemContainerStyle>
	<ListBox.ItemTemplate>
		<DataTemplate>
			<Grid>
				<Grid.ColumnDefinitions>
					<ColumnDefinition Width="Auto"/>
					<ColumnDefinition Width="20*"/>
					<ColumnDefinition Width="20*"/>
					<ColumnDefinition Width="20*"/>
					<ColumnDefinition Width="Auto"/>
				</Grid.ColumnDefinitions>
				<Grid Padding="5" Grid.Column="0" Background="{ThemeResource AccentButtonBackground}">
					<TextBlock Text="{Binding Id}" VerticalAlignment="Center"
					Foreground="{ThemeResource AccentButtonForeground}"/>
				</Grid>
				<Grid Padding="5" Grid.Column="1" Background="{ThemeResource AccentButtonForeground}">
					<TextBlock Text="{Binding Album}" VerticalAlignment="Center"
					Foreground="{ThemeResource AccentButtonBackground}"/>
				</Grid>
				<Grid Padding="5" Grid.Column="2" Background="{ThemeResource AccentButtonForeground}">
					<TextBlock Text="{Binding Artist}" VerticalAlignment="Center"
					Foreground="{ThemeResource AccentButtonBackground}"/>
				</Grid>
				<Grid Padding="5" Grid.Column="3" Background="{ThemeResource AccentButtonForeground}">
					<TextBlock Text="{Binding Genre}" VerticalAlignment="Center"
					Foreground="{ThemeResource AccentButtonBackground}"/>
				</Grid>
				<StackPanel Grid.Column="4" Orientation="Horizontal">
					<AppBarButton Name="Edit" Icon="Edit" Label="Edit" Tag="{Binding}" Click="Edit_Click"/>
					<AppBarButton Name="Delete" Icon="Delete" Label="Delete" Tag="{Binding}" Click="Delete_Click"/>
				</StackPanel>
			</Grid>
		</DataTemplate>
	</ListBox.ItemTemplate>
</ListBox>
<CommandBar VerticalAlignment="Bottom">
	<AppBarButton Icon="Add" Label="Add" Click="Add_Click"/>
</CommandBar>

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.

Step 15

From the Menu choose View and then Code

vs2017-view-code

Step 16

Once in the Code View, below the end of public MainPage() { … } the following Code should be entered:

Library library = new Library();

protected async override void OnNavigatedTo(NavigationEventArgs e)
{
	await library.CreateAsync();
	Display.ItemsSource = await library.ListAsync();
}

private async void Add_Click(object sender, RoutedEventArgs e)
{
	if (await library.AddAsync())
	{
		Display.ItemsSource = await library.ListAsync();
	}
}

private async void Edit_Click(object sender, RoutedEventArgs e)
{
	if (await library.EditAsync((AppBarButton)sender))
	{
		Display.ItemsSource = await library.ListAsync();
	}
}

private async void Delete_Click(object sender, RoutedEventArgs e)
{
	if (await library.DeleteAsync((AppBarButton)sender))
	{
		Display.ItemsSource = await library.ListAsync();
	}
}

Below the MainPage() Method an instance of the Library Class is created, OnNavigatedTo is Called when the MainPage is Displayed and calls the CreatAsync and ListAsync Methods in the Library Class. Add_Click, Edit_Click and Delete_Click call their respective methods in the Library Class and ListAsync to set the ItemsSource of the ListBox.

Step 17

That completes the Universal Windows Platform Application so Save the Project then in Visual Studio select the Local Machine to run the Application

vs2017-local-machine

Step 18

After the Application has started running you can then select Add to create a new Item, this will display a Dialog where you can enter the Album, Artist and Genre. Items added will be displayed in the List where you can Edit or Delete them.

ran-database

Step 19

To Exit the Application select the Close button in the top right of the Application

vs2017-close

Although this is just a basic example of creating and using a Database it shows how straight-forward 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 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 Database.

Creative Commons License

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