void ProcessOneDataElem(DataTable table, ExpandoObject data)
{
var row = table.NewRow();
var dataD = data as IDictionary <String, Object>;
for (int c = 0; c < table.Columns.Count; c++)
{
Object rowVal;
var col = table.Columns[c];
if (col.ColumnName.Contains("."))
{
// complex value
if (GetComplexValue(data, col.ColumnName, out rowVal))
{
var dbVal = SqlExtensions.ConvertTo(rowVal, col.DataType);
dbVal = CheckId(col.ColumnName, dbVal, col.DataType);
row[col.ColumnName] = dbVal;
}
}
else if (dataD.TryGetValue(col.ColumnName, out rowVal))
{
var dbVal = SqlExtensions.ConvertTo(rowVal, col.DataType);
dbVal = CheckId(col.ColumnName, dbVal, col.DataType);
row[col.ColumnName] = dbVal;
}
}
table.Rows.Add(row);
}
void SetParametersWithList <T>(SqlCommand cmd, Object prms, IEnumerable <T> list) where T : class
{
Type listType = typeof(T);
Type prmsType = prms != null?prms.GetType() : null;
var props = listType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
var propsD = new Dictionary <String, PropertyInfo>();
DataTable dt = new DataTable();
foreach (var p in props)
{
var column = new DataColumn(p.Name, p.PropertyType);
if (p.PropertyType == typeof(String))
{
column.MaxLength = 32767;
}
dt.Columns.Add(column);
propsD.Add(p.Name, p);
}
for (int i = 0; i < cmd.Parameters.Count; i++)
{
SqlParameter prm = cmd.Parameters[i];
var simpleParamName = prm.ParameterName.Substring(1); // skip @
if (prm.SqlDbType == SqlDbType.Structured)
{
foreach (var itm in list)
{
var row = dt.NewRow();
for (int c = 0; c < dt.Columns.Count; c++)
{
var col = dt.Columns[c];
var rowVal = propsD[col.ColumnName].GetValue(itm);
var dbVal = SqlExtensions.ConvertTo(rowVal, col.DataType);
row[col.ColumnName] = dbVal;
}
dt.Rows.Add(row);
}
prm.Value = dt;
prm.RemoveDbName(); // remove first segment (database name)
}
else if (prmsType != null)
{
// scalar parameter
var pi = prmsType.GetProperty(simpleParamName);
if (pi != null)
{
prm.Value = pi.GetValue(prms);
}
}
}
}
SqlParameter SetParametersFromExpandoObject(SqlCommand cmd, ExpandoObject element)
{
if (element == null)
{
return(null);
}
var elem = element as IDictionary <String, Object>;
SqlCommandBuilder.DeriveParameters(cmd);
var sqlParams = cmd.Parameters;
SqlParameter retParam = null;
if (cmd.Parameters.Contains(RET_PARAM_NAME))
{
retParam = cmd.Parameters[RET_PARAM_NAME];
retParam.Value = DBNull.Value;
}
foreach (var kv in elem)
{
var paramName = "@" + kv.Key;
if (sqlParams.Contains(paramName))
{
var sqlParam = sqlParams[paramName];
var sqlVal = kv.Value;
if (sqlParam.SqlDbType == SqlDbType.VarBinary)
{
if (!(sqlVal is Stream stream))
{
throw new IndexOutOfRangeException("Stream expected");
}
sqlParam.Value = new SqlBytes(stream);
}
else
{
sqlParam.Value = SqlExtensions.ConvertTo(sqlVal, sqlParam.SqlDbType.ToType());
}
}
}
return(retParam);
}
SqlParameter SetParametersFrom <T>(SqlCommand cmd, T element)
{
Type retType = typeof(T);
var props = retType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
SqlCommandBuilder.DeriveParameters(cmd);
var sqlParams = cmd.Parameters;
SqlParameter retParam = null;
if (cmd.Parameters.Contains(RET_PARAM_NAME))
{
retParam = cmd.Parameters[RET_PARAM_NAME];
retParam.Value = DBNull.Value;
}
foreach (var p in props)
{
var paramName = "@" + p.Name;
if (sqlParams.Contains(paramName))
{
var sqlParam = sqlParams[paramName];
var sqlVal = p.GetValue(element);
if (sqlParam.SqlDbType == SqlDbType.VarBinary)
{
if (!(sqlVal is Stream stream))
{
throw new IndexOutOfRangeException("Stream expected");
}
sqlParam.Value = new SqlBytes(stream);
}
else
{
sqlParam.Value = SqlExtensions.ConvertTo(sqlVal, sqlParam.SqlDbType.ToType());
}
}
}
return(retParam);
}
