Universal Windows Platform – Database

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 locate and select Visual Studio 2017.

vs2017-home

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 the Name as Database and select a Location and then select Ok to create the Project

vs2017-new-project

Step 4

Then in New Universal Windows Project you need to select the Target Version to be Windows 10 Creators Update (10.0; Build 15063) and the Minimum Version to be at least Windows 10 Anniversary Update (10.0; Build 14393) or Windows 10 Creators Update (10.0; Build 15063)

vs2017-platform

Step 5

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

vs2017-manage-nuget

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.

nuget-database

Step 7

Then if Preview for Review Changes is displayed, select Ok

nuget-review-database

Step 8

License Acceptance will then be displayed, read through the terms displayed then to continue, select I Accept to install the package.

nuget-license-database

Step 9

From the Menu choose Project, then Add New Item…

vs2017-add-new-item

Step 10

From the Add New Item choose Visual C# from Installed then choose Code then select Code File and then in the Name as Library.cs and then select Add to add the file to the Project

vs2017-library

Step 11

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

using Microsoft.Data.Sqlite;
using Microsoft.Data.Sqlite.Internal;
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;
        SqliteEngine.UseWinSqlite3();
        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;
    }
}

Step 12

Then in the Solution Explorer select MainPage.xaml

vs2017-library-mainpage

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:

<Grid.RowDefinitions>
	<RowDefinition Height="*"/>
	<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>
<CommandBar Grid.Row="1" VerticalAlignment="Bottom">
	<AppBarButton Name="Add" Icon="Add" Label="Add" Click="Add_Click"/>
</CommandBar>
<ListBox Grid.Row="0" Name="Display" Loaded="Display_Loaded">
	<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>

It should appear as such:

xaml-database

Step 15

From the Menu choose View and then Code

vs2017-view-code

Step 16

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

Library library = new Library();

private async void Display_Loaded(object sender, RoutedEventArgs 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();
	}
}

It should then appear as such:

code-database

Step 17

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

vs2017-debug

Step 18

Once started the Application should then appear

run-database

Step 19

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 Genra. Items added will be displayed in the List where you can Edit or Delete them.

ran-database

Step 20

To Exit the application select Stop in Visual Studio

vs2017-stop

Creative Commons License

Advertisements

One thought on “Universal Windows Platform – Database

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s