Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

Not SharePoint but cool anyway MVC5 EF6 and Azure

Précis

This post is about using Model View Controller (MVC) 5 in Visual Studio 2013 with Entity Framework 6 to do CRUD operations on a SQL Azure Database.  It is definitely NOT Rocket Science but there are a few GOTCHAS to look out for and definitely a new way of doing business with MVC 5 as it relates to Controllers and Scaffolding with Entity Framework.

My code will be up at Code.MSDN.com and I will put it in Git Hub as well. I will update the post with the links when i upload them.

Create your Project

The first thing you have to do is create your project. So, Launch VS 2013 as Administrator and under the Installed Templates click Web and select the “ASP.NET Web Application”, provide a meaningful name and click OK.

image

The next thing we will do is select MVC since that is what we are doing here today. As this is DEMOware we will not be doing any Unit Testing as well so unselect that if it is selected for you.

 

image

Once complete your project will look as mine does below.

 

image

and a project ReadMe that invites you to customize the Applications etc. will be there for you as well.

Create your Model

Since we are connecting to a Data Source, as in our case it is my Northwind Customers Table in SQL Azure, what we need to do is create an Object (Class) that represents that schema, so take a look at your Columns and lets add a Class file next

image

Above is the Silverlight App that ships with SQL Azure Management and you can see some of the fields we will Model. To Model the entity, you add a new Class to your Project as i have below. Right click on the Model Folder and..

image

Inside that Class go ahead and create the necessary auto generated properties as you can see in the code snippet below

 

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5.  
  6. namespace AzureNorthWindCustMVC5Demo.Models
  7. {
  8.     public class Customer
  9.     {
  10.         public string CustomerID { get; set; }
  11.         public string CompanyName { get; set; }
  12.         public string ContactName { get; set; }
  13.         public string ContactTitle { get; set; }
  14.         public string Address { get; set; }
  15.         public string City { get; set; }
  16.         public string Region { get; set; }
  17.         public string PostalCode { get; set; }
  18.         public string Country { get; set; }
  19.         public string Phone { get; set; }
  20.         public string Fax { get; set; }
  21.     }
  22. }

With that, you have your Model. This is a good time to Build your project so the Model will be available for the Controller which we will do next.

Create your Scaffolding and Controller

This is one place where the cool new features of MVC 5 kicks in, you can Right Click on your Controller Folder and add “New Scaffolded Item”. What does for you is literally ALL THE WORK necessary to have full CRUD capabilities back to your Data Store. It will

  • Create your Context Class for your Database
  • Create your Connection String in the Web.Config [which you will need to Modify anyway because by default it points local]
  • Create an instance of the Context Class in your Controller so you can interact with the Database
  • Create all the Views for you to
    • See a List of All items returned
    • See Single Item
    • See Detailed Items
    • Edit Items
    • Delete Items
    • Create a New Item

Here is how you do it, right click on the Controller Folder in your Project and do Add, New Scaffolded Item…

image

You have the option to do this manually by doing and Empty Controller but since this example is about Entity Framework 6, we will use a controller that will create all views with EF6.

image

Once that is done, you can wire up the Data Connection for the Model and the Data Context. Assuming that you Build your project as i said earlier, you should see as i do a Model Class with the name you need. In my case “Customer”

image

Once you have selected that you can now do “New Data Context” for it to create that Class for you as well. I named mine to be reflective of the Table i am working with. See below…

 

image

One minor change that I did. You will notice above my Controller Name “WAS” Home… but I realized that there was one already created called Home so i changed it to Customer as you can see below.

 

image

Once you click Add, you should see your project grow in leaps and bounds like mine below

 

image

GOTCHA Moment

Now you may think your work is done but there are at least Two (2) more things needed to make this connect to your Azure Database. The first thing is to (1) Modify or ADD the connection string to your SQL Azure Database to the Web.Config in the root of your Project. If you dont MVC will create a NEW database for you locally in SQL Express more than likely if the Controller DOES NOT find a Connection String of its Name inside the Web.Config. That is why you will see below that the Name of MY connections string is the same name of my Context Class.

image

Below is a code snippet of the connection string minus my credentials.

  1. <entityFramework>
  2.   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  3.   <providers>
  4.     <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  5.   </providers>
  6. </entityFramework>
  7. <connectionStrings>
  8.   <add name="CustomerContext" connectionString="Server=tcp:uboxhere.database.windows.net,1433;Database=FabianNorthwind;User ID=UrUserNameHere;Password=P@ssword123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" providerName="System.Data.SqlClient" />
  9. </connectionStrings>
  10. </configuration>

 

The second thing (number 2) is to Add a command to the Global.asax file which is fired upon App Launch to Initialize the Database connection I want in Azure, you will need to have a reference to the System.Data.Entity namespace as i have shown below when you do this. You will notice it is also bound to the Model Context Class.

image

Just a little weird thing that I noticed.  I did this on my Windows 8.1 box and when I tried to launch the browser to test my work, IIS Express failed with some Process number, so i changed my Build to point to my Local IIS and created a Virtual Directory as you see below.

image

Once I did that, when I clicked F5 to test this out, it worked as expected. The circle is complete 🙂

image

 

Summary

This is really cool, i know I usually talk about SharePoint but this is equally important and you will also see a new MVC Template in SharePoint for Visual Studio 2013 in Cloud Apps, make sure you give that a try. Chaks has a good post out on it here.

My code will be up at Code.MSDN.com and I will put it in Git Hub as well. I will update the post with the links when i upload them.

For now Enjoy.. c u l8r

November 6, 2013 Posted by | Azure, Entity Framework, MVC, Visual Studio 2013 | , , , | 2 Comments

Come see my three session at SPLive360 in Orlando

What am I speaking on

I am honored to be accepted to speak at this auspicious event led by co-Chairs Andrew Connell and Dan Holme this November 18th through 22nd, at the Royal Pacific Resort at Universal in Orlando, Florida. I will be delivering three sessions

  1. SPH14 Case Study: When Should I Use SharePoint 2013 Business Connectivity Services (BCS) and When Should I Use SharePoint 2013 Workflows to Interact with External
  2. SPW11 No-Code CRUD Business Connectivity Services (BCS) Solutions Using SharePoint Designer 2013
  3. SPW02 What’s New with SharePoint Business Connectivity Services (BCS) and OData Services

SAVE MORE when your REGISTER by using PromoCode: SPLSP21

image

What to expect?

if you have ever been to any of my sessions before, you will know it is highly interactive, and we remain in dialog for the entire time. My demos will incorporate your ideas and challenges, therefore we all walk away winners!

Indeed, there is a good mix for just about everybody over these three sessions; Im actually getting back to my BCS roots after spending the last few months, maybe a year now i think focusing in large part on Workflows and External Data. Infact, the Case Study session is all about how to make the decision of using a Workflow v/s using BCS.

Emphasis on External Data

All these sessions have a common theme…External Data… and in that vein, we will begin the sessions by discussing various types of External Data, their entry point to SharePoint and also how to manipulate them in the browser and Fiddler. I feel it is important for us to get/set our expectations of what our desired results should be before we get too far into the technical weeds of how SharePoint can expose/surface this data.

At this very moment of blog authorship [October, 10, 2013 1721 hrs] Im actually building out my Data Services, different flavors (Native SQL, oData, WCF) so we can have a few interaction points and see full CRUD-Q capacities between SharePoint Designer and Visual Studio.

Takeaway

As we work in a world consumed by data, we are often challenged to make sense out of it, i.e. get INFORMATION from DATA, and the speed to which you can accomplish that usually will determine your success over another. My job in these sessions is to demonstrate various techniques to that end (the HOW), and also in the case of the Case Study Session, engage you in conversation as to the WHY.

 

October 10, 2013 Posted by | Business Connectivity Services, Public Speaking, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint Development, SharePoint How-To, Visual Studio 2012, Where is Fabian, Workflows | , , , , , , | 1 Comment