public BaseAltiumDbEntity GetEntityByPartNumber(DatabaseTable table, string partNumber)
{
try
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM `{table.TableName}` WHERE `Part Number`= @PartNumber;";
cmd.Parameters.Add("@PartNumber", DbType.String).Value = partNumber;
MySqlDataReader reader = cmd.ExecuteReader();
var row = new BaseAltiumDbEntity();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
row[table.TableColumns[i].ColumnName] = reader[i].ToString();
}
}
reader.Close();
return(row);
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void UpdateDataInTable(DatabaseTable table, string partNumber, BaseAltiumDbEntity data)
{
try
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
$"UPDATE `{table.TableName}` SET {table.GenerateUpdateBinding()} WHERE `Part Number` = @WherePartNumber";
cmd.Parameters.Add("@WherePartNumber", DbType.String).Value = partNumber;
foreach (var column in table.TableColumns)
{
cmd.Parameters.Add(column.ParameterName, MySqlDbType.String).Value = data[column.ColumnName];
}
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void InsertIntoTable(DatabaseTable table, BaseAltiumDbEntity data)
{
try
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
$"INSERT INTO `{table.TableName}` ({table.GetColumns()}) VALUES ({table.GenerateValueBinding()})";
foreach (var column in table.TableColumns)
{
cmd.Parameters.Add(column.ParameterName, MySqlDbType.String).Value = data[column.ColumnName];
}
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public List <BaseAltiumDbEntity> GetDataFromTable(DatabaseTable table)
{
try
{
List <BaseAltiumDbEntity> result = new List <BaseAltiumDbEntity>();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM `{table.TableName}`;";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var row = new BaseAltiumDbEntity();
for (int i = 0; i < reader.FieldCount; i++)
{
row[table.TableColumns[i].ColumnName] = reader[i].ToString();
}
result.Add(row);
}
reader.Close();
return(result);
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public async Task UpdateEntityAsync(string tableName, string partNumber, BaseAltiumDbEntity data)
{
var table = await GetTableByNameAsync(tableName);
if (table == null)
{
throw new NotImplementedException();
}
_tableHelper.UpdateDataInTable(table, partNumber, data);
}
public async Task InsertIntoTableByTableNameAsync(string tableName, BaseAltiumDbEntity data)
{
var table = await GetTableByNameAsync(tableName);
if (table == null)
{
throw new NotImplementedException();
}
_tableHelper.InsertIntoTable(table, data);
}
public async Task <IActionResult> EditEntity([FromRoute] string tableName, [FromRoute] string partNumber, BaseAltiumDbEntity data)
{
await _databaseService.UpdateEntityAsync(tableName, partNumber, data);
return(RedirectToAction(nameof(GetDataFromTable), new { tableName = tableName }));
}
public async Task <IActionResult> CreateEntity([FromRoute] string tableName, BaseAltiumDbEntity data)
{
await _databaseService.InsertIntoTableByTableNameAsync(tableName, data);
return(RedirectToAction(nameof(GetDataFromTable), new { tableName = tableName }));
}
