public async Task <Supplier> CreateAndAddAsync(IEnumerable <KeyValuePair <string, object> > data)
{
try
{
var flattenedData = DbSchemaHelper.FlattenChanges(new Dictionary <string, object>(data));
var sql = string.Join(";",
this.GetInsertSingleSQLFormat(flattenedData),
this.GetSelectScopeIdentitySQL());
// TODO: consider using QueryMultipleAsync
var id = await SqlMapper.ExecuteScalarAsync <long>(
this._connection,
new CommandDefinition(
commandText : sql,
parameters : flattenedData.Select(kvp => new KeyValuePair <string, object>("@" + kvp.Key, kvp.Value)),
transaction : this._transaction));
sql = this.GetSelectAllSQL() + " " + this.GetSortingSQL();
var lastInsertedRow = await SqlMapper.QueryFirstOrDefaultAsync(
cnn : this._connection,
sql : sql, //$"select * from [{nameof(Supplier)}] where [{nameof(Supplier.Id)}] = "+id,
param : new Dictionary <string, object>()
{
{ nameof(Supplier.Id), id }
},
transaction : this._transaction)
as IDictionary <string, object>;
// TODO: improve parsing, handling nulls etc.
var insertedSupplier = new Supplier(
(int)lastInsertedRow[nameof(Supplier.Id)],
(string)lastInsertedRow[nameof(Supplier.Name)],
(string)lastInsertedRow[nameof(Supplier.Email)],
(string)lastInsertedRow[nameof(Supplier.Website)],
(string)lastInsertedRow[nameof(Supplier.Phone)],
(string)lastInsertedRow[nameof(Supplier.Fax)],
(string)lastInsertedRow[nameof(Supplier.Notes)],
Address.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))]),
Agent.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))]));
//await Task.Delay(1000 * 3);
return(insertedSupplier);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(null);
}
}
public async Task UpdateAsync(long id, IEnumerable <KeyValuePair <string, object> > changes)
{
try
{
// TODO: find a way to avoid parameter keys collisions, like id / changes.id, maybe =<param_name> insread of @param_name ()
var queryParams = new
{
UpdateId = id,
Changes = changes
};
var flattenedChanges = DbSchemaHelper.FlattenChanges(new Dictionary <string, object>(changes));
var sets = flattenedChanges.Select(x => $"[{x.Key}] = @{x.Key}").ToArray();
var sql = $"update [{nameof(Supplier)}] set {string.Join(",", sets)} where [{nameof(Supplier.Id)}] = {id};"; // TODO: is it ok to encode id into string?
var affectedRows = await SqlMapper.ExecuteAsync(
this._connection,
new CommandDefinition(
commandText : sql,
parameters : flattenedChanges,
transaction : this._transaction));
if (affectedRows <= 0)
{
throw new KeyNotFoundException();
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
}
//public const string PropertySeparator = ".";
//public static string SubProp(params string[] propertyNames)
//{
// return string.Join(PropertySeparator, propertyNames);
//}
public static Supplier ToSupplier(this IDictionary <string, object> values)
{
// TODO: create helper method dictionary -> supplier
return(new Supplier(
(int)values[nameof(Supplier.Id)],
(string)values[nameof(Supplier.Name)],
(string)values[nameof(Supplier.Email)],
(string)values[nameof(Supplier.Website)],
(string)values[nameof(Supplier.Phone)],
(string)values[nameof(Supplier.Fax)],
(string)values[nameof(Supplier.Notes)],
//(Address)values[nameof(Supplier.Address)],
Address.Create(
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))]),
//(Agent)values[nameof(Supplier.Agent)]
Agent.Create(
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))],
(string)values[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))])));
}
public override async Task <Bill> CreateAndAddAsync(IEnumerable <KeyValuePair <string, object> > data)
{
try
{
var flattenedData = DbSchemaHelper.FlattenChanges(new Dictionary <string, object>(data));
var columns = flattenedData.Select(x => "[" + x.Key + "]");
//var values = data.Select(x => x.Value);
var values = flattenedData.Select(x => "@" + x.Key);
// TODO: consider using QueryMultipleAsync
var id = await SqlMapper.ExecuteScalarAsync <int>(
this._connection,
new CommandDefinition(
commandText : string.Join(";",
$"insert into [{nameof(Bill)}] ({string.Join(",", columns)}) values ({string.Join(",", values)})",
"SELECT last_insert_rowid()"),
parameters : flattenedData.Select(kvp => new KeyValuePair <string, object>("@" + kvp.Key, kvp.Value)),
transaction : this._transaction))
.ConfigureAwait(false);
var lastInsertedCmd = new CommandDefinition(
commandText: $"select * from [{nameof(Bill)}] where \"{nameof(Bill.Id)}\" = @{nameof(Bill.Id)}",
parameters: new Dictionary <string, object>()
{
[nameof(Bill.Id)] = id
},
transaction: this._transaction);
var lastInsertedRow = await SqlMapper.QueryFirstOrDefaultAsync(
cnn : this._connection,
lastInsertedCmd)
.ConfigureAwait(false)
as IDictionary <string, object>;
// TODO: improve parsing, handling nulls etc.
var insertedBill = new Bill(
(long)lastInsertedRow[nameof(Bill.Id)],
(long)lastInsertedRow[nameof(Bill.SupplierId)],
(DateTime)lastInsertedRow[nameof(Bill.ReleaseDate)],
(DateTime)lastInsertedRow[nameof(Bill.DueDate)],
(DateTime?)lastInsertedRow[nameof(Bill.PaymentDate)],
(DateTime)lastInsertedRow[nameof(Bill.RegistrationDate)],
(double)lastInsertedRow[nameof(Bill.Amount)],
(double)lastInsertedRow[nameof(Bill.Agio)],
(double)lastInsertedRow[nameof(Bill.AdditionalCosts)],
(string)lastInsertedRow[nameof(Bill.Code)],
(string)lastInsertedRow[nameof(Bill.Notes)]);
//await Task.Delay(1000 * 3);
return(insertedBill);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(null);
}
}
public override async Task <IReadOnlyCollection <Supplier> > GetMultipleAsync(SupplierCriteria criteria = null)
{
try
{
var suppliers = new List <Supplier>();
var cmd = new CommandDefinition(
commandText: $"select * from [{nameof(Supplier)}] order by { nameof(Supplier.Name) } asc",
transaction: this.GetTransactionIfAvailable());
//var resultsGrid = SqlMapper.QueryMultipleAsync(this._connection, cmd);
//var x = await resultsGrid.Result.ReadAsync();
await using (var reader = await SqlMapper.ExecuteReaderAsync(cnn: this._connection, cmd).ConfigureAwait(false))
{
while (await reader.ReadAsync())
{
// TODO: cache column names composition
var address = Address.Create(
country: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))).ConfigureAwait(false),
province: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))).ConfigureAwait(false),
city: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))).ConfigureAwait(false),
zip: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))).ConfigureAwait(false),
street: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))).ConfigureAwait(false),
number: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))).ConfigureAwait(false));
var agent = Agent.Create(
name: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))).ConfigureAwait(false),
surname: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))).ConfigureAwait(false),
phone: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))).ConfigureAwait(false));
suppliers.Add(
new Supplier(
id: await reader.GetSafeAsync <long>(nameof(Supplier.Id)).ConfigureAwait(false),
name: await reader.GetSafeAsync <string>(nameof(Supplier.Name)).ConfigureAwait(false),
eMail: await reader.GetSafeAsync <string>(nameof(Supplier.Email)).ConfigureAwait(false),
webSite: await reader.GetSafeAsync <string>(nameof(Supplier.Website)).ConfigureAwait(false),
phone: await reader.GetSafeAsync <string>(nameof(Supplier.Phone)).ConfigureAwait(false),
fax: await reader.GetSafeAsync <string>(nameof(Supplier.Fax)).ConfigureAwait(false),
notes: await reader.GetSafeAsync <string>(nameof(Supplier.Notes)).ConfigureAwait(false),
address: address,
agent: agent));
}
}
return(suppliers);
}
catch (InvalidCastException ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Supplier>());
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Supplier>());
}
}
public async Task <IReadOnlyCollection <Supplier> > GetMultipleAsync(SupplierCriteria criteria = null)
{
try
{
var suppliers = new List <Supplier>();
var reader = await SqlMapper.ExecuteReaderAsync(
cnn : this._connection,
sql : this.GetSelectAllSQL() + " " + this.GetSortingSQL(), // $"select * from [{nameof(Supplier)}];",
transaction : this.GetTransactionIfAvailable());
await using (reader)
{
while (await reader.ReadAsync())
{
var address = Address.Create(
country: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))),
province: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))),
city: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))),
zip: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))),
street: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))),
number: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))));
var agent = Agent.Create(
name: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))),
surname: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))),
phone: await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))));
suppliers.Add(
new Supplier(
id: await reader.GetSafeAsync <int>(nameof(Supplier.Id)),
name: await reader.GetSafeAsync <string>(nameof(Supplier.Name)),
eMail: await reader.GetSafeAsync <string>(nameof(Supplier.Email)),
webSite: await reader.GetSafeAsync <string>(nameof(Supplier.Website)),
phone: await reader.GetSafeAsync <string>(nameof(Supplier.Phone)),
fax: await reader.GetSafeAsync <string>(nameof(Supplier.Fax)),
notes: await reader.GetSafeAsync <string>(nameof(Supplier.Notes)),
address: address,
agent: agent));
}
}
//await Task.Delay(1000 * 3);
return(suppliers);
}
catch (InvalidCastException ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Supplier>());
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Supplier>());
}
}
public async Task <IReadOnlyCollection <Supplier> > GetByIdAsync(params long[] ids)
{
try
{
var query = $"select * from [{nameof(Supplier)}] where [{nameof(Supplier.Id)}] in ({string.Join(",", ids)});";
var suppliers = new List <Supplier>();
await using (var reader = await SqlMapper.ExecuteReaderAsync(
cnn: this._connection,
sql: query,
transaction: this.GetTransactionIfAvailable()))
{
while (await reader.ReadAsync())
{
suppliers.Add(
new Supplier(
await reader.GetSafeAsync <int>(nameof(Supplier.Id)),
await reader.GetSafeAsync <string>(nameof(Supplier.Name)),
await reader.GetSafeAsync <string>(nameof(Supplier.Email)),
await reader.GetSafeAsync <string>(nameof(Supplier.Website)),
await reader.GetSafeAsync <string>(nameof(Supplier.Phone)),
await reader.GetSafeAsync <string>(nameof(Supplier.Fax)),
await reader.GetSafeAsync <string>(nameof(Supplier.Notes)),
Address.Create(
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number)))),
Agent.Create(
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))),
await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))))));
}
}
return(suppliers);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(null);
}
}
public override async Task <Supplier> GetByIdAsync(long id)
{
try
{
var queryParams = new { SearchedId = id };
var query = $"select * from [{nameof(Supplier)}] where \"{nameof(Supplier.Id)}\" = @{nameof(queryParams.SearchedId)};";
var lastInsertedRow = await SqlMapper
.QueryFirstOrDefaultAsync(
cnn : this._connection,
sql : query,
param : queryParams,
transaction : this.GetTransactionIfAvailable())
.ConfigureAwait(false)
as IDictionary <string, object>;
// TODO: create helper method dictionary -> supplier
var supplier = new Supplier(
(long)lastInsertedRow[nameof(Supplier.Id)],
(string)lastInsertedRow[nameof(Supplier.Name)],
(string)lastInsertedRow[nameof(Supplier.Email)],
(string)lastInsertedRow[nameof(Supplier.Website)],
(string)lastInsertedRow[nameof(Supplier.Phone)],
(string)lastInsertedRow[nameof(Supplier.Fax)],
(string)lastInsertedRow[nameof(Supplier.Notes)],
Address.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.Country))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.Province))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.City))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.Zip))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.Street))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Supplier.Address.Number))]),
Agent.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Supplier.Agent.Name))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Supplier.Agent.Surname))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Supplier.Agent.Phone))]));
return(supplier);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(null);
}
}
public override async Task <IReadOnlyCollection <DetailedBillDto> > HandleAsync(DetailedBillsQuery query)
{
try
{
var cmd = new CommandDefinition(
$"select bill.*, supplier.{nameof(Supplier.Id)}, supplier.{nameof(Supplier.Name)}" +
$"from {nameof(Bill)} bill left join {nameof(Supplier)} supplier " +
$"on bill.{nameof(Bill.SupplierId)} = supplier.{nameof(Supplier.Id)}",
this.Transaction);
var detailedBills = new List <DetailedBillDto>();
await using (var reader = await SqlMapper.ExecuteReaderAsync(this.Connection, cmd).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
var detailedBillDto = new DetailedBillDto()
{
Id = await reader.GetSafeAsync <long>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
AdditionalCosts = await reader.GetSafeAsync <double>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
Agio = await reader.GetSafeAsync <double>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
Amount = await reader.GetSafeAsync <double>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
Code = await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
DueDate = await reader.GetSafeAsync <DateTime>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
Notes = await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
PaymentDate = await reader.GetSafeAsync <DateTime?>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
RegistrationDate = await reader.GetSafeAsync <DateTime>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
ReleaseDate = await reader.GetSafeAsync <DateTime>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
SupplierHeader = new SupplierHeaderDto()
{
Id = await reader.GetSafeAsync <long>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false),
Name = await reader.GetSafeAsync <string>(DbSchemaHelper.ComposeColumnName(nameof(Bill), nameof(Bill.Id))).ConfigureAwait(false)
}
};
}
}
return(detailedBills.ToArray());
}
catch (Exception)
{
throw;
}
}
private TransformManager GetTransformManager(bool validate)
{
TransformManager manager;
if (!validate)
{
manager = new TransformManager();
manager.Initialize(_colMappings, _colMappings.IdColumn);
}
else
{
var dsh = new DbSchemaHelper();
var schema = dsh.GetSchema(_destinationConn, _destinationTable);
var trans = new ValidatingTransformManager();
trans.Initialize(_colMappings, _colMappings.IdColumn, schema);
trans.DataValidationError += new EventHandler <DataValidationErrorEventArgs>(delegate(object sender, DataValidationErrorEventArgs e) { OnDataValidationError(e); });
manager = trans;
}
return(manager);
}
public override async Task <Supplier> CreateAndAddAsync(IEnumerable <KeyValuePair <string, object> > data)
{
try
{
var flattenedData = DbSchemaHelper.FlattenChanges(new Dictionary <string, object>(data));
var columns = flattenedData.Select(x => "[" + x.Key + "]");
//var values = data.Select(x => x.Value);
var values = flattenedData.Select(x => "@" + x.Key);
var queryParams = flattenedData.Select(kvp => new KeyValuePair <string, object>("@" + kvp.Key, kvp.Value));
//var rawQuery = $"insert into [{nameof(Supplier)}] ({string.Join(",", columns)}) values ({string.Join(",", flattenedData.Select(x => "\"" + x.Value + "\""))})";
var insertBillsCommand = new CommandDefinition(
commandText: string.Join(";",
$"insert into [{nameof(Supplier)}] ({string.Join(",", columns)}) values ({string.Join(",", values)})",
"SELECT last_insert_rowid()"),
parameters: queryParams,
transaction: this._transaction);
// TODO: consider using QueryMultipleAsync
var id = await SqlMapper
.ExecuteScalarAsync <int>(this._connection, insertBillsCommand)
.ConfigureAwait(false);
var getLastInsertedSupplierCommand = new CommandDefinition(
commandText: $"select * from [{nameof(Supplier)}] where \"{nameof(Supplier.Id)}\" = @{nameof(Supplier.Id)}",
parameters: new Dictionary <string, object>()
{
[nameof(Supplier.Id)] = id
},
transaction: this._transaction);
var lastInsertedRow = await SqlMapper
.QueryFirstOrDefaultAsync(cnn : this._connection, getLastInsertedSupplierCommand)
.ConfigureAwait(false)
as IDictionary <string, object>;
// TODO: improve parsing, handling nulls etc.
var insertedSupplier = new Supplier(
(long)lastInsertedRow[nameof(Supplier.Id)],
(string)lastInsertedRow[nameof(Supplier.Name)],
(string)lastInsertedRow[nameof(Supplier.Email)],
(string)lastInsertedRow[nameof(Supplier.Website)],
(string)lastInsertedRow[nameof(Supplier.Phone)],
(string)lastInsertedRow[nameof(Supplier.Fax)],
(string)lastInsertedRow[nameof(Supplier.Notes)],
Address.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))]),
Agent.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))]));
//await Task.Delay(1000 * 3);
return(insertedSupplier);
}
catch (SQLiteException ex)
{
// UNIQUE: error code = 19
Debug.WriteLine(ex);
switch (ex.ErrorCode)
{
case 19: // UNIQUE
var tableField = ex.Message.Split(' ', StringSplitOptions.RemoveEmptyEntries).LastOrDefault()?.Split('.', StringSplitOptions.RemoveEmptyEntries);
throw new UniqueConstraintViolationException(tableField?.FirstOrDefault(), string.Join('.', tableField?.Skip(1)));
}
return(null);
}
}
public static IDictionary <string, object> ToDictionary(this Supplier supplier, DictionaryWritingTools tools = null)
{
var dict = tools?.Dictionary ?? new Dictionary <string, object>();
dict.Add(nameof(Supplier.Id), supplier.Id);
dict.Add(nameof(Supplier.Name), supplier.Name);
dict.Add(nameof(Supplier.Email), supplier.Email);
dict.Add(nameof(Supplier.Website), supplier.Website);
dict.Add(nameof(Supplier.Phone), supplier.Phone);
dict.Add(nameof(Supplier.Fax), supplier.Fax);
dict.Add(nameof(Supplier.Notes), supplier.Notes);
supplier.Address.ToDictionary(new DictionaryWritingTools(dict, (string propName) => DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), propName)));
supplier.Agent.ToDictionary(new DictionaryWritingTools(dict, (string propName) => DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), propName)));
//if (supplier.Address != null)
//{
// //(Address)values[nameof(Supplier.Address)
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.Country)), supplier.Address.Country);
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.Province)), supplier.Address.Province);
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.City)), supplier.Address.City);
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.Zip)), supplier.Address.Zip);
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.Street)), supplier.Address.Country);
// dict.Add(SubProp(nameof(Supplier.Address), nameof(Address.Number)), supplier.Address.Number);
//}
//if (supplier.Agent != null)
//{
// //(Agent)valuesdict.Add(nameof(Supplier.Agent)]
// dict.Add(SubProp(nameof(Supplier.Agent), nameof(Agent.Name)), supplier.Agent.Name);
// dict.Add(SubProp(nameof(Supplier.Agent), nameof(Agent.Surname)), supplier.Agent.Surname);
// dict.Add(SubProp(nameof(Supplier.Agent), nameof(Agent.Phone)), supplier.Agent.Phone);
//}
return(dict);
}
private void DoLoadDbSchema()
{
MainViewModel.SelectedSprint.DbSchemaTables = DbSchemaHelper.ConvertDbSchemaTables(DbSchemaHelper.GetTablesWithColumns(dbConnection));
}
public override async Task <Supplier> CreateAndAddAsync(IEnumerable <KeyValuePair <string, object> > data)
{
try
{
var flattenedData = DbSchemaHelper.FlattenChanges(new Dictionary <string, object>(data));
var columns = flattenedData.Select(x => "[" + x.Key + "]");
//var values = data.Select(x => x.Value);
var values = flattenedData.Select(x => "@" + x.Key);
var sql =
$"insert into [{nameof(Supplier)}] ({string.Join(",", columns)}) values ({string.Join(",", values)});" +
"SELECT SCOPE_IDENTITY();";
// TODO: consider using QueryMultipleAsync
var id = await SqlMapper.ExecuteScalarAsync <int>(
this._connection,
new CommandDefinition(
commandText : sql,
parameters : flattenedData.Select(kvp => new KeyValuePair <string, object>("@" + kvp.Key, kvp.Value)),
transaction : this._transaction)).ConfigureAwait(false);
sql = $"select * from [{nameof(Supplier)}] where [{nameof(Supplier.Id)}] = @{nameof(Supplier.Id)};";
var lastInsertedRow = await SqlMapper.QueryFirstOrDefaultAsync(
cnn : this._connection,
sql : sql, //$"select * from [{nameof(Supplier)}] where [{nameof(Supplier.Id)}] = "+id,
param : new Dictionary <string, object>()
{
{ nameof(Supplier.Id), id }
},
transaction : this._transaction).ConfigureAwait(false)
as IDictionary <string, object>;
// TODO: improve parsing, handling nulls etc.
var insertedSupplier = new Supplier(
(int)lastInsertedRow[nameof(Supplier.Id)],
(string)lastInsertedRow[nameof(Supplier.Name)],
(string)lastInsertedRow[nameof(Supplier.Email)],
(string)lastInsertedRow[nameof(Supplier.Website)],
(string)lastInsertedRow[nameof(Supplier.Phone)],
(string)lastInsertedRow[nameof(Supplier.Fax)],
(string)lastInsertedRow[nameof(Supplier.Notes)],
Address.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Country))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Province))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.City))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Zip))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Street))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Address), nameof(Address.Number))]),
Agent.Create(
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Name))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Surname))],
(string)lastInsertedRow[DbSchemaHelper.ComposeColumnName(nameof(Supplier.Agent), nameof(Agent.Phone))]));
//await Task.Delay(1000 * 3);
return(insertedSupplier);
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(null);
}
}
