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);
row[col.ColumnName] = dbVal;
}
}
else if (dataD.TryGetValue(col.ColumnName, out rowVal))
{
var dbVal = SqlExtensions.ConvertTo(rowVal, 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);
}
}
}
}
public async Task <IList <T> > LoadListAsync <T>(String source, String command, Object prms) where T : class
{
using (var token = _host.Profiler.Start(ProfileAction.Sql, command))
{
Type retType = typeof(T);
var props = retType.GetProperties();
var result = new List <T>();
using (var cnn = await GetConnectionAsync(source))
{
using (var cmd = cnn.CreateCommandSP(command))
{
SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
using (var rdr = await cmd.ExecuteReaderAsync())
{
var keyMap = new Dictionary <String, Int32>();
for (int c = 0; c < rdr.FieldCount; c++)
{
keyMap.Add(rdr.GetName(c), c);
}
while (await rdr.ReadAsync())
{
T item = System.Activator.CreateInstance(retType) as T;
Int32 fieldIndex;
foreach (var p in props)
{
if (keyMap.TryGetValue(p.Name, out fieldIndex))
{
var dbVal = rdr.GetValue(fieldIndex);
if (dbVal == DBNull.Value)
{
dbVal = null;
}
p.SetValue(item, dbVal);
}
}
result.Add(item);
}
}
}
}
return(result);
}
}
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)
{
var stream = sqlVal as Stream;
if (stream == null)
{
throw new IndexOutOfRangeException("Stream expected");
}
sqlParam.Value = new SqlBytes(stream);
}
else
{
sqlParam.Value = SqlExtensions.ConvertTo(sqlVal, sqlParam.SqlDbType.ToType());
}
}
}
return(retParam);
}
internal void SetTableParameters(SqlCommand cmd, Object data, Object prms)
{
IDictionary <String, Object> scalarParams = SqlExtensions.GetParametersDictionary(prms);
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)
{
Tuple <DataTable, String> table;
if (_tables.TryGetValue(prm.ParameterName, out table))
{
// table parameters (binging by object name)
FillDataTable(table.Item1, GetDataForSave(data as ExpandoObject, table.Item2 /*path*/));
prm.Value = table.Item1;
prm.RemoveDbName(); // remove first segment (database name)
}
else
{
throw new DataWriterException($"Parameter {simpleParamName} not found");
}
}
else if (prm.SqlDbType == SqlDbType.VarBinary)
{
throw new NotImplementedException();
}
else
{
Object paramVal;
// simple parameter
if (scalarParams != null && scalarParams.TryGetValue(simpleParamName, out paramVal))
{
prm.Value = SqlExtensions.Value2SqlValue(paramVal);
}
}
}
}
public async Task <T> LoadAsync <T>(String source, String command, Object prms = null) where T : class
{
using (var p = _host.Profiler.Start(ProfileAction.Sql, command))
{
var helper = new LoadHelper <T>();
using (var cnn = await GetConnectionAsync(source))
{
using (var cmd = cnn.CreateCommandSP(command))
{
SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
using (var rdr = await cmd.ExecuteReaderAsync())
{
helper.ProcessRecord(rdr);
if (await rdr.ReadAsync())
{
T result = helper.ProcessFields(rdr);
return(result);
}
}
}
}
}
return(null);
}