Survey Web App Using ASP.NET MVC 4, Entity Framework 5

• Download MySurveyDB.zip - 2.9 KB

 

 

 

 MySurvery Azure Website 

 

Introduction  

A survey application using ASP.NET MVC 4, Entity Framework 5 and SimpleMembership.

Azure Benefit   

I believe that Azure can help in simplifying the deployment process using the Publish Profile within Visual Studio 2012. I don't have to worry about configuring the database and website to play nice... Azure just takes care of it. 

Background    

I work for a consulting company and noticed that we send out Excel documents with simple questions for the client to fill out. But what if we just sent them a link and they login using Google, Twitter or Facebook? What if...  

The database design is based from this Stack Overflow question/answer: http://stackoverflow.com/questions/540885/db-design-survey-quiz-with-questions-and-answers 

It will likely morph as the project moves along. I decided against using "Code First" database design as I found it easier to generate the tables within SQL Server Management Studio and then have Entity Framework generate my Models.  

I know there are some .NET OpenSource survey projects out there. But nothing has been updated within those projects in the past few years. I'm looking to create something that is flexible, secure and easy to use.  

Using the code  

This is a work in progress... I will upload the script to create the database and also the important pieces of code. 

Source Control...because we can 

First, let's create a new project in source control using Team Foundation Server (TFS) at tfs.visualstudio.com ... it's free and all you will need is a Microsoft  Account (Windows Live ID). You can create a Microsoft Account here https://signup.live.com/signup.aspx?lic=1 Note: All steps were completed using Internet Explorer 10 . 

1. Go to http://tfs.visualstudio.com/ 
2. Click "Sign up for free" in the upper right or click the "Sign In" if you already have an account
3. Create your unique Account URL by entering your choice into the text box ... you can make it easy and just use your first name and last name. 
4. Click the "Create Account" button.
5. You will be redirected to sign in using your Microsoft Account.
6. Once logged in the Team Foundation Service homepage is displayed. Click the "New Team Project" button on the left.  

Create Project in TFS 

1. Enter a Project Name (ie: MySurveyProject) 
2. Enter a Description (optional) 
3. Select your Process template (I kept the default of Microsoft Visual Studio Scrum 2.2) 
4. Select Version control  (again, leave the default Team Foundation Version Control) 
5. Click the Create Project button  
6. After the project is created click the Navigate to Project button
7. Under Activities click Open a new instance of Visual Studio  
8. Click Allow on the popup... it's just making sure you want the webpage to open the application (Visual Studio)  
9. Enter your Microsoft Account credentials to complete the connection to Team Foundation Server
10. Congratulations! You have successfully created a new project in TFS and configured Visual Studio to connect to your project. Now... let's create a new ASP.NET MVC 4 Web application. 

Create ASP.NET MVC 4 Web Application  

1. Back to your newly opened instance of Visual Studio. From the Start Page click the New Project... link.
2. Select the ASP.NET MVC 4 Web Application project 
3. Enter MySurvey in the Name textbox. 
4. Make sure the checkbox is ticked for Create directory for Solution and Add to source control

   

5. Make sure the checkbox for Create a unit test project is ticked. Leave the View engine as Razor

   

6. Leave the defaults for the Add Solution MySurvey to Source Control and click the OK button.

   

7. The web application has been created but it has not been uploaded/saved to TFS. Once the project is done loading click VIEW -> Team Explorer. Then click the Pending Changes link. Enter Initial check-in in the Comment field.
8. Click the Check In button above the comment field.
9. Click Yes to the Check-in Confirmation dialog box. Your first check-in will take the longest because you are checking in everything. Future check-in's won't take as long as you will only be checking in a few files at a time.
10. Press F5 to build and see your new ASP.NET MVC 4 Web Application. Now, let's make some quick changes to it and get it deployed to Azure Websites.

Update header and page title 

 

1. Within the MySurvey project expand the Views folder then the Shared folder. Double click and open _Layout.cshtml 
2. On line 15 change the text from "your logo here" to "MySurvey". This will change the text in the upper left of all pages to read MySurvey 
3. _Layout.cshtml is referenced on all pages and allows you to make quick site wide changes easily.  
4. Now lets update the homepage title. Open the HomeController.cs in the Controller folder.  
5. Add the following line in the "ActionResult Index()" function  
   ViewBag.Title = "MySurvey HomePage";   
6. The whole thing should look like this:   

public ActionResult Index()
{
ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";
ViewBag.Title = "MySurvey HomePage";
return View();
}  

Now run the web application. When the page is displayed it still says "Homepage" when it should say "MySurvey HomePage". What gives?

Well the Title property is currently set on the view. Let's open up the view and comment out that line.

 

1.  Expand the View folder then the Home folder. Open up the Index.cshtml file.
2.  Comment out line two. Should look like this

@{
//ViewBag.Title = "Home Page";
}   

 Now run the web application. The title is correct! I show you this just to get a little more comfortable around ASP.NET MVC. Now that we know how to make simple changes and our default ASP.NET MVC Web App is now customized (a little " />  let's go create an account on Windows Azure, create a website and then deploy to that website.

Sign up for Windows Azure 

 

1. Go to http://www.windowsazure.com 
2. Sign up for a free 90 trial using your Microsoft Account you used for TFS. 

Create Azure Website  

 

Now that you have successfully created a Windows Azure account, let's create a website that we can deploy our ASP.NET MVC 4 Web Application to. 

 

1.  Once logged into the Windows Azure Portal click the New button in the lower left then Compute -> Website -> Custom Create 
   
2. Enter a URL and select the Region that is closest to you. Under Database select Create a new SQL Database. Leave the DB Connection String Name as DefaultConnection. Make sure to tick the checkbox Publish from source control. (I knew there was a reason we were putting this into Source Control). Click the "Next" arrow.
   
   
3. Database settings. I usually leave the default database name. I don't know if Microsoft is practicing "security through obscurity" or if they just randomize the name to avoid name conflicts. In the Server dropdown select New SQL database server.  Enter a username and password. I like to use http://correcthorsebatterystaple.net/ to generate passwords. Again I select the region that is closest to me. Click the "Next" arrow.
   
   
4. Where is your source code? Select Team Foundation Server and click the "Next" arrow. 
   
   
5. Enter the URL to TFS that you created earlier. And click the Authorize Now button/arrow.
6. A new window will be displayed asking if it's "OK" if Windows Azure connects to TFS. Click the green Accept button. 
   
   
    
7. Choose a repository to deploy page. Select the MySurveyProject under the Repository Name drop down list. Then click the "Check mark".
   
   
    
8. Your website will be displayed in the portal and the status will be "Creating". Once complete it will say "Running" and the URL will be displayed. Click on the URL to view your site. You will see the default Windows Azure Web Site.

And you are thinking to yourself, "Self, we told Azure to use source control for deployment... why don't we see our awesome ASP.NET MVC 4 website?"  Let's go take a look at the Windows Azure Portal and see what's going on.

If you log into the portal and click on your newly created website you will see the "Quick start" screen. At the bottom of that screen click the View deployments link under the Integrate  source control heading. I SPY!

 

The website will deploy automatically when we check-in some code. Remember those files we edited? We never checked those in! Let's get to it.

Check-in and Engage Deploy! 

 I apologize for the Star Trek reference, couldn't resist. Back on track... 

 

1.  Go back to Visual Studio 2012 and view Team Explorer (View -> Team Explorer). If it doesn't show the Pending Changes section click the "Home" button at the top and then click the Pending Changes link. 
2. Enter a comment. You can see I entered "Updated homepage title". Click the "Check In" button 
   
   
3. Confirm the check-in.
   
   
    
4. Now go back to Windows Azure Portal. Click on your website and go to the Deployments section. It may take a minute or two... but it will automatically show that it is deploying.
   
   
5. And once the deployment is complete it will tell you that it is the Active Deployment.
   
   
    
6. Go to your website now. (You may have to hold down the Shift key while you click the refresh button. Doing this pulls directly from the server and not from cache.) You should now see your ASP.NET MVC 4 website.

That was the easy stuff. Let's add a database. 

Challenge 3 - SQL Azure Database 

Download MySurveyDB.zip 

As mentioned earlier in this article I will be using the database diagram from an answer on Stack Overflow. I created the database using the designer within SQL Server Management Studio and have included the SQL script so you can create the database quickly and easily. 

 

• Open Microsoft SQL Server Management Studio and connect to your local DB 
   
• Right click on your local server and select New Query 
  
• Paste in the SQL from the MySurveyDB.sql included at the top of this article (or from the top of this section).
• Hit the F5 button to execute the SQL  
• If all goes as planned you will see "Command(s) completed successfully." in the messages window. And you should now have a database named MySurvey (you may have to refresh the "Databases" folder Right Click -> Refresh). 

 

Generate Scripts for SQL Azure 

There are a couple ways we can move or copy the database to Azure. We are going to take the database we just created and have SQL Server Management Studio (SSMS) 2012 generate the scripts for SQL Azure. We are having the scripts generated because when we created the website we also had it create a database. Now we are just going to update the database with our new tables. 

 

• Right click on your new database -> Tasks -> Generate Scripts
   
•  You will be presenting with the following screen. Click the "Next" button.
  
• Make sure the radio button "Script entire database and all database objects" is selected. Click the "Next" button.
  
• "Save scripts to a specific location" radio button should be selected in the "Output Type" section. Then select the "Save to new query window" radio button. We are just going to copy from the query window in SSMS to SQL Azure Query Window.
  
• Click the "Advanced" button. Under the "General" section find "Script DROP and CREATE" and change the option from "Script CREATE" to "Script DROP and CREATE". Then three options down is the "Script for the database engine type" ... change that to "SQL Azure Database". Click "OK" in the advanced window and then click "Next"
  
   
• Review the summary screen and click the "Next" button.
  
   
• Everything should read "Success". Click the "Finish" button.
  
•  You will then be presented with a script within SSMS 2012. We can now take this script and copy/paste it to SQL Azure. 
•  

Update database in SQL Azure 

 

• Log into the Windows Azure portal and select the "SQL DATABASES" option on the left menu. 
• Select the database that was created with your website. Select the database by clicking under the "STATUS" column. Once the database is selected click the "MANAGE" button across the bottom.
• A new window will open. Enter the database credentials you created when we created the Azure website and database. Click the "Log on" button.
• Once logged in you will be shown a "Summary" screen of the database. Click the "New Query" button across the top.
• Copy the script we created in the previous section and paste it into the window. Right click doesn't work because it's a Silverlight app. Paste it in using Ctrl+V. Click the "Run" button.
• Don't worry if you see some messages about not being able to drop constraints. 

We now have our database in SQL Azure! And since we created the Website and SQL Azure database at the same time Windows Azure automatically linked those resources together. So when we deploy (check-in to TFS) it will automatically use the database we just updated. No changing the web.config!

 

Entity Framework and Scaffolding

Now that we have our database setup on our local machine and also in SQL Azure we can now add the tables we created as Models in our ASP.NET MVC 4 Web Application using Entity Framework and let Visual Studio's scaffolding do the CRUD work for us.

Add Data Model

First we need to add our database as a Data Model using Entity Framework.

 

• Right click on the "Models" folder and select "Add" -> "New Item..." 
  
• On the left select "Data" then select "ADO.NET Entity Data Model" on the right. At the bottom of the window in the "Name:" field enter "Survey.edmx". Click the "Add" button.
  
• Select "Generate from database" ... we are going to generate the model from the database we just created. Click  the Next button. 
  
• Click the "New Connection..." button 
  • Enter the server name for your database. Everything is running on my local machine but I'm running a named instance for SQL Server. So I enter "(local)\SQLEXPRESS" 
  • In the "Log on to the server" section select Use Windows Authentication
  • Under the "Connect to a database" section select the new database we created with the SQL script... "MySurvey" 
    
  • Click the "Test Connection" button. Click "OK"
     
  • On the Connection Properties window click "OK" button.
• Leave the defaults and click the "Next" button 
  
• Select "Tables" checkbox. Click the "Finish" button.  
  

After you click finish you will be presenting with a diagram of the database. Now that we have our data model we can now use scaffolding to make our lives easier. Before we continue and use scaffolding to make the controller and view we need to build the project. Hit the F6 button. 

Create controller and view using scaffolding  

Lets's add the "Organization" controller first. 

 

 

• Right click on the Controllers folder, "Add" -> "Controller..."
   
• For the "Controller name:" field enter "OrganizationController"
• For the scaffolding options  
  • Template: MVC controller with read/write actions and views, using Entity Framework
  • Model class: Select "Organization (MySurvey.Models)
  • Data context class: Select "MySurveyEntities (MySurvey.Models)
  • Views: Select "Razon (CSHTML)"
  • Click the "Add" button and Visual Studio's scaffolding will automagically create the controller and associated views. The files for the views will open.  
    
• Press F5 to build and run the web app.
• Once the homepage loads just tack on "Organization" to the end and hit Enter.
  Test it out here 
• Click the "Create" link
• Enter an organization name. Example: "Microsoft"
• Click the "Create" button. 

You just added an organization to the database. You did all that without writing a single line of code. Pretty slick if you ask me. 

 

We have added the Organization controller. Do the same thing for the following Models: 

 

• InputType
• OptionChoice
• OptionGroup
• Question
• QuestionOption
• SurveyHeader
• SurveySection
• UnitOfMeasure

Those are the easy ones that we can create using scaffolding. Models like "Answer" we are going to have to dig in.   Once all that is complete, check-in the code to kick off the deployment on Azure. 

 

Points of Interest 

First hiccup was changing the SimpleMembership from "(LocalDB)" to SQLExpress. Since I was using Entity Framework it didn't like the connection string. I had to create a basic connection string to SimpleMembership could initialize correctly. I'll explain more later.

History 

1. • 2013-04-26 - Initial submission for Windows Azure Developer Challenge 
   • 2013-05-01 - Added TFS section
   • 2013-05-02 - Added Azure website creation