sticky
Connecting to Database Services Like SQLServer/MySQL/MongoDB in UWP

    General discussion

  • With the addition of support for .NET Standard2.0 in the Windows 10 Fall Creator Update (10.0.16299)  you can now connect programmatically to database services like SQLServer, MySql and MongoDB in your UWP app. You can now do this directly in code as you did in non UWP .NET Framework applications.
    Before the windows 10 Fall Creator Update (10.0.16299), you were required to access databases such as SQLServer, MySql and MongoDB, via a Web Service.
    To connect to supported database services directly, you need to make sure that Project's target version and min version are 16299 or higher.


    UWP applications restrict network access by default. In order to connect to the database successfully, you will need to add Internet (Client & Server), Internet (Client) and Private Networks (Client & Server) capabilities in "Package.appxmanifest".

    If you do not add these capabilities, you will get something like the following exception:

    'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.'

    The Following are the specific code samples:

    Scenario 1: Connect MS SQL Server in UWP

    [using "SQL Server Authentication" to connect database]

    SQL connection string should be like this: Data Source=10.xxx.xx.xxx;Initial Catalog=UWPTest;User Id=sa;Password=xxxxxxxx;

    Code sample:

    String connsql = @"Data Source=10.xxx.xx.xxx;Initial Catalog=database name;User Id=sa;Password=xxxxxxxx;";
    try
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = connsql;
            conn.Open();
            String sql = "select * from dbo.Table_1";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                 Debug.WriteLine(dr["ID"] + ": " + dr["Name"]);
            }
                 conn.Close();
         }
    }
    catch (Exception ex)
    {
         Debug.WriteLine(ex);
    }

    [Using "Windows Authentication" to connect database]

    Note: If you want to use "windows authentication" to connect to a database, you need to add the "enterpriseAuthenticationcapability in "Package.appxmanifest".

    SQL connection string should be like this: Data Source=10.xxx.xxx.xxx;Initial Catalog=database name;Trusted_Connection=True;

    Scenario 2:  Connect MongoDB database in UWP

    1. Create a UWP project -- target platform version and min version is 16299
    2. Open the "Package Manager Console" and use the command (PM> Install-Package MongoDB.Driver) to install the "MongoDB.Driver".
    3. The following simple code allows you to connect to the MongoDB.

    Code sample:

    var client = new MongoClient("mongodb://10.xxx.xx.xxx:27017");
    var database = client.GetDatabase("uwpmongodbtest");
    var collection = database.GetCollection<BsonDocument>("c1");
    var document = new BsonDocument
                {
                     { "name","MongoDB"},
                     { "type","Database"},
                     { "count",1},
                     { "info",new BsonDocument { { "x", 203 }, { "y", 102 } }}
                };
    await collection.InsertOneAsync(document);
    var count = collection.Count(document);
    Debug.WriteLine(count);
    var document1 = collection.Find(document);
    Debug.WriteLine(document1.ToString());

    Scenario 3: connect MySQL database in UWP

    1. Create a UWP project -- target platform version and min version is 16299
    2. Open the "Package Manager Consoleand use the command (PM> Install-Package MySql.Data) to install the "MySql.Data".
    3. The following simple code allows you to connect to the MySQL database.

    Code sample:

    string M_str_sqlcon = "server=10.xxx.xx.xxx;user id=root;password=pwd;database=uwptest";
    MySqlConnection mysqlcon = new MySqlConnection(M_str_sqlcon);
    MySqlCommand mysqlcom = new MySqlCommand("select * from table1", mysqlcon);
    mysqlcon.Open();
    MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
    while (mysqlread.Read())
    {
        Debug.WriteLine(mysqlread.GetString(0)+":"+mysqlread.GetString(1));
    }
    mysqlcon.Close();

    Reference:

    Use a SQL Server database in a UWP app

    App capability declarations

    Announcing UWP Support for .NET Standard 2.0


    Friday, November 16, 2018 7:36 AM
    Moderator