Friday, March 15, 2013

Flex your ODATA

Silly humor moment,
 When I first heard the term ODATA, I thought they were talking about an intimate moment between Lt Data and the Star Trek Enterprise Computer, I said WHOA! there partner, TMI dude. LOL!

But ODATA stands for Open Data Protocol ODATA is a web protocol used for querying and updating your data that may exist in what's called silos in your apps. With ODATA you can utilize and enhance other technologies such as HTTP, ATOM Publishing Protocol, XML and JSON. You can read more about ODATA at  or view Scott Hanselman's talk

OData Basics - At the AZGroups "Day of .NET" with ScottGu

This post is about developing an Apache Flex mobile application that talks with a SQL Server Express database via ODATA. We're going to build a simple Project Manager application.

What! No LightSwitch!!! But But I thought you were a LightSwitch developer,  hold your tater tots there partner. You can still use LightSwitch to create your ODATA service and manage your data.
But for this post I'm going to show you another way by using Visual Studio Express 2012 for Web.

Why am I using Flex instead of LightSwitch, because I wanted to see if it could be done.

I've been developing Flex/Flash applications since Flash Builder, formerly known as Flex Builder version 1.1, so I have all the necessary components on my MacBook. And I just learned I could build an ODATA service using Visual Studio 2012 Express which I will pass on to you now.

There are other ways to connect your Flex application to SQL Server, such as WebORB for .NET from but that is beyond the scope of this tutorial.

NOTE: If you have Visual Studio 2012 Pro you already have WCF Data Services installed.

My Setup on my wi-fi home network, I have a MacBook Pro OSX 10.7.5 and an ACER Aspire Ultrabook Windows 7:

Hosted on my ACER Ultrabook
Hosted on my MacBook Pro
Useful but not required - if you are on Windows you can download the open source application LinqPad to help with your ODATA services.

What this tutorial is about:
A simple Project Management application that we can run on our iPhone or iPad. If you have an Android phone this should work also, I only have an iPhone though.

I'll try and keep this simple but make the project easy to expand upon. We will use straight out of the box components and technologies from Flex and Actionscript. We won't use any special frameworks like the excellent Inversion of Control Frameworks such as RobotLegs, Swiz, Mate, Parsley or the Event helper library AS3Signals. Maybe in another post but for now I'll keep this simple, I only provided the links for you to browse at your convenience if wish to learn more.

Setup our development environment:
 Download and install the items mentioned above if you don't already have them installed. I won't go over how to install all the above in this tutorial, each of the resources above have good documentation covering this.

Ok assuming you have everything installed and configured:
Let's make sure SQL Server can recieve requests from other machines on our home network, this should be the same setup for your company's internal network, consult your network admin about this.

WARNING! If you are following this tutorial from your Corporate Network, please check with your Network Administrator before making any of the following changes. You probably won't have the necessary privileges to complete this but make sure you know what you are doing first if you do. Your Network Administrator may have a better solution.

On your Windows machine


Click the Windows Start button then right click Computer, select Manage.
  1. Expand the SQL Server Configuration Manager located under Services and Applications.
  2. Click Protocols for SQLEXPRESS.
  3. Enable Shared Memory, Named Pipes, and TCP/IP
  4. Double click TCP/IP then click the IP Addresses tab
  5. Locate IPALL, if there is an entry for TCP Dynamic ports delete this, leave it blank. You need a hard coded port to be able to setup the Windows Firewall Inbound Rule.
  6. For TCP Port set it to 1433, SQL Server usually listens on port 1433. You will get an alert that no changes will take effect until SQL Server is restarted
  7. Click OK to close this dialog.
  8. Click SQL Server Services, click SQL Server then click the Restart button in the menu bar or right click and select Restart
  9. Make sure SQL Server Browser is running.
  10. You can now close Computer Management
Setup an Inbound Rule in Windows Firewall
Click the Windows Start button and select Control panel, then open Windows Firewall
  1. Click Advanced Settings
  2. Click Inbound Rules
  3. Under Actions select New Rule
  4. In the Name field I chose to give this rule the name SQL SERVER
  5. Make sure the Enabled box is checked
  6. Make sure the Allow the connection box is checked
  7. Click the Protocols and Ports tab
  8. For Protocol Type select TCP
  9. Local Port - select Specific Ports and type in 1433 or whatever you typed for IPALL
  10. Remote Ports -  select Specific Ports, and type in 1433 or whatever you typed for IPALL
  11. Under the Advanced tab, for Profiles I deselected Domain and Public. I only have Private checked.
  12. Click OK to close this dialog
  13. Click New Rule again
  14. In the Name field I chose to give this rule the name SQL SERVER BROWSER
  15. Make sure the Enabled box is checked
  16. Make sure the Allow the connection box is checked  
  17. Click the Protocols and Ports tab
  18. For Protocol Type select UDP
  19. Local Port - select Specific Ports and type in 1434
  20. Remote Ports -  select Specific Ports, and type in 1434
  21.  Under the Advanced tab, for Profiles I deselected Domain and Public. I only have Private checked. 
  22. Click OK to close this dialog
  23. Close the Windows Firewall with Advanced Security screen
  24. Close Control Panel
Now let's setup our Database tables

We will use three tables Projects, Clients, Technologies and two look up tables Project_Technologies and Client_Projects. 

NOTE: You could setup your database from within Visual Studio using the Entity Data Model Designer but that creates the database in the app data and this is not covered by our Firewall Rule.

  1. Open SQL Server Management Studio and login to your server
  2. Right click the Databases folder icon and select New Database
  3. For Database name I gave mine the name of MyProjects
  4. Click OK to create the DB
  5. Expand the db and right click the Tables folder icon and select New Table
  6. Create the table with the following structure:
    • projid (PK, int, not null) Identity set to YES
    • project_name (varchar(50), not null)
    • start_date (date, not null)
    • end_date (date, null) 
    • notes (text, null)  
  7. Save the table with the name Projects
  8. Create a second table with the following:
    • clientid (PK, int, not null) Identity set to YES
    • client (varchar(50), not null)
    • notes (text, null) 
  9. Save this table as Clients
  10. Create a third table with the following
    • techid (PK, int, not null) Identity set to YES
    • technology (varchar(50), not null)
    • notes (text, null)
  11. Save this table as Technologies
  12. Create our two look up tables
    • 1st table for our Clients and Projects relationships
    • id (PK, int, not null) Identity set to YES
    • projid (int, not null)
    • clientid (int, not null)
    • Save this table as Client_Projects
    • 2nd table for our Projects and Technologies relationships
    • id (PK, int, not null) Identity set to YES
    • techid (int, not null)
    • projid (int, not null) 

  13. Next expand the Database Diagrams folder
    Create a new diagram
    Add all the tables and set the appropriate relationships.

    Client (One) - Client_Projects (Many)
    Project (One) - Client_Projects (Many)
    Project (One) - Project_Technologies (Many)
    Technology (One) - Project_Technologies (Many)

    You should have something similar to the following:

  14. We now need to give permission to the user who will be used to connect to our project database.
  15. Open the Server-Security folder, then select Logins.

    If NT AUTHORITY\NETWORK SERVICE is NOT present follow Step 16. If NT AUTHORITY\NETWORK SERVICE IS present skip to step 17.
  16. Right click Logins, select New Login

    Click Search

    1. The easiest way I've found is to click Advanced
    2. Then click Find Now
    3. Locate and select Network Service

    4. Click OK
    5. Click OK again
    6. Select User Mapping
    7. Check the box next to MyProjects, or whatever you named the db in this tutorial
    8. Then check the box next to db_datareader in the Database role membership for : MyProjects

    9. Click OK
  17. If NT AUTHORITY\NETWORK SERVICE IS present following Steps 6 - 9 above to map the user to our MyProjects database.

Setup Our ODATA Service

 Open Visual Studio for Web or Visual Studio Professional and create a new
 WCF Service Application.

NOTE: I'm choosing the C# version, but the Visual Basic version will work as well.

I named this new service application ProjectManagerWCFService
Delete the default IService1.cs and Service1.svc, we will create our own.

Right click the ProjectManagerWCFService and select Add then WCF Data Service 5.3, I named this new Data Service ProjectManagerWCFDataService, click OK.

NOTE: If  WCF Data Service 5.3 is not present, select Add New Item and look for this library under your language, then Web. Or you may need to install it.

Next we need to add a data model.
Right click ProjectManagerWCFService again, click Add and choose ADO.NET Entity Data Model.

NOTE: If  ADO.NET Entity Data Model. is not present, select Add New Item and look for this library under your language, then Data.

I named this ProjectManagerDataModel
You should now see the following screen shot:

I chose the default, Generate from database.

  1. Click Next.
  2. Click New Connection.
    You should see the following screen shot:

  3. Select your Server - HINT: If the server is on the same machine, just put a dot in the Server name field, this is a shortcut for localhost.
  4. I left the default Windows Authentication
  5. Select your database
  6. You can test the connection if you want at this point or just click OK
  7. I accepted the defaults of this screen.
  8. Click OK
    The following screen will be shown

    : Make a note of the name I've circled in the screen shot, we will need this later.

  9. Click Next
  10. Expand the Table, dbo disclosure buttons 
  11. Check Clients_Projects, Clients, Project_Technologies, Projects and Technologies
  12. Accept the defaults and click Finish
  13. You may receive a warning message that "Running this template could possibly harm your computer, I chose to ignore". You have to make your own decision.

  14. OK, if the template above did not generate the connector lines? How do I correct this? Well the reason this will happen is if I forget to set the relationships in the database as I described in step 13. But not to worry, just go back to the database and follow step 13,  then in Visual Studio update the model based on the database.

    You could just create the relationships in the Entity Model Designer by Right clicking the Client Model, make sure your clicking in the title area of the Model box. Choose Add New then select Association. Create a One to Many relationship with the Client table table on the one side and Client_Projects on the many side. But be aware this is only for the data model not the database, so if there are any changes in the database you will need to update the model any ways so I suggest to just make the changes in the database.

  15. Open the ProjectManagerWCFDataService.svc file
  16. In the line
    Public class ProjectManagerWCFDataService : DataService< /* TODO: put your data source class name here. */ > replace the /* TODO: put your data source class name here. */ comment with the name I mentioned above MyProjectsEntities

    This line should now look like the following
    Public class ProjectManagerWCFDataService : DataService<MyProjectsEntities> 
  17. We need to make one more change to this file. Uncomment the first config line
    config.SetEntitySetAccessRule("MyEntitySet", EntitySetRights.AllRead);
    replace "MyEntitySet" with an Asterisk, it should look the following
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    This will give us unrestricted read access, you may need to change this before releasing to production.
  18. OK now we can now test the service.
  19. Click the the green arrow button for Internet Explorer or the run button in Visual Studio.
  20. If all goes well you should see the following:

  21. I'll cover running queries against this service a little later. 
  22. Under the wwwroot create a new folder named ProjectManager
  23. We now need to create an application named ProjectManager in IIS Manager
  24. Open IIS Manager, click the Application Pool, I like to create a new Application Pool for each of my apps to isolate them from other processes. I'm not a NetworkAdmin so let me know if this is wrong and why.
  25. Click Add Application Pool, for the name I chose Project Manager
  26. Set .NET Framework version to .NET Framework v4.0.3019 or higher
  27. Managed pipeline mode:  I selected Integrated 
  28. Leave the check box for Start application pool immediately checked
  29. Click OK
  30. Now highlight the App Pool you just created 
  31. Even though we set the .NET Framework to v4.0, the Managed .NET Framework version default is 2.0, we need to change this to 4.0 as well.

  32. Change the highlighted .NET Framework from v2.0 to v4.0
  33. Right click Default Web Site then select Add Application
  34. Click the Select button
  35. Select the Application Pool we just created
  36. Click OK
  37. For Alias I just type PM
  38. Click the button with the ... next to the Physical Path field
  39. Navigate to the ProjectManager folder we created in Step 22.
  40. Click OK
  41. You can now close IIS Manager.

    : If you are on a public server, check with your Network Admin. This tutorial is for localhost development. Be aware additional security settings may need to be set on a public server.
  42. You have to complete this next part as the Administrator. Launch Visual Studio as Administrator.
  43. We need to set up publishing.
  44. Click Build in the menu bar, then select Publish Selection or Publish in Visuaal Studio.
  45. Select Profile, Select or Publish Profile. In the drop down, select new.
  46. For Profile Name type localhost|  
  47. Connection
    Service URL: localhost 
    Site / application: Default Web Site/ProjectManager
  48. Click the Validate Connection Button you should see  
  49. Click Next 
  50. Settings: In the Databases section select the drop down under MyProjectEntities make sure the connection strings are correct.
  51. Select Next
  52. Click the Start Preview button or you can click the Publish button.
  53. If all goes well open a browser on the web server and type
  54. You should see the same screen as Step 20.
  55. OK now if your setup is like mine for your MAC, you need to know the ip address of your Windows machine, the web service host.
  56. Open a browser on your MAC and type your ip address, mine is
  57. You should see the same screen as Step 20.
  58. You can now close Visual Studio.

        Setup Flash Builder 

        My PowerApps Videos

        Demo of using the PowerApps Office365Users Connector and Microsoft Graph to surface User information from Azure Active Directory with the Us...