Microsoft – SQL Server / Internet Information Server (IIS) – OData – Service

What is OData?

Here is a buzzword compliant definition for OData

http://www.odata.org/introduction
The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today.

OData does this by applying and building upon Web technologies such as HTTP,Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores.

The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.

OData is consistent with the way the Web works – it makes a deep commitment to URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with those resources (just like the Web). This commitment to core Web principles allows OData to enable a new level of data integration and interoperability across a broad range of clients, servers, services, and tools.

Introduction

So here I am, a lowly DBA trying to support some development folks who will like to use new plumbing to serve data from a database.

Problems

Yes, always problems.  In this case some of our Database Table’s structural changes are not being automatically exposed in our OData XML Payload.

And, so what choices but a quick read up of OData.

Corollary

This short write-up is based on viewing the excellent videos available @

Beginner’s Guide to WCF Data Services
http://msdn.microsoft.com/en-us/data/ee720180

Specifically:

Getting Started With OData Part 1: Building an OData Service
http://msdn.microsoft.com/en-us/data/gg601462

Pre-requsites

  • Database – Microsoft – SQL Server – Database ( as this is just a mock-up, we have just a few tables, but we made sure that they we have actual primary and foreign keys — that is, the tables are related and displaying records will show links to dependent records)
  • Development Environment – Visual Studio 2010 – Later is better, but at least Visual Studio 2010 with SP 1.

Development – Visual Studio

New Project

Launched Visual Studio and create a new Project.

  • Menu Items -> File \ New Project
  • The list of Templates are listed
  • In the left panel from the list of Templates, select Visual C#, Web
  • In the right panel, select “ASP.Net Empty Web Application
  • In the Location entry field, make sure to indicate the proper folder you will like to keep your files
  • Ensure that the “Create directory for solution” is checked
  • Click OK to continue

VisualStudio - NewProject

Add Data Layer (ADO.Net Entity Data Model)

Thankfully Microsoft’s implementation of OData is able to use Entity Framework.  Thus, we can accelerate our RAMP up time by relying on Entity Frameworks as a conduit to our Database.

  • Click on the menu items Project \ “Add New Item”
  • In the “Installed Templates” panel, select Visual C# \ Data
  • In the right panel, select “ADO.Net Entity Data Model”

VisualStudio - AddADO-NetEntityDataModel

Configure “ADO.Net Entity Data Model” – Entity Data Model Wizard

Once you select “ADO.Net Entity Data Model”, use the wizards to configure your Data Source and Data Elements.

Choose Model Contents

  • Choose “Generate from database

EntityDataModel-ChooseModelContents_v2

Choose Your Data Connection

  • Select existing Data Connection or configure a new one

EntityDataModel-ChooseYourDataConnection-v2

Choose Your Database Objects

  • In the “Choose Your Database Objects”, select Tables \ Views \ Stored Procedures
  • Ensure that you are OK with the name suggested for your “Model Namespace“; by convention for MS SQL Server Database the name suggested is your Database Name suffixed by the literal “Model

EntityDataModel-ChooseYourDatabaseObjects

Entity Data Model – Source Files

The Wizard auto-generates the following files :

  • Model1.edmx – I will suggest that you change the name from the generic given name of Model1 to  something more suiting of your DBName or function.  In our case, we changed the file name to ModelDBLab.edmx

What is Windows Communication Framework (WCF)?

OData services are published via Windows Communication Framework (WCF Data Service).

What is WCF?

http://msdn.microsoft.com/en-us/library/ms731082.aspx
Windows Communication Foundation (WCF) is a framework for building service-oriented applications. Using WCF, you can send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS, or it can be a service hosted in an application. An endpoint can be a client of a service that requests data from a service endpoint. The messages can be as simple as a single character or word sent as XML, or as complex as a stream of binary data.

Add New Service – WCF Data Service

The next step was and is a bit tricky for me.  Basically, via Visual Studio, I need to create a WCF Service.

So I clicked on the menu items “Project\Add New Item..”.

But, upon reviewing the list of Templates displayed, I see no mention of “WCF Data Service“.

“WCF Services” are not one of the often use Templates and so we have to “suggest” it.  We do do by entering “WCF” in the entry box on the top right hand corner.

VisualStudio - WCFDataService

Upon entering WCF, WCF Templates are displayed.

Before it went bye, bye … I quickly chose “WCF Data Service” and clicked on the “Add” button.

Bind WCF Data Service

There are a couple of code changes we have to make to bind our WCF Data Service to our Entity Framework Service.

The source code that we have to modify is the file generated when we added the WCF Service.  Conventionally, the file’s name is WCFService[nnn].svc.



namespace WebApplicationDBLab
{
    public class WcfDataService1 : DataService
                                 < /* TODO: put your data source class name here */ >
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            // TODO: set rules to indicate which entity sets and service 
             operations are visible, updatable, etc.
            // Examples:
            // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
            // config.SetServiceOperationAccessRule("MyServiceOperation"
                                                    , ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion
                 = DataServiceProtocolVersion.V2;
        }
    }
}

Replace the DataService Line

public class WcfDataService1 : DataService< /* TODO: put your data source class name here */ >

And, have it reference your Entity Framework class.

  • Our Entity Framework class’s name is listed in the Model1.Designer.cs source file
  • Specifically the class name noted in the “public partial class DBLabEntities ObjectContext” ( DBLabEntities )

If you compile your source code \build your project prior to making the change documented above, you will get the error pasted below:


Type Expected

Expose Entities

In the “InitializeServices” method, indicate which Entity Framework entities you will like exposed.

To expose all entities

Syntax:

config.SetEntitySetAccessRule("*", <EntitySetRights>);

Sample:

config.SetEntitySetAccessRule("*", EntitySetRights.All);

Explanation

  • The asterisks indicate that all entities should be exposed

 

To expose explicitly listed entities


Syntax
:

config.SetEntitySetAccessRule(<entity set>, <EntitySetRights>);

 

Sample:

config.SetEntitySetAccessRule(clients, EntitySetRights.AllRead);
config.SetEntitySetAccessRule(salesPersons
                              , EntitySetRights.AllRead);
salesPersons config.SetEntitySetAccessRule(orders
                                      , EntitySetRights.AllRead);

Explanation:

  • Note that the first argument is the “Entity Set”.
  • To determine the entity set’s name access the edmx file and select each entity and note the set’s name from the Property window.
  • Note that the Entity Set’s name is case-sensitive

Build Project

Access the menu items “Build” \ “Build Project” to build the Project.

Test WCF Service

Within our IDE, we can quickly see how far along we are coming.

Within Solution Explorer, Access the WCF Service file.  Right click on the file and from the  drop-down menu, select “View in Browser”.

WCFService - Main

The screen shot above shows our entities…clients, orders, and salesPersons.

Test WCF Service – View clients

To view all clients:

Append out entity-set to the base URL.

Syntax

http://localhost:42877/WcfDataService1.svc/<entity-set>

Sample

In our case, the working URL is http://localhost:42877/WcfDataService1.svc.  And, the entity set name is clients.

Hence to display all clients, let us use:

http://localhost:42877/WcfDataService1.svc/clients

Within IE, our default browser, the output displayed was not what we expected…

WCFService - IE Output

So we know we needed help…By way of Google.

But, what to Google on?

What about entering:

odata not showing xml in IE

 

Good, reliable help came right away from Microsoft’s own Vitek Karas:

http://stackoverflow.com/questions/10267396/odata-service-entitys-data-not-showing-up-in-browser

This behavior depends on the actual browser you use. If it’s IE you will see so called “Feed view” since the payload is a valid ATOM feed. To see the underlying raw XML go to Internet Options -> Content -> Feeds and Web Slices Settings and uncheck the “Turn on feed reading view”. Then restart the browser, you should see the raw XML from the service.

BTW, Vitek Karas is a developer in Microsoft’s WCF Data Services group and he blogs extensively @ http://blogs.msdn.com/b/vitek/.

Once we followed Vitek Karas’s helpful guidance, we are able to view XML output:
WCFService - IE Output (Corrected)

Reflect Database Changes  (GUI)

As discussed earlier, my original interest was sparked because of an error we experienced when we changed our Client code and attempted to access a couple of new columns being exposed by our database view.

So how do we update our Entity Framework model and have it capture database changes and avail them to our WCF Data Service.

Well ..

  • Within Visual Studio using Solution Explorer, access the .edmx file
  • Once the edmx file is shown and is the active document, right click on the Document
  • From the drop-down menu, select “Update Model from Database….”

Reflect Database Changes  (Command Line)

Once your project is in a place, you can also track Database Schema changes through Helper tools.

We will describe available tools below.

Reflect Database Changes  – Command Line – edmgen2

What is edmgen2?

http://archive.msdn.microsoft.com/EdmGen2
EdmGen2 is a command-line tool for the Microsoft ADO.NET Entity Framework. The tool can be used as a replacement for the EdmGen.exe tool that ships with the .Net framework 3.5 SP1. EdmGen.exe can only read and write the CSDL, SSDL & MSL file formats. However, EdmGen2.exe can read and write the EDMX file format used by the Visual Studio design tools. Additionally, EdmGen2.exe can translate between EDMX and CSDL, SSDL & MSL formats, and the source code can act as examples on using the tooling APIs defined in the System.Data.Entity.Designassembly. 

Download edmgen2

Edmgen2 is remarkable stable for an RTM Product.  Since its release there does not appear to be have being any bug updates.

It is available for download @
http://archive.msdn.microsoft.com/EdmGen2/Release/ProjectReleases.aspx?ReleaseId=4216

Compile\Build edmgen2

Unfortunately, edmgen2 is not packaged as a compiled Executable.  It is a compressed (zip) file and you will have to launch Microsoft Visual Studio and compile it.

Use edmgen2 to generate EDMX File



rem SQL Instance is named DBHR\QA
set datasourceserver="DBHR\QA"

rem generate Entity Framework
edmgen2.exe /ModelGen "Data Source=%datasourceserver%; Initial Catalog=DBLab; Integrated Security=SSPI" "System.Data.SqlClient" "DBLab" 2.0

Explanation:

  • Database Server’s name is set in code and saved in Environment Variable (datasourceserver)
  • Database name is DBLab
  • Using Integrated Security — Integrated Security=SSPI
  • Provider = System.Data.SqlClient
  • Model name = DBLab
  • Entity Framework Version = 2.0
  • The resultant file is named DBLab.edmx

Conclusion

Obviously, there is a lot more to OData..

  • I am struggling with Error Logging. I intentionally broke things a few times, but have yet to be able to capture and log error messages.  The error messages are just not showing up in my defined log file
  • Deployment Choices
  • Accessing our service from Client Applications

Refererences

References – OData

  1. OData
    http://www.odata.org/introduction

References – Tutorial

  1. Getting Started With OData Part 1 – Building an OData Service
    http://msdn.microsoft.com/en-us/data/gg601462
  2. Deploying OData Services using IIS & SQL Server
    http://msdn.microsoft.com/en-us/data/gg192995.aspx
  3. Using Microsoft ADO.NET Data Services
    http://msdn.microsoft.com/en-us/library/cc907912.aspx
  4. An Introduction To RESTful Services With WCF
    http://msdn.microsoft.com/en-us/magazine/dd315413.aspx

References – API

Blogs

Tools – Edmgen2

References – Service Error Handling

  1. Why is my Edit Wcf Confiuguration Option
    http://stackoverflow.com/questions/4089507/why-is-my-edit-wcf-configuration-option-missing
  2. WCF Data Service – Where are the Server Logs  http://social.msdn.microsoft.com/Forums/en-US/8275b410-67a2-4a4c-9458-844cba670e43/wcf-data-service-where-are-the-server-logs

One thought on “Microsoft – SQL Server / Internet Information Server (IIS) – OData – Service

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s