Accessing REST based Web services using SQL CLR
2021/2/2 1
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.