public static string BuildUpsertQuery(string tableName, string[] columns, string values, string[] columnsToUpsert)
{
var upsertStatement = new StringBuilder();
foreach (string propertyName in columnsToUpsert)
{
upsertStatement.Append($"{propertyName}=VALUES({propertyName}),");
}
MySqlFormatter.RemoveLastCharacter(upsertStatement);
return(string.Format(upsertQuery, tableName, string.Join(",", columns), values, upsertStatement));
}
private void BuildValueQuery <T>(T value, string[] dbColumnList, ref StringBuilder queryBuilder)
where T : IDbTable
{
queryBuilder.Append("(");
foreach (var column in dbColumnList)
{
var propertyValue = value.GetType().GetProperty(column)?.GetValue(value, null);
queryBuilder.Append(MySqlFormatter.ConvertValueToMySqlFormatstring(propertyValue));
queryBuilder.Append(",");
}
MySqlFormatter.RemoveLastCharacter(queryBuilder);
queryBuilder.Append("),");
}
public void Delete <T>(IEnumerable <T> items) where T : IDbTable
{
var firstItem = items.FirstOrDefault();
string keyColumn = firstItem.GetIdColumn();
string tableName = firstItem.GetTableName();
List <string> values = new List <string>();
var builder = new StringBuilder();
foreach (var item in items)
{
BuildValueQuery(item, new string[] { keyColumn }, ref builder);
}
MySqlFormatter.RemoveLastCharacter(builder);
var deleteQuery = MySqlQuery.BuildDeleteQuery(tableName, keyColumn, builder.ToString());
ExecuteNonQuery(deleteQuery);
}
public void Upsert <T>(IEnumerable <T> items) where T : IDbTable
{
var firstItem = items.FirstOrDefault();
if (firstItem != null)
{
var columnList = GetOrBuildColumnList(firstItem);
string[] objColumnList = columnList.Keys.ToArray();
string[] dbColumnList = columnList.Values.ToArray();
string[] ignoreColumns = firstItem.GetIgnoreColumnsOnUpdate().Split(',')?.ToArray();
string tableName = firstItem.GetTableName();
string[] dbColumnListWithIgnoreColumnList = GetColumnListWithIgnoreProperties(firstItem.GetType(), firstItem, ignoreColumns).ToArray();
var builder = new StringBuilder();
foreach (var item in items)
{
BuildValueQuery(item, objColumnList, ref builder);
}
MySqlFormatter.RemoveLastCharacter(builder);
var upsertQuery = MySqlQuery.BuildUpsertQuery(tableName, dbColumnList, builder.ToString(), dbColumnListWithIgnoreColumnList);
ExecuteNonQuery(upsertQuery);
}
}