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.
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.
Consistently two of my most heavily viewed/visited post based on WordPress Metrics is the one highlighted below, see the image for the top 5 all
- How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services
- Creating a SharePoint 2010 External Content Type with CRUD Methods using LINQ and a SQL LOB System
Since I have already done this in SharePoint 2010 using Secure Store and a SQL Server Native Line of Business (LOB) System, as well as a Visual Studio 2010 .Net Assembly, I figured I can offer value by updating this post to include the following Scenarios
- BCS Full CRUD using SharePoint Designer with a Windows Communications Foundation (WCF) Data Connection [I will actually show you how to build the WCF, otherwise why even bother :-)] using the Users Identity as the Auth (YOU ARE HERE!)
- BCS Full CRUD with Visual Studio 2012 .Net Assembly using Secure Store [I will put in Code Snippets this time rather than Images :-)]
- BCS Full CRUD with Visual Studio 2012 OData LOB System Type SharePoint App [Yes App Model] to include the External List Instantiation
There are tons of examples (the #1 viewed blog above uses that as an example) on how to use SharePoint Designer to do a SQL Native Connection, so i wont bother beating that horse to death, and I would like to also point out to this excellent post by my good friend Chris Givens on how he extended the third bullet point example above to include Notification and Subscriptions Alert, so i will not be doing that either.
As this is an update, I am specifically leaving out the precursor activities that are needed before you begin to create the External Content Type (ECT), I will just pick up at the point of Creating the ECT and as for cases where I introduce new methodologies, I will ofcourse showcase those aspects.
In this blog, specifically I will be showing the following
- Creating a WCF Source
- Basically, creating the WCF Service Library
- Moving the Service App DLL & Hosting the WCF in IIS (since automatically it is hosted in the local virtual Visual Studio IIS app)
- Using SharePoint Designer 2013 and that WCF Data Connection to do Full CRUD Operations, CRUD-Q infact
Lets Begin – Creating the WCF Service Library
My thought process for this since I am going to be exposing data in an underlying SQL Server is to use Visual Studio Entity Framework 5 to create my access to the Data Store. I will then (I don’t have to but because I want to extrapolate the Entity and manipulate it as my own object) I will create my own class(object) to represent the Northwind Employee and then create my Visual Studio Interface and then my Service Contract. If all of this sounds like gibberish to you, here is a beginners tutorial on how to create a WCF Service Library (its too involved for this post and will take away from my objective) NOW… I am not going to implement it totally the way the post describes it, as i said above, but I WILL show you the code of my
- Connecting to the Northwind Database
- My Northwind Employee Entity (Data Contract)
- Service Contract (Interface)
- Service Itself(Implementation of the Interface)
New Project & Connecting to the Database
First you will start Visual Studio as Administrator and create a new Project as such
Project > New Project > Dub CF > WCF Service Library – Then name it appropriately
Now by default you will get a Service Contract called (IService1.cs) and a Service Implementation (SErvice1.cs) – I don’t like using the OOB templates that are provided although hey say you can Rename/Refactor it; i just never trust it. So, you can go ahead and delete those, because we will create our own classes for our Data Contract, Service Contract and Service Implementation.
Once you have deleted those two files, then next we will do is add an ADO.NET Entity Data Model item to our project, name it appropriately for the Data you are modeling after and click Add.
Follow the Wizard by doing “Generate from Database”, make a new connection or select from one already there, because I am using SQL Server Authentication and I want to have this persisted in my project I also select the option for “Yes, include the sensitive data in the connection string” which is basically your Uname and PW along with Data Source and Initial Catalogue. Under Tables I am selecting my Northwind Employee Table, and following the rest of the wizard to the end. When you are done, you should have an entity as i do below as well as a “NameYouChose”.edmx file which shows you the below
Once you have done that, now I am actually going to create my own Data Contact class which abstracts from the ADO.NET Employee entity. You do that by adding a new class to your project. I am calling mine “Emp.cs” as seen below in the code snippet
You will notice that i am decorating the class with the [DataContract] attribute and also setting the fields that i want as [DataMember] then next thing I do is create my Service Contract which as I said before is a Interface; I do that by adding another class called IEmpService.cs as seen in the code snippet below
Here we decorate the interface with the attribute [ServiceContract] and this is the fist part where we have an eye towards what SharePoint BCS needs. As you can see above we have a the following
- Create Method called “CreateEmployee” which takes the data contract as a input parameter
- Read List Method called “GetAllEmp” which returns a list of all the Employees
- Read Item (Specific Finder) Method called “GetSpecificEmp” which takes the employee Id as an input parameter
- Delete Method called “RemoveEmp” which also takes employee Id as an input parameter
- Updater Method called “UpdateEmp” which takes the data contract as an input parameter
We also decorate these as Operation Contract what we MUST implement to use our Interface. Next we will create the service that will implment the Interface and this is done by creating another class called EmpService.cs
Thats a heap a code I know, and there are other ways to do this, Im sure, this is just “my way”, you will notice I am employing the Using Statement to encapsulate the Context of the Data Connection then Im either Creating, Updating, Deleting and as in the case of Reading I have one that returns everything and one that returns just one.
THATS IT for the Code part of this!!!
Once all of that is done, you will NEED, YES NEED, to modify the App.config file because it still thinks that the Service Contract is tied to IService1.cs that we deleted earlier. We can also create other endpoiints, yada yada, but thats anotehr story. So here is what you need to do for your App.Config. Right click on it, and click “Edit WCF Configuration”, when you do you will need to change the Service to your EmpService by browsing to the Bin Directory and clicking the Dll & you will also need to set the Contract to IEmpService by doing the same. You will notice I have two endpoints as well, one for the Service Contract endpoint and the other is MEX for the announcement of the Service.
This GUI is just and abstract of the .config file so here is the full code for that below
Test your Work
Now that you have done all that you can certainly test your work even before it goes into IIS to be hosted outside of Visual Studio. You can do that by using a nifty tool inside your Visual Studio Command Promt called “WCFTestClient.exe” and passing it the URI of your Service, but the easiest way is just Press F5 in Visual Studio and it will do all that for you LOL, we will use the tool later on when we move it to IIS. Just for giggles sake, I will just test the GetAllEmp for this blog. IF you want to see more come see my VS Live 360 Session that i spoke about on my blog here.
As you can see above, we have the Service exposed and all the methods, and we invoked one them and we can see the results there.
Move this to IIS – Hosting this Service in IIS
Now unless you want to have to start up Visual Studio every time you need to use this WCF, what you need to do is host this in IIS or you can do TCP or NetBinding but We are all working in SharePoint for this blog so we need IIS. Its simple, just add a new Web Site Project to this Visual Studio Solution; for this we will use the WCF Service Template. This too will add a sample implementation again in the App_Code folder which includes Service1.cs and ISerivce1.cs. Delete those again & and set a reference to the WCF Service Library DLL from the first project. If you are asking why we deleted both those files again its because we will be using the files from our first project referenced in our .svc page. Your solution may look like this now.
You will also have a file called Service.svc, that we will rename to something more descriptive of our Service we are trying to expose, I name mine NWindEmployee.svc Once you have renamed it, you will need to set the Service to the Project Namespace and remove the codebehind attribute since we wont need it.
Save your file, and now its time to modify the Web.config file this time to point to our Service Reference, this is JUST like we did for the App.config so I wont bother going into detail here, Ill just include the code snippet for the web.config
Make this available in IIS
Now you can test this guy again using the Test Client, but the hosting into IIS Is quite simple and Ill just show you that. All you have to do is either (1) Publish the site into a new Directory for PROD consumption (2) just copy and paste the folder where you want on a box running IIS and create a new Web App / Site pointing to that location and setting a FQDN and as in my case a port number where it can be consumed.
As you can see from the image above, I have a site with my FQDN port 57758 where it is located. Navigating to that site will yield
and clicking on the end point will show the WSDL
Now we will use SharePoint Designer to Create an External Content Type (ECT) that will consume this WCF.
Open up SPD2013 and create a NEW ECT, Name it apprpriately, as Ive done here calling it BlogPostWCFExample, Next click on “External SYtems” link to discover, its the last link on imabe below
Once you have done that click Add Connecation
Then Select WCF from the Drop Down…and click OK then
As a reminder we are using User Identity but i wanted to point out we just as easlily could be using Secure Store, if we were we would use the Target App ID as seen here
but for my example you will see it is more like this
When all of that is complete you will be able to see all the methods exposed, see below
Next you can right click on them individually and create the respective Methods, this has been done many times so i wont belabor it. But once you are complete you should have an ECT like so
and you should be able to create a List like so…
So, that is it for my Part 1 of 3, I will tackle the next one later tonight or tomorrow. Cheers, hope this helps, let me know your thoughts, critiques, etc.