2015 Ignite New Zealand demo prep: Step 6


  1. Intro
  2. VSO
  3. Docker
  4. xUnit
  5. Build
  6. Back end
  7. Selenium
  8. Docker
  9. Release Management
  10. Testing

In this post we will setup the back end.

While I was preparing my demo for New Zealand the only official ASP.NET Docker image was based on Mono.  The ADO.NET implementation in Mono does not support the required encryption to connect to SQL Azure databases.  As I write this there is a new image based on the Core CLR that might remove this limitation.  Nevertheless, this blog series describes the system demonstrated at Ignite New Zealand 2015 .  Therefore, we are going to do as I did then and create an ASP.NET 4 Web API to access our SQL Azure database from an Azure Web App using Entity Framework.  We are also going to use SQL Server Data Tools to version and deploy our database changes.

My demo made the assumption that we were working on a brown field project that had an existing database schema to which we had to code.  So we will create the database now. In addition to the database we are also going to create the Azure Web Apps that are going to host the Web API we are going to write to provide access to our database. When we created our Docker host using the Docker Tools in Visual Studio a resource group was created for each host. 

  1. Locate the Dev resource group
    1. Browse to the Azure Preview Portal
    2. Click Resource groups
    3. Select your Dev resource group
  2. Add a Web App + SQL
    1. Click Add Button
    2. Select Web App + SQL in New Resource blade
      1. You may have to search for the item.
    3. Click Create button on the Web App + SQL blade
    4. Click Configure required settings under Web App
    5. Enter a name
    6. Enter a AppService plan name
    7. Click OK
    8. Click Configure required settings under Database
    9. Click Create a new database
    10. Enter name
    11. Click Configure required settings under Server
    12. Enter a name
    13. Enter login
    14. Enter Password
    15. Select the same region as the Linux VM
    16. Click OK
    17. Click OK on New database blade
    18. Click Or select existing link under Create new resource group
    19. Click the resource group name
    20. Select your Dev Resource Group
      1. It should be the same resource group as your Linux Docker Host.
    21. Click Create

    You will need to repeat this process of adding a web app and database in each resource group you created to deploy your application to. 

    Once the server and database are created we are going to open the database in Visual Studio so we can add our table. 

  3. Add People Table
    1. Locate your SQL Database in the Preview Portal
    2. Click the Open in Visual… button at the top of the blade
    3. Click Configure your firewall link to add your client IP
    4. Enter the password you created on the Connect to Server dialog
    5. Click Connect
      1. Your SQL Database will appear in the SQL Server Object Explorer.
    6. Expand your database to view the Tables folder
    7. Right-click the Tables folder and select Add New Table…
    8. Add a firstname and lastname column as non-nullable nvarchars of length 50
    9. Down in the T-SQL section change the name of the table from “Table” to “People”
    10. Click the Update button
    11. Click the Update Database button on the Preview Database Updates dialog

    This will now represent our existing database.

    In the spirit of total transparency I had no luck getting my ASP.NET 5 projects to play nice with any of my other project types when trying to build using VSO. So my work around was to create two solutions. The one we have already created that holds only ASP.NET 5 projects and the one we will create in this post that holds everything else.  The nice thing about our new build system is that building multiple solutions is as easy as building one.

    So if you are following along close the ASP.NET 5 solution.  Because the ASP.NET 5 project adopts a convention of placing all projects in a src folder we are going to have to take some special steps to achieve our goal. The goal is to have the sln file for our second solution I the same folder as our ASP.NET 5 solution.  This is not a technical requirement I just wanted to follow the same convention for both of my solutions.  So what we are going to do is create an empty solution so we can control where each project we add gets created.

  4. Create empty solution
    1. From the File menu select New / Project
    2. Select Visual Studio Solutions
    3. Select a Blank Solution template
    4. Use the Browse… button and select the folder that has our original solution file
    5. Name our solution PeopleTrackerWebService
    6. Unchecked Add to source control check box
      1. The reason we do not want to add to source control yet is because we cannot uncheck the box for Create directory for solution which is not what we want. Once the solution is created we are going to move it before we add it to source control.
    7. Click OK
  5. Move solution to root folder
    1. From Solution Explorer right-click on the solution and select Open Folder in File Explorer
    2. Close Visual Studio
    3. Move the solution file up one folder level to the same folder as the sln for our first solution
      1. Once the solution is moved you can delete the folder that was created for the second solution.
    4. Double-click the new sln to open in Visual Studio

    Now that the solution is in the correct location we can add it to source control.

  6. Add solution to source control
    1. Right-click the solution in Solution Explorer and select Add Solution to Source Control…

    Now we can begin adding the projects that will define the backend of our demo.

    We are going to start from the bottom and work our way up.  So we are first going to add our SQL Server Database Project.

  7. Add SQL Server Database Project
    1. Right-click on the solution and select Add / New Project…
    2. From the Other Languages section select SQL Server
    3. Select the SQL Server Database Project template
    4. Enter PeopleTracker.Database for the name
    5. Click the Browse… button next to Location
    6. Select the src folder within the solution folder
    7. Click OK on the Add New Project dialog

    With our database project created will need to import our existing schema.

  8. Import Database Schema
    1. Right-click on the database project and select Import / Database…
    2. Click the New Connection… button
    3. Enter the fully qualified domain name for your Azure SQL Server for the Server name
      1. It should the name you entered when we create the server followed by .database.windows.net.
    4. Select the radio button for Use SQL Server Authentication
    5. Enter the user name and password you entered when create the SQL Server
    6. Select your database from the dropdown in the Connect to a database section
    7. Click OK
    8. Click Start
    9. Click Finish

    The final step is to change the Target platform for our database project. 

  9. Change Database Target platform to Azure SQL Database
    1. Right-click on the project and select properties
    2. Change the Target platform to Microsoft Azure SQL Database
      1. Note if you created a V12 database earlier select Microsoft Azure SQL Database V12 instead.

    With our database project in place we can add a simple class library project to hold our data access layer.  We are going to be using Entity Framework. However, we are not going to be using code first migrations .  I am a fan of code first migrations especially for green field development.  Yet, many of us are working on brown field projects with an existing database schema that we must support and maintain. And yes I am aware you can enable code first migrations with an existing database .  Nevertheless, we are going to be using a technique called code first with an existing database .

  10. Add class library project
    1. Right-click on the solution and select Add / New Project…
    2. From the Visual C# section select Windows
    3. Select the Class Library project template
    4. Enter PeopleTracker.DAL for the name
    5. Click the Browse… button next to Location
    6. Select the src folder within the solution folder
    7. Click OK

    Our data access layer project will be using Entity Framework so we will add that now.

  11. Add reference to Entity Framework
    1. Right-click on the class library project and select Manage Nuget Packages…
    2. Select Entity Framework and install it
  12. Add Person model class
    1. Right-click the Class1.cs file and select Rename
    2. Rename the file to Person.cs
    3. Click the Yes button the Microsoft Visual Studio dialog confirming that you want the class name changed as well
    4. Remove all the existing using statements from the top of the file
    5. Copy and paste this code into the namespace block replacing the existing code

      using System.ComponentModel.DataAnnotations;

      public partial class Person

      public int ID { get; set; }

      [Display(Name = "First Name")]
      public string FirstName { get; set; }

      [Display(Name = "Last Name")]
      public string LastName { get; set; }


  13. Add DbContext Class
    1. Right click on your class library project and select Add / New Class
    2. Enter Models.cs as the class name
    3. Remove all the existing using statements from the top of the file
    4. Copy and paste this code into the namespace block replacing the existing code

      using System.Data.Entity;

      public partial class Models : DbContext

      public Models() : base("name=Models")

      public virtual DbSet<Person> People { get; set; }

      protected override void OnModelCreating(DbModelBuilder modelBuilder)


    Now we can create our Web API to provide access to our database. 

  14. Add ASP.NET 4.5.2 Web API project
    1. Right-click on the solution and select Add / New Project…
    2. From the Visual C# section select Web
    3. Select the ASP.NET Web Application project template
    4. Enter PeopleTracker.NetService for the name
    5. Click the Browse… button next to Location
    6. Select the src folder within the solution folder
    7. Click OK
    8. Select Web API from the ASP.NET 4.5.2 Templates
    9. Check the Add unit tests check box
    10. Change the Authentication to No Authentication
      1. Click Change Authentication
      2. Select No Authentication
      3. Click OK
    11. Uncheck Host in the cloud check box
    12. Click OK

    Despite the fact we selected the src folder for our Web API project the test project was not created in the correct location. The demo will work as is but if you want to move the project you can follow these steps.

    1. Right-click PeopleTracker.NetService.Tests and select Remove
    2. Click OK
    3. Right-click on solution and select Open Folder in File Explorer
    4. Drag the PeopleTracker.NetService.Tests folder into src
    5. Close File Explorer
    6. Right-click on solution and select Add / Existing Project...
    7. Browse into the PeopleTracker.NetService.Tests folder
    8. Double-click PeopleTracker.NetService.Tests

    Once the project is added to the solution build the entire solution to make sure everything is compiling.

  15. Add reference to class library to Web API project
    1. Right click on the Web API project and select Add / Reference…
    2. Select Solution
    3. Check the box for your class library project
    4. Click OK

    Before we can add a controller for our person model we need to add a connection string to the web.config of our Web API project.

  16. Add connection string
    1. Open Web.config
    2. Right after the appSettings section of your web.config copy and paste the text below.

      <add name="Models" connectionString=" Server=tcp:[yourServerName].database.windows.net,1433;Database=[yourDBName];User ID=[yourUsername]@[yourServerName];Password=[yourPassword];Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient"/>

    3. Replace all the values in square brackets with the values for your deployment
    4. Save and close your web.config
  17. Add Person Controller class
    1. Right-click on the Controllers folder and select Add / Controller
    2. Select Web API 2 Controller with actions using Entity Framework
    3. Click Add
    4. Select the Person class from your class library project
    5. Select the Models class for the Data context class
    6. Check the box for Use async controller actions
    7. Click Add

    The final project we are going to add in this post is another test project we will complete in the next post to hold our Selenium UI tests.

  18. Add Test Project
    1. Right-click on the solution and select Add / New Project…
    2. From the Visual C# section select Test
    3. Select the Unit Test project template
    4. Enter PeopleTracker.UITests for the name
    5. Click the Browse… button next to Location
    6. Select the src folder within the solution folder
    7. Click OK
  19. Commit and push your changes
    1. From the View menu select Team Explorer
    2. Click Changes button
    3. Enter a comment
    4. Select Commit and Push

    Committing your changes will trigger the build we configured in a previous step.  Verify that the build succeeds.

In our next post we will add the Selenium tests.

Pingbacks and trackbacks (16)+

Add comment