Return a recordset from a SQL CLR Assembly?

Question

Guys lease help me out I think I'm close, but I'm missing something.

Background

I'm rewriting a SQL CLR assembly (stored procedure), my assembly contacts another resource and gets back XML. I want to return this XML as a recordset, NOT a scalar value.

From what I've seen, this is how to return a recordset:

SqlContext.Pipe.Send(mySqlDataReader)

The Send method takes in 3 possible parameters:

public void Send(string message);
public void Send(SqlDataRecord record);
public void Send(SqlDataReader reader);

The SqlDataReader class does not have a constructor, how does SqlCommand.ExecuteReader() return one?

What I think I need to do

• Create my own class inheriting from IDataReader.
• Make this class consume the XML, and expose it as records (like a DataReader).
• Override SqlDataReader and pass this to SqlContext.Pipe.Send(mySqlDataReader) ???

This is where it gets fuzzy.

How do I return my custom recordset back to SQL Server?

Answer 1

I guess you'd better use SqlDataRecord. You can see a description here

It should look like follows:

 SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Column1", SqlDbType.NVarChar)});

 // Set the record fields.
 record.SetString(0, youVariableWithXmlData);

 // Send the data
 SqlContext.Pipe.Send(record);