Calling Web Service From SQL Server Using SQL CLR

Problem

In my previous project, I was asked to call Web Services from SQL Server stored procedures.

It was done using SQL CLR. By using CLR, we can run and manage the code inside the SQL Server.

Code that runs within CLR is referred to as a managed code.

We can create the stored procedures, triggers, user defined types and user-defined aggregates in the managed code. We can achieve significant performance increases because the managed code compiles to the native code prior to the execution. We can use SQL CLR in in SQL Server 2005 and later.

Why SQL CLR in SQL Server?

In some cases, some tasks are not possible by T-SQL as per my requirement. We can go with SQL CLR.

The tools used in this post are.

• Visual Studio 2015
• SQL Server 2014

In Action

1. Create SQL Server Database project in VS 2015.
   
   
   
   
2. Add SQL CLR C# stored procedure. 
   
   
   
   Name the stored procedure As CallWebService.
   
   
3. Add C# codes to Call Webservice. I am using the Web Service, mentioned below to do the test.
   
   http://www.webservicex.net/globalweather.asmx?op=GetCitiesByCountry
   

   HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET//globalweather.asmx//GetCitiesByCountry?CountryName=Sri Lanka");
   
   request.Method = "GET";
   request.ContentLength = 0;
   request.Credentials = CredentialCache.DefaultCredentials;
   HttpWebResponse response = (HttpWebResponse)request.GetResponse();
   Stream receiveStream = response.GetResponseStream();
   // Pipes the stream to a higher level stream reader with the required encoding format.
   StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);
   
   Console.WriteLine("Response stream received.");
   System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd());
   
   response.Close();
   readStream.Close();

   I am simply writing a response to a text file. You can do it as per your desire. 
   
   
4. Enable CLR and set trust worthy on the database. I am using AdventureWorks database.
   

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'clr enabled', 1;
   GO
   RECONFIGURE;
   GO
   
   alter database [AdventureDatabase] set trustworthy on;

5. Build Visual Studio Project.
   
   It will generate DLL in bin folder.
   
   
6. Register assembly in the database.
   
   Go to AdventureWorks > Programmability > Assemblies
   
   Right click on Assemblies and click new Assembly.
   
   
   
   Set Permission to External access and browse for our DLL. It is in the bin folder of your project.
   
   Once you add it, we can see the assembly registered in side Assemblies, as mentioned below.
   
   
   
   
7. Create stored procedures to call assembly’s stored procedure. 
   
   
   
   Once you created a stored procedure, you can see locked stored procedure.
   
   
   
   
8. Hence, we have finished executing the stored procedure. You can see the text file is generated in the drive.