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