So yesterday, I was talking with Ted Pattison as he did a Critical Path class on the Great SharePoint Adventure (GSA) and a question was asked by one of his students about using Entity Framework to connect to a Oracle Database and then make an External Content Type and External List. I suggested that you can use various Database Technologies to connect to your backend Data and serve it up. in this case I am connecting to a SQL Azure Data in this case Northwind Customers and I will be using ADO.NET Entity Framework to work with it. I will then use WCF Data Service to render this data into OData format and use that URI as a source for my External Content Type in a Visual Studio SharePoint App.
In all of this I will be targeting a SharePoint Online Instance, but this can be used for On Prem, in fact the way i do my example I would either need to have (1) set up my SPO for Hybrid Inbound or it wouldn’t work because my URI is internally hosted, I could of course (2) do a Azure Cloud Service [as i have done in previous posts] as well.
Grab the Code here: http://code.msdn.microsoft.com/SQL-Azure-to-EF5-to-OData-2b2bd362
For this you will need a Visual Studio Solution with two projects. One will be for the ASP.NET project that will make the connection to the SQL Azure Data and host it, alternatively this could be a Web Role Project targeting a Cloud Service in Azure. The second project will be a SharePoint App Model Project that will create the External Content Type.
Create a ASP.NET Project
As you can see above we are creating a project of an ASP.NET Empty Web Application. So, we are also targeting a SQL Azure Database, i want to show that to you, see below
We will be targeting a Database called FabianNorthWind
Above what you see is us adding a ADO.NET Entity Model to the project to connect to the Azure SQL Instance.
Follow the wizard through and you should see what I see here
Finally you will have your EF in your project
Save your project here and next we will have to add a WCF Data Service, we do this so we can have a URI to connect to that will expose the SQL Azure Data, or Oracle Data etc.
Above is our URI endpoint and it is at NWindCustomer.svc
Once you do that you will get a template helper as you see below, some of it is stubbed out for you. You will need to enter the name of your ADO.NET Entity Model as well as configure the access for the Data you want
As this is Demoware, I am going to use an (*) for the configuration and tell it to do “ALL” but you can be more discrete.
Once you have done that you can preview the .SVC file in a browser and you should see what i have below
by typing in Customers in the URI, you will get an OData result set coming back to you. Pay attention to this URI you will need it for your next project.
Create a SharePoint App Project for your ECT
Ok, add a new project to your solution as i have below.
We are targeting a SPO tenant but this can be anywhere really. This is also a SharePoint Hosted App
Follow through on the wizard till your project is added.
Its really easy from here on in. You add a Content Type for an External Type to the new project
You provide the URI we have from the first project.
You select your Table, and leave the “check mark” for creating the List Instance so you will get your External Content Type and External List.
One you are done you will have an ECT and an EL as below in your project
If you open up your External Content Type you can see what I have below. At this point you can
- deploy it via visual studio
- give it to someone to upload to a farm
- deploy it manually yourself
here is the ECT below
Finally your project will look as i have below and you are done
I hope this is helpful, it was rather rushed, but i promised it in a few hours turnaround.
So, I as a cure for insomnia, decided to troll the CODE.MSDN site for questions that interested me. I found this request and figured it was something i could knock out, so i did. There was not much to go on interms of HARD requirements so i posed a question, I assumed if Im up and about, the person making the request must be up too 🙂 but alas no. So, i went ahead and did the sample as I would envision it. In fact, internally at work, we have something On Prem that is similar so this actually made it more appealing.
I can and will make this example more robust and may submit it up on the MarketPlace, I dont know, just to say that I have, but it seems something that Im sure must already be there, Ill just endeavor to make mine better 🙂
THIS CODE IS AVAILALE ON MSDN: Here
As this is an Office 365 SharePoint Online example, you basically don’t need anything as you would for an On-Prem solution, all you need really is:
- A Office 365 SharePoint Online Site to build against
- Visual Studio [for completeness I am using 2013 but others will do]
- Your Brain & some Idle time
What this example will set out to do in this version is the following
- Permit an End User to Request Leave off Work/ Holiday/ Vacation etc.
- Send a Request for Approval to the End Users Manager/ or the person On Behalf Of
- Permit the Approver to Adjudicate the request in an Approval Workflow in SharePoint 2013
- Send a ‘Contract of Sorts” to the Requester alerting them of the decision for their acknowledgement
- Notification includes a process to Accept the decision or Appeal it
- If Approved, update a Leave/Out of Office (OOF) Calendar with the relevant information to show the person being OOF
I plan to extend this example later on to flesh out the Appeals Process, make pretty App Icon and put some more bells and whistles on it. Sexy it up so to speak 🙂
As with all my work when I set out to do a SharePoint project that involves List/Libraries, Content Types, Workflows/Event Receivers, etc, I often will create my own Site Columns, Content Types, List Definition and Instance (see post here on how to do that). Once I complete that process I typically will Branch my Project (in TFS Online) and start to Dev out the Unit of work/ Story (Agile). I created a branch for Workflows and set up folders for what I will build out now and later on
This is what my Visual Studio Solution looks like now
So, with that heavy lifting complete, lets focus on the Workflow.
Workflow Build out
First, you add a Workflow to your project and associate it with your List, in my case I am associating it with the “TimeOffRequest” list instance. I also set the Workflow to Start on “On Item Created” and have New Task List and Workflow History List created. Basically this…
Once you do that, you will get a few assets created for you
- The Workflow itself
- A History List
- A Task List
The canvas will open up and you will a blank slate with one Sequence added. What I typically do here and I learned this from Andrew Connell in all his Demos/ Preso is to change that Default Sequence to “Root” and then embed your other abstracted logic flow buckets inside that sequences as nested Sequences. I do this for the following reason
- It makes the code much more readable and manageable
- It allows you to scope your Variables etc to its own sequence
- Helps me troubleshoot if/when I get issues
So mine looks like this now
and you can drill down into each section like so
Update Time Off Calendar
Other Important items not so obvious
So, there are variables that are crated along the way either by an objects Return Type or Auto Loaded by Variable Declaration in SPLookupItem Object, etc
For example here is what is in scope for “Root” Sequence
here is what is inscope for “Notification”
How it Works
So, when you get everything working you publish it to your Office 365 SharePoint Tennant. Were I paid for this I would mock up the Default.aspx page to show links for the Time Off Request and the Calendar with an Appropriate View, but for now we will just use Different Tabs in the Browser
You will have a new App in your Site Contents like so
Here are the Tabs
First the TimeOffRequest
Second the TimeOffCalendar
Lets put it through the paces now.
Now lets submit this Form
Now we have a form submitted
lets check the Status of the Workflow, and we can see below we have one already going
We even got an email in Outlook. for now, lets adjudicate this as Approved so it will appear in the calendar eventually
Here you can see that we have buttons to Approve and Reject and we have meaningful information in the Email Body. Finally you can see that the Workflow is running in the Context of the App Principal at the bottom, look to the left of the buttons.
Next, and just showing the Task and History Log we can see now that a Contract has been sent back to the Requestor after the decision has been made
lets go ahead and do that, this time I wont do a screen shot as its similar to the same Approve Reject one earlier.
Finally you can see everything done, and the only thing we need to do is check the Calendar to see if the Item has been added.
and sure enough it is….
So, i hope this was helpful, if so, drop me a comment, share this online, etc.. hook a brotha up 🙂
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