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