Stored procedures ontsluiten met OData service operations

OData is een heel interessant communicatie middel om informatie uit te
wisselen. De grootste vernieuwing is dat de client, niet de server, uitmaakt
welke data over het lijntje gaat. Waar ‘klassieke’ webservices een keur aan
methodes beschikbaar stellen om alle mogelijke selecties te ondersteunen, heeft
OData voldoende aan het ontsluiten van een context met entiteiten.

Maar zoals altijd moeten nieuwe technieken aansluiten op al bestaande
oplossingen. Voor OData geldt hetzelfde. In deze blog laat ik zien hoe OData
gebruikt kan worden om stored procedures te ontsluiten.

OData ontsluiten

Hoewel inhoudelijk niet zo relevant, laat ik eerst even het model van
entiteiten die al door een OData service ontsloten kunnen worden.

datamodel

Deze entiteiten zijn een directe afgeleide van drie tabellen in de
database. Maar in diezelfde database is ook een stored procedure beschikbaar
welke de machines toont die een minimum aantal storingen
hebben.


CREATE PROCEDURE USP_Machines_With_Minimum_Amount_Of_Failures

@MinimumCount int = 99

AS

BEGIN

SELECT M.MachineId, M.MachineName, M.SupervisorName

FROM Machines M

WHERE @MinimumCount <=

( SELECT COUNT(P.PartId)

FROM Parts P

JOIN Failures F ON P.MachineId = M.MachineId

AND P.PartId = F.PartId )

END

Merk op dat we als resultaat een lijst van records teruggeven waarvan
de opmaak niet 1-op-1 overeenkomt met de Machine entiteit. We missen enkele kolommen.

Voor de te bouwen WCF Data Service maken we hier gebruik van een
Entity Framework model. We voegen dus een EDMX toe aan een webproject en geven aan het model de opdracht om de drie tabellen en de stored procedure te gaan representeren.

EDMX

De stored procedure moet hierbij opnomen zijn om de volgende stap
te kunnen maken. Stored procedures die niet aan het model kenbaar zijn gemaakt in deze stap, kunnen later ook niet geselecteerd worden.

Als we de model-browser openen dan zien we ook netjes dat de stored
procedure opgenomen is:

Model browser

Maar we zijn hiermee nog niet klaar met het model. De stored procedure is nu
nog niet beschikbaar in de context. We moeten nu een function import uitvoeren
zodat de context de stored procedure in entiteiten kan uitdrukken.  Een
rechtermuis klik op de EDMX moet voldoende zijn om de function import te
starten:

Function import

Dit geeft een wizard waarbij een aantal zaken ingesteld moeten worden.

Function import wizard

Eerst geven we bovenaan een nettere naam aan de functie. En we kunnen onze
stored procedure selecteren (..)

Vervolgens moeten we de te retourneren waarden vaststellen en hierbij
moeten we goed opletten. Omdat de stored procedure geen complete entiteiten
retourneert, gaan we een nieuw complex type genereren.

Druk hiervoor op de “Get Column Information” knop. De te retourneren
waarden worden vastgesteld uit de stored procedure . Druk vervolgens op de knop “Create new complex type”:

Wizard part 2

En kies als laatste in het midden van het scherm voor het retourneren van
een lijst van het nieuw gegenereerde complex type (te herkennen aan de naam met _Result achter de stored procedure naam).

Function imported

Let op: Het return type moet bij de eigenschappen ingesteld zijn!

Nu is de stored procedure als functie beschikbaar in de context. We gaan dus
over op het aanmaken van OData service.

De volgende stap is dus het toevoegen  van een WCF Data service.

public class WcfDataService1 : DataService<SdnODataEntities>
{
  public static void InitializeService(DataServiceConfiguration config)
  {
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    config.SetEntitySetPageSize("*", 10);
    config.DataServiceBehavior.MaxProtocolVersion =
      DataServiceProtocolVersion.V2;
  }
}

Als we deze service controleren op het bestaan van het nieuwe complex type,
dan blijkt dat er nog niks is veranderd, voor de service zelf. Alleen de drie
entiteiten zijn nog steeds direct te benaderen:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<service xml:base="htt p://localhost:2239/WcfDataService1.svc/"
    xmlns:atom="htt p://www.w3.org/2005/Atom"
    xmlns:app="htt p://www.w3.org/2007/app"
    xmlns="htt p://www.w3.org/2007/app">
  <workspace>
    <atom:title>Default</atom:title>
    <collection href="Failures">
      <atom:title>Failures</atom:title>
    </collection>
    <collection href="Machines">
      <atom:title>Machines</atom:title>
    </collection>
    <collection href="Parts">
      <atom:title>Parts</atom:title>
    </collection>
  </workspace>
</service>

Het nieuwe type is dus niet te kiezen en zal ook nooit zo direct
beschikbaar zijn. En ook via de metadata is het type (nog) niet
controleerbaar.

Maar de stored procedure is wel beschikbaar binnen de context en deze gaan we
als Service Operation ontsluiten:

public static void InitializeService(
DataServiceConfiguration config)
{
  config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
  config.SetEntitySetPageSize("*", 10);
  config.DataServiceBehavior.MaxProtocolVersion
     = DataServiceProtocolVersion.V2;
  config.SetServiceOperationAccessRule(
     "GetMachinesWithMinimumCountOfFailures",
     ServiceOperationRights.AllRead);
}

[WebGet]
public List<MachinesWithMinimumCountOfFailures_Result>
        GetMachinesWithMinimumCountOfFailures(int minimumCount)
{
  return this.CurrentDataSource.
        MachinesWithMinimumCountOfFailures(minimumCount).ToList();
}

We maken dus een methode aan die met een WebGet attribuut opgemaakt is.
En deze service operation is voor alle leesacties beschikbaar.

Als we nu de OData service starten en de metadata controleren (http://localhost:2239/WcfDataService1.svc/$metadata)
dan komt alles beschikbaar:

<ComplexType Name="MachinesWithMinimumCountOfFailures_Result">
  <Property Name="MachineId"
            Type="Edm.Int32"
            Nullable="false" />
  <Property Name="MachineName"
            Type="Edm.String"
            Nullable="false"
            MaxLength="50" />
  <Property Name="SupervisorName"
            Type="Edm.String"
            Nullable="false"
            MaxLength="50" />
</ComplexType>
...
<FunctionImport Name="GetMachinesWithMinimumCountOfFailures"
                ReturnType="Collection(ODataModel.MachinesWithMinimumCountOfFailures_Result)"
                m:HttpMethod="GET">
   <Parameter Name="minimumCount"
              Type="Edm.Int32"
              Mode="In" />
</FunctionImport>

Mooi. Als we nu een selectie uitvoeren via de service operation…
http://localhost/WcfDataService1.svc/GetMachinesWithMinimumCountOfFailures?minimumCount=1

…dan krijgen we netjes alles informatie in het formaat van de complex
type terug.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<GetMachinesWithMinimumCountOfFailures
  xmlns="htt p://schemas.microsoft.com/ado/2007/08/dataservices">
  <element p2:type="ODataModel.MachinesWithMinimumCountOfFailures_Result"
      xmlns:p2="htt p://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <MachineId p2:type="Edm.Int32">1</MachineId>
    <MachineName>MixerNew</MachineName>
    <SupervisorName>user</SupervisorName>
  </element>

  <element p2:type="ODataModel.MachinesWithMinimumCountOfFailures_Result"
      xmlns:p2="htt p://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <MachineId p2:type="Edm.Int32">2</MachineId>
    <MachineName>Machine that goes Ping</MachineName>
    <SupervisorName>user</SupervisorName>
  </element>

  <element p2:type="ODataModel.MachinesWithMinimumCountOfFailures_Result"
      xmlns:p2="htt p://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <MachineId p2:type="Edm.Int32">7</MachineId>
    <MachineName>Mr. Coffee</MachineName>
    <SupervisorName>Dark Helmet</SupervisorName>
  </element>

  <element p2:type="ODataModel.MachinesWithMinimumCountOfFailures_Result"
      xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <MachineId p2:type="Edm.Int32">17</MachineId>
    <MachineName>Rage against the machine</MachineName>
    <SupervisorName>user</SupervisorName>
  </element>

  <element p2:type="ODataModel.MachinesWithMinimumCountOfFailures_Result"
      xmlns:p2="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <MachineId p2:type="Edm.Int32">18</MachineId>
    <MachineName>Mr. Radar</MachineName>
    <SupervisorName>Dark Helmet</SupervisorName>
  </element>

</GetMachinesWithMinimumCountOfFailures>

We hebben nu gezien dat het heel eenvoudig is om bestaande stored
procedures in een OData service op te nemen. Natuurlijk had deze logica ook
zonder de stored procedure dooe een client achterhaald kunnen worden maar voor meer complexere en specialistische selecties kan dit een uitkomst zijn.

Overigens wil ik nog even wijzen op een aardige bron van informatie
rond OData. Ik kan OData Primer aanbevelen vanwege de constant stroom van interessante links. Ik heb mijzelf op de RSS feed geabbonneerd en zojuist kwam (tijdens het schrijven van deze blog) een verwijziging voorbij naar een andere blog waarin bovenstaande techniek vergelijkbaar beschreven werd. Blijkbaar is het onsluiten van stored procedures een hot item 🙂

Advertenties

OData Batch met DataJS: Twee vliegen in één slag

Zoals ik in mijn vorige blog over datajs al vermeldde, is het aanroepen
van een OData service met deze DataJS  bibliotheek stukken
eenvoudig geworden. Hoewel we dicht tegen het ijzer (lees: de soap berichten)
aan programmeren, blijft het samenstellen van de te versturen opdrachten simpel en leesbaar.

En de grootste winst van datajs is de mogelijkheid om meerdere opdrachten
tegelijkertijd te versturen. Het Open Data Protocol ondersteunt namelijk batch operaties. Hierdoor kunnen in één keer verschillende zoekopdrachten verstuurd worden of juist meerdere gewijzigde entiteiten in één keer opgeslagen worden.

Hier duiken we vandaag eens in.

Gremlins2

OData maakt bij batch opdrachten gebruik van twee krachten. Ten eerste beidt
Soap de mogelijkheid om in één keer een “multi-part” bericht te sturen.
Daarnaast kan de WCF Data Service meerdere gewijzigde entiteiten in één keer
submitten naar de data-context via de SaveChanges methode.

Dit is dus twee (of meer) vliegen in één slag. Er moet minder over de
lijn gestuurd worden wat dus een besparing van bandbreedte en tijd is. En dit
maakt het ook mogelijk om meerdere wijzigingen als één transactie te
verwerken.

Om dit te demonstreren gaan we eerst twee aparte queries uitvoeren. Kijk
naar het volgende voorbeeld. Op zich zijn dit niet de meest spannende zoekacties
(we vragen twee entiteiten apart van elkaar op via de unieke sleutels) maar dit
zijn aparte selecties!

De code is eenvoudig en goed leesbaar, los van de {} en [] tekens hier en
daar…

// GET two calls within
  BatchOData.request({
    requestUri: "htt p://localhost:2976/WcfDataService1.svc/$batch",
    method: "POST",
    data: { __batchRequests:
    [
      { requestUri: "Machines(1)?$select=MachineId,MachineName",
        method: "GET" },
      { requestUri: "Machines(2)?$select=MachineId,MachineName",
        method: "GET" }
    ]
    }
    },
      BatchSuccess,
      Error,
      OData.batchHandler
);

Als we dit aanroepen wordt met slechts één call beide requests uitgevoerd
en als één call afgehandeld. Dit is mooi te bekijken met Fiddler. Deze geeft
een mooi overzicht van wat er bij de request en response over het lijntje
gestuurd wordt:

POST http://localhost:2976/WcfDataService1.svc/$batch HTTP/1.1Content-Type:
multipart/mixed;boundary=batch_f2ba-bfa6-d506
Accept-Language: nl,en-US;q=0.5
dataserviceversion: 1.0
Accept: multipart/mixed
Referer: http://localhost:2976/datajs_GET_HTMLPage.htm
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0;
 .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR
 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)
Host: localhost:2976
Content-Length: 482
Connection: Keep-Alive
Pragma: no-cache
--batch_f2ba-bfa6-d506
Content-Type: application/http
Content-Transfer-Encoding: binary
GET Machines(1)?$select=MachineId,MachineName HTTP/1.1
Accept: application/atomsvc+xml;q=0.8, application/json;q=0.5, */*;q=0.1
--batch_f2ba-bfa6-d506
Content-Type: application/http
Content-Transfer-Encoding: binary
GET Machines(2)?$select=MachineId,MachineName HTTP/1.1
Accept: application/atomsvc+xml;q=0.8, application/json;q=0.5, */*;q=0.1
--batch_f2ba-bfa6-d506--

Wat opvalt is dat de aanroep met context-type : multipart wordt
opgemaakt. Vervolgens staan de twee aparte GET acties in de body.

Dit geeft het volgende response als antwoord:

HTTP/1.1 202 AcceptedCache-Control: no-cache
Content-Length: 903
Content-Type: <strong>multipart</strong>/mixed;
 boundary=batchresponse_f259fb24-2f68-41d6-95a4-fbe89cf29362
Server: Microsoft-IIS/7.5
DataServiceVersion: 1.0;
X-AspNet-Version: 4.0.30319
X-SourceFiles:
 =?UTF-8?B?QzpcU0RFXFN0 [knip] mF0Y2g=?=
X-Powered-By: ASP.NET
Date: Mon, 04 Apr 2011 16:58:10 GMT
--batchresponse_f259fb24-2f68-41d6-95a4-fbe89cf29362
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 <strong>200</strong> OK
Cache-Control: no-cache
DataServiceVersion: 1.0;
Content-Type: application/json;charset=utf-8
{
"d" : {
"__metadata": {
"uri": "http://localhost:2976/WcfDataService1.svc/Machines(1)", "type":
"SdnODataModel.Machine"
}, "MachineId": 1, "MachineName": "Blender"
}
}
--batchresponse_f259fb24-2f68-41d6-95a4-fbe89cf29362
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 <strong>200</strong> OK
Cache-Control: no-cache
DataServiceVersion: 1.0;
Content-Type: application/json;charset=utf-8
{
"d" : {
"__metadata": {
"uri": "http://localhost:2976/WcfDataService1.svc/Machines(2)", "type":
"SdnODataModel.Machine"
}, "MachineId": 2, "MachineName": "Machine that goes Ping"
}
}
--batchresponse_f259fb24-2f68-41d6-95a4-fbe89cf29362--

Ook de response heeft een multipart context-type. En in hetzelfde bericht
zijn de responses van beide aanvragen opgenomen, ieder met een 200 OK antwoord. Overigens zijn de geretourneerde entiteiten in JSON formaat beschreven. Dit wordt door datajs weer netjes in objecten omgezet.

function BatchSuccess(data) {
var html = data.__batchResponses[0].data.MachineId
             + "-" +
             data.__batchResponses[0].data.MachineName
             + " / " +
             data.__batchResponses[1].data.MachineId
             + "-" +
             data.__batchResponses[1].data.MachineName;
  $("#responsePlaceHolder").html(html);
}

En op deze manier kan dus heel efficiënt data opgehaald worden waarbij de
aanvraag over meerdere queries is verdeeld.

Maar hoe zit het met wijzigingen van data? Nou, dat zit wel goed. Met
hetzelfde gemak kunnen ook meerdere insert, updates en deletes uitgevoerd
worden.

Pas echt interessant is de mogelijkheid om verschillende types aan
wijzigingen te combineren.

Stel je voor dat we een soort van master-detail scherm hebben. Zou het
mogelijk zijn zowel wijzigingen aan de master (bv. klantgegevens) als
toevoegingen aan de details (bv. orders) in één keer te versturen? In mijn
vorige blog bleek al dat een MERGE (het opsturen van alleen de gewijzigde
kolommen) veel efficienter is dan een PUT maar dat de MERGE een buitenbeentje is qua notatie.

Zou een merge samen met een POST (het toevoegen van records) kunnen
plaatsvinden?

Nou, dat kan. Kijk maar eens naar onderstaande voorbeeld. Hierin
combineer ik een wijziging van een bestaande entiteit (MERGE) met het opvoeren van een nieuwe entiteit (POST):

// BATCH MERGE-POSTOData.request({
requestUri: "htt p://localhost:2976/WcfDataService1.svc/$batch",
method: "POST",
data: {
 __batchRequests: [
{ __changeRequests:
 [
{ requestUri: "Machines(1)",
 method: "MERGE",
 data: { MachineName: 'MixerNew'} }
, { requestUri: "Machines", method: "POST",
 data: { MachineName: 'DataNew', SupervisorName:
 'userNew'} }
] }
] }
},
Success,
Error,
OData.batchHandler
);

Ook hier komt Fiddler ons helpen met het doorgronden van de request en de
response.

De request ziet er als volgt uit:

POST http://localhost:2976/WcfDataService1.svc/$batch HTTP/1.1Content-Type:
multipart/mixed;boundary=batch_1451-c1ce-8a01
Accept-Language: nl,en-US;q=0.5
dataserviceversion: 1.0
Accept: multipart/mixed
Referer: http://localhost:2976/datajs_POST_HTMLPage.htm
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0;
 .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR
 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)
Host: localhost:2976
Content-Length: 746
Connection: Keep-Alive
Pragma: no-cache
--batch_1451-c1ce-8a01
Content-Type: multipart/mixed; boundary=changeset_b3dd-3981-60b2
--changeset_b3dd-3981-60b2
Content-Type: application/http
Content-Transfer-Encoding: binary
MERGE Machines(1) HTTP/1.1
Accept: application/atomsvc+xml;q=0.8, application/json;q=0.5, */*;q=0.1
DataServiceVersion: 1.0
Content-Type: application/json
{"MachineName":"MixerNew"}
--changeset_b3dd-3981-60b2
Content-Type: application/http
Content-Transfer-Encoding: binary
POST Machines HTTP/1.1
Accept: application/atomsvc+xml;q=0.8, application/json;q=0.5, */*;q=0.1
DataServiceVersion: 1.0
Content-Type: application/json
{"MachineName":"DataNew","SupervisorName":"userNew"}
--changeset_b3dd-3981-60b2--
--batch_1451-c1ce-8a01--

Ook hier is het een multipart bericht met daarin de MERGE en de POST
gebroederlijk naast elkaar.

De daarop volgende response is niet veel spannender:

HTTP/1.1 202 AcceptedCache-Control: no-cache
Content-Length: 1137
Content-Type: multipart/mixed;
 boundary=batchresponse_a689df61-3d2a-4533-9e88-522b3d519196
Server: Microsoft-IIS/7.5
DataServiceVersion: 1.0;
X-AspNet-Version: 4.0.30319
X-SourceFiles:
 =?UTF-8?B?QzpcU0RFXFN [knip] wkYmF0Y2g=?=
X-Powered-By: ASP.NET
Date: Mon, 04 Apr 2011 17:36:25 GMT
--batchresponse_a689df61-3d2a-4533-9e88-522b3d519196
Content-Type: multipart/mixed;
 boundary=changesetresponse_17e960ab-699f-40f0-8f1b-6003cf4dd4ae
--changesetresponse_17e960ab-699f-40f0-8f1b-6003cf4dd4ae
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 204 No Content
Cache-Control: no-cache
DataServiceVersion: 1.0;
--changesetresponse_17e960ab-699f-40f0-8f1b-6003cf4dd4ae
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 201 Created
Cache-Control: no-cache
DataServiceVersion: 1.0;
Content-Type: application/json;charset=utf-8
Location: http://localhost:2976/WcfDataService1.svc/Machines(54)
{
"d" : {
"__metadata": {
"uri": "http://localhost:2976/WcfDataService1.svc/Machines(54)", "type":
"SdnODataModel.Machine"
}, "MachineId": 54, "MachineName": "DataNew", "SupervisorName": "userNew",
"Image": null, "ImageName": null, "Parts": {
"__deferred": {
"uri": "htt p://localhost:2976/WcfDataService1.svc/Machines(54)/Parts"
}
}
}
}
--changesetresponse_17e960ab-699f-40f0-8f1b-6003cf4dd4ae--
--batchresponse_a689df61-3d2a-4533-9e88-522b3d519196--

De MERGE wordt met een 204 (no content) beantwoord. Dit betekent dat de
opslag heeft plaatsgevonden maar er is geen nieuwe entiteit opgestuurd. Vanuit
het oogpunt van javascript heeft de client al alle kennis over het gewijzigde
object op de client.

De POST wordt met een 201 (Created) afgehandeld. Dit betekent dat de
entiteit in de context op de server is geaccepteerd. En we krijgen tevens de
gehele entiteit terug, compleet met de unieke sleutel van het object.

Met bovenstaande voorbeelden wordt duidelijk hoe efficient over het
netwerk gecommuniceerd kan worden als men zich verdiept in het Open Data
Protocol. Natuurlijk zal een batch opdracht niet altijd op zijn plaats zijn,
maar het is een krachtig stuk gereedschap die iedere OData ontwikkelaar moet
kunnen beheersen. En datajs maakt het wel heel eenvoudig te gebruiken.