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);
}
}
}
}
public async Task <IList <T> > LoadListAsync <T>(String source, String command, System.Object prms) where T : class
{
using var token = _profiler.Start(command);
var listLoader = new ListLoader <T>();
using var cnn = await GetConnectionAsync(source);
using var cmd = cnn.CreateCommandSP(command, CommandTimeout);
SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
using (var rdr = await cmd.ExecuteReaderAsync())
{
listLoader.ProcessFields(rdr);
while (await rdr.ReadAsync())
{
listLoader.ProcessRecord(rdr);
}
}
return(listLoader.Result);
}
public T Load <T>(String source, String command, System.Object prms = null) where T : class
{
using var token = _profiler.Start(command);
using var cnn = GetConnection(source);
using var cmd = cnn.CreateCommandSP(command, CommandTimeout);
var helper = new LoadHelper <T>();
SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
using var rdr = cmd.ExecuteReader();
helper.ProcessRecord(rdr);
if (rdr.Read())
{
T result = helper.ProcessFields(rdr);
return(result);
}
return(null);
}
internal void SetTableParameters(SqlCommand cmd, Object data, Object prms)
{
IDictionary <String, Object> scalarParams = SqlExtensions.GetParametersDictionary(prms);
for (Int32 i = 0; i < cmd.Parameters.Count; i++)
{
SqlParameter prm = cmd.Parameters[i];
var simpleParamName = prm.ParameterName.Substring(1); /*skip @*/
if (prm.SqlDbType == SqlDbType.Structured)
{
if (_tables.TryGetValue(prm.ParameterName, out Tuple <DataTable, String> 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 if (_jsonParams.TryGetValue(prm.ParameterName, out String tablePath))
{
var dataForSave = (data as ExpandoObject).Eval <ExpandoObject>(tablePath);
prm.Value = JsonConvert.SerializeObject(dataForSave);
}
else
{
// simple parameter
if (scalarParams != null && scalarParams.TryGetValue(simpleParamName, out Object paramVal))
{
prm.Value = SqlExtensions.Value2SqlValue(paramVal);
}
}
}
}
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);
}
public IList <T> LoadList <T>(String source, String command, Object prms) where T : class
{
using (var token = _profiler.Start(command))
{
var listLoader = new ListLoader <T>();
using (var cnn = GetConnection(source))
{
using (var cmd = cnn.CreateCommandSP(command))
{
SqlExtensions.SetFromDynamic(cmd.Parameters, prms);
using (var rdr = cmd.ExecuteReader())
{
listLoader.ProcessFields(rdr);
while (rdr.Read())
{
listLoader.ProcessRecord(rdr);
}
}
}
}
return(listLoader.Result);
}
}
public async Task <T> LoadAsync <T>(String source, String command, System.Object prms = null) where T : class
{
using (var p = _profiler.Start(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);
}