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.