How do you return query results in a streaming table-valued function?
I am trying to write a CLR TVF that will run a query and return the results. I am doing a CLR vs. a TSQL TVF because there will be a great deal of business logic and string manipulation in the actual TVF. In the meantime, I am just trying to get a test one to work.
Here is my class:
--
Imports
System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Text
Partial
PublicClass UserDefinedFunctions<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=
True, _
IsPrecise:=True, SystemDataAccess:=SystemDataAccessKind.None, TableDefinition:="ListingID INT", _
Name:="Search.ExecuteListingSearch")> _
PublicSharedFunction ExecuteListingSearch(ByVal searchCriteriaAs SqlXml)As IEnumerableUsing cnAsNew SqlConnection("context connection=true")Dim sqlAsNew StringBuilderWith sql
.AppendLine("Select top 500 l.ListingID")
.AppendLine("From ListingModel.Listing l")
.AppendLine("Where l.ListingCategoryID = 1")
.AppendLine("And Exists(Select ListingID")
.AppendLine("From ListingModel.ListingFeature lf")
.AppendLine("Where lf.ListingID = l.ListingID)")
.AppendLine("Order By l.DateTimeCreated DESC")
EndWithDim cmdAsNew SqlCommand(sql.ToString, cn)
cmd.CommandType = CommandType.Textcn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)EndUsingEndFunctionEnd
Class--
The class compiles just fine, but when I try and deploy it, I get "Error 1 The SqlFunctionAttribute of the Init method for a CLR table-valued function must set the FillRowMethodName property. Trend.Mls4.SqlClr.SearchEngine"I understand it is asking for the enemurator row fill, but I don't need a row fill method, I simply want to stream back the DataReader.
Am I taking a wrong approach here? Is there a different/better way to return a query result set thru a STVF?
Thanks ahead of time for any help!
[4329 byte] By [
salafa] at [2008-2-24]
A data reader can not be streamed back as a TVF. You have a couple of options:
1. If you're not doing anything withe the data in the reader before it's being passed back, you can set-up your method as a stored proc and use the SqlPipe to send back the result:
SqlContext.Pipe.ExecuteAndSend(your-command-object)
2. If you are manipulating the data before you send it back, you can use the SqlDataRecord class and use the Pipe to send a SqlDataRecord back (or if it is SqlRecord - I'm writing this from memory). If you choose this method, you should use the pipe's SendResultsStart, SendResultRow and SendResultEnd methods to buffer up the data (once again, this is written from memory, check the method names).
Hope this helps.
Niels
Thanks for the response. It sounds as though option 1 comes closest to what I need to do. However, within my overall process, I still need to be able to select from a TVF in my other procs. Is it possible to create the CLR Proc as you describe and then call it from within a standard TSQL TVF? If that is possible, are there any performance implications that you may be aware of in doing so?
A UDF can not execute a proc. What you need to do if you need a TVF is to create a wrapper class which implements IEnumerator and you populate that class from your reader. Then you return the class from your TVF and it will be passed to you FillRowMethod by the CLR, where you read out the values and populate your columns.
Niels
Sorry for not having replied earlier, been on the road.
I believe you can not do what you suggested above. I think that what happens is that when you exit your TVF method, the reader closes and with that the enumerator.
Niels
