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;
}
}
}