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.
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.
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.
Once complete your project will look as mine does below.
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
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..
Inside that Class go ahead and create the necessary auto generated properties as you can see in the code snippet below
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…
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.
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”
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…
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.
Once you click Add, you should see your project grow in leaps and bounds like mine below
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.
Below is a code snippet of the connection string minus my credentials.
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.
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.
Once I did that, when I clicked F5 to test this out, it worked as expected. The circle is complete 🙂
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.
For now Enjoy.. c u l8r
Why write this blog post?
Sure its late and YES I could be rotting my brain away on the Idiot Box, but what started off as a Taunt by Ram turned out to consume my life for 2 days straight but with useful results, challenges that were overcome, and a better appreciation for not just Cloud, but the integration of Cloud and On-Prem using Windows Azure. Let me tell you what I did, and how easy it was… in the end, plus the real implications of it.
First a little history – Pre Taunt
So, as I prepare for my sessions at #SPLive in Orlando in 3 weeks, I wanted to knock out a few Demos and make sure that I could showcase just about anything anyone asked of me based on the Session Abstract/Topic. In furtherance of that, I decided to do the following, build out solutions employing
SharePoint Designer 2013
- External Content Type using a Native SQL Data Connection
- External Content Type using an On-Prem (i.e. Hosted on one of my Dev Rig VMs) Windows Communications Foundation [WCF] Data Connection – JSON enabled
- External Content Type using a .NET Assembly (built on my Visual Studio 2012, Deployed as a Farm Solution to my On-Prem SP2013) Data Connection
- Workflow using the same On-Prem WCF above in my On-Prem Farm
Visual Studio 2012
- A Locally IIS hosted WCF Service Library that exposes Northwind data from a Local SQL Server 2012 Box
- A Locally IIS hosted OData Service Endpoint that exposes Northwind data from a Local SQL Server 2012 Box
- External Content Type built as a .NET Assembly to be deployed to my ON-Prem SharePoint 2013 Farm
- External Content Type AND External List SharePoint 2013 App built as an OData Sourced Application deployed to my ON-Prem SharePoint 2013 Farm
For every Instance of the WCF and the App Model External Content Type, I also targeted my Office 365 Tennant to surface the Data as well. I was pretty satisfied with myself until my good friend sent this tweet, and I know it was in jest…
but… I took it as a challenge and, I rarely back down from a challenge 🙂 so, since I have a MSDN subscription, there was “really” nothing holding me back besides my inexperience creating Azure Web Roles, which is what you need to do in order to have the requisite Data Access Points (URI) to create External Content Types and potentially Workflows.
What Next you say…
So, with a little research on Google scratch that, I mean Bing, I found a few MSDN, and TechNet blogs on how to create Web Roles, although not many of them “accurately” showed you how to use Visual Studio to DEPLOY your solution back to Azure. Anyway, it was surprisingly easy, although initially it was somewhat intimidating and i did get snagged on a GOTCHA which i will blog about later when doing a ASP.NET Web Role where it requires you to lower your version of the System.Data.Client assembly from 5.0 to 4.something in order for it work in Azure, i say that because it worked perfectly when I debugged it locally on my Visual Studio IIS. I also had to create an Azure SQL Instance and used a script I downloaded from CodePlex to restore a Northwind Database to my Azure SQL Instance and then I set up firewall rules to allow me to access it over the internet.
But with quick turn around and because i could just Refactor my original On-Prem code logic, I was able to create the following
Visual Studio 2012
- Azure ASP.NET Web Role that published as a Cloud Service which used OData via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on
- Azure WCF Web Role that published as a Cloud Service which used WCF via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on as well
Now I can really Mix and Match, I can
- Use Azure (Cloud) Hosted Data via an ON-Prem WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
- Use Azure (Cloud) Hosted Data via an Azure Cloud Service WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
- Plus everything I had when I started off
The takeaway here is that with very little investments in time and effort I extended my solution to a “MODERN” approach. I had Cloud in the sentence 🙂
Other Positive Implications
So, what made this so easy for me was I signed up for TFS Online here http://tfs.visualstudio.com/en-us/tfs-welcome.aspx and EVEN WITHOUT a MSDN Subscription you can sign up for FREE and get 5 accounts in one instance.
What I think is GOLD is…
- You have a place to store all your CODE/ Work and before you ask, it supports different formats (see image below)
- Your Code/Work is accessible from Anywhere you have an Internet Connection now
- You can reliably share your Code/Work with anyone now, rather than Zipping it up and Email it
- Need Help with some of you work… Invite people as smart or smarter than you to review our code, many hands make light load 🙂
- Most beneficial of all is that you can configure Continual Integration. I know my good friend Jeremy Thake always talk about Application Lifecycle Management (ALM) and you can employ some of those techniques from Azure through TFS Online and your Visual Studio with multiple Team Member… and guess what… FOR FREE
So that’s all I have to say, I guess we really Paid It Forward RAM, you encouraged me to do this, I in turn, documented my efforts and hope that someone else can take it from here.
Cheers all, have a great night. Oh yeah, VS 2013 is out now. GO get it.