Share via

Accessing REST based Web services using SQL CLR

  • Article

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

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

https://bitworking.org/news/How_to_create_a_REST_Protocol

https://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 'https://sampledev03/wcfweb/calendarWS.svc/?appid=MMM&AuctionCode=ABC&months=4',@Response OUT

SELECT @Response

GO

Declare @Response NVARCHAR(2000)

EXECUTE SampleWSPUT 'https://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

  • Anonymous
    June 18, 2008
    PingBack from http://wordnew.acne-reveiw.info/?p=84

  • Anonymous
    October 11, 2011
    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?

  • Anonymous
    April 30, 2013
    Thanks for the valuable information.

  • Anonymous
    July 14, 2015
    To helpful for me ... Thanks a tone....