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

Deploying a service broker

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.

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).

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

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:

Binding the MS SQL service to the .NET app

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

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:

Getting credentials

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

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!

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.

Further reaading

• .NET on Pivotal CF: Connecting and Pushing an App
• How to Set Up a Cloud Foundry Infrastructure for .NET Apps in Minutes
• .NET on Pivotal Cloud Foundry: Scaling an App on Diego