VB.Net CLR Assembly type Datatable into SQL

are you aware of Table Value Parameter: http://emoreau.com/Entries/Articles/2010/11/SQL-Table-Value-parameters.aspx

also have a look at http://emoreau.com/Entries/Articles/2015/04/SQL-CLR-Integration-in-2015-year-not-product-version.aspx

Het Eric
This is a CLR 2.0 and SQL Server 2008R2

This is what I have as a function in my SQL Server. (All other functions load fine)

SELECT [dbo].[EverywareTemplates]('ew6F98', 'DB8E0DE3')
CREATE FUNCTION [dbo].[EverywareTemplates] (@user NCHAR(200), @token NCHAR(200))
RETURNS NCHAR(4000) WITH EXECUTE AS CALLER
EXTERNAL NAME [HelloEveryware].[HelloEveryware.PassIn].[MessageTemplates];

Select allOpen in new window

Returns this error
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "EverywareTemplates":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
   at HelloEveryware.PassIn.MessageTemplates(String user, String token)

This is the MessageTemplates Method(s) in .Net CLR

The api.brkGetTemplates(user,token) definitely returns data

It is a List<ListName> in a C# Web Service... I retrieve data from it elsewhere.

   <Microsoft.SqlServer.Server.SqlFunction>
    Public Shared Function MessageTemplates(user As String, token As String) As String
        Try
            Dim api As New wsclientapi.Service
            Return GenerateXML(ConvertToDataTable(api.brkGetTemplates(user, token)))
        Catch ex As Exception
            File.WriteAllText(Convert.ToString("D:\netAssemblies\errorfile.txt"), ex.Message + Environment.NewLine + Environment.NewLine + ex.StackTrace + Environment.NewLine + Environment.NewLine + ex.InnerException.Message + Environment.NewLine + ex.InnerException.StackTrace + Environment.NewLine + Environment.NewLine)
            Return ex.StackTrace
        End Try
    End Function

    Public Shared Function GenerateXML(ByVal ds As DataTable) As String
        Dim obj As New StringWriter()
        Dim xmlstring As String
        ds.WriteXml(obj)
        xmlstring = obj.ToString()
        Return xmlstring
    End Function

    Public Shared Function ConvertToDataTable(Of T)(ByVal list As IList(Of T)) As DataTable
        Dim table As New DataTable()
        Dim fields() As FieldInfo = GetType(T).GetFields()
        For Each field As FieldInfo In fields
            table.Columns.Add(field.Name, field.FieldType)
        Next
        For Each item As T In list
            Dim row As DataRow = table.NewRow()
            For Each field As FieldInfo In fields
                row(field.Name) = field.GetValue(item)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    Public Shared Function ConvertDatatableToXML(ByVal dt As DataTable) As String
        Dim str As New MemoryStream()
        dt.WriteXml(str, True)
        str.Seek(0, SeekOrigin.Begin)
        Dim sr As New StreamReader(str)
        Dim xmlstr As String
        xmlstr = sr.ReadToEnd()
        Return (xmlstr)
    End Function

Select allOpen in new window

Hey Eric,
  The issue is I really need to load this datatable through a clr.

As far as complex....

I already have several other very complex assemblies working.

I simply cannot get the datatable in.

Its a SQL 2008 so I HAVE to use .Net 2.0... which stops some of the other items in your article.

I tried using XML instead but the CLR Character limit of NCHAR(4000) STopped that.

I can't use VARCHAR(8000) or VARCHAR(MAX) or TEXT

So...
Unless something else can be done I'll need to send them an Excel file

It should work with the framework 2.0. Follow the example and recreate it using your versions.

Finally slogged my way through.
Thanks