Hello people,
Good afternoon!
In this post I will be demonstrating to you how to perform POST and GET requests (actually it can be any type) on a SQL Server database using SQL CLR.
If you don't know what CLR is or have questions about how to use it, how to post it, etc, see this post here.
I had already made some blog posts here demonstrating how to do this using OLE Automation, but as CLR is a much safer and more flexible alternative, I decided to demonstrate how to do this using CLR (C #).
StpWs_Requisition procedure source code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Net; using System.IO; using System.Text; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT) { var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value; var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value.ToUpper(); var url = Ds_Url.Value; var feedData = string.Empty; var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value; try { var request = (HttpWebRequest) WebRequest.Create(url); request.Method = metodo; request.ContentType = "application/x-www-form-urlencoded"; if (metodo == "POST" && parametros.Length > 0) { var data = parametros; var dataStream = Encoding.UTF8.GetBytes(data); request.ContentLength = dataStream.Length; var newStream = request.GetRequestStream(); newStream.Write(dataStream, 0, dataStream.Length); newStream.Close(); } var response = (HttpWebResponse) request.GetResponse(); var stream = response.GetResponseStream(); var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding)); feedData = streamReader.ReadToEnd(); response.Close(); stream?.Dispose(); streamReader.Dispose(); } catch (Exception ex) { if (SqlContext.Pipe != null) SqlContext.Pipe.Send(ex.Message); } Ds_Retorno_OUTPUT = feedData; } } |
Example of use
Once the procedure has already been created and the assembly has been published to your server, simply use SP to perform your requests normally, as in the examples below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @Url VARCHAR(MAX) = 'http://viacep.com.br/ws/29090090/xml', @Retorno VARCHAR(MAX) EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @Url, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max) SELECT CAST(@Retorno AS xml) |
Next Steps
Now that you have learned how to make requests from the database itself, you can implement the SP's below in your database, this time, using the power of the CLR:
- Consuming the Google Maps API Using OLE Automation in SQL Server
- Querying SQL Server Object Tracking
- Consuming the Google Maps API to Get Address or Zip Code Information in SQL Server
The change is quite simple, just change these instructions:
|
1 2 3 4 5 6 7 8 |
EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false EXEC sys.sp_OAMethod @obj, 'send' EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT EXEC sys.sp_OADestroy @obj SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS |
for this:
|
1 2 3 4 5 6 7 8 9 |
EXEC CLR.dbo.stpWs_Requisicao @Ds_Url = @Url, -- nvarchar(max) @Ds_Metodo = N'GET', -- nvarchar(max) @Ds_Parametros = N'', -- nvarchar(max) @Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max) @Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max) SET @XML = CAST(@Retorno AS XML) |
Much simpler, no?
Regards and see you next post!
sql server clr integration integration post get requests requests database c # csharp sqlclr
sql server clr integration integration post get requests requests database c # csharp sqlclr
Very good, Dirceu! ??
Good day.
I am testing the code, but my @ Ds_Retorno_OUTPUT = null and my @return_value = 0.
Do you know what could be going wrong? I really need this code to use the database as a trigger to make requests in a webservice, since it is the only way I found to integrate an automation system with a web api.