Add Sql Server as user provided service in Cloud Foundry

In this post you’ll add Sql Server from Azure to a dotnet core mvc app. We presume you’ve already created the Sql Server in Azure. If not follow my post azure sql server for centralised database.

User Provided Service

A user provided service is a way to connect non-marketplace services to your cloud foundry app. The service will show up in the services tab and the connectionstring (or anything else you need) is added to the configuration.

We use the cli to add Sql Server as a user provided service and then hook it up to the core app. The connectionstring is provided by Azure on the portal. The line below is very long!

cf cups mySqlServerService -p '{"connectionstring": "Server=tcp:cfnorthwind.database.windows.net,1433;Initial Catalog=Northwind;Persist Security Info=False;User ID=eric@cfnorthwind;Password=4Pjby8$wTk;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"}'
cf bind-service core mySqlServerService

In stratos the service is visible now

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 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 SqlConnection and inject it into the IoC container. With a Steeltoe extension and the microsoft Options extension:

using System.Data.SqlClient;
using Microsoft.Extensions.Options;
using Steeltoe.Extensions.Configuration.CloudFoundry;

// Class with same property as provided in the cf cups statement
public class SqlServerInfo {
   public string Connectionstring { get; set; }
}
// Class that wraps the mySqlServerService in the cf cups statement
// Property is always called Credentials (look at the environment variables)
public class MySqlServiceOptions : AbstractServiceOptions {
   public SqlServerInfo Credentials { get; set; }
}

public void ConfigureServices(IServiceCollection services)
{
   services.AddMvc();
   // link the mySqlServerService from configuration to the class defined above
   services.ConfigureCloudFoundryService<MySqlServiceOptions>(Configuration, "mySqlServerService");
   services.AddScoped<SqlConnection>(x => {
      // retrieve the user provided service from the configuration
      var monitor = x.GetRequiredService<IOptionsMonitor<MySqlServiceOptions>>();
      var config = monitor.Get("mySqlServerService");
      return new SqlConnection(config.Credentials.Connectionstring);
   });
}

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

using System.Data.SqlClient;
using System.Data;

public IActionResult Contact([FromServices] SqlConnection dbConnection)
{
    var cmd = dbConnection.CreateCommand();
    cmd.CommandText = "SELECT 'hello world from sqlserver'";
    dbConnection.Open();
    ViewData["Message"] = "Your contact page. " + cmd.ExecuteScalar().ToString() ;
    dbConnection.Close();

    return View();
}

The code changes are done. Now cf push the application.
After the app is restarted the Contact page shows us “hello world from sqlserver”.

Tip

Since the Sql Server is hosted outside of Cloud Foundry, you can reference it while debugging. Just add the connection information to the appsettings.json and load it in the program.cs.

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "vcap:services" : {
    "user-provided": [
      {
        "name": "mySqlServerService",
        "instance_name": "mySqlServerService",
        "binding_name": null,
        "credentials": {
          "connectionstring": "Server=tcp:cfnorthwind.database.windows.net,1433;Initial Catalog=Northwind;Persist Security Info=False;User ID=eric@cfnorthwind;Password=4Pjby8$wTk;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
        },
        "syslog_drain_url": "",
        "volume_mounts": [],
        "label": "user-provided",
        "tags": []
      }
    ]
  }
}

public static IWebHost BuildWebHost(string[] args) =>
  WebHost.CreateDefaultBuilder(args)
         .ConfigureAppConfiguration((builderContext, configBuilder) =>
         {
            // will be overwritten in Cloud Foundry
            configBuilder.AddJsonFile("appsettings.json");
         })
         .AddCloudFoundry()
         .UseStartup<Startup>()
         .Build();

References

https://docs.cloudfoundry.org/devguide/services/user-provided.html
https://github.com/SteeltoeOSS/Configuration/issues/40

About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.