Trying to create a Function in .Net framework, which returns a response to Microsoft SQL Server Management Studio

Hello everyone,

I have made a simple program using C# and RestSharp in .Net Framework . My program contacts an endpoint, with a POST method, sends authentification credentails, and saves the response in a variable called 'responseBody'.

Then I have made a function called 'SendToSQL'. The idea is that it should be possible to call the function 'SendToSQL' from Microsoft SQL Server Management Studio, and it will return the content of the responseBody to Microsoft SQL Server Management Studio. I have called the function from within my C# code to check if it works correctly, and at the moment it doesn't work. What am I doing wrong?

Here is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RestSharp;
using RestSharp.Authenticators;
using Stock;
using System.Data.SqlTypes;
using System.Security.Cryptography.X509Certificates;

namespace RestSharpApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string url = "https://endpoint/consultstock";

            
            var options = new RestClientOptions(url)
            {
                Authenticator = new HttpBasicAuthenticator("123", "abc_")
            };

            var client = new RestClient(options);
                   
            var request = new RestRequest();
            request.RequestFormat = DataFormat.Xml;


            StockRequestBody requestBody = new StockRequestBody() {
                Material = "9200010"
            };
            request.AddXmlBody(requestBody, ContentType.Xml);

            var response = client.Post(request);

            StockResponseBody responseBody = client.Serializers.DeserializeContent<StockResponseBody>(response);


             func SendToSQl(/* SqlString*/ string idArticulo )
            {
              requestBody.Material = idArticulo;

              Console.WriteLine(responseBody);
              return (responseBody);
            };

            SendToSQl("123");


           
            Console.ReadLine();

        }
    }
}

Kind regards,

LaryAI

3 years ago

Best Answer

Level 58

To create a function in .NET Framework that returns a response to Microsoft SQL Server Management Studio, you can follow these steps:

Define a function that takes a parameter for the ID of the article and returns the response body:

public string SendToSQL(string idArticulo)
{
    string url = "https://endpoint/consultstock";
    var options = new RestClientOptions(url)
    {
        Authenticator = new HttpBasicAuthenticator("123", "abc_")
    };
    var client = new RestClient(options);
    var request = new RestRequest();
    request.RequestFormat = DataFormat.Xml;
    StockRequestBody requestBody = new StockRequestBody() {
        Material = idArticulo
    };
    request.AddXmlBody(requestBody, ContentType.Xml);
    var response = client.Post(request);
    StockResponseBody responseBody = client.Serializers.DeserializeContent<StockResponseBody>(response);
    return responseBody.ToString();
}

In Microsoft SQL Server Management Studio, create a new function that calls the SendToSQL function:

CREATE FUNCTION GetStock(@idArticulo nvarchar(50))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @response nvarchar(max)
    EXEC @response = [RestSharpApp1].[Program].[SendToSQL] @idArticulo
    RETURN @response
END

Call the function in a query:

SELECT dbo.GetStock('9200010')

Please or to participate in this conversation.