Return array in CLR function

Question

I need to download regularly the data from link like below and save it to the MS database. I made CLR function with WebClient class but everythig is in one row, I need to separate it.

I got the idea to save the data in the array, use split and return it in loop but I don't know how to return line by line to save it in database.

public partial class UserDefinedFunctions
{

private static readonly WebClient webClient = new WebClient();

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DownloadSynop(string uri)
{
    string synop = webClient.DownloadString(uri);
    string[] lines = synop.Split(new string[] { Environment.NewLine, "\n",  "\"r" }, StringSplitOptions.None);

    for (int i=0; i<lines.Length - 1; i++)
    {
      string kod = lines[i];           

    }

    return new SqlString(kod);  //problem
}
}

Edmond Quinton · Accepted Answer · 2016-05-04 19:31:00Z

SQL Server does not really support “Arrays” per say and in general I would recommend that you develop a separate service or application that parses the web page and then simply insert the needed data into a table formatted for your needs. Using a CLR to query a web page means that you would have to publish the CLR as Unsafe to SQL Server. Some organization will not allow CLR’s marked as unsafe on their servers.

Having said that you can create a table valued CLR function. This will allow you to query your result from your function like a standard table. Below is a code example of how you can achieve this:

    public partial class UserDefinedFunctions
    {

        private struct Record
        {
            public int RowNr;
            public string SampleValue;
        }


        [SqlFunction(FillRowMethodName = "MyClrTableValuedFunction_FillRow",
            TableDefinition = "[RowNr] INT, [SampleValue] NVARCHAR(50)")]
        public static IEnumerable MyClrTableValuedFunction()
        {
            ArrayList list = new ArrayList();

            for (int sampleRowNr = 0; sampleRowNr < 100; sampleRowNr++)
            {
                Record sampleRecord = new Record();
                sampleRecord.RowNr = sampleRowNr;
                sampleRecord.SampleValue = string.Format("Sample Value: {0}", sampleRowNr);

                list.Add(sampleRecord);
            }

            return list;
        }


        public static void MyClrTableValuedFunction_FillRow(Object obj, out SqlInt32 rowNr, out SqlString sampleValue)
        {
            Record record = (Record)obj;

            rowNr = record.RowNr;
            sampleValue = record.SampleValue;
        }
    }

You can call your function as a standard select statement in SQL Server as follow:

    SELECT   [RowNr]
            ,[SampleValue]
    FROM    [dbo].[MyClrTableValuedFunction]()

Collectives™ on Stack Overflow

Return array in CLR function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related