Various utility methods for working DataTable and the SQL CLR.

namespace DotnetDataHelpers
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Text.RegularExpressions;

    internal class FakeAnonymousType
    {
        public Dictionary<string, object> Properties = new Dictionary<string, object>();

        public FakeAnonymousType()
        {
        }

        public void Add<T>(string name, T value)
        {
            Properties.Add(name, value);
        }

        public T GetValue<T>(string name)
        {
            return (T)Properties[name];
        }
    }

    /// <summary>
    /// Data table helper.
    /// </summary>
    /// <remarks>
    /// https://github.com/zzzprojects/Eval-SQL.NET/blob/master/src/Z.Expressions.SqlServer.Eval/Helper/DataTableHelper.cs
    /// </remarks>
    public static class DataTableHelper
    {
        private const string Unsupported_DataTable_ResultSet = "" +
            "GetDataTable(object value): The specified value could not be converted to a ResultSet because the DataSet type is not supported.";

        /// <summary>Gets a DataTable built from the value.</summary>
        /// <exception cref="Exception">Thrown when an exception error condition occurs.</exception>
        /// <param name="value">The value.</param>
        /// <returns>The DataTable built from the value.</returns>
        public static DataTable GetDataTable(object value)
        {
            DataTable dt;
            if (value is DataSet)
            {
                throw new Exception(Unsupported_DataTable_ResultSet);
            }
            if (value is DataTable)
            {
                dt = (DataTable)value;
            }
            else if (value is IEnumerable<DataRow>)
            {
                var drs = (IEnumerable<DataRow>)value;
                dt = new DataTable();

                bool isFirst = true;

                foreach (var dr in drs)
                {
                    if (isFirst)
                    {
                        var ownerTable = dr.Table;
                        dt = ownerTable.Clone();
                        isFirst = false;
                    }

                    dt.ImportRow(dr);
                }
            }
            else if (value is IEnumerable<FakeAnonymousType>)
            {
                var list = (IEnumerable<FakeAnonymousType>)value;

                bool isFirst = true;

                dt = new DataTable();

                foreach (var item in list)
                {
                    if (isFirst)
                    {
                        foreach (var property in item.Properties)
                        {
                            dt.Columns.Add(property.Key);
                        }
                        isFirst = false;
                    }

                    var row = dt.NewRow();
                    dt.Rows.Add(row);
                    foreach (var property in item.Properties)
                    {
                        row[property.Key] = property.Value;
                    }
                }
            }
            else if (value is IEnumerable && value.GetType().IsGenericType && value.GetType().GetGenericArguments().Length == 1)
            {
                var genericType = value.GetType().GetGenericArguments()[0];

                if (genericType.IsGenericType && (
                    genericType.GetGenericTypeDefinition() == typeof(Tuple)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,,,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,,,,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,,,,,>)
                    || genericType.GetGenericTypeDefinition() == typeof(Tuple<,,,,,,,>)
                ))
                {
                    var list = (IEnumerable)value;

                    var properties = genericType.GetProperties();
                    dt = new DataTable();

                    foreach (var property in properties)
                    {
                        dt.Columns.Add(property.Name);
                    }

                    foreach (var item in list)
                    {
                        var dr = dt.NewRow();
                        dt.Rows.Add(dr);

                        foreach (var property in properties)
                        {
                            dr[property.Name] = property.GetValue(item, null);
                        }
                    }
                }
                else if (genericType.IsArray)
                {
                    var list = (IEnumerable)value;

                    dt = new DataTable();

                    foreach (var item in list)
                    {
                        var itemArray = (object[])item;

                        if (itemArray.Length > dt.Columns.Count)
                        {
                            for (var i = dt.Columns.Count; i < itemArray.Length; i++)
                            {
                                dt.Columns.Add("Value_" + (i + 1));
                            }
                        }

                        var dr = dt.NewRow();
                        dt.Rows.Add(dr);

                        for (var i = 0; i < itemArray.Length; i++)
                        {
                            dr[i] = itemArray[i];
                        }
                    }
                }
                else
                {
                    var list = (IEnumerable)value;

                    dt = new DataTable();
                    dt.Columns.Add("Value_1");
                    foreach (var item in list)
                    {
                        dt.Rows.Add(item);
                    }
                }
            }
            else if (value is IEnumerable && value.GetType().IsGenericType && value.GetType().GetGenericArguments().Length > 1)
            {
                var list = (IEnumerable)value;

                //bool isFirst = false;

                dt = new DataTable();
                dt.Columns.Add("Value_1");
                foreach (var item in list)
                {
                    dt.Rows.Add(item);
                }
            }
            else if (value is IEnumerable && value.GetType().HasElementType)
            {
                var list = (IEnumerable)value;

                dt = new DataTable();
                dt.Columns.Add("Value_1");
                foreach (var item in list)
                {
                    dt.Rows.Add(item);
                }
            }
            else if (value is MatchCollection)
            {
                dt = new DataTable();
                dt.Columns.Add("Value_1");
                foreach (Match item in (MatchCollection)value)
                {
                    dt.Rows.Add(item.Value);
                }
            }
            else
            {
                throw new Exception(string.Format(Unsupported_DataTable_ResultSet, value.GetType()));
            }

            return dt;
        }

        /// <summary>Gets the DataRow collections built from the value.</summary>
        /// <param name="value">The value.</param>
        /// <returns>The DataRow collections built from the value.</returns>
        public static IEnumerable<DataRow> GetDataRows(object value)
        {
            var dt = GetDataTable(value);

            var rows = new DataRow[dt.Rows.Count];
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                rows[i] = dt.Rows[i];
            }

            return rows;
        }
    }
}

//
// Usage:
// ---------------------------------------------------------------------------

// Description: Evaluate C# code and expression in T-SQL stored procedure, function and trigger.
// Website & Documentation: https://github.com/zzzprojects/Eval-SQL.NET
// Forum & Issues: https://github.com/zzzprojects/Eval-SQL.NET/issues
// License: https://github.com/zzzprojects/Eval-SQL.NET/blob/master/LICENSE
// More projects: http://www.zzzprojects.com/
// Copyright © ZZZ Projects Inc. 2014 - 2016. All rights reserved.

using System;
using System.Data;
using Microsoft.SqlServer.Server;

namespace Z.Expressions.SqlServer.Eval
{
    public partial struct SQLNET
    {
        /// <summary>Eval the code or expression and return a result set.</summary>
        /// <exception cref="Exception">Throw an exception if the result from the code or expression is not supported.</exception>
        /// <param name="sqlnet">The SQLNET object to evaluate.</param>
        [SqlProcedure]
        public static void SQLNET_EvalResultSet(SQLNET sqlnet)
        {
            var value = sqlnet.Eval();

            if (value == null || value == DBNull.Value)
            {
                return;
            }

            if (value is DataTable)
            {
                SqlContextHelper.SendDataTable((DataTable) value);
                return;
            }

            if (value is DataSet)
            {
                var ds = (DataSet) value;
                SqlContextHelper.SendDataSet(ds);
                return;
            }

            SqlContextHelper.SendDataTable(DataTableHelper.GetDataTable(value));
        }
    }
}

// Description: Evaluate C# code and expression in T-SQL stored procedure, function and trigger.
// Website & Documentation: https://github.com/zzzprojects/Eval-SQL.NET
// Forum & Issues: https://github.com/zzzprojects/Eval-SQL.NET/issues
// License: https://github.com/zzzprojects/Eval-SQL.NET/blob/master/LICENSE
// More projects: http://www.zzzprojects.com/
// Copyright © ZZZ Projects Inc. 2014 - 2016. All rights reserved.

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace Z.Expressions.SqlServer.Eval
{
    internal static class SqlContextHelper
    {
        /// <summary>Send a DataSet to a SqlContext.</summary>
        /// <exception cref="Exception">Throw an exception if the DataSet is null.</exception>
        /// <param name="ds">The DataSet to send to the SqlContext.</param>
        internal static void SendDataSet(DataSet ds)
        {
            if (ds == null)
            {
                throw new Exception(ExceptionMessage.Unexpected_NullResultSet);
            }

            foreach (DataTable dt in ds.Tables)
            {
                SendDataTable(dt);
            }
        }

        /// <summary>Send a DataTable to a SqlContext.</summary>
        /// <exception cref="Exception">Throw an exception if the DataTable is null.</exception>
        /// <param name="dt">The DataTable to send to the SqlContext.</param>
        internal static void SendDataTable(DataTable dt)
        {
            if (dt == null)
            {
                throw new Exception(ExceptionMessage.Unexpected_NullResultSet);
            }

            bool[] useToString;
            var metaData = ExtractDataTableColumnMetaData(dt, out useToString);

            var record = new SqlDataRecord(metaData);
            var pipe = SqlContext.Pipe;

            pipe.SendResultsStart(record);

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    for (var index = 0; index < record.FieldCount; index++)
                    {
                        var value = row[index];
                        if (value != null && useToString[index])
                        {
                            value = value.ToString();
                        }

                        record.SetValue(index, value);
                    }

                    pipe.SendResultsRow(record);
                }
            }
            finally
            {
                pipe.SendResultsEnd();
            }
        }

        /// <summary>Extracts the data table column meta data.</summary>
        /// <param name="dt">The dt.</param>
        /// <param name="useToString">[out] The coerce to string.</param>
        /// <returns>The extracted data table column meta data.</returns>
        private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] useToString)
        {
            var metaDataResult = new SqlMetaData[dt.Columns.Count];
            useToString = new bool[dt.Columns.Count];
            for (var index = 0; index < dt.Columns.Count; index++)
            {
                var column = dt.Columns[index];
                metaDataResult[index] = SqlMetaDataFromColumn(column, out useToString[index]);
            }

            return metaDataResult;
        }

        /// <summary>SQL meta data from column.</summary>
        /// <exception cref="Exception">Throw an exception when an invalid or unsupported type is found.</exception>
        /// <param name="column">The column.</param>
        /// <param name="useToString">[out] The coerce to string.</param>
        /// <returns>A SqlMetaData.</returns>
        private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool useToString)
        {
            useToString = false;
            SqlMetaData sqlMetaData;
            var clrType = column.DataType;
            var typeCode = Type.GetTypeCode(clrType);
            var name = column.ColumnName;
            switch (typeCode)
            {
                case TypeCode.Boolean:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.Bit);
                    break;
                case TypeCode.Byte:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.TinyInt);
                    break;
                case TypeCode.Char:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, 1);
                    break;
                case TypeCode.DateTime:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.DateTime);
                    break;
                case TypeCode.Decimal:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.Decimal, 18, 0);
                    break;
                case TypeCode.Double:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.Float);
                    break;
                case TypeCode.Int16:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.SmallInt);
                    break;
                case TypeCode.Int32:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.Int);
                    break;
                case TypeCode.Int64:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.BigInt);
                    break;
                case TypeCode.Single:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.Real);
                    break;
                case TypeCode.String:
                    sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
                    break;
                case TypeCode.Object:
                    sqlMetaData = SqlMetaDataFromObjectColumn(name, column, clrType);
                    if (sqlMetaData == null)
                    {
                        sqlMetaData = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);
                        useToString = true;
                    }
                    break;
                case TypeCode.DBNull:
                case TypeCode.Empty:
                case TypeCode.SByte:
                case TypeCode.UInt16:
                case TypeCode.UInt32:
                case TypeCode.UInt64:
                    throw new Exception(string.Format(ExceptionMessage.Unsupported_SqlMetaData_TypeCode, typeCode));
                default:
                    throw new Exception(string.Format(ExceptionMessage.Unsupported_SqlMetaData_Type, clrType));
            }

            return sqlMetaData;
        }

        /// <summary>SQL meta data from object column.</summary>
        /// <param name="name">The name.</param>
        /// <param name="column">The column.</param>
        /// <param name="clrType">Type of the colour.</param>
        /// <returns>A SqlMetaData.</returns>
        private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)
        {
            SqlMetaData sqlMetaData;
            if (clrType == typeof (byte[])
                || clrType == typeof (SqlBinary)
                || clrType == typeof (SqlBytes)
                || clrType == typeof (char[])
                || clrType == typeof (SqlString)
                || clrType == typeof (SqlChars))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);
            }
            else if (clrType == typeof (Guid))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
            }

            else if (clrType == typeof (object))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Variant);
            }
            else if (clrType == typeof (SqlBoolean))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Bit);
            }
            else if (clrType == typeof (SqlByte))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.TinyInt);
            }
            else if (clrType == typeof (SqlDateTime))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.DateTime);
            }
            else if (clrType == typeof (SqlDouble))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Float);
            }
            else if (clrType == typeof (SqlGuid))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.UniqueIdentifier);
            }
            else if (clrType == typeof (SqlInt16))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.SmallInt);
            }
            else if (clrType == typeof (SqlInt32))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Int);
            }
            else if (clrType == typeof (SqlInt64))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.BigInt);
            }
            else if (clrType == typeof (SqlMoney))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Money);
            }
            else if (clrType == typeof (SqlDecimal))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);
            }
            else if (clrType == typeof (SqlSingle))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Real);
            }
            else if (clrType == typeof (SqlXml))
            {
                sqlMetaData = new SqlMetaData(name, SqlDbType.Xml);
            }
            else
            {
                sqlMetaData = null;
            }

            return sqlMetaData;
        }
    }
}