In this post you’ll add MySql from the marketplace to a dotnet core mvc app. Mysql was added to the marketplace in this post.

Service

We could use the cli to add MySql and then hook it up to the core app. But since we’ve installed Stratos, we can do this with a wizard:

1. Select Service: choose p-mysql
2. Select Plan: choose 10mb (or whatever applies to your needs)
3. Binding Params (Optional): leave empty
4. Service Instance:
   • Create and Bind to a new Service Instance
   • Name: mydatastore

You can wait for the changes to be applied or move to Code Changes below.

Code changes

We’re going to edit the dotnet core app.

First add some nugets:

dotnet add package Steeltoe.CloudFoundry.ConnectorCore
dotnet add package Steeltoe.Extensions.Configuration.CloudFoundryCore
dotnet add package MySql.Data
dotnet restore

Then make the changes below.

In the program.cs we need to load the environment variable VCAP_SERVICES and parse it into the configuration. For this we’ll use the Steeltoe extension:

using Steeltoe.Extensions.Configuration.CloudFoundry;

public static IWebHost BuildWebHost(string[] args) =>
WebHost.CreateDefaultBuilder(args)
.AddCloudFoundry()
.UseStartup<Startup>()
.Build();

Then we need to create a MySqlConnection and inject it into the IoC container. Again with a Steeltoe extension:

using Steeltoe.CloudFoundry.Connector.MySql;

public void ConfigureServices(IServiceCollection services)
{
   services.AddMvc();
   // get the connection to the mysql service
   services.AddMySqlConnection(Configuration);
}

We plan to connect to MySql on the About page. The code below runs a query that returns some text (no mysql objects needed) but only when the connection succeeds.

using MySql.Data.MySqlClient;

public IActionResult About([FromServices] MySqlConnection dbConnection)
{
   var cmd = new MySqlCommand("SELECT \"hello world from mysql \"; ", dbConnection);
   dbConnection.Open();
   ViewData["Message"] = "Your application description page. " + cmd.ExecuteScalar().ToString() ;
   dbConnection.Close();
   return View();
}

The code changes are done. Now cf push the application.

Security

After the cf push completes the application throws an exception:

fail: Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware[0]
An unhandled exception has occurred: Unable to connect to any of the specified MySQL hosts.
MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. 
---> System.AggregateException: One or more errors occurred. (Connection refused 10.244.0.145:3306) 
---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: Connection refused 10.244.0.145:3306

This means the security is not setup for the app to communicate with the MySql endpoint. With a json file we allow communication from our app to the MySql machine over port 3306.

[
   {
      "protocol": "tcp",
      "destination": "10.244.0.145",
      "ports": "3306",
      "log": true,
      "description": "Allow traffic to mysql"
    }
  ]

With the cf statements below we install the security profile in the demo organisation, dev space.

cf create-security-group mysql mysql-asg.json
cf bind-security-group mysql demo dev
cf restart core

After the app is restarted the about page shows us “hello from mysql”.

References

https://github.com/amithn/cloudfoundry-steeltoe-mysql-app
https://steeltoe.io/docs/steeltoe-configuration/#1-2-usage