Returning a DataTable over SqlContext.Pipe

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.SqlServer.Server;
using Vielife.Amr.DataAccess;
using Vielife.Amr.DataAccess.Interfaces;

namespace Conchango.SqlServer.SqlClrToolkit
{
    /// <summary>
    /// Holds the C# stored proc for calculating measures
    /// </summary>
    public static class SqlPipeUtil
    {
        /// <summary>
        /// Send some data over the SqlPipe
        /// </summary>
        /// <param name="tbl">The data to send</param>
        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();
        }

        /// <summary>
        /// Private ctor
        /// </summary>
        private SqlPipeUtils()
        {
        }
    }

    /// <summary>
    /// Convert a base data type to another base data type
    /// </summary>
    /// <remarks>
    /// Based on code from http://dotnetpulse.blogspot.com/2006/04/convert-net-type-to-sqldbtype-or.html
    /// </remarks>
    public sealed class TypeConverter
    {
        private struct DbTypeMapEntry
        {
            public Type Type;
            public DbType DbType;
            public SqlDbType SqlDbType;

            public DbTypeMapEntry(Type type, DbType dbType, SqlDbType sqlDbType)
            {
                this.Type = type;
                this.DbType = dbType;
                this.SqlDbType = sqlDbType;
            }
        };

        private readonly static List<DbTypeMapEntry> _DbTypeList = new List<DbTypeMapEntry>();

        #region Constructors

        static TypeConverter()
        {
            DbTypeMapEntry dbTypeMapEntry = new DbTypeMapEntry(typeof(bool), DbType.Boolean, SqlDbType.Bit);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry= new DbTypeMapEntry(typeof(byte), DbType.Double, SqlDbType.TinyInt);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(byte[]), DbType.Binary, SqlDbType.Image);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(DateTime), DbType.DateTime, SqlDbType.DateTime);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(Decimal), DbType.Decimal, SqlDbType.Decimal);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(double), DbType.Double, SqlDbType.Float);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry  = new DbTypeMapEntry(typeof(Guid), DbType.Guid, SqlDbType.UniqueIdentifier);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(Int16), DbType.Int16, SqlDbType.SmallInt);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(Int32), DbType.Int32, SqlDbType.Int);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(Int64), DbType.Int64, SqlDbType.BigInt);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(object), DbType.Object, SqlDbType.Variant);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(string), DbType.String, SqlDbType.VarChar);
            _DbTypeList.Add(dbTypeMapEntry);

            dbTypeMapEntry = new DbTypeMapEntry(typeof(string), DbType.String, SqlDbType.NVarChar);
            _DbTypeList.Add(dbTypeMapEntry);
        }

        private TypeConverter()
        {
        }

        #endregion

        #region Methods

        /// <summary>
        /// Convert db type to .Net data type
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static Type ToNetType(DbType dbType)
        {
            DbTypeMapEntry entry = Find(dbType);
            return entry.Type;
        }

        /// <summary>
        /// Convert TSQL type to .Net data type
        /// </summary>
        /// <param name="sqlDbType"></param>
        /// <returns></returns>
        public static Type ToNetType(SqlDbType sqlDbType)
        {
            DbTypeMapEntry entry = Find(sqlDbType);
            return entry.Type;
        }

        /// <summary>
        /// Convert .Net type to Db type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DbType ToDbType(Type type)
        {
            DbTypeMapEntry entry = Find(type);
            return entry.DbType;
        }

        /// <summary>
        /// Convert TSQL data type to DbType
        /// </summary>
        /// <param name="sqlDbType"></param>
        /// <returns></returns>
        public static DbType ToDbType(SqlDbType sqlDbType)
        {
            DbTypeMapEntry entry = Find(sqlDbType);
            return entry.DbType;
        }

        /// <summary>
        /// Convert .Net type to TSQL data type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static SqlDbType ToSqlDbType(Type type)
        {
            DbTypeMapEntry entry = Find(type);
            return entry.SqlDbType;
        }

        /// <summary>
        /// Convert DbType type to TSQL data type
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static SqlDbType ToSqlDbType(DbType dbType)
        {
            DbTypeMapEntry entry = Find(dbType);
            return entry.SqlDbType;
        }

        private static DbTypeMapEntry Find(Type type)
        {
            foreach (DbTypeMapEntry entry in _DbTypeList)
            {
                if (entry.Type == type)
                {
                    return entry;
                }
            }

            throw new ApplicationException("Referenced an unsupported Type");
        }

        private static DbTypeMapEntry Find(DbType dbType)
        {
            foreach (DbTypeMapEntry entry in _DbTypeList)
            {
                if (entry.DbType == dbType)
                {
                    return entry;
                }
            }

            throw new ApplicationException("Referenced an unsupported DbType");
        }

        private static DbTypeMapEntry Find(SqlDbType sqlDbType)
        {
            foreach(DbTypeMapEntry entry in _DbTypeList)
            {
                if (entry.SqlDbType == sqlDbType)
                {
                    return entry;
                }
            }

            throw new ApplicationException("Referenced an unsupported SqlDbType");
        }

        #endregion
    }
}