Using MySQL Database With UWP

Design MySQL DB

We can use many tools to connect to the DB, but here we'll use the official MySQL Workbench.

MySQL Workbench can be downloaded from here along with the Connectors.

What's a Connector?

A Connector is the DB driver that you include in your project to be able to interact and connect to your DB through your application.

After installing the Workbench with the default settings open it:

Then we click the Add button to add a new DB:

We insert the DB info then click Ok.

Now we click the New DB to establish a connection.

Here you can do whatever you want to your DB. let's go and add a Table (That's what you will usually do).

Now we start adding the columns we need at our DB, in this case we'll need only two columns:

• idtodo: INT, Primary Key, Not Null, Unique, Auto Incremental
• whatToDO: varchar(200)

And

Then we click apply, wait for a little bit, then we review or add any SQL Script we need, then click Apply in the wizard.

Note:

You can see the script for creating the table, you can absolutely go and execute your script directly without having to walkthrough what I did here.

The table is created successfully and ready to use!

UWP Demo (Basic Todo app)

Before we begin please note that the Dreamspark free MySQL is limited to four concurrent connections. So, it will be optimal for your private use or for testing purposes.

What we'll do?

• Create the UWP Project
• Reference the connector
• Implementing our MVVM (Model-View-ViewModel)

Creating the project

We click File, New Project, then from the Installed List we click -> Visual C#, Windows, Universal, then pick the UWP Project: Blank App (Universal Windows).

Reference the connector

Then we click browse and go to the following path, please note that the "Connector.NET 6.9" version might differ by the time you're reading this so you should go and look it up yourself.

C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\RT

We select the connector then we're ready to go.

Implementing the MVVM

We'll only create one page that contains a list of our Todo items, and a TextBox with a button to add the Text Box content as a new Todo.

Todo Model:

public class Todo
{
    public string whatToDO { get; set; }
    public Todo(string what)
    {
        whatToDO = what;
    }
}

ViewModel (TodoViewModel.cs):

public class TodoViewModel
{
    private static TodoViewModel _todoViewModel = new TodoViewModel();
    private ObservableCollection<Todo> _allToDos = new ObservableCollection<Todo>();

    public ObservableCollection<Todo> AllTodos
    {
        get
        {
            return _todoViewModel._allToDos;
        }
    }

    public IEnumerable<Todo> GetTodos()
    {
        try
        {

            using (MySqlConnection connection = new MySqlConnection("Your connectionString + SslMode=None"))
            {
                connection.Open();
                MySqlCommand getCommand = connection.CreateCommand();
                getCommand.CommandText = "SELECT whatToDO FROM todo";
                using (MySqlDataReader reader = getCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        _todoViewModel._allToDos.Add(new Todo(reader.GetString("whatToDO")));
                    }
                }
            }
        }
        catch(MySqlException)
        {
            // Handle it :)
        }
            return _todoViewModel.AllTodos;
    }

    public bool InsertNewTodo(string what)
    {
        Todo newTodo = new Todo(what);
        // Insert to the collection and update DB
        try
        {
            using (MySqlConnection connection = new MySqlConnection("Your connectionString + SslMode=None"))
            {
                connection.Open();
                MySqlCommand insertCommand = connection.CreateCommand();
                insertCommand.CommandText = "INSERT INTO todo(whatToDO)VALUES(@whatToDO)";
                insertCommand.Parameters.AddWithValue("@whatToDO", newTodo.whatToDO);
                insertCommand.ExecuteNonQuery();
                _todoViewModel._allToDos.Add(newTodo);
                return true;

            }
        }
        catch(MySqlException)
        {
            // Don't forget to handle it
            return false;
        }

    }


    public TodoViewModel()
    { }
}

What we did at GetTodos()

• Established the connection
• We opened it
• Initializing the command ( Query )
• Execute the command
• Read incoming values and initializing new Todo objects then adding it to our ObservableCollection for data binding later.

What we did at InsertNewTodo (string what)

• Started by creating new object of the Todo class & initialize it.
• Established the connection
• We opened the connection
• Initializing the command ( Query )
• Add the Query string
• Add any parameters to the Query
• Execute the command(Query)
• Add the new Todo to the ObservableCollection

Note:

MySQL API for WinRT is not supporting SSL connection, So you'll have to turn it off by adding SslMode=None to your connection string.

App.xaml.cs code:

We just create a public static instance from our ViewModel to be consumed from all over the app.

public static TodoViewModel TODO_VIEW_MODEL = new TodoViewModel();

MainPage.xaml Code:

Within the Main Grid:

<StackPanel Orientation="Vertical">
    <ListView x:Name="Todos">
        <ListView.ItemTemplate>
            <DataTemplate>
                <StackPanel>
                    <TextBlock FontSize="25" Text="{Binding whatToDO}"/>
                </StackPanel>
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
    <TextBox x:Name="NewTodoTxtBox" FontSize="25" Header="New Todo:"/>
    <Button x:Name="InsertTodoBtn" Click="InsertTodoBtn_Click" Content="Insert New Todo" Margin="0,20,0,0"/>
</StackPanel>

MainPage Code-Behind(within MainPage Class):

public sealed partial class MainPage : Page
{

    public MainPage()
    {
        this.InitializeComponent();
    }

    private void InsertTodoBtn_Click(object sender, RoutedEventArgs e)
    {

        App.TODO_VIEW_MODEL.InsertNewTodo(NewTodoTxtBox.Text);
    }

    protected override void OnNavigatedTo(NavigationEventArgs e)
    {
        Todos.ItemsSource = App.TODO_VIEW_MODEL.GetTodos();
    }
}

We're done!

Download the full sample from here.