Microsoft – WCF/OData – Deploying Services to IIS

Introduction

There are a couple of paths we can take towards deploying our web services.

Prerequisite – Msdeploy

Introduction

I really enjoyed realizing the benefits of Microsoft MSDeploy.  It allows us to deploy to the local server and to remote hosts, as well.

Here is a list of arguments that are supported:

 Argument  Meaning
/T Simulates deployment
/Y Actual deployment
/M Destination Name of remote computer
/U Destination user name
/P Destination password
/A Authentication – NTLM/Basic

Visual Studio Publishing

We can publish from Visual Studio and here are the steps to take:

  • Launch Visual Studio
  • Load the Project
  • Access the “Solution Project”
  • Select the project, make sure to select the Project and not the Solution
  • Right click on your selection
  • And, from the drop-down menu, select “Publish…”

Publish Web Application – Profile

The first screen upon launching “Publish Web Application” is the screen pasted below:

publishWebApplication

As we do not have existing profiles, we will access the drop-down menu and select the “New” option.

Publish Web Application – Profile

In the screen below, we have chosen the name “SoftApps”; for our Profile.

publishWebApplication - NewProfile

Publish Web Application – Connections

The connection method is the most important screen.  It in we will choose the publish method, the service URL, and the Site/Application.

To just show how important it is, I burnt up a couple of hours, upon entering an incorrect “Site/application” in the screen below.

publishWebApplication - Connection - WebDeploy

The right choice for “Site/application” is:

publishWebApplication - Connection - WebDeploy (good choice)

The important distinction been that the former choice has SoftAppsWCF and the corrected choice has “Default Web Site/SoftAppsWcfService“.

IIS has a default web site known as “Default Web Site”.  And, you likely want to use that one.  On the other hand, If you are an expert IIS Administrator, you very well might have created a new web site that you want to indicate here.

Publish Web Application – Settings

The next important screen is the “Settings” screen.  In it, we choose between “debug” and “release”.

During your first iterations, I will suggest that you choose the Debug choice.

The next important decision is confirming your Database Connection Strings.  Please keep in mind that if you have the “Use this connection string at runtime (update destination web.config)” checked, you may end-up over-writing previously carefully tailored DB connection profiles on targeted hosts.

publishWebApplication - Settings

Publish Web Application – Preview

The preview screen is next.

publishWebApplication - Preview

Msdeploy

Background

Pasted below is the output from *.deploy.cmd:   

We unsuccessfully targeted a remote host.



wcfServiceSoftApps.deploy.cmd /T /M:WebServerRemote

SetParameters from: "WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='WcfServiceSoftApps.zip' -dest:auto,computerName="WebServerRemote",includeAcls="Fal
se" -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disa
bleLink:CertificateExtension -setParamFile:"WcfServiceSoftApps.SetPar
ameters.xml" -whatif
Info: Using ID '2158a280-82ae-462d-9117-683b7c2aaf35' for connections to the rem
ote server.
Error Code: ERROR_DESTINATION_NOT_REACHABLE
More Information: Could not connect to the remote computer ("WebServerRemote"). On 
the remote computer, make sure that Web Deploy is installed and that the required 
process ("Web Deployment Agent Service") is started.  Learn more at: 
http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_DESTINATION_NOT_REACHABLE.
Error: The remote server returned an error: (404) Not Found.
Error count: 1.

… due to the error code (ERROR_DESTINATION_NOT_REACHABLE), I realized I needed to do a couple of things:

  • Install & configure WebDeploy
  • Configure hosts firewall rules to allow network port connections

Msdeploy Download Matrix

I think you can go with the latest version supported by your OS.

And, for most people starting from Windows XP to Windows 2008 that will be MSDeploy version 3.

Version URL OS Version
Version 1.1 http://www.microsoft.com/en-us/download/details.aspx?id=24709 (x86)http://www.microsoft.com/en-us/download/details.aspx?id=4148 (x64) Windows 7, Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP
Version 2 http://www.microsoft.com/en-us/download/details.aspx?id=25230 Windows 7, Windows Server 2003 Service Pack 2, Windows Server 2008, Windows Server 2008 R2 SP1, Windows Vista Service Pack 2, Windows XP Service Pack 3
Version 3 http://www.microsoft.com/en-us/download/details.aspx?id=30436 Windows 7 Professional, Windows Server 2003 Service Pack 2, Windows Server 2008, Windows Server 2008 R2 SP1, Windows Server 2012 Release Candidate, Windows Vista, Windows XP
Version 3.5  http://www.iis.net/downloads/microsoft/web-deploy

Download Msdeploy

Download the latest available MSDeploy for your deployment host.  As I often say, in terms of “Enterprise” Development and Deployment roadmap, Microsoft’s train is really a high speed train.  Yesterday it was called MSDeploy, Today is called WebPI, and next week it will yet be called something else.

So as I am on yesterday’s timeline, I will stay with MSdeploy and download it from http://www.microsoft.com/en-us/download/details.aspx?id=30436.

The version is v3.

As an aside and as a satire, I am trying to play music on this MS Windows 2012 box, and I do not even know how to get to MS Media Player.  So what to do, but yet again Goggle for help!

Salvation came from David Trounce:

Add Feature … User Interface and Infrastructure … Desktop Experience. This is similar to Server 2008 R2.

http://social.technet.microsoft.com/Forums/windowsserver/en-US/a816bbdc-5a9c-4cf9-aa8c-090a61da3e3a/need-to-install-windows-media-player-on-windows-server-2012-release-candidate-many-apps-requires?forum=winserver8gen

… Must not be my time, as still no WMPlayer.  Will just go back to Apple’e iTune, as that is installed and working.

About an hourly later, as I closed some of the multiple screens that I had left opened, discovered that I needed to restart.

This was indicated as I closed “Sever Manager”.  And, noticed the information detailed below:

ServerManager-IIS-RestartPending

That might be why no WindowsMediaPlayer.

Install Msdeploy

On the targeted host, here is a sample DOS Batch script that does the following:

  • It installs WebDeploy_2_10_x85_en-US.msi
  • The destination port is 8080 and the targeted Virtual Directory is /MSDeployAgentService2
  • And, it restarts the “Web Deployment Agent Service” (MsDepSvc)


rem msiexec /I <msi_filename> /passive ADDLOCAL=ALL LISTENURL=http://+:8080/MsDeployAgentService2/
msiexec /I WebDeploy_2_10_x86_en-US.msi /passive ADDLOCAL=ALL LISTENURL=http://+:8080/MsDeployAgentService2/

net stop msdepsvc /y
net start msdepsvc

Validate Msdeploy

Once the deployment package is prepared, test it out by issuing the *.deploy.cmd and passing in the /T command argument.

The *.deploy.cmd does a few important things:

  • It reads the *.SetParameters.xml file and one is able to change the Web Application Name, the Physical Path, and DB ConnectionString

When I issued “WcfServiceSoftApps.deploy.cmd /T” to test out my install, I get the sampled output shown below:


SetParameters from: "WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='WcfServiceSoftApps.zip' -dest:auto,includeAcls="False" -verb:sync -disa
bleLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateE
xtension -setParamFile:"WcfServiceSoftApps.SetParameters.xml" -whatif

Info: Adding directory (Default Web Site/SoftAppsWcfService\bin).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\EntityFramework.dll).

Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Edm.dl
l).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.OData.
dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Servic
es.Client.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\Microsoft.Data.Servic
es.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\System.Spatial.dll).
Info: Adding file (Default Web Site/SoftAppsWcfService\bin\WcfServiceSoftApps.dl
l).
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.Context.tt)
.
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.edmx.diagra
m).
Info: Adding file (Default Web Site/SoftAppsWcfService\ModelSoftApps.tt).
Info: Adding file (Default Web Site/SoftAppsWcfService\packages.config).
Info: Adding file (Default Web Site/SoftAppsWcfService\Service1.svc).
Info: Adding file (Default Web Site/SoftAppsWcfService\WcfDataServiceSoftApps.sv
c).
Info: Adding file (Default Web Site/SoftAppsWcfService\Web.config).
Info: Adding ACL's for path (Default Web Site/SoftAppsWcfService)
Info: Adding ACL's for path (Default Web Site/SoftAppsWcfService)
Total changes: 17 (15 added, 0 deleted, 2 updated, 0 parameters changed, 4302476
 bytes copied)

Actual Deploy

Actual deploy by passing in the /Y argument.  To truly deploy against a host named Sunlight we will issue:

WcfServicesSoftApps.deploy.cmd /T /M:Sunlight

IIS – Backup IIS Configuration

Backup IIS Configuration

I later found out that IIS as a capable and easily accessible tool for backing up IIS Configuration.

I will say use it before mucking up IIS too much.

Sample backup invocation

%windir%\system32\inetsrv\appcmd add backup 20131123_b4WcfInstall

List backups

%windir%\system32\inetsrv\appcmd.exe list backup

List backups

I was curious as to where backups are saved and so inquisitively goggled for where.

Thankfully did not over indulge as Mike Volodarsky’s blogged about same – Most Important AppCmd Commands: Backing up and restoring IIS7 configuration –  http://mvolo.com/most-important-appcmd-commands-backing-up-and-restoring-iis7-configuration/.

And, so I now that I need to do backups via appcmd and that they are stored in the C:\windows\system32\inetsrv\backup.

inetsrv-backup-listfolders
And, also importantly, I should occasionally back that folder up to an external storage.

IIS – Application Pool

Introduction

It is possible that this is not actually needed, but I think it is best to create or identify an appropriate Application Pool.  And, then create and seed a Virtual Directory.

Create Application Pool

Before now I had stayed in the shadows of Microsoft .Net v2.  And, so I know I need a new v4.0 Application pool:

appPool

Please keep in mind that there are only two available baseline Frameworks:

  • .Net Framework v4.0.30319
  • .Net Framework v2.0.50727

I know that Windows Communication Framework was introduced in Microsoft .Net 3.5 and so I know needed the v4.0 version.

Even the lure of an accurate blog post is not enough to lure me into digging more into the difference between the Classic and Integrated Managed Pipeline Modes.  I am sure I will sooner than later run into a problem that will force me to learn.

IIS – Virtual Directory

Add a new Virtual Directory

Once you have decided on an Alias and Physical Path, please indicate as much.

But, more importantly click on the “Test Settings…”and validate your choice.

AddVirtualDirectory

Msdeploy – Helpful Hint

Attempted MSDeploy again, by issuing “WcfServiceSoftApps.deploy.cmd /y”..

And, here is the output I received.



U:WcfServiceSoftApps.deploy.cmd /y 
SetParameters from:
"U:\WcfServiceSoftApps.SetParameters.xml"
You can change IIS Application Name, Physical path, connectionString
or other deploy parameters in the above file.
-------------------------------------------------------
 Start executing msdeploy.exe
-------------------------------------------------------
 "C:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -source:package='U:\WcfServiceSoftApps.zip' -dest:auto,in
udeAcls="False" -verb:sync -disableLink:AppPoolExtension -disableLink:ContentExtension -disableLink:CertificateExtensi
 -setParamFile:"U:\WcfServiceSoftApps.SetParameters.xml"
Error Code: ERROR_APPPOOL_VERSION_MISMATCH
More Information: The application pool that you are trying to use has the 'managedRuntimeVersion' property set to 'v2.

This application requires 'v4.0'.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_APPPOOL_VERSIOMISMATCH.
Error count: 1.

The important error informant is the line that read:

Error Code: ERROR_APPPOOL_VERSION_MISMATCH
More Information: The application pool that you are trying to use has the 
'managedRuntimeVersion' property set to 'v2.
This application requires 'v4.0'.  
Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_APPPOOL_VERSIO
MISMATCH.

Microsoft .Net Framework / OS Compatibility

I think it is important to note that you probably should think a bit about your development goals and weigh that against current deployment state.  That is, which WinOS version are you currently running and which .Net Frameworks does that OS Support.

For example, if you ‘re running MS Windows 2003, it does not support .Net Framework 4.5.

This is stated as much @ .NET Framework System Requirements – .NET Framework 4.5 (http://msdn.microsoft.com/en-us/library/8z6watww(v=vs.110).aspx).

.Net Framework 4.5 can only be installed on MS Windows 2008, 2008/R2, 2012, 2012/R2.

Review & Confirm your Visual Studio Project Targeted Microsoft .Net Framework

While we are it, let us quickly and briefly confirm which .Net Framework we are targeting:

  • Launch Visual Studio
  • In Visual Studio, Access “Solution Explorer”
  • Select the Project – Please be sure to choose the Project and not the Solution
  • Right click on the Project
  • From the drop-down menu, select the “Properties” entry
  • On the left side of the window, move up and select the “Application” tab
  • The “Target Framework” choices are availed, please review and confirm that this is the actual Framework you will like to target

SelectProject

IIS Manager – Review & Change CLR Version

Accessed IIS Manager and fumbled quite a bit with changing the targeted CLR version.

Even though I had accessed the Advanced configuration panel:

AdvancedSettings

There was still no obvious path to changing the CLR Version.

Thought a bit more, and decided to change from a simple Virtual Directory to an Actual Application.

To do so:

  • Select the Virtual Directory
  • Right click on your selection and from the drop-down menu select “Convert to Application”

Select “Convert to Application” from drop-down menu

convertToApplication

Add Application screen

Here is the initial “Add Application” screen:

addApplication-before

Application Pool 2.0 Screen

Here is the default “Application Pool” screen:

It is obviously referencing “.Net Framework version: 2.0”.

addApplication-showing-appoolv2

Application Pool 4.0 Screen

Here is us changing to our preferred custom 4.0 pool.

addApplication-showing-appoolv4

Test Settings

Here is what our screen looks like, once we have the Application Pool that we want.

addApplication-after

Test Settings

Please make sure to Test things out – Just click on “Test Settings…”.

TestConnections

Click Close.

 

And, once you are good “Click OK” and let us see…

Msdeploy – Good!

Attempted MSDeploy again, by issuing “WcfServiceSoftApps.deploy.cmd /y”..

And, here is the output I received.

msdeploy-good

IIS – Virtual Directory Configuration

Introduction

I spent the whole day trying to get this application to work.

For those who feel the same struggle, here are screen shots from a working version.

Connection Strings

ConnectionString

Authentication

Authentication

Directory Browsing

DirectoryBrowsing

Failed Request Tracing Rules

FailedRequestTracingRules

Logging

Logging

The important configuration items to note are:

 Feature Why important?
Connection Strings Lists DB Connection items
Authentication The ones we have enabled are Anonymous / Asp.Net / Basic / Windows
Directory Browsing Enabled – To list individual files – Should be disabled once you have things working well
Failed Request Tracing Files As I said I experienced quite a bit of hardship and so resorted to this to try and see what was wrong
Logging Will talk more about this in a later post

Error

Error: The server was unable to process the request due to an internal error.

You might get the error pasted above, if you do not add a couple of code snippets to your source code.

You need this class decoration statement:

 [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]

And, in your class initializeService method, you need:

 //very important as otherwise you will get the very unhelpful
            //The server encountered an error processing the request. 
            //See server logs for more details.
            //use Verbose Errors
            config.UseVerboseErrors = true;

Our new source code reads:



using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace WcfServiceSoftApps
{
    //public class WcfDataServiceSoftApps : DataService
    //< /* TODO: put your data source class name here */ >
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class WcfDataServiceSoftApps : DataService< SoftAppsEntities>
    {

        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {

            config.DataServiceBehavior.MaxProtocolVersion 
                    = DataServiceProtocolVersion.V3;

            //very important as otherwise you will get the very unhelpful
            //The server encountered an error processing the request. 
            //See server logs for more details.
            //use Verbose Errors
            config.UseVerboseErrors = true;

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

            config.SetServiceOperationAccessRule(
                                        "ApplicationListFetchAll"
                                        , ServiceOperationRights.All
                                    );

            config.SetServiceOperationAccessRule(
                            "ApplicationListFetchFiltered"
                            , ServiceOperationRights.All
                        );

        }

        [WebGet]
        public IQueryable ApplicationListFetchAll()
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered
                      (null, null, null, null, null).AsQueryable();

        }

        [WebGet]
        public IQueryable ApplicationListFetchFiltered
            (
              Guid? ApplicationTypeID
            , Guid? VendorID
            , Guid? LicensingID
            , short? PlatformID
            , String SearchTag 
            )
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered
                (
                    ApplicationTypeID, VendorID, LicensingID, PlatformID, SearchTag
                ).AsQueryable();

        }

    } // ApplicationListFetchFiltered

}

IIS – Configuration – Error – 0x80070002

Once I added the debug statements, ran into a prescient error.

Web Browser - Error - HTTP Error - Compilation - Target Framework

This error pointed me to the fact that I had stipulated for .Net Framework 4.5

IIS – Configuration – Error – 0x80070002

Thought everything is well.  But, stayed in the wilderness for most of my day.  The exact error message is.

Item  Value
Error HTTP Error – 404.0 – Not Found
Module IIS Web Core
Notification MapRequestHandler
Handler StaticFile
Error Code 0x80070002

HTTP Error 404

Web Browser - Error - HTTP Error 404.0 - Not Found

Solution:

On a MS Windows 2012

Download & Install Microsoft .Net 4.5

As my installed MS Visual Studio 2012 was configured to point at Microsoft .Net 4.5 I needed to upgrade from v4.0 to v4.5.

Install it

Install it

Configure Microsoft WCF  – ServiceModelReg.exe


C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\ServiceModelReg.exe –i

Configure Microsoft WCF Services – HTTP Activation

  • Launch Control Panel \ Programs and Features
  • Access “Windows Features”
  • In the “Select Install Type ” – Role based or Featured-based installation
  • In the “Select destination server” – Select a server from the server pool
  • In the “Feature roles” – Make sure that “.Net Framework 3.5 Features is installed” — “.Net Framework 3.5 Features” and “HTTP Activation” and “Non-HTTP Activation”
  • In the “Feature roles” – Make sure that “.Net Framework 4.5 Features is installed” — “.Net Framework 4.0/4.5 Features” and “WCF Services – HTTP Activation” / “WCF Services – TCP Activation” / “WCF Services – TCP Port Sharing”

Summary:

Aforementioned, I spent a whole day trying to get this to work.  I wish that I can say that using this or that tool gave me specific error messages per MS Windows 2012.

Yes, I know that I was having “Microsoft WCF Service – HTTP Activation” problem.

As I struggled a bit and Googled for help, I found out that I should pay more attention to my targeted Framework.

But, the time wasted was more with properly provisioning WCF for .Net 4.0/4.5.  I assumed that the one for v3.5 will suffice; and had a blind spot to the need for checking the v4.0/v4.5 package(s), as well.

All problems stayed same until I goggled specifically for MS Windows 2012 & WCF.  And, found a fellow WordPresser – Steve – Host WCF in IIS8  – Windows Server 2012 ( http://3water.wordpress.com/2012/09/05/host-wcf-in-iis8-windows-server-2012/ ).

His guidance along proper configuration of Microsoft .Net v4.0 on a MS Windows 2012 finally brought me across the line.

References:

References – IIS

 

References – IIS – Error Logging

References – IIS – WCF – MS Windows 2012

References – IIS – appcmd

References – Debugging WCF Services

.Net – Exposing SQL Server Stored Procedures via WCF/ODdata

Introduction

I wanted to get down the steps that are needed to expose Database Objects, specifically Stored Procedures via OData and Entity Frameworks (EF).

Software Requirements

Here are some of the Software requirements

  1. Visual Studio 2010/2012
  2. Microsoft SQL Server – v2005/v2008, v2008 R2/ v2012

Using Microsoft SQL Server Management Studio, Create SQL Account and grant sufficient permission

USE [master]
GO

CREATE LOGIN [ef] 
           WITH PASSWORD=N'tammie'
           , DEFAULT_DATABASE=[myAppBooks]
           , CHECK_EXPIRATION=OFF
           , CHECK_POLICY=OFF
GO

USE [myAppBooks]
GO

CREATE USER [ef] FOR LOGIN [ef]
GO

USE [myAppBooks]
GO

EXEC sp_addrolemember N'db_datareader', N'ef'
GO

EXEC sp_addrolemember N'db_datawriter', N'ef'
GO

GRANT SELECT ON SCHEMA::dbo TO [ef]
go

GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [ef]
go

GRANT EXECUTE ON SCHEMA::dbo TO [ef]
go

Using Microsoft SQL Server Management Studio, Create Stored Procedure


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_GetApplicationList_FilteringByID]') is null
begin

	exec('create procedure [dbo].[usp_GetApplicationList_FilteringByID] as select 1/0 as [undefined]')

end
go

ALTER procedure [dbo].[usp_GetApplicationList_FilteringByID]
(
   	  @ApplicationTypeID uniqueidentifier = null
	, @VendorID uniqueidentifier = null
	, @LicensingID uniqueidentifier = null
	, @PlatformID smallint = 0
	, @SearchTag  varchar(255) = null
)
as

   select *
   from   table
   where  a = 1

Create A New Visual Studio Project

I have Visual Studio v2012 installed and will launch it to create a new Application.  There are a few different types of Applications one can create within Visual Studio.  And, if one is not in lockstep with Microsoft’s fluid Application Delivery Best Practices it is easy to become easily bewildered.

More on that later.

As I will like to deliver DB data via Web Services, I will for now choose to create “WCF Service Application”.

Here are the quick steps:

  • Launch Visual Studio
  • Choose the menu items – File / “New Project…”
  • Transverse the left panel  —   select “Templates” \ “Visual C#” \ “WCF”
  • Select “WCF Services Application”.   A summary of why we want this is available to the right of each selection.  And, it reads – “A project for creating WCF Service Application that is hosted in IIS/WAS.”

WCFOData-NewProject

Add New Item – Data – “ADO.Net Entity Data Model”

Add New Item

Let us go and add new Data Item to our empty project.  The Item will encapsulate our Database Access and it will be an “ADO.Net Entity Data Model”.

Here are the quick steps:

  • Click on the menu items – “Project” \ “Add New Item …”
  • In the “Add New Item” window, on the left panel navigate to “Data”
  • On the right panel, “Select ADO.Net Entity Data Model”
  • Though quite easy to miss and syntactically un-needed, I will suggest that you make sure that you follow best practice and give a meaningful name to your edmx file.
  • We will use the name ModelSoftApps.edmx
  • Note that you can give proper name later by selecting the item from the Project Tree, right clicking on your selection, and choosing to rename

AddNewItem - ADO.Net Entity Data Model

Entity Data Model Wizard

The next step is to configure the Data Model Wizard. The first window being the “Connection Properties”.

The settings we will use are:

  • Data Source :- “Microsoft SQL Server (SqlClient)
  • Server name :- <Server name>
  • Choose Authentication Mode :- Use SQL Server Authentication
  • For username/password :- Please enter your previously created username and password credentials

EntityDataModelWizard - ChooseModelContents - GenerateFromDatabase - ConnectionProperties

Once you click OK, you will return to the configuration detail screen:

EntityDataModelWizard - ChooseModelContents - GenerateFromDatabase - ChooseYourDataConnection {Post}

There are a couple of items that you want to keep in mind.  And, those are:

  • You have a choice as to whether you want to embed sensitive data; principally user-password in the configuration file (web.config, app.config) or whether you will like to add them programmatically
  • The App name is written as “EntityFramework”.  That information may later be useful during Application Profiling
  • The EntityConnectionString is also very important as we will definitely need it when we create the WCF Service and override the DataService class

Choose Your Database Objects and Settings

It is now time to choose which actual Database Objects you will like to bring into your “Entity Framework” object model.

Tables

  • Under “Tables”, I will suggest that you tick off dtproperties and sysdiagrams
  • Dtproperties is used by SQL Server Management gui (SQL Server Management Studio)
  • And, sysdiagrams is used by a Object relationship mapper, better known as the Diagram module

Views

  • No obvious gotchas

Stored Procedures and Functions

  • Tick off the dbo objects that start with dt_*
  • And, the objects that have the word “diagram” in them

Add Stored Procedure

On the other hand, if already have an Entity Model in place and just want to add one or more newly created Stored Procedures (SP), please follow the steps below:

  • In the Solution Explorer, select the edmx file
  • Open the edmx file by double-clicking on your selection
  • Right click on an empty space and choose the “Update Model from Database…” from the drop-down menu
  • Choose the Stored Procedures you want to expose

Adding new Object – Stored Procedure  (usp_GetApplicationList_FilteredByID)

EntityDataModelWizard - ChooseModelContents - GenerateFromDatabase - ChooseYourDatabaseObjectsAndSettings - ListApplicationsFiltered

Model Browser

As you spend time adding new Database Objects, you might find out that Programmable Objects such as Stored Procedures and Functions are part of your model, but they are not showing up in the Entity Model GUI.

This opaqueness makes it a bit difficult to remove and otherwise review these objects.  To gain more clarity per this object classes, I will suggest that you use “Entity Data Model Browser”.

There are a couple of ways to get to it:

  • Access the emdx file and use the menu items — View / Other Windows / “Entity Data Model Browser”
  • Access the emdx file, right click on an empty spot, and select “Model Browser”

Once you have effected one of the choices listed above, the Model Browser will be displayed on the right side of your Application; in the same space usually reserved for the Solution Explorer.

Model Browser – What good is it?

In our case, I simply wanted to remove some of the Stored Procedures that I had renamed to a more aesthetic name.

Leaving the old names will simply clutter our address space.  And, worse probably reference DB Objects that were no longer in the DB itself.

Add Function Import

  • Browse the Property Window
  • Under the edmx file
  • Transverse to the Model / Stored Procedures
  • Select the Stored Procedure and right click on your selection
  • From the drop-down menu, select “Add Function Import…”
  • Click on the “Get Column Information” button
  • The names and datatypes of the columns that are being returned from the Stored Procedure will show up…
  • To properly encapsulate all the attributes that are being returned, it is best to group them under one umbrella and place them in an identifiable “bag”.
  • Click the “Create New Complex Type” button
  • The system automatically brings the “Return a collection of” panel into focus
  • And, in it the “Complex” dropdown is brought to focus
  • For our new Complex Type, please use a good name; for lack of imagination I will use ApplicationListFilteringByID
  • Press the OK button to effect your choices
  • Access the “Complex Types” list end ensure that Complex Type you just created is available

AddFunctionImport - ApplicationListFilteredByID

Browse Function Import

To review the Functions that you have imported, please do the following:

  • Access the Edmx file
  • Use menu and access View / Other Windows / “Entity Other Model Browser”
  • Review Model \ “Function Imports”

Add WCF Service

As previously noted, we will be exposing our OData as a Web Service.  To do so, we need to add a new WCF Data Service.

The steps are:

  • Use menu items – Add “Project” \ “Add New Item”
  • In the left panel, navigate to “C#” \ “Web”
  • In the middle panel, choose “WCF Data Service”

Again, make sure that you change Microsoft’s given name to one more reflective of your project:

AddNewItem - WCF Data Service

Modifications to WCFDataService – Inherit from DataService

Once the DataService item has been added, you will need to make a couple of quick changes.

This step is very important, but depending on the sample code name you ‘re using, it might not be properly and fully covered.

When left empty, you will get:

error CS1031: Type expected

And, if entered in-correctly you will get:

error CS0246: The type or namespace name <entry> could not be found (are you missing a using directive or an assembly reference?)

To properly specify the class name, please get the class name from your web.config file; specifically the connectionStrings bracket:


<connectionStrings>
<add name="SoftAppsEntities" connectionString="metadata=res://*/ModelSoftApps.csdl|res://*/ModelSoftApps.ssdl|res://*/ModelSoftApps.msl;provider=System.Data.SqlClient;provider connection string="data source=OData;initial catalog=DBSales;persist security info=True;user id=jamesbond;password=707;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>


The changes will go in the place indicated by the entry ” /* TODO: put your data source class name here */

using System.Linq;
using System.ServiceModel.Web;
using System.Web;

namespace WcfServiceSoftApps
{
    //public class WcfDataServiceSoftApps : DataService< /* TODO: put your data source class name here */ >
    public class WcfDataServiceSoftApps : DataService< SoftAppsEntities >
    {
         ..
         ..
    }
}

Explicitly Specify Entity / Stored Procedure Permission Set

There are a couple of code lines that we need to add to our code.  These lines will specify the permission sets that can happen on our objects. Basically, there are two types of objects – Entities and Services.  Entities are tables and Services are Stored Procedures and Functions.

The permissions on entities are more granular.  They include Read/Append/Merge/Update.  In our increasingly service world, read and write. The privileges are listed in “EntitySetRights Enumeration“{http://msdn.microsoft.com/en-us/library/system.data.services.entitysetrights(v=vs.113).aspx“}

And, the permission set on Services are akin to Reads.  Perhaps I will have preferred them, to be labeled Execute.  Nevertheless, they are available @ “ServiceOperationRights Enumeration { http://msdn.microsoft.com/en-us/library/system.data.services.serviceoperationrights(v=vs.113).aspx for public consumption.

Please keep in mind that we are not going to use the actual Stored Procedures name, but the symbolic names defined in the “Function Import” name.

To get the names of “Function Imports”:

  • Access “Model Browser”
  • Transverse to Model \ “Function Imports”

 

Here is what we ended up with:


        // 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.V3;

          // Grant permission to ApplicationListFetchAll
            config.SetServiceOperationAccessRule(
                                                    "ApplicationListFetchAll"
                                                    , ServiceOperationRights.All
                                                );

           // Grant permission to ApplicationListFetchFiltered
            config.SetServiceOperationAccessRule(
                                                      "ApplicationListFetchFiltered" 
                                                    , ServiceOperationRights.All
                                                 );

        }

Unfortunately, if you ‘re anything like me, you want to quickly test things out.  If you do, run off and test your application, you will get an error like this:

The given name '<procedure name>' was not found in the service operations.

The next step we will discuss, will cover how to expose service / custom operations in the WCF Data Service class.

 

Calling Service Data Operations

To expose Service Data Operations via OData, please do the following:

  • Create a new method
  • The method should have the same name as the method you are setting permissions for in the InitializeService method
  • Map your SQL Server Stored Procedure Argument types to .Net datatypes

As always, you are good; as there a few publicly available documents that map SQL Server Data types to .Net Framework types:

  • SQL Server Data Type Mappingshttp://msdn.microsoft.com/en-us/library/cc716729.aspx

Add Custom Data Operation – ApplicationListFilteringByID


        [WebGet]
        public IQueryable ApplicationListFetchAll()
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered(null, null, null, null, null).AsQueryable();

        }

Add Custom Data Operation – ApplicationListFiltered

In the example below, we do the following:

  • Specify attributes that can be null-able by prefixing them with ?

        [WebGet]
        public IQueryable ApplicationListFetchFiltered
            (
              Guid? ApplicationTypeID
            , Guid? VendorID
            , Guid? LicensingID
            , short? PlatformID
            , String SearchTag 
            )
        {

            return this.CurrentDataSource.usp_GetApplicationList_Filtered
                (
                        ApplicationTypeID
                      , VendorID
                      , LicensingID
                      , PlatformID
                      , SearchTag
                ).AsQueryable();

        }

    } // ApplicationListFetchFiltered

XML

Once the last step detailed above is in place, you may now quickly test your App.

Here are some sample XML payload signature.

XML Payload signature

 Need  Query Sample
List all entities /WcfDataServiceSoftApps.svc/
List specific entity – data /WcfDataServiceSoftApps.svc/vendors
Invoke Stored Procedure (does not have any arguments) /WcfDataServiceSoftApps.svc/ApplicationListFetchAll
Invoke Stored Procedure (single numeric argument) /WcfDataServiceSoftApps.svc/ApplicationListFetchFiltered?PlatformID=5
Invoke Stored Procedure (single GUI argument) /WcfDataServiceSoftApps.svc/ApplicationListFetchFiltered?VendorID eq guid ‘9CDD5896-25FC-4C17-930F-D857B20D561A’
Invoke Stored Procedure (two arguments – A Guid and String) /ApplicationListFetchFiltered?VendorID%20eq%20guid%20’9CDD5896-25FC-4C17-930F-D857B20D561A’&searchTag=’sql’

XML – Base feed


http://localhost:49947/WcfDataServiceSoftApps.svc/

XML-Base

XML – Objects – Entity – Vendors


http://localhost:49947/WcfDataServiceSoftApps.svc/vendors

XML-Entity-Vendors

XML – Objects – Programmable – Stored Procedure

http://localhost:49947/WcfDataServiceSoftApps.svc/ApplicationListFetchAll

XML-Programmable-StoredProcedures-ApplicationListFetchAll

XML – Objects – Programmable – Stored Procedure

http://localhost:49947/WcfDataServiceSoftApps.svc/ApplicationListFetchAll

XML-Programmable-StoredProcedures-ApplicationListFetchAll

XML – Objects – Programmable – Stored Procedure – Lone numeric argument

http://localhost:49947/WcfDataServiceSoftApps.svc/ApplicationListFetchFiltered?PlatformID=5

  • Passing in one parameter / argument
  • The lone argument been PlatformID; a numeric argument and so need for quotes around it

AddFunctionImport - ApplicationListFilteredByID-PlatformID

XML – Objects – Programmable – Stored Procedure – Passing GUID


http://localhost:49947/WcfDataServiceSoftApps.svc/ApplicationListFetchFiltered?VendorID eq guid '9CDD5896-25FC-4C17-930F-D857B20D561A'

AddFunctionImport - ApplicationListFilteredByVendorID (a guid)

XML – Objects – Programmable – Stored Procedure – Two arguments – One a GUID and the other a string


http://localhost:49947/WcfDataServiceSoftApps.svc/ApplicationListFetchFiltered?VendorID%20eq%20guid%20'9CDD5896-25FC-4C17-930F-D857B20D561A'&searchTag='sql'

AddFunctionImport - ApplicationListFilteredByVendorID (a guid) and a string

References

Window Communication Framework (WCF)

Entity Framework and Stored Procedures

Entity Rights – Set Rights

References – OData – Query Syntax

 

References – Data Type Mappings

Technical: Microsoft – SQL Server / Internet Information Server (IIS) – OData – Service – Error Logging

Technical: Microsoft – SQL Server / Internet Information Server (IIS) – OData – Service – Error Logging

Background

As a follow-up to Technical: Microsoft – SQL Server / Internet Information Server (IIS) – OData – Service ( https://danieladeniji.wordpress.com/2013/07/30/technical-microsoft-sql-server-internet-information-server-iis-odata-service/ ) , let us discuss error logging.

Impectus

The impetus to explicitly address error logging was seeded by this error message:

Screen Shot

TheServerEncounteredAnErrorProcessingTheRequest

Text


The server encountered an error processing the request. See server logs for more 
details.

Launch Visual Studio

Launch Visual Studio and load our project.

WCF Configuration

In Solution Explorer, right click on the Application’s Configuration file.  Note that if this is a Web Project the name of the configuration file will be web.config and if it a Console Application, it will be named App.config.

Please be sure to choose the generic configuration file.  In the case of a web project, choose web.config file and not the web.debug.config nor the web.release.config.

Once you right click on App’s Config file, a drop-down menu will appear.  From the drop-down menu an entry labelled “Edit WCF Configuration” will be visible.

If the “Edit WCF Configuration” menu item is not present, access the main  menu and click on the menu items “Tool”\WCF Service Configuration Editor”.  Exit the ensuring “WCF Service Configuration Editor” as changes made here are disjointed from our App’s Configuration.  Return to the App Configuration and click on the now visible “Edit WCF Configuration”.

WCF Configuration Choices

Diagnostics

Access the Diagnostics section and use the provided hyperlinks 

  • WMI Provider (checked)
  • Log Auto Flush (checked)
  • MessageLogging  (checked)
  • Tracing (checked)
  • Trace Level – Warning \ Activity Tracing (checked)

WCFConfiguration-Diagnostics
Diagnostics – Message Logging

  • LogEntireMessage (checked)
  • LogKnownPii (checked)
  • LogMalformedMessages (checked)
  • LogMessagesAtServiceLevel (checked)
  • LogMessageaAtTransportLevel (checked)

MicrosoftServiceConfigurationEditor-Diagnostics-MessageLogging

OS Folder & File Level Permissions

Depending on the folder where the Log File will be kept, please ensure that the Account that your Web Pool is running has OS Level folder permissions to that folder.

Review Log File

Conventionally, the log file’s extension is svclog.  And, the OS is configured to rely on the Microsoft Trace File Viewer to view changes being logged in that file.

To launch “Trace File Viewer” access Windows Explorer and sojourn to your Log Folder, select your log file, and choose to view using the aforementioned utility.

Sample Errors

Sample Errors – Error EntitySet Name

I intentionally entered the wrong “Entity Set” name:


config.SetEntitySetAccessRule("xclients", EntitySetRights.AllRead);

Note that the our entity set name is clients and not xclients.

 
Error Message



System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---&gt; System.ArgumentException: The given name 'xclients' was notfound in the entity sets.

Parameter name: name

Screen Shot:
MicrosoftServiceTraceViewer

Editor Error Messages

During the course of using the GUI to effect configuration changes, we ran into an error stating:


propagateActivity is a duplicate name

The only way we were able to get a bit ahead of that logjam was to use an editor (notepad++ for me) and remove wholesale the configuration section area of our configuration file (web.config) and reload the uncluttered file.

Please be sure to effect this change in the generic web.config file and not the web.debug.config nor the web.release.config.