CODEWITHSUNDEEP
c#sqlsql-servert-sql
akaco
akaco

Reputation: 673

Asynchronous output from CLR function

I have create a CLR function like this

public class GETJSONFROMURL
{
   [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GETData(SqlString URL) 
    {
        SqlString Data = "N/A";


        using (var webClient = new System.Net.WebClient())
        {

            var json = webClient.DownloadString(new Uri(URL.ToString()));
            // Now parse with JSON.Net
            JObject o = JObject.Parse(json);
            Data = (string)o["Data_results"]["MyData"];


        }
        return Data;


    }
}

and made a function in Sql Like this

CREATE ASSEMBLY GetDataURL
FROM 'C:\GETJSONFROMURL.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

CREATE FUNCTION GetDataURL (@URL NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME GetDataURL.GETJSONFROMURL.GETData

when I select this function in query window I get result very fast under 0.03 sec 

SELECT [dbo].[GetDataURL] ('http://www.Testserver.com/123')

but when I call this function as multiple call , like inside Triggers after insert something call that select I get delay for get result .

can you please help me to create an asynchronous function which is when I call inside sql as multiple call didn't get delay for returning result .

thanks

Upvotes: 1

Views: 2081

Answers (1)

Hannah Vernon
Hannah Vernon

Reputation: 3472

Instead of doing this inside a trigger, you could create a SQL Server Agent job that updates the table every 5 minutes (or more or less often depending on your requirements).

The job could call your CLR function once for every row that doesn't have the data inserted yet.

However, I would strongly suggest you make the client-side software get the data from the URL and insert it into the table.

You could consider having a separate machine get the json data for newly inserted rows; this takes the load off the SQL Server, and allows the client to not worry about getting the data.

Upvotes: 2

Related Questions