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