public override IDbCommand INSERT(DBConnection db, string tableName, Dictionary <string, object> item)
{
MySqlCommand command = new MySqlCommand();
IEnumerable <string> columnNames = item.Keys.Except(new string[] { PrimaryKey, FullPrimaryKey(tableName, false) });
command.CommandText =
$"INSERT INTO {ToRealTableName(db.Application, tableName)}({string.Join(",", columnNames.Select(c => AddQuote(c)))}) " +
$"VALUES ({string.Join(",", columnNames.Select(c => $"@{command.AddParam(c, item[c])}"))});" +
$"SELECT LAST_INSERT_ID() {PrimaryKey};";
return(command);
}
public static ITable Insert(this IDbTransaction transaction, ITable table)
{
var mappedProps = table.GetMappedProperties();
var insertCommand = new MySqlCommand
{
CommandText = table.CreateInsertCommand(mappedProps),
Connection = (MySqlConnection)transaction.Connection,
Transaction = (MySqlTransaction)transaction
};
foreach (var item in mappedProps)
{
insertCommand.AddParam(item.Value);
}
return(transaction.Query(insertCommand, true) ? table : null);
}
public override IDbCommand UPDATE(DBConnection db, string tableName, int rowId, DBItem item)
{
MySqlCommand command = new MySqlCommand();
var columnTuples = ColumnsToTuple(db.Application, tableName, item.getFullColumnNames()).Where(ct => ct.Item2 != PrimaryKey);
command.CommandText =
$"UPDATE {ToRealTableName(db.Application, tableName)} SET {string.Join(", ", columnTuples.Select(pair => $"{ToRealTableName(db.Application, pair.Item1)}.{AddQuote(pair.Item2)}=@{command.AddParam(pair.Item2, item[$"{pair.Item1}.{pair.Item2}"])}"))} WHERE {ToRealTableName(db.Application, tableName)}.{AddQuote(PrimaryKey)}=@_{PrimaryKey}_;";
command.Parameters.Add(new MySqlParameter($"_{PrimaryKey}_", rowId));
return(command);
}
