.NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App

by Raman YurkinFebruary 4, 2016

Learn how to configure a MS SQL DB service and bind it to a .NET app on a private Pivotal CF using the commands also viable for Pivotal Web Services.

Table of Contents

Deploying a service broker

.NET Applications on Pivotal CF: Binding MySQL

In the previous post, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational—it still doesn’t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect it to a .NET app running on PCF.

We will be using a private PaaS deployment, but the same commands will work for Pivotal Web Services (PWS). You can find the full versions of files used in the examples below in this GitHub repo.

MS SQL is an external service that can be added to the PaaS via a service broker, which is not available out-of-the-box. Fortunately, it is trivial to install one. The broker is a Java app, and we can simply push it to the Pivotal CF instance. There are other ways to add it, too. You can find a detailed guide in this GitHub repository.

Once the broker has been installed, we can go ahead and add the MS SQL service to the demo .NET app.

Creating a service

First, we need to check if MS SQL is available in the “marketplace” of the PCF instance and what plans it has. If you run the same commands on PWS, it will display publicly available services with corresponding pricing.

     > cf marketplace
     Getting services from marketplace in org Altoros / space dev as {UserName}...
     OK
 
     service 	plans     description
     mssql-dev   default   Microsoft SQL Server service for application development and testing
 
     TIP:  Use 'cf marketplace -s SERVICE' to view descriptions of individual plans of a given service.
     > cf marketplace -s mssql-dev
     Getting service plan information for service mssql-dev as {UserName}...
     OK
 
     service plan   description     	free or paid
     default    	Shared SQL Server   free
     >

Judging by the output, mssql-dev is available with the default plan. There is no need to specify any additional parameters for this service at this moment. We only need the service name, the service plan, and a name for the instance (let’s use samplewebappSQL).

     > cf create-service mssql-dev default samplewebappSQL
     Creating service instance samplewebappSQL in org Altoros / space dev as {UserName}...
     OK
     >

Now let’s check that the service has been enabled successfully.

     > cf services
     Getting services in org Altoros / space dev as {UserName}...
     OK
 
     name          	service 	plan      bound apps   last operation
     samplewebappSQL   mssql-dev   default            	create succeeded
     >

Looks like it’s working.

Modifying the .NET app

After this step, we can bind the MS SQL service to the demo app, but we would like to do some modifications to the app first.

Parameters of bound services are available to apps via environment variables. Right now, we do not know which parameters will be provided, so we add an additional view and a related controller method (action) to the demo app. To do this, we need to modify the HomeController.cs and Environment.cshtml files. The examples below are the parts that need to be changed in:

HomeController.cs:

public ActionResult Environment()
    	{
        	ViewBag.Message = "Your environment page.";
 
        	return View();
    	}

Environment.cshtml:

@using System.Collections
 
@{
	ViewBag.Title = "title";
	IDictionary environmentVariables = Environment.GetEnvironmentVariables();
}
 
<h2>Environment Variables</h2>
<div class="environment">
	@foreach (DictionaryEntry entry in environmentVariables)
	{
    	<div class="item">
        	<div class="key">@entry.Key</div>
        	<div class="value">@entry.Value</div>
    	</div>
	}
 
</div>

Then, we add a new ActionLink to _Layout.cshtml:

<div class="navbar-collapse collapse">
            	<ul class="nav navbar-nav">
                	<li>@Html.ActionLink("Home", "Index", "Home")</li>
 
                	<li>@Html.ActionLink("Environment", "Environment", "Home")</li>
 
                	<li>@Html.ActionLink("About", "About", "Home")</li>
                	<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
            	</ul>
            	@Html.Partial("_LoginPartial")
        </div>

Now, we can publish the demo application to PCF, bind it to the MS SQL service, and view what environment variables are provided to us.

Publishing the .NET app

We push SampleWebApp to the Published folder (as described in the previous post), using the same set of commands:

     > cf push SampleWebApp -p .\ -s windows2012R2 --no-start -b https://github.com/ryandotsmith/null-buildpack.git
     Using stack windows2012R2...
     OK
     Creating app SampleWebApp in org Altoros / space dev as {UserName}...
     OK
 
     Using route samplewebapp.cf-dev.altoros.com
     Binding samplewebapp.cf-dev.altoros.com to SampleWebApp...
     OK
 
     Uploading SampleWebApp...
     Uploading app files from: .\
     Uploading 1.1M, 76 files
     Done uploading
     OK
 
     > cf enable-diego samplewebapp
     Setting samplewebapp Deigo support to true
     ←[1;32mOk
     ←[0m
     Verifying samplewebapp Deigo support is set to true
     ←[1;32mOk
     ←[0m
     >

Binding the MS SQL service to the .NET app

Then, we bind the MS SQL service instance, samplewebappSQL, to the demo app:

     > cf bind-service SampleWebApp samplewebappSQL
     Binding service samplewebappSQL to app SampleWebApp in org Altoros / space dev as {UserName}...
     OK
     TIP: Use 'cf.exe restage SampleWebApp' to ensure your env variable changes take effect
     >

Please keep in mind that not all CF commands are case-insensitive. For example, application-related commands recognize samplewebapp and SampleWebApp as the same application, but the service-related set of commands is case-sensitive, so you cannot use SampleWebAppSQL instead of samplewebappSQL.

At this point, we can start our demo .NET app:

> cf start SampleWebApp
Starting app SampleWebApp in org Altoros / space dev as {UserName}...
Creating container
Successfully created container
Downloading app package...
Downloaded app package (7.8M)
Downloading buildpacks (https://github.com/ryandotsmith/null-buildpack.git)...
Downloaded buildpacks
Staging...
Exit status 0
Staging complete
Uploading droplet, build artifacts cache...
Uploading build artifacts cache...
Uploading droplet...
Uploaded droplet (7.8M)
Uploading complete

0 of 1 instances running, 1 starting
0 of 1 instances running, 1 starting
1 of 1 instances running

App started

App SampleWebApp was started using this command `..\tmp\lifecycle\WebAppServer.exe`

Showing health and status for app SampleWebApp in org Altoros / space dev as {UserName}...
OK

requested state: started
instances: 1/1
usage: 1G x 1 instances
urls: samplewebapp.cf-dev.altoros.com
last uploaded: Fri Jan 15 06:23:23 UTC 2016
stack: windows2012R2
buildpack: https://github.com/ryandotsmith/null-buildpack.git

state since cpu memory disk details
#0 running 2016-01-15 09:33:04 AM 0.4% 296.4M of 1G 46.8M of 1G
>

Getting credentials

Open a new environment page and check the list of variables like it’s shown in the picture below.

.NET Applications on Pivotal CF: Binding MySQL

We need the VCAP_SERVICES variable that contains all the information we need in the JSON format.

VCAP_SERVICES
{"mssql-dev":[{"name":"samplewebappSQL","label":"mssql-dev","tags":["mssql","relational"],"plan":"default","credentials":{"hostname":"10.92.0.109","host":"10.92.0.109","port":1433,"name":"cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8","username":"cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7","password":"EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0","connectionString":"Address=10.92.0.109,1433;Database=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8;UID=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7;PWD=EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0;"}}]}

The credentials in this quote were automatically generated during service binding.

Connecting the app to the MS SQL service

We could just replace the default connection string in the web.config file, but we prefer this connection string to change dynamically during application startup. The examples below are the parts that we have changed in:

Web.config:

<appSettings>
	…
	<add key="DBServiceName" value="mssql-dev"/>
  </appSettings>

Startup.cs:

public void Configuration(IAppBuilder app)
    	{
        	ConfigureAuth(app);
        	ConfigureDB(app);
    	}
 
    	/// <summary>
    	/// Get connection string from environment variables and replace it in web.config
    	/// </summary>
    	/// <param name="app"></param>
    	private void ConfigureDB(IAppBuilder app)
    	{
        	string dbServiceName = ConfigurationManager.AppSettings["DBServiceName"];
        	string vcapServices = System.Environment.GetEnvironmentVariable("VCAP_SERVICES");
 
        	// if we are in the cloud and DB service was bound successfully...
        	if (vcapServices != null)
        	{
            	dynamic json = JsonConvert.DeserializeObject(vcapServices);
            	foreach (dynamic obj in json.Children())
            	{
                	if (((string)obj.Name).ToLowerInvariant().Contains(dbServiceName))
                	{
                    	dynamic credentials = (((JProperty)obj).Value[0] as dynamic).credentials;
 
                    	// replace connection string
                    	var settings = ConfigurationManager.ConnectionStrings["DefaultConnection"];
                        var fi = typeof(ConfigurationElement).GetField(
                                  	"_bReadOnly",
                                  	BindingFlags.Instance | BindingFlags.NonPublic);
                    	fi?.SetValue(settings, false);
                        settings.ConnectionString = credentials?.connectionString;
 
                    	break;
                	}
            	}
        	}
    	}

Repeat the publishing and deployment procedures, then start the application and test the connection to MS SQL by registering a new user. It works!

Cloud Foundry Services: Binding MySQL to .NET App

See this GitHub repo for the full versions of example files used in this tutorial.

We hope this post has helped you to get started with adding services to .NET apps on PCF. In the next tutorial, we will talk about scaling .NET applications to multiple instances.