Accessing REST based Web services using SQL CLR


In one of scenario I came across with requirements to consume REST based web services in SQL Server, I have done research and found that this can be very well done using SQL CLR features, before I start with how to use SQL CLR to consume REST web services, it is imperative to explain you the REST web services concept.


What is REST web service?


REST can be described as an architecture style. The acronym REST stands for ‘Representational State Transfer’, this means that  every unique URI is representation of some object and you can get the content of this object using HTTP GET as well as you can use POST, PUT and DELETE to play with content of this object.


REST - An Architecture style, not a standard


This is really good to know that REST is not a standard as you will not see any W3C standards putting any REST specifications. The reason is that REST is simply an architecture style, you can’t pack a toolkit of this; instead you can use this style during writing your web services.  Though REST is an architecture style but it use certain standards like:


·         HTTP


·         text/xml, text/html, etc (MIME types)


·         URL


You can get in detail information on REST web services by referring to below links


http://www.xfront.com/REST-Web-Services.html


http://bitworking.org/news/How_to_create_a_REST_Protocol


http://www.petefreitag.com/item/431.cfm


PS: REST based web services in .NET can be written using WCF .NET version 3.5


 


How to Call REST Web Service using SQL CLR


REST based web services are most of the time in form of URI, to consume these web services one has to write SQL CLR stored procedures. I have taken an example to make standard PUT and POST calls to REST based web services, this code can be used to practically make call to any REST web services that have PUT or POST payload. Below code snippet describes .NET code written to make CLR stored procedures


using System;


using System.Data;


using System.Data.SqlClient;


using System.Data.SqlTypes;


using Microsoft.SqlServer.Server;


using System.Net;


using System.IO;


 


 


public partial class StoredProcedures


{


    [Microsoft.SqlServer.Server.SqlProcedure]


    public static void SampleWSPut(SqlString weburl, out SqlString returnval)


    {


        string url = Convert.ToString(weburl);


        string feedData=string.Empty;


        try


        {


            HttpWebRequest request = null;


            HttpWebResponse response = null;


            Stream stream = null;


            StreamReader streamReader = null;


 


            request = (HttpWebRequest)WebRequest.Create(url);


            request.Method = "PUT"; // you have to change to


            //PUT/POST/GET/DELETE based on your scenerio…


            request.ContentLength = 0;


            response = (HttpWebResponse)request.GetResponse();


            stream = response.GetResponseStream();


            streamReader = new StreamReader(stream);


            feedData = streamReader.ReadToEnd();


 


            response.Close();


            stream.Dispose();


            streamReader.Dispose();


 


        }


 


        catch (Exception ex)


        {


            SqlContext.Pipe.Send(ex.Message.ToString());


        }


              returnval = feedData;


    }


 


    [Microsoft.SqlServer.Server.SqlProcedure]


    public static void SampleWSPost(SqlString weburl, out SqlString returnval)


    {


        string url = Convert.ToString(weburl);


        string feedData = string.Empty;


        try


        {


            HttpWebRequest request = null;


            HttpWebResponse response = null;


            Stream stream = null;


            StreamReader streamReader = null;


 


            request = (HttpWebRequest)WebRequest.Create(url);


            request.Method = "POST";


            response = (HttpWebResponse)request.GetResponse();


            stream = response.GetResponseStream();


            streamReader = new StreamReader(stream);


            feedData = streamReader.ReadToEnd();


            response.Close();


            stream.Dispose();


            streamReader.Dispose();


 


        }


 


        catch (Exception ex)


        {


            SqlContext.Pipe.Send(ex.Message.ToString());


        }


        returnval = feedData;


    }


 


};


 


The above code depicts that we have used HttpWebRequest class object to create an URI based request, and later we have defined the type of method to make this URI calls, that is ‘PUT’ and ‘POST’. You can change this method based on your payload request.


When the above code will be compiled in assembly and later registered as an assembly in SQL Server, we’ll create two CLR stored procedures to bind assembly stored procedure methods with them.


 


Generate Assembly and Register in SQL Server


The code here guide you to create assembly of class and later register this assembly in SQL Server


¾  Generate Assembly


// Creates StoredProcedures.dll


csc /t:library StoredProcedures.cs


 


¾  Register Assembly in SQL Server


USE [SampleDB]


GO

CREATE ASSEMBLY SPAssembly
FROM 'C:\SampleCLRSP\StoredProcedures.dll'
WITH PERMISSION_SET = UNSAFE;
GO

 


Create CLR Stored Procedure


I have put the T-SQL scripts here to create your CLR stored procedure to make call to any REST web service for PUT and POST payload


USE [SampleDB]


GO


/****** Object:  StoredProcedure [dbo].[SampleWSPut]    Script Date: 06/18/2008 06:02:32 ******/


CREATE PROCEDURE [dbo].[SampleWSPut]


      @weburl [nvarchar](4000),


      @returnval [nvarchar](2000) OUTPUT


WITH EXECUTE AS CALLER


AS


EXTERNAL NAME [SPAssembly].[StoredProcedures].[SampleWSPut]


GO


/****** Object:  StoredProcedure [dbo].[SampleWSPost]    Script Date: 06/18/2008 06:02:19 ******/


CREATE PROCEDURE [dbo].[SampleWSPost]


      @weburl [nvarchar](4000),


      @returnval [nvarchar](2000) OUTPUT


WITH EXECUTE AS CALLER


AS


EXTERNAL NAME [SPAssembly].[StoredProcedures].[SampleWSPost]


 


 


Calling CLR stored procedures


Below example shows that how can you make call to REST based web services using CLR stored procedures. CLR stored procedures written by us make call to REST web service and collect the response send by them, response could be a simple HTTP response message, for example 100, 200, 401 etc. or it could be full XML or MIME message returned to your application.


Declare @Response NVARCHAR(2000)


EXECUTE SampleWSPOST 'http://sampledev03/wcfweb/calendarWS.svc/?appid=MMM&AuctionCode=ABC&months=4',@Response OUT


SELECT @Response


GO


 


Declare @Response NVARCHAR(2000)


EXECUTE SampleWSPUT 'http://sampledev03/wcfweb/calendarWS.svc/?appid=MMM&AuctionCode=ABC&months=4',@Response OUT


SELECT @Response


 


Hope this article will help you to create CLR stored procedures to make REST based web service calls.


 


Thanks,


Kuldeep


Comments (4)

  1. Alex says:

    Hello!

    Thanks a lot for this article.

    Please extend with post processing of Xml serialization – how to do it better?

    add

    using System.Xml;

    and after create a XML serialized assembly with sgen.exe?

  2. Mohan says:

    Thanks for the valuable information.

  3. Ganesh says:

    To helpful for me … Thanks a tone….

Skip to main content