2015 Ignite New Zealand demo prep: Step 6 24 October 2015 Donovan-Brown Work (0) Steps: Intro VSO Docker xUnit Build Back end Selenium Docker Release Management 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. Locate the Dev resource group Browse to the Azure Preview Portal Click Resource groups Select your Dev resource group Add a Web App + SQL Click Add Button Select Web App + SQL in New Resource blade You may have to search for the item. Click Create button on the Web App + SQL blade Click Configure required settings under Web App Enter a name Enter a AppService plan name Click OK Click Configure required settings under Database Click Create a new database Enter name Click Configure required settings under Server Enter a name Enter login Enter Password Select the same region as the Linux VM Click OK Click OK on New database blade Click Or select existing link under Create new resource group Click the resource group name Select your Dev Resource Group It should be the same resource group as your Linux Docker Host. 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. Add People Table Locate your SQL Database in the Preview Portal Click the Open in Visual… button at the top of the blade Click Configure your firewall link to add your client IP Enter the password you created on the Connect to Server dialog Click Connect Your SQL Database will appear in the SQL Server Object Explorer. Expand your database to view the Tables folder Right-click the Tables folder and select Add New Table… Add a firstname and lastname column as non-nullable nvarchars of length 50 Down in the T-SQL section change the name of the table from “Table” to “People” Click the Update button 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. Create empty solution From the File menu select New / Project Select Visual Studio Solutions Select a Blank Solution template Use the Browse… button and select the folder that has our original solution file Name our solution PeopleTrackerWebService Unchecked Add to source control check box 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. Click OK Move solution to root folder From Solution Explorer right-click on the solution and select Open Folder in File Explorer Close Visual Studio Move the solution file up one folder level to the same folder as the sln for our first solution Once the solution is moved you can delete the folder that was created for the second solution. 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. Add solution to source control 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. Add SQL Server Database Project Right-click on the solution and select Add / New Project… From the Other Languages section select SQL Server Select the SQL Server Database Project template Enter PeopleTracker.Database for the name Click the Browse… button next to Location Select the src folder within the solution folder Click OK on the Add New Project dialog With our database project created will need to import our existing schema. Import Database Schema Right-click on the database project and select Import / Database… Click the New Connection… button Enter the fully qualified domain name for your Azure SQL Server for the Server name It should the name you entered when we create the server followed by .database.windows.net. Select the radio button for Use SQL Server Authentication Enter the user name and password you entered when create the SQL Server Select your database from the dropdown in the Connect to a database section Click OK Click Start Click Finish The final step is to change the Target platform for our database project. Change Database Target platform to Azure SQL Database Right-click on the project and select properties Change the Target platform to Microsoft Azure SQL Database 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 . Add class library project Right-click on the solution and select Add / New Project… From the Visual C# section select Windows Select the Class Library project template Enter PeopleTracker.DAL for the name Click the Browse… button next to Location Select the src folder within the solution folder Click OK Our data access layer project will be using Entity Framework so we will add that now. Add reference to Entity Framework Right-click on the class library project and select Manage Nuget Packages… Select Entity Framework and install it Add Person model class Right-click the Class1.cs file and select Rename Rename the file to Person.cs Click the Yes button the Microsoft Visual Studio dialog confirming that you want the class name changed as well Remove all the existing using statements from the top of the file 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; } [Required] [Display(Name = "First Name")] [StringLength(50)] public string FirstName { get; set; } [Required] [Display(Name = "Last Name")] [StringLength(50)] public string LastName { get; set; } } Add DbContext Class Right click on your class library project and select Add / New Class Enter Models.cs as the class name Remove all the existing using statements from the top of the file 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. Add ASP.NET 4.5.2 Web API project Right-click on the solution and select Add / New Project… From the Visual C# section select Web Select the ASP.NET Web Application project template Enter PeopleTracker.NetService for the name Click the Browse… button next to Location Select the src folder within the solution folder Click OK Select Web API from the ASP.NET 4.5.2 Templates Check the Add unit tests check box Change the Authentication to No Authentication Click Change Authentication Select No Authentication Click OK Uncheck Host in the cloud check box 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. Right-click PeopleTracker.NetService.Tests and select Remove Click OK Right-click on solution and select Open Folder in File Explorer Drag the PeopleTracker.NetService.Tests folder into src Close File Explorer Right-click on solution and select Add / Existing Project... Browse into the PeopleTracker.NetService.Tests folder Double-click PeopleTracker.NetService.Tests Once the project is added to the solution build the entire solution to make sure everything is compiling. Add reference to class library to Web API project Right click on the Web API project and select Add / Reference… Select Solution Check the box for your class library project 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. Add connection string Open Web.config Right after the appSettings section of your web.config copy and paste the text below. <connectionStrings> <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"/> </connectionStrings> Replace all the values in square brackets with the values for your deployment Save and close your web.config Add Person Controller class Right-click on the Controllers folder and select Add / Controller Select Web API 2 Controller with actions using Entity Framework Click Add Select the Person class from your class library project Select the Models class for the Data context class Check the box for Use async controller actions 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. Add Test Project Right-click on the solution and select Add / New Project… From the Visual C# section select Test Select the Unit Test project template Enter PeopleTracker.UITests for the name Click the Browse… button next to Location Select the src folder within the solution folder Click OK Commit and push your changes From the View menu select Team Explorer Click Changes button Enter a comment 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.