.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

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