how to connect my asp.net core in bluemix to Mysql to migrate the database

Question

I created an ASP.NET Core project in IBM Bluemix and added a connection to a Compose for MySQL MySQL database

I cloned the project from the git repository on hub.jazz.com in Visual Studio 2015 and I want to generate the database from the context that I create but I can't connect to the database.

 using HunterViews.Domain.Entities;
 using System.Collections.Generic;
 using Microsoft.EntityFrameworkCore;
 using Microsoft.Extensions.Options;

 namespace HunterViews.Data
 {
   public class HunterViewsContext : DbContext 
   {
      public HunterViewsContext(DbContextOptions<HunterViewsContext> options) : base(options) 
      {
      }

      public HunterViewsContext()
      {
      }

      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=HunterViewsCore;Trusted_Connection=true;");
      }

      public DbSet<User> users { get; set; }
      public DbSet<JobSeeker> jobseekers { get; set; }
      public DbSet<HeadHunter> headHunters { get; set; }
      public DbSet<Offer> offers { get; set; }
      public DbSet<Post> posts { get; set; }
      public DbSet<Skill> skills { get; set; }
      public DbSet<Reclamation> reclamations { get; set; }
      public DbSet<Evaluation> evaluations { get; set; }
      public DbSet<Formation> formations { get; set; }
      public DbSet<Notification> notifications { get; set; }
      public DbSet<Certification> certifications { get; set; }
    }
}        

So, I want to change optionsBuilder : optionsBuilder.UseSqlServer(@"Server(localdb)\mssqllocaldb;Database=HunterViewsCore;Trusted_Connection=true;");

to use the MySQL database I've created on Bluemix to generate my DB. How can I do this?

Answer 1

To use MySQL instead of SQL Server, you'll need to include one of the MySQL providers for Entity Framework Core in your project. In Visual Studio 2015, this means modifying your project.json file to include the dependency. Which dependency you add depends on which provider you choose to use. For example, if you choose to use SapientGuardian, you would add SapientGuardian.EntityFrameworkCore.MySql to your project.json's dependencies section like so:

"dependencies": {
  "SapientGuardian.EntityFrameworkCore.MySql": "7.1.19"
}

The standard convention for ASP.NET Core is to do configuration in the Startup class, but you can also configure the database connection string in your DbContext as you're doing in the example that you posted. To configure in the Startup class (assuming your connection string is stored in a string variable named connectionString, modify the line where you add your DbContext to this:

app.AddDbContext<HunterViewsContext>(options => options.UseMySQL(connectionString));

By doing that you will no longer need to override the OnConfiguring method in your DbContext. But if you would like to use OnConfiguring in the DbContext instead, you can simply call optionsBuilder.UseMySQL(connectionString) there instead.

Now here's where it gets a bit tricky. Since the Compose for MySQL services use self-signed SSL certificates which are provided as a Base64-encoded string representing the PEM format certificate in the VCAP_SERVICES environment variable, you'll need to convert that certificate to PFX format before you can use it with the MySQL provider.

You have two options for doing this conversion. The first option is to use some external tool to convert the certificate to PFX format and push that file with your application. The other solution is to convert the certificate on the fly at application startup when you configure the database connection.

You can use the BouncyCastle NuGet package to do the conversion on the fly like so:

private static void CreatePfxFromPemCertificate(string base64encodedPem, string pfxFilePath, string pfxPassword)
    {
        // get the PEM certificate, then convert to pfx format
        byte[] bytes = Convert.FromBase64String(base64encodedPem);
        Pkcs12Store store = new Pkcs12StoreBuilder().Build();
        X509CertificateEntry[] chain = new X509CertificateEntry[1];

        object pemObject;
        using (var streamReader = new StreamReader(new MemoryStream(bytes)))
        {
            PemReader pemReader = new PemReader(streamReader);
            if ((pemObject = pemReader.ReadObject()) is X509Certificate)
            {
                chain[0] = new X509CertificateEntry((X509Certificate)pemObject);
            }
        }

        store.SetCertificateEntry(pfxFilePath, chain[0]);
        var certFile = File.Create(pfxFilePath);
        store.Save(certFile, pfxPassword.ToCharArray(), new SecureRandom());
        certFile.Flush();
        certFile.Dispose();
    }

This function will require these using statements:

using Org.BouncyCastle.OpenSsl;
using Org.BouncyCastle.Pkcs;
using Org.BouncyCastle.Security;
using Org.BouncyCastle.X509;

And this dependency in project.json:

"Portable.BouncyCastle-Signed": "1.7.0.2",

This function accepts 3 parameters:

1. The Base64-encoded string (provided in VCAP_SERVICES environment variable)
2. The path to the pfx file that will be written
3. The password that should be used to protect the pfx file

Now that we have a function which can convert the Base64-encoded certificate, it's time to put it all together and create the connection string from the VCAP_SERVICES environment variable in the Startup class.

First, in the constructor of the Startup class:

public Startup(IHostingEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("vcap-local.json", optional:true); // when running locally, store VCAP_SERVICES credentials in vcap-local.json

    Configuration = builder.Build();

    // try to get the VCAP_SERVICES environment variable (when running on Bluemix)
    string vcapServices = Environment.GetEnvironmentVariable("VCAP_SERVICES");
    if (vcapServices != null)
    {
        JObject json = JObject.Parse(vcapServices);
        var credentialsToken = json.SelectToken("compose-for-mysql")? // look for compose-for-mysql instance
                .FirstOrDefault()?                 // get first database instance
                .SelectToken("credentials");       // get the credentials
        // get the uri
        Configuration["compose-for-mysql:0:credentials:uri"] = credentialsToken?.SelectToken("uri");
        // get the base64 certificate
        Configuration["compose-for-mysql:0:credentials:ca_certificate_base64"] = credentialsToken?.SelectToken("ca_certificate_base64");
    }
}

This code will grab the database Uri from the VCAP_SERVICES environment variable, or a json file called vcap-local.json in your project directory if you're running locally (you can copy the credentials from the connections tab in the Bluemix UI).

To put this all together to create the database string in your ConfigureServices method in the Startup class:

public void ConfigureServices(IServiceCollection services)
{
    var databaseUri = Configuration["compose-for-mysql:0:credentials:uri"];
    var username = (databaseUri.Split('/')[2]).Split(':')[0];
    var password = (databaseUri.Split(':')[2]).Split('@')[0];
    var port = (databaseUri.Split(':')[3]).Split('/')[0];
    var hostname = (databaseUri.Split('@')[1]).Split(':')[0];
    var database = databaseUri.Split('/')[3];

    // create the connection string
    var connectionString = $"Server={hostname};Port={port};uid={username};pwd={password};Database={database};SSL Mode=Required;";

    // convert the Base64 encoded PEM SSL certificate to PFX format
    CreatePfxFromPemCertificate(config[$"compose-for-mysql:0:credentials:ca_certificate_base64"],
        "compose-for-mysql0.pfx", password);

    // add the ssl certificate to the connection string
    connectionString += "CertificateFile=compose-for-mysql0.pfx;";
    connectionString += $"CertificatePassword={password};";

    // add database context
    services.AddDbContext<HunterViewsContext>(options => options.UseMySQL(connectionString));

    // Add framework services.
    services.AddMvc();
}