Keep Nuget Packages Out of Source Control with Nuget Package Manager Restore

Posted on November 10 2013 07:33 AM by jatten in C#, Tools, CodeProject, ASP.Net, ASP.NET MVC   ||   Comments (0)

NuGet_project_logo.svgNuget is cool.

What is not cool is when you push a project to source control and include a ton of installed packages, bulking up your repo with stuff that could be easily pulled in via Nuget by anyone cloning your project. However, what was equally irritating was having to include instructions for what packages need to be installed, if they were excluded from the source repo.

Enter Nuget Package Restore.

 

Allow Visual Studio to Restore Nuget Packages During Build

Before you can avail yourself of Nuget Package Restore, we need to explicitly allow this behavior on our machine. This is required on a per-machine basis before Nuget can restore packages for a solution automagically, and can be set in Tools => Options => Package Manager:

Visual Studio Options Menu:

vs-tools-options-menu

Package Manager Settings Dialog:

package-manager-settings

Now that our machine has been told it is ok to restore Nuget Packages automatically, we need to enable this behavior in our specific solution as well.

Enable Nuget Package Restore for the Solution

To enable Nuget Package Restore in our specific solution, right-click on Solution Explorer => Your Solution => Enable Package Restore:

Enabling Nuget Package Restore for a Specific VS Solution:

enable-package-restore-in-solution

This may take a few seconds. When VS is done processing, an examination of Solution Explorer reveals a new folder at the root solution level:

Files Added to Solution by Nuget Package Restore:nuget-package-restore-files-added

 

Notice the NuGet.exe file in there? This file is required when, for example, someone clones your project from source and attempts to build with Nuget Package Restore enabled. Therefore, it needs to be committed to source control.

We also now want to exclude all the package files from source by editing our .gitignore file (I am assuming Git as the source control provider, but the principle here applies to whichever provider you are using – you need to tell your version control system what to include in the repository and what to keep out).

Edit .gitignore to Exclude Packages and Include NuGet.exe

A typical ASP.NET MVC 5 project created from the standard VS 2013 template will contain (as of this writing) 161 files from various Nuget packages included as part of the default solution. This is some significant bulk. Using the default .gitignore file (or any of the most common in use for Visual Studio/C# projects) the total number of files which will tend to be included as part of the project repo numbers over 200. Note this is after excluding all the binaries and VS housekeeping files. In other words, nearly 75% of the common files in a VS 2013 ASP.NET MVC 5 solution consist of Nuget packages, which can now be restored automatically instead of pushed up to our repo.

To cut down the bulk, lets modify our .gitignore as follows. Note, your .gitignore (or .hgignore, or .tfsignore, what have you) will most likely look a little different than mine. That's okay, because we're only going to do a few small tweaks to the file – keep the rest the same.

Ignore Packages Folder

Open your .gitignore file and add the following line to exclude the packages folder from source:

Ignore Packages Folder in .gitignore:
packages*/

 

Make an Exception to Include NuGet.exe

Most likely, your .gitignore file already excludes files with the .exe extension. We may want to make an exception for Nuget.exe, since it will be needed to restore packages by anyone cloning our repo:

Look through your .gitignore until you find the following:

*.exe

 

Then add this right AFTER it (the order here is important):

*.exe
!NuGet.exe

 

The above tells Git to ignore .exe files, but to make an exception for NuGet.exe.

The step above is optional, since someone cloning your solution could simply Enable Nuget Package Restore for the solution, at which point the NuGet.exe would be installed. However, this saves the step.

Inform Potential Consumers of Your Code

Now, we no longer need to include all the installed Nuget package files in our source repo. However, we should probably add a blurb to the README.txt file (or otherwise inform consumers of our code) letting users know that they will need to perform the VS configuration needed to allow Nuget to restore packages as described in the first step in this article.

Additional Resources and Items of Interest

 

Posted on November 10 2013 07:33 AM by jatten     

Comments (0)

Configuring Db Connection and Code-First Migration for Identity Accounts in ASP.NET MVC 5 and Visual Studio 2013

Posted on October 27 2013 02:42 PM by jatten in ASP.NET MVC, ASP.Net, C#, CodeProject, Database, Web   ||   Comments (6)

Tree-320The recent release of Visual Studio 2013 and ASP.NET MVC 5 brought significant changes to the Accounts management system (formerly ASP.NET Forms Membership). Also, there have been some minor changes in how we manage Entity Framework Code-First Migrations.

In this post we will review setting up the basic Identity accounts, pointing them at an external SQL Server (or whatever other database you choose) instance instead of the default (local) SQL Server CE or SQL Express database in App_Data and configuring Entity Framework Migrations so seed the database with initial data.

Image by Wonderlane | Some Rights Reserved

The Basic Components of the ASP.NET Identity System

Out of the box, when you create an ASP.NET MVC 5 project using the default template in Visual Studio 2013, your get a basic, ready-to-run website with the elementary Identity and Account management classes already in place. In the current configuration, the default action is that, when you run the application fro the first time and register as a user, the database will be created as a SQL Server CE (.sfd) or SQL Express (.mdf) file in the App_Data folder in your project.

The Identity Account classes in the Default MVC 5 Project Solution Explorer:

solution-explorer-identity-classes

 

In the above, the IdentityModel.cs file is where the essential Identity system components are defined. Opening that file in the code editor, we see two classes defined:

Code in the IdentityModel.cs Code File:
using Microsoft.AspNet.Identity.EntityFramework;
namespace DbMigrationExample.Models
{
    public class ApplicationUser : IdentityUser
    {
    }
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }
    }
}

 

The ApplicationUser class, which inherits from a framework class IdentityUser. This is the basic identity unit for managing individual accounts in the ASP.NET MVC 5 Account system. This class is empty as defined in the default project code, and so brings with it only those properties exposed by the base class IdentityUser. However, we can extend the ApplicationUser class by adding our own properties, which will then be reflected in the generated database. More on this in a moment.

We also find here the class ApplicationDbContext. This is the Entity Framework context used to manage interaction between our application and the database where our Account data is persisted (which may, or may not be the same database that will be used by the rest of our application). Important to note that this class inherits not from DBContext (as is the usual case with EF), but instead from IdentityDbContext. In other words, ApplicationDbContext inherits from a pre-defined DB context defined as part of Microsoft.AspNet.Identity.EntityFramework which contains the "Code-First" base classes for the Identity system.

Between these two classes, the MVC framework has provided the basics for generating and consuming the complete Identity Account database. However, the basic classes are extensible, so we can tailor things to suit our needs.

Lastly, note the AccountViewModels.cs file. Here are defined the ViewModels which are actually used by the Views in our application, such that only that information needed to populate a view and perform whatever actions need to be performed is exposed to the public-facing web. View Models are not only an effective design component from an architectural standpoint, but also prevent exposing more data than necessary.

Configuring the Database Connection

As mentioned previously, the default MVC project will create a SQL CE or SQL Express in the project's App_Data folder unless we tell it to do otherwise. All we really need to do is change the connection string used by the ApplicationDbContext, and point it at our target database.

The ApplicationDbContext class passes a connection string name to the base class in the constructor, namely "DefaultConnection." If we open the Web.config file, we find that under the <connectionStrings> element there is a single node, in which the "DefaultConnection" string is added to the connection strings collection:

The Default Connection String in Web.config:
  <connectionStrings>
    <add name="DefaultConnection" 
    connectionString="Data Source=(LocalDb)\v110;
    AttachDbFilename=|DataDirectory|
    \aspnet-DbMigrationExample-20131027114355.mdf;
    Initial Catalog=aspnet-DbMigrationExample-20131027114355;
    Integrated Security=True"
    providerName="System.Data.SqlClient" />
  </connectionStrings>

 

The easiest way to change our target database is to simply change the connection string details for "DefaultConnection" in Web.config. In this case, we will swap out the SQL CE connection for a SQL Server development database I have running on my development machine (obviously, you can point this at any database you wish, or simply leave it set to use the local SQL CE database):

Pointing "DefaultConnection" at a Local SQL Server Instance:
  <connectionStrings>
    <add name="DefaultConnection" 
    connectionString="Data Source=XIVMAIN\XIVSQL;
    Initial Catalog=DbMigrationExample;Integrated Security=True"
    providerName="System.Data.SqlClient" />
  </connectionStrings>

 

Now, if I run the application, I am greeted with the default home screen offered by the VS 2013 MVC project template:

The Home Page of the Default MVC Project Template:

default-home-page-aspnet-mvc-5-before-register

From here, I click on the "register" link upper right:

The Registration Page of the Default MVC Project Template:

default-home-page-aspnet-mvc-5-register

When I complete registration and hit the "Register" button, I am redirected back to the home page. However, we can see now, in the upper right corner, that I am, in fact, signed in as a registered user:

default-home-page-aspnet-mvc-5-after-register

None of this is surprising. The reason we just went through that little exercise was to see the resulting database created once we registered. If I open SQL Server Management Studio (SSMS), I should find a new Database named DbMigrationExample:

The New SQL Server Database Viewed in SSMS:

SSMS-new-database

Note the tables created here. Despite the fact that only ApplicationUser is defined in our application, all of the above are created as a result of the IdentityDbContext class from which ApplicationDbContext inherits.

The default project configuration only actually makes use of the data from dbo.AspNetUsers. However, you can see that a full range of identity management tables have been created, including those for role management, and external authorization (using Google/Facebook/Twitter accounts).

Configuring Entity Framework Migrations and Seeding the Database

As we develop, we may make changes to our classes which need to be reflected in the database. Also, quite often we would like to redeploy our development database either in a fresh state, or possibly with some initial data (or Seed data) present. As with previous version of Entity Framework and MVC, we can use EF Code First Migrations to do this.

Before proceeding, I am going to delete the SQL Server database created when I registered on the site and start fresh. If you did something similar, do the same.

In order to start using Migrations with your project, go to the tools menu in Visual Studio, and select Library Package Manager => Package Manager Console. When the console opens at the bottom of the screen, we simply type:

PM> Enable-Migrations –EnableAutomaticMigrations

 

Once we hit enter, the console will be busy for a moment as the Migrations folder is configured in our project. When the task is complete, our console window should look like this:

Console After Enable-Migrations Command:

console-enable-migrations

 

Seeding the Database with an Initial User Records

For various reasons we may want to deploy our database with some initial records in place . We may need tables for certain static values pre-populated, or we might simply want some test data to work with in development. In our current case, we will deploy the database with a couple of User records pre-populated for testing purposes.

Once we have run the Enable-Migrations command as above, there should be a Migrations folder at the root of our project. If we open Configuration.cs file in that folder, we see this:

Default Code in Migrations Configuration Class:
namespace DbMigrationExample.Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;
  
    internal sealed class Configuration 
        : DbMigrationsConfiguration<DbMigrationExample.Models.ApplicationDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }
  
        protected override void Seed(
            DbMigrationExample.Models.ApplicationDbContext context)
        {
            //  This method will be called after migrating to the latest version.
  
            //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
            //  to avoid creating duplicate seed data. E.g.
            //
            //    context.People.AddOrUpdate(
            //      p => p.FullName,
            //      new Person { FullName = "Andrew Peters" },
            //      new Person { FullName = "Brice Lambson" },
            //      new Person { FullName = "Rowan Miller" }
            //    );
            //
        }
    }
}

 

 

We want to modify the Configuration class as follows so that our test user records are created whenever the Database is created. Note that we have added some using statements to the top of the file as well to being in the Microsoft.AspNet.Identity and Microsoft.AspNet.Identity.EntityFramework, namespaces, as well as the Models namespace from our own project:

Modified Code in Migrations Configuration Class:
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using DbMigrationExample.Models;
  
namespace DbMigrationExample.Migrations
{
    internal sealed class Configuration 
        : DbMigrationsConfiguration<DbMigrationExample.Models.ApplicationDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }
  
        protected override void Seed(ApplicationDbContext context)
        {
            var manager = new UserManager<ApplicationUser>(
                new UserStore<ApplicationUser>(
                    new ApplicationDbContext()));
  
            for (int i = 0; i < 4; i++)
            {
                var user = new ApplicationUser()
                {
                    UserName = string.Format("User{0}", i.ToString())
                };
                manager.Create(user, string.Format("Password{0}", i.ToString()));
            }
        }
    }
}

 

Now that we have added code to seed the user table, we run the following command from the console:

Enable-Migration Init Command:
Add-Migration Init

 

When the command is finished running (it can take a few seconds) our console window looks like this:

Console Output from Enable-Migration Init:

console-add-migration-init

The previous Add-Migration Init command created the scaffolding code necessary to create the database, but to this point. If we look in the Migrations folder now, we will see a couple new files added containing that code. However, nothing we have done to this point has created or modified an actual database. All the preceding has been set up.

Create/Update the Seeded Database using the Update-Database Command

With all that done, run the following command from the console:

Update-Database

 

When the command finishes, your console should look like this:

Console Output from Update-Database:

console-update-database

If all went well, we now see the database has been re-created with all the expected tables as before. Additionally, if we SELECT * FROM dbo.AspNetUsers we find that we now have four test users:

Query Result from dbo.AspNetUsers Table:

query-aspnetusers-table

Now that we have a basic migration strategy in place, let's take a look at extending the elementary ApplicationUser class to incorporate some additional data fields.

Extending the IdentityModel Class with Additional Properties

Under the ASP.NET new Identity Model, it is easier than before to extend the basic user representation to include arbitrary fields suitable for our application management. For example, Let's assume we would like our user information to include an email address, as well as full first and last names. We can add properties for these items to the ApplicationUser class, and then update the Controllers, ViewModels, and Views which rely on Application user for registration and such.

First, let's go back to the ApplicationUser class and add the properties we want:

using Microsoft.AspNet.Identity.EntityFramework;
// Add this to bring in Data Annotations:
using System.ComponentModel.DataAnnotations;
  
namespace DbMigrationExample.Models
{
    public class ApplicationUser : IdentityUser
    {
        [Required]
        public string FirstName { get; set; }
  
        [Required]
        public string LastName { get; set; }
  
        [Required]
        public string Email { get; set; }
    }
  
    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }
    }
}

In the above, we added our three new properties to the ApplicationUser class, and also added the [Required] attribute. To do so, we need to add System.ComponentModel.DataAnnotations to our using statements at the top of the class file.

Update the Register Method of the AccountController

We also need to update the Register method on the AccountController. Currently, the code creates an instance of ApplicationUser and sets only the UserName property:

Currently, Only the UserName Property is Set:
var user = new ApplicationUser() { UserName = model.UserName };

 

We need to add the following (the code following the comment) to make our controller work properly:

Register Method of AccountController Updated to Set the New User Properties:
[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Register(RegisterViewModel model)
{
    if (ModelState.IsValid)
    {
        // Add the following to populate the new user properties
        // from the ViewModel data:
        var user = new ApplicationUser() 
        { 
            UserName = model.UserName, 
            FirstName = model.FirstName,
            LastName = model.LastName,
            Email = model.Email
        };
        var result = await UserManager.CreateAsync(user, model.Password);
        if (result.Succeeded)
        {
            await SignInAsync(user, isPersistent: false);
            return RedirectToAction("Index", "Home");
        }
        else
        {
            AddErrors(result);
        }
    }
  
    // If we got this far, something failed, redisplay form
    return View(model);
}

 

Update the Register ViewModel

Now that we have added the new properties to our ApplicationUser class, we also need to provide a way for the user to input the values during the registration process. If we open the AccountViewModels.cs file, we see a number of ViewModel classes defined. At the bottom is the RegisterViewModel class. It currently looks like this:

The Default RegisterViewModel Class:
public class RegisterViewModel
{
    [Required]
    [Display(Name = "User name")]
    public string UserName { get; set; }
  
    [Required]
    [StringLength(100, ErrorMessage = 
        "The {0} must be at least {2} characters long.", MinimumLength = 6)]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    public string Password { get; set; }
  
    [DataType(DataType.Password)]
    [Display(Name = "Confirm password")]
    [Compare("Password", ErrorMessage = 
        "The password and confirmation password do not match.")]
    public string ConfirmPassword { get; set; }
}

 

We want to add our new properties, so we modify it as follows:

Modified RegisterViewModel Class:
public class RegisterViewModel
{
    [Required]
    [Display(Name = "User name")]
    public string UserName { get; set; }
  
    [Required]
    [StringLength(100, ErrorMessage = 
        "The {0} must be at least {2} characters long.", MinimumLength = 6)]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    public string Password { get; set; }
  
    [DataType(DataType.Password)]
    [Display(Name = "Confirm password")]
    [Compare("Password", ErrorMessage = 
        "The password and confirmation password do not match.")]
    public string ConfirmPassword { get; set; }
  
    [Required]
    [Display(Name = "First name")]
    public string FirstName { get; set; }
  
    [Required]
    [Display(Name = "Last name")]
    public string LastName { get; set; }
  
    [Required]
    [Display(Name = "Email")]
    public string Email { get; set; }
}

 

Update the Register View

Now that we have that taken care of, we need to also modify the Register.cshtml View to match. In the folder Views => Account open the Register.cshtml file. It should look like this:

The Default Register.cshtml File:
@model DbMigrationExample.Models.RegisterViewModel
@{
    ViewBag.Title = "Register";
}
  
<h2>@ViewBag.Title.</h2>
  
@using (Html.BeginForm("Register", "Account", 
    FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{
    @Html.AntiForgeryToken()
    <h4>Create a new account.</h4>
    <hr />
    @Html.ValidationSummary()
    <div class="form-group">
        @Html.LabelFor(m => m.UserName, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.UserName, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Password, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.PasswordFor(m => m.Password, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.ConfirmPassword, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.PasswordFor(m => m.ConfirmPassword, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" class="btn btn-default" value="Register" />
        </div>
    </div>
}
  
@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

 

Add the new properties after the existing form-group element for "ConfirmPassword" as follows:

Modified Register.cshml File:
<h2>@ViewBag.Title.</h2>
  
@using (Html.BeginForm("Register", "Account", 
    FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{
    @Html.AntiForgeryToken()
    <h4>Create a new account.</h4>
    <hr />
    @Html.ValidationSummary()
    <div class="form-group">
        @Html.LabelFor(m => m.UserName, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.UserName, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.Password, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.PasswordFor(m => m.Password, new { @class = "form-control" })
        </div>
    </div>
    <div class="form-group">
        @Html.LabelFor(m => m.ConfirmPassword, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.PasswordFor(m => m.ConfirmPassword, new { @class = "form-control" })
        </div>
    </div>
     
    // Add new properties here:
    <div class="form-group">
        @Html.LabelFor(m => m.FirstName, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.FirstName, new { @class = "form-control" })
        </div>
    </div>
    
    <div class="form-group">
        @Html.LabelFor(m => m.LastName, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.LastName, new { @class = "form-control" })
        </div>
    </div>
  
    <div class="form-group">
        @Html.LabelFor(m => m.Email, new { @class = "col-md-2 control-label" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.Email, new { @class = "form-control" })
        </div>
    </div>
    
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" class="btn btn-default" value="Register" />
        </div>
    </div>
}
  
@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

 

Updating the Database to Reflect Modified Entity Classes

So far, we have modified one of our Data model Entity classes – namely, the ApplicationUser class. In our application, EF is mapping this class to the dbo.AspNetUsers table in our backend. We need to run Migrations again to update things. Before we do that though, there is one more thing we need to do. Our seed method is no longer in sync with what our classes (and shortly, our back-end tables) require. We need to add values for the new FirstName, LastName, and Email properties to our user Seed data:

Updated Seed method:
protected override void Seed(ApplicationDbContext context)
{
    var manager = new UserManager<ApplicationUser>(
        new UserStore<ApplicationUser>(
            new ApplicationDbContext()));
  
    for (int i = 0; i < 4; i++)
    {
        var user = new ApplicationUser()
        {
            UserName = string.Format("User{0}", i.ToString()),
  
            // Add the following so our Seed data is complete:
            FirstName = string.Format("FirstName{0}", i.ToString()),
            LastName = string.Format("LastName{0}", i.ToString()),
            Email = string.Format("Email{0}@Example.com", i.ToString()),
        };
        manager.Create(user, string.Format("Password{0}", i.ToString()));
    }
}

 

Now, if we were to run the Update-Database command again, the changes to our entity objects will be reflected in the dbo.AspNetUsers table schema, but our seed data will not be updated, because Entity Framework doesn't like to do things which will cause data loss. While there are ways to make this happen, they are beyond the scope of this article. Instead, we will manually delete the database, and then run Update-Database again. However, since EF thinks that we have existing data from our previous migration, we have to use Update-Database -force.

Once we have deleted the database and run the Update-Database –force command, our console output should look like this:

Console Output Update-Database –force After Deleting Database:

console-update-database-force

Quickly re-running our query shows that indeed, the new fields have been added to our table, and the test data populated:

Query Result from dbo.AspNetUsers Table:

query-aspnetusers-table-updated

User Registration with Updated Registration Form

Now that we have updated our Registration.cshtml View, Controller method, ViewModel, and Database, when we run the application and go to register, we see the updated Registration form:

The Updated Registration Form:

site-registration-with-new-properties

Once we complete the form and hit the Register button, we are logged in successfully, wit our additional data persisted in the database, ready to be used in our application.

Logging in Using Seed Data

Alternatively, we can also log out, and log in as one of our test users by clicking the "Log in" link:

Logging in with Test Data:

log-in-as-test-user

Successful Log-in:

successfully-logged-in-as-test-user

Only the Beginning

The updates to ASP.NET MVC 5 are numerous, and cool. In this article I have kept it really basic, and only scratched the surface of the updated Account management possibilities. The ASP.NET and MVC team has made it easy to do things like add role-based identity management, as well as external log-ins from social networks such as Google +, Facebook, and Twitter.

In my next post, I will take a closer look at creating a role-based identity management model for administering a "closed" site.

Additional Resources and Items of Interest

 

Posted on October 27 2013 02:42 PM by jatten     

Comments (6)

ASP.NET MVC–Azure Websites and EF Model First Connection Strings when Deploying from Github

Posted on October 20 2013 08:46 PM by jatten in ASP.NET MVC, ASP.Net, C#, CodeProject, Web, Windows Azure, Database   ||   Comments (0)

guitar-strings-320One of the coolest things about Windows Azure Websites is the integration with source control, and the automated deployment hooks Azure offers. However, there are a number of small gotcha's related to deployment, chief among these is the protection of confidential configuration items such as database connection string.

In this post, we are going to look specifically at the vexing situation I ran into deploying from source control with an application using Entity Framework's "Model-First" strategy (everything here is pretty much applicable to "Database-First" as well, since in terms of the conceptual modeling of the database within the application, they are the same).

Image by Hammad Ali | Some Rights Reserved

NOTE: If you are an MSDN Subscriber, you can access free Azure benefits. If you are not an MSDN subscriber, you can still have up to 10 Windows Azure Websites for free. Additionally, anyone can take advantage of an introductory free 30 day trial to check out the rest of the Azure platform.

If you wish to take advantage of Windows Azure Websites Deployment from Source Control feature, and your application uses Entity Framework's Model-or-Database-First approach, there are some non-intuitive set-up concerns that need to be addressed before your deployment will work properly.


EF Model-First Connection String Meta-Data

When you use Entity Framework to either build a database based upon the model you create in the VS Entity Designer, or use EF to reverse-engineer a conceptual model from an existing database, EF creates a bunch of meta-data mappings used to manage the interaction of your application with the database.

Unlike the "Code-First" approach, in which much of the meta-data exists as annotations on your model classes which EF then uses to create and map the relations of your classes to the database, the Model-First paradigm leaves you with straight POCO ("Plain Old CLR Objects" or, alternately, "Plain Old Class Objects") classes.

In order for EF (and thus, your application) to work properly in a Model-First approach, it needs to know where all this meta-data lives, and this is evident in the connection string created by EF in your Web.config file:

EF Model First Connection Strings in Web.config:
<connectionStrings>
<add name="DefaultConnection" 
    connectionString="Data Source=(LocalDb)\v11.0;
    Initial Catalog=aspnet-AzureModelFirstExample-20131020060646;
    Integrated Security=SSPI;
    AttachDBFilename=|DataDirectory|\aspnet-AzureModelFirstExample-20131020060646.mdf" 
    providerName="System.Data.SqlClient" />
<add name="ExampleDbEntities" 
     connectionString="metadata=res://*/Models.ExampleEntities.csdl
     |res://*/Models.ExampleEntities.ssdl|res://*/Models.ExampleEntities.msl;
     provider=System.Data.SqlClient;
     provider connection string=&quot;data source=XIVMAIN\XIVSQL;
     initial catalog=ExampleDb;integrated security=True;
     MultipleActiveResultSets=True;App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />
</connectionStrings>

 

In the above, the first XML node in <connectionStrings> , named "DefaultConnection" is the connection string for the standard Forms Membership feature included out of the box with the default MVC project template.

The second node, for the connection string named "ExampleDbEntities" is what you get when you create an Entity Framework Model First implementation of the actual domain database your application will consume. All that metadata=res: stuff essentially tells EF where to find the meta data it needs to properly relate your generated model classes to database tables, and to keep track of the relationships between them.

In the example above, the membership database exists in the App_Data folder of the project. The ExampleEntities database is hosted on my local development server. As a result, both connections are able to use Windows Integrated Security, and no confidential connection information is persisted in the Web.config file. 

When deploying a website to Windows Azure Websites, there are any number of variations with respect to how the two-database application above might be implemented. You might create a separate membership database on your Azure account, as well as an application database, similar to the above.

More likely, you might decide to combine the two such that all of your application data is served by the same Azure SQL Database instance. For this article, we are going to look at the free Websites tier, which allows one free Azure SQL Database (<= 20 Mb) for use by all of your free websites. We will also assume that you must (for whatever reason) develop your application against an existing database schema, such that the Model/Database First approach is indicated.

Windows Azure Databases, Website Deployment, and Connection Strings

In the configuration section of the Azure Website Management Portal, there is a section specifically for setting up connection strings. Azure connection strings are key/value pairs where the NAME field (the Key) is the name of the connection string as defined in our Web.config file, and the VALUE represents the actual connection string to be used to connect to our Azure SQL Database instance.

Connection String Management in the Windows Azure Websites Portal

azure-configuration-connection-strings1

The above is from the Configuration portal for an example I set up. I have an existing Azure SQL Database set up, and when I created the example site, the DefaultConnection string called out in red was associated with that database.

When we deploy our site from source control, Azure maps the connection string names defined in our Web.config file to the correspondingly-named connection strings defined here. This preserves the privacy of our confidential Azure database connection information, since it does not need to be committed to our source control repo.

Recall from the previous section that our Membership database on our local machine also uses a connection string named "DefaultConnection." Since for our example we are presuming that in deployment the Membership tables actually reside in the same database as the rest of our application data, the connection string above is appropriate. Of course, we need to make sure to migrate our Membership tables into this database (or assume they are already there).

In any case, this connection string should point to wherever our membership tables live. As it sits currently, the DefaultConnection is a standard Azure SQL Database connection. If we click on the "Show Connection Strings" link, we see that with a few small exceptions, the Azure connection string closely resembles a standard SQL Server connection string:

Default Azure Connection String for a Linked Azure SQL Database
Data Source=tcp:yourAzureServerName.database.windows.net,1433;
    Initial Catalog=ModelFirstExampleDb;
    User ID=yourDbUserName@yourAzureServerName;
    Password=YourPassword

 

This should work properly with the membership functions of our application out of the box, so long as the Membership data resides in the target database to which this connection string points.

Now we come to the issue of our application database. Since our Entity Framework models expect a Model-First implementation, they will be looking for that funky, meta-data-laden second connection string we saw in our Web.config file.

The Model-First Connection String Pattern for Windows Azure

In order for Windows Azure to work with our EF Model-First Deployment, we need to set up the second connection string very specifically. First off, we need to name it to match the name defined for our application data connection string in Web.config. Second, we need to use the following pattern, and carefully substitute all of the <angleBracketed> values with our proper values (the angle brackets are not part of the connection string – remove those when putting your own values in):

The Model-First Connection String for Windows Azure SQL Database:
metadata=res://*/Models.<EFModel>.csdl|res://*/Models.<EFModel>.ssdl|res://*/Models.<EFModel>.msl;
provider=System.Data.SqlClient;
provider connection string=
"data source=tcp:<AzureServer>.database.windows.net;
initial catalog=<AzureDatabaseName>;
Persist Security Info=True;
User ID=<UserLogIn>@<AzureServer>;
Password=<UserPassword>"

 

Obviously, this should actually be a single line, but I have condensed it here as much as was reasonable for readability. You can find the unmodified template at my Github repo.

  • The value for <EFModel> is the name of the Entity Framework Model in your project
  • The value for <AzureDatabaseName> is obviously, the name of your deployed Azure SQL database.
  • When you initially set up your SQL Database, you will have created the values for <UserLogIn> and <UserPassword> . Replace those bracketed items with the values you created with the database.
  • To find the value to use for <AzureServer> , navigate to your Azure SQL Database management area in the Azure Portal SQL Databases => Database Name => Dashboard. Replace <AzureServer> with the value from your own configuration called out (but redacted) in red below:
Find the Value for <AzureServer> in SQL Database Dashboard:

locate-azure-server-name

Filling in the Example Azure Connection String Settings

So, that all seems like a lot to have read just to get to this, but here goes . . . Your DefaultConnection is already defined, and points directly at your Azure SQL Database. Assuming your ASP.NET Membership tables are all defined in your one application database, all we need to do is paste in our EF Model-First connection string, once we have replaced the placeholders with the values for our own SQL Database, and configure the rest of the connection string (again, it is important that the NAME value here match the name defined for this connection in Web.config).

In the case of our initial example from the beginning of this post:

  • The NAME is ExampleDbEntities
  • The Connection String for the example at the beginning of the post is (Remember this actually needs to be all in a single line, not formatted with line returns like this)::
metadata=res://*/Models.ExampleEntities.csdl|res://*/Models.ExampleEntities.ssdl|res://*/Models.ExampleEntities.msl;
provider=System.Data.SqlClient;
provider connection string=
"data source=tcp:MyAzureServer.database.windows.net;
initial catalog=ModelFirstExampleDb;
Persist Security Info=True;
User ID=MyUserLogin@MyAzureServer;
Password=MySuperSecretPassword"

 

Lastly, there is a drop-down selector for the provider type. The default is SQL Databases, but for Entity Framework, we want to select the "Custom" option.

If we were configuring the example from the beginning of this post, the configuration settings on the Azure portal would look something like this:

configure-azure-settings-values

Once you have entered all these values, Click the Save icon at the bottom of the window. \

Troubleshooting

There are a lot of places for this to go just slightly wrong, thus borking your connection. For example, if you are developing against a local development database, and it is named slightly different than your Azure SQL Database, your will get an exception if this is not correctly addressed in the Azure Connection string. Also, there are simply a lot of places where a typographical error might creep in when replacing the values in the connection string template with your actual values.

Another area to keep an eye on is that your production database on Azure is identical to your development database. While we are working under the premise that the database is driving our development, it is possible that changes may be introduced to one and not the other.

Unfortunately, when we work in the Model-First approach, we don't get the easy database creation during deployment. Therefore, it is important to keep the database schema's in sync.

Windows Azure offers a number of helpful features in this regard. I will take a closer look at the overall building out data-backed websites on Windows Azure in an upcoming post.

Additional Resources and Items of Interest

 

Posted on October 20 2013 08:46 PM by jatten     

Comments (0)

About the author

My name is John Atten, and my username on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, Java, SQL Server 2012, learning ASP.NET MVC, html 5/CSS/Javascript. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

jatten@typecastexception.com

Web Hosting by