Access MySQL data from a UWP application without using services

In Windows Store and UWP applications, web services must be used to access databases stored on the network. If you use Azure databases, then you can use a service like Azure Mobile Apps

Let me teach you the “bad” and tell you how you can access data from the MySQL database in the UWP application directly using Connector / Net. The code will be identical for .Net WPF applications.
')


Using direct data access is not recommended for obvious reasons. The connection string is stored in the application, so anyone who downloads the application can potentially access the string. This is partly why in UWP applications there are no specific opportunities for working with data directly. In addition, updating the service logic will immediately affect all applications attached to it, while forcing all users to instantly update the application will fail.

Why use a non-recommended method? You can find some use cases. For example, I came up with the option of creating the MySQL database admin application for easy editing of phone records. Of course, there is also an option to create a user with read-only rights. In some cases, it can be used, despite the fact that the name of the server and database is compromised in the connection string. Alternatively, you can force the user to enter the data required for authorization each time.

As a test database, I use ClearDB's MySQL cloud database, which can be created in Azure. Owners of a free student account DreamSpark can create such a database for free. However, the method that I describe is suitable for any MySQL database, and not just for databases deployed in the Azure cloud.

Creating a database is quite simple. On the new Azure portal, click the plus sign in the menu, select “Data + Storage” - “MySQL Database”, set the name of the database, configure the parameters and agree to the terms.



Literally in a minute the base will be deployed.



Now you can find the connection string in the properties.



As I already mentioned, we need Connector / Net to work with the MySQL database.

At the time of writing, NuGet has issued MySQL.Net Connector / Net version 6.6.4.
And on the site mysql Chapter 2 Connector / Net Versions indicated that WinRT applications are supported, starting with version 6.7
So you have to download the installer from the official site: Download Connector / Net

When installing using the installer, you will be prompted to install the examples in C # and VB. The examples turned out to be on .Net version 2.0, so they seemed to me not very relevant (for me, even .Net of this version was not installed).

Let's proceed to the creation of the application. In Visual Studio, we create a project for a universal Windows application. Add to it a link to the library MySql.Data.RT.dll , which should be located at
C: \ Program Files (x86) \ MySQL \ MySQL Connector Net 6.9.8 \ Assemblies \ RT \
Officially, UWP is not yet supported, but the WinRT library is quite suitable for universal applications (after all, the UWP is based on the WinRT API, although not all of WinRT is available or used in UWP).

If you have a WPF application, then you need a library.
C: \ Program Files \ MySQL \ MySQL Connector Net 6.9.8 \ Assemblies \ v4.5 \ MySql.Data.dll

In the manifest of the universal application, you need to add the ability to use the Internet - the Internet (Client). Usually this feature is installed by default, so just go in and check that the check mark is in the right place.

You can write code.
The namespace in which the MySQL classes are located is MySql.Data.MySqlClient. Add it in order not to be mentioned in the code every time:

using MySql.Data.MySqlClient; 

First, an example of how you can create a table and enter data into it:

  using (MySqlConnection connection = new MySqlConnection("Database=as_bfb6f501597b777;Data Source=us-cdbr-azure-west-c.cloudapp.net;User Id=b74038821f5aea;Password=2564f4e5;SslMode=None;")) { connection.Open(); MySqlCommand createCommand = new MySqlCommand("CREATE TABLE demotable(salary int,surname varchar(255))", connection); createCommand.ExecuteNonQuery(); MySqlCommand addCommand = new MySqlCommand("INSERT INTO demotable (salary,surname) VALUES (1234,'')", connection); addCommand.ExecuteNonQuery(); } 

Here I created a table with the name demotable with two fields, salary and surname, and entered into it a row with the values ​​of last name and salary. The employee with the surname Vader will receive 1234 for meritorious service.
Pay attention to the connection string. We took it from the portal. At the end, you need to add " SslMode = None; ", since Connector / Net does not support SSL for the time being.

Now a simple example of reading data.
We need to create a class of the model with the same fields that are in our database. We do it like this:

  public class CloudTable { private string _surname; public string surname { get { return _surname; } set { _surname = value; } } private int _salary; public int salary { get { return _salary; } set { _salary = value; } } } 

Then you can declare a collection in the code of our page:

  public ObservableCollection<CloudTable> demodata = new ObservableCollection<CloudTable>(); 

The data will be displayed in a ListView. Let the XAML code be like this:

  <ListView x:Name="myListView" Width="400" Height="400" Margin="20,20,0,0" ItemsSource="{Binding}"> <ListView.ItemTemplate> <DataTemplate> <StackPanel> <TextBlock Text="{Binding Path=surname}"></TextBlock> <TextBlock Text="{Binding Path=salary}"></TextBlock> </StackPanel> </DataTemplate> </ListView.ItemTemplate> </ListView> 

As you can see, I didn’t bother too much and made the display template for the list item just as a panel, inside which there are 2 text blocks. The block text values ​​are bound to the surname and salary values ​​from the data context. We will set the data context in C # code. You can, if you wish, make a better design, of course.

The code itself for reading data into a collection and linking it to a ListView is as follows:

  using (MySqlConnection connection = new MySqlConnection("Database=as_bfb6f501597b777;Data Source=us-cdbr-azure-west-c.cloudapp.net;User Id=b74038821f5aea;Password=2564f4e5;SslMode=None;")) { connection.Open(); MySqlCommand readCommand = new MySqlCommand("SELECT * FROM demotable", connection); using (MySqlDataReader reader = readCommand.ExecuteReader()) { while (reader.Read()) { demodata.Add(new CloudTable { surname = reader.GetString(1), salary = reader.GetInt32(0) }); } } } myListView.DataContext = demodata; 

Data is read in a loop, which looks at least old-fashioned. However, thanks to Connector / Net and for the opportunities that exist.