How to Connect to SQL Server Database from a Windows 10 Uwp App

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

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

how to connect local SQL database from UWP application that can run in Raspberry PI3 device?

With the Windows 10 Fall Creators Update we have added support for .NET Standard 2.0 to UWP. With this you can now use SqlClient APIs to enable this scenario.

For this scenario to work, you need the following configuration: - Visual Studio 2017 Update 4 (or later) - Min version in your UWP project 16299 (=Fall Creators Update) - NETCore version 6.0.1 (or later)

Here is a sample app: https://github.com/StefanWickDev/IgniteDemos/tree/master/NorthwindDemo

Here is the session at Microsoft Ignite 2017 where we demo'ed it: https://myignite.microsoft.com/videos/53541

SQL connection in UWP app

If you want to connect a local database, for example the SQLite, there are implemented libraries could be used do this stuff:

A Developer's Guide to Windows 10: (10) SQLite Local Database

If you want to connect a server-based database, for example, the SQL Server database, unfortunately, there is not a built-in API like ADO.NET that could be used to connect the SQL Server directly. And for a workaround, you would have to utilize a middle layer for example, the WCF Serrvie:

How to access data from SQL Server database in Windows Store app, although this sample is written for store app, the used approach is the same for UWP application.

---

Related Topics