How to connect to SQL server from C++/WinRT UWP app?

Question

I am trying to create C++/WinRT UWP app that connects to MySQL database but really any way of querying anything would be great; I just need to store and update my data in a convenient way and use it over LAN.

Learning about WinRT I was redirected to UWP docs where I've found ways to use System.Data.SQLite/SqlClient or MySQL.Data but all these return this:

You are trying to install this package into a project that targets 'native,Version=v0.0', but the package does not contain any assembly references or content files that are compatible with that framework.

It means WinRT ("native") is not supported.

I've found "SQLite for Windows Runtime (Windows 8.1)" but when I try to do install-Package SQLite.WinRT I'm getting the same error. What is the preferred way of storing relational data in WinRT or what am I doing wrong?

Answer 1

I've been getting SQLite working in a C++/WinRT app over the last couple of weeks. SQLite is built into the windows sdk but I didn't discover that soon enough and went down the path of using the official version available in vcpkg and put out by sqlite.org.

I was able to get the library working fairly easily but I wasn't able to get the hang of the C interface. There are some good C++ wrappers also available in vcpkg so I tried 2 of them and settled on using sqlite_modern_cpp. It doesn't seem to be actively worked on but it's just an interface and works well with version 3.28 of sqlite.

To start using either sqlite or sqlite_modern_cpp I use basically the same method.

1. Install one of them using vcpkg

.\vcpkg.exe install sqlite-modern-cpp:x64-uwp

or

.\vcpkg.exe install sqlite3:x64-uwp

2. Enable user-wide integration in vcpkg

.\vcpkg.exe integrate install

3. Create a new project using C++/WinRT Blank App template

4. Change to x64 build

5. Once you change to x64, you should be able to add the following to pch.h

#include "sqlite_modern_cpp.h"

or

#include "sqlite3.h"

6. Now you can create a sqlite db and add a table

This example uses sqlite-modern-cpp and was put in the click handler of the blank app template just to have somewhere to put it.

void MainPage::ClickHandler(IInspectable const&, RoutedEventArgs const&)
{
    myButton().Content(box_value(L"Clicked"));

    // path for the temporary database file
    hstring path{ ApplicationData::Current().LocalFolder().Path() + L"\\temp.db" };

    // store the file name for use later
    std::string fname = to_string(path.c_str());

    // open a connection to the database or create the file if it doesn't exist
    sqlite::database db(fname); // default is READWRITE | CREATE

    // enable foreign keys
    db << "PRAGMA foreign_keys = ON;";

    // create a new table, if needed
    db << "CREATE TABLE IF NOT EXISTS division ("
        "   id          INTEGER PRIMARY KEY AUTOINCREMENT,"
        "   name        TEXT    NOT NULL,"
        "   distance    INTEGER NOT NULL,"
        "   size        INTEGER NOT NULL,"
        "   lanes       INTEGER NOT NULL,"
        "   rank        INTEGER NOT NULL,"
        "   progression TEXT    NOT NULL,"
        "   UNIQUE (name, distance, size)"
        ");";
}

7. Running the app and clicking the button will generate the db.

Using the method above, the file will be created in the app local folder. C:\Users\\AppData\Local\Packages\\LocalState\temp.db