How to connect to SQL server database from a Windows 10 UWP app

Question

I'm trying to connect to an on-prem MS SQL database from a universal windows app. I'm making a LOB app using UWP, to support desktop, tablet and mobile use. When trying to connect to a local (intranet) SQL server database, I'm used to using an instance of SqlConnection to connect to a local server, but since SqlConnection is not included in the .NET subset used in UWP, how is this done when using UWP?

I've looked over the official Microsoft samples as well as the how-to guides, and found nothing there about database connection that is not an Azure database. DbConnection seemed like it could be a good way to go, but can't be used since it's abstract, and it's children (for instance Data.SqlClient.SqlConnection) does not seem to be included in the .NET subset for UWP.

Am I missing something super obvious here? As an aside, does anyone know a good namespace reference for UWP?

Edit for non-duplicate: The linked question suggested as a duplicate is for Windows 8/8.1 apps, and while there are some similarities, the code in the accepted answer for that question won't work on UWP. The principle is the same, however, but there should be a better technical reference for Windows 10 apps build with UWP.

Answer 1

With the Windows 10 Fall Creators Update (build 16299) UWP apps can now access SQL Server directly via the standard NET classes (System.Data.SqlClient) - thanks to the newly added support for .NET Standard 2.0 in UWP.

Here is a Northwind UWP demo app: https://github.com/StefanWickDev/IgniteDemos

We have presented this demo at Microsoft Ignite in September 2017, here is the recording of our session (skip to 23:00 for the SQL demo): https://myignite.microsoft.com/sessions/53541

Here is the code to retrieve the products from the Northwind database (see DataHelper.cs in the demo). Note that it is exactly the same code that you would write for a Winforms or WPF app - thanks to the .NET Standard 2.0:

public static ProductList GetProducts(string connectionString)
{
    const string GetProductsQuery = "select ProductID, ProductName, QuantityPerUnit," +
        " UnitPrice, UnitsInStock, Products.CategoryID " +
        " from Products inner join Categories on Products.CategoryID = Categories.CategoryID " +
        " where Discontinued = 0";

    var products = new ProductList();
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            if (conn.State == System.Data.ConnectionState.Open)
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = GetProductsQuery;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new Product();
                            product.ProductID = reader.GetInt32(0);
                            product.ProductName = reader.GetString(1);
                            product.QuantityPerUnit = reader.GetString(2);
                            product.UnitPrice = reader.GetDecimal(3);
                            product.UnitsInStock = reader.GetInt16(4);
                            product.CategoryId = reader.GetInt32(5);
                            products.Add(product);
                        }
                    }
                }
            }
        }
        return products;
    }
    catch (Exception eSql)
    {
        Debug.WriteLine("Exception: " + eSql.Message);
    }
    return null;
}

If you need to support earlier versions than the Fall Creators Update, there is also a way for you to call SqlClient APIs from your UWP app package, via the Desktop Bridge. I have a sample for this published here: https://github.com/Microsoft/DesktopBridgeToUWP-Samples/tree/master/Samples/SQLServer