Returning a DataTable over SqlContext.Pipe

I was writing a SQLCLR C# stored proc and wanted to return some results which were held in a DataTable.  Inconveniently, there is no built-in way of translating from System.Data class to Microsoft.SqlServer.Server classes.  So I wrote something which does just that, and here it is.

private static void SendDataTableOverPipe(DataTable tbl)
{
    // Build our record schema
    List<SqlMetaData> OutputColumns = new List<SqlMetaData>(tbl.Columns.Count);
    foreach(DataColumn col in tbl.Columns) 
    {
        SqlMetaData OutputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
        OutputColumns.Add(OutputColumn);
    }

    // Build our SqlDataRecord and start the results
    SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
    SqlContext.Pipe.SendResultsStart(record);

    // Now send all the rows
    foreach (DataRow row in tbl.Rows)
    {
        for (int col = 0; col < tbl.Columns.Count; col++)
        {
            record.SetValue(col, row.ItemArray[col]);
        }
        SqlContext.Pipe.SendResultsRow(record);
    }

    // And complete the results
    SqlContext.Pipe.SendResultsEnd();
}

static void SendDataTableOverPipe(DataTable tbl)
{
    // Build our record schema
    List<SqlMetaData> OutputColumns = new List<SqlMetaData>(tbl.Columns.Count);
    foreach(DataColumn col in tbl.Columns) 
    {
        SqlMetaData OutputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
        OutputColumns.Add(OutputColumn);
    }

    // Build our SqlDataRecord and start the results
    SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
    SqlContext.Pipe.SendResultsStart(record);

    // Now send all the rows
    foreach (DataRow row in tbl.Rows)
    {
        for (int col = 0; col < tbl.Columns.Count; col++)
        {
            record.SetValue(col, row.ItemArray[col]);
        }
        SqlContext.Pipe.SendResultsRow(record);
    }

    // And complete the results
    SqlContext.Pipe.SendResultsEnd();
}

This relies on the TypeConverter class.  I used some code from http://dotnetpulse.blogspot.com/ to give me a head start, but refactored it slightly.  All the code is available to download, for those who don't like copy-and-pasting from a web page.

September 13 2006
Older Posts