How to: Using MySQL Connector/NET with Windows Store Apps

Overview

This post will show how to use Connector/NET and Windows Store Apps to connect and retrieve data from a MySql Server database.

Requirements:

  • MySQL Connector/NET 6.7 or later
  • Visual Studio 2012
  • Windows 8
  • MySql Server 5.1 or above
  • Sakila sample database installed

Creating a Windows Store project

The first step is to create a new Windows Store project in Visual Studio 2012 selecting File / New / Project menu and Template / Visual C# / Windows Store / Grid App (XAML):

Using Connector/NET in the Windows Store application

The first thing to do is add the reference for the Connector/NET assembly compiled for Windows Store apps (if you use the Connector/NET installer it should be located in C:\Program Files (x86)\MySQL\MySQL Connector Net 6.7.X\Assemblies\RT\MySql.Data.RT.dll)

 We are going to reuse some data source base classes from SampleDataSource.cs but adding a new one for the database data.

Add a new class in DataModel folder called FilmDataSource and add the following code:

  public sealed class FilmDataSource
  {
    private static FilmDataSource _filmDataSource = new FilmDataSource();
 
    private ObservableCollection<SampleDataGroup> _allGroups = new ObservableCollection<SampleDataGroup>();
    public ObservableCollection<SampleDataGroup> AllGroups
    {
      get { return this._allGroups; }
    }
 
    public static IEnumerable<SampleDataGroup> GetGroups()
    {
      return _filmDataSource.AllGroups;
    }
 
    public static SampleDataGroup GetGroup(string uniqueId)
    {
      var matches = _filmDataSource.AllGroups.Where((group) => group.UniqueId.Equals(uniqueId));
      if (matches.Count() == 1) return matches.First();
      return null;
    }
 
    public static SampleDataItem GetItem(string uniqueId)
    {
      var matches = _filmDataSource.AllGroups.SelectMany(group => group.Items).Where((item) => item.UniqueId.Equals(uniqueId));
      if (matches.Count() == 1) return matches.First();
      return null;
    }
 
    public FilmDataSource()
    {
      using (MySqlConnection connection = new MySqlConnection("server=*****;database=sakila;uid=*****;password=*****;"))
      {
        connection.Open();
 
        // Category list
        MySqlCommand categoriesCommand = new MySqlCommand("SELECT category_id, name FROM category", connection);
        using (MySqlDataReader reader = categoriesCommand.ExecuteReader())
        {
          while (reader.Read())
          {
            SampleDataGroup group = new SampleDataGroup(reader.GetString("category_id"),
              reader.GetString("name"),
              string.Empty,
              "Assets/LightGray.png",
              string.Empty);
 
            // Film list by Category
            using (MySqlConnection filmsConnection = (MySqlConnection)connection.Clone())
            {
              filmsConnection.Open();
              MySqlCommand filmsCommand = new MySqlCommand(@"SELECT film_list.* FROM film_category"
                + " JOIN film_list ON film_category.film_id = film_list.FID"
                + " WHERE category_id = ?id",
                filmsConnection);
              filmsCommand.Parameters.Add(new MySqlParameter("?id", reader.GetInt32("category_id")));
              using (MySqlDataReader filmsReader = filmsCommand.ExecuteReader())
              {
                while (filmsReader.Read())
                {
                  group.Items.Add(new SampleDataItem(filmsReader.GetString("FID"),
                    filmsReader.GetString("title"),
                    filmsReader.GetString("actors"),
                    "Assets/DarkGray.png",
                    filmsReader.GetString("description"),
                    filmsReader.GetString("description"),
                    group));
                }
              }
            }
            this.AllGroups.Add(group);
          }
        }
      }
    }
  }

Edit LoadState method in GroupedItemsPage.xaml.cs to change the data source class:

protected override void LoadState(Object navigationParameter, Dictionary<StringObject> pageState)
{
  var filmDataGroups = FilmDataSource.GetGroups();
  this.DefaultViewModel["Groups"] = filmDataGroups;
}

Run the application or press F5 to show Categories and some Films:


Modify LoadState method in GroupDetailPage.xaml.cs to add the reference to the new data source in order to show the details for a selected Category:

protected override"> void LoadState(Object navigationParameter, Dictionary<StringObject> pageState)
{
    var group = FilmDataSource.GetGroup((String)navigationParameter);
    this.DefaultViewModel["Group"] = group;
    this.DefaultViewModel["Items"] = group.Items;
}

Run the application again and select a Category to show the details and the Films full list:

Edit LoadState method in ItemDetailPage.xaml.cs as follow to use FilmDataSource for Film details:

protected override void LoadState(Object navigationParameter, Dictionary<StringObject> pageState)
{
    // Allow saved page state to override the initial item to display
    if (pageState != null && pageState.ContainsKey("SelectedItem"))
    {
        navigationParameter = pageState["SelectedItem"];
    }
 
    var item = FilmDataSource.GetItem((String)navigationParameter);
    this.DefaultViewModel["Group"] = item.Group;
    this.DefaultViewModel["Items"] = item.Group.Items;
    this.flipView.SelectedItem = item;
}

Run the application and select a Film to show the details:

Now the Windows Store App is ready using the connection to a MySQL database to retrieve and show data.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today