In my previous project I was been asked call web service from sql server stored procedure.
So I get it done using SQL CLR. So using CLR we can run managed code inside sql server.
Code that runs within the CLR is referred to as managed code.
We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code.
We can achieve significant performance increases because managed code compiles to native code prior to 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 my requirement.
So in that point we can go with SQL CLR.
What tools I have used in this post
Visual Studio 2015
Sql Server 2014
- Create SQL Server Database project in VS 2015
- Add SQL CLR C# Stored Procedure
Name stored procedure As CallWebService
3. Add C# codes to Call Web service. I am using below web service to do test
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); System.IO.File.WriteAllText("d://response.txt", readStream.ReadToEnd()); response.Close(); readStream.Close();
I am simply writing response to a text file. You can do whatever in here.
4.Enable clr and set trust worthy on 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
Then it will generate dll in bin folder
6. Register assembly in 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 bin folder of your project
Once you added we can see assembly registered in side Assemblies as below.
7. Create stored procedures to call assembly’s stored procedure
Once you created a stored procedure you can see locked stored procedure.
8. So Now we have finished just execute the stored procedure. You can see text file is generated in drive