public static string UpdateSetTable(TableInfo tableInfo, string tableName = null)
{
tableName ??= tableInfo.TableName;
List <string> columnsList = tableInfo.PropertyColumnNamesDict.Values.ToList();
List <string> primaryKeys = tableInfo.PrimaryKeysPropertyColumnNameDict.Select(k => tableInfo.PropertyColumnNamesDict[k.Key]).ToList();
var commaSeparatedColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList, equalsTable: "@", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = @[", "] = @").Replace(".", "_");;
var commaSeparatedPrimaryKeys = SqlQueryBuilder.GetANDSeparatedColumns(primaryKeys, equalsTable: "@", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = @[", "] = @").Replace(".", "_");;
var q = $"UPDATE [{tableName}] " +
$"SET {commaSeparatedColumns} " +
$"WHERE {commaSeparatedPrimaryKeys};";
return(q);
}
public static string InsertIntoTable(TableInfo tableInfo, OperationType operationType, string tableName = null)
{
tableName ??= tableInfo.InsertToTempTable ? tableInfo.FullTempTableName : tableInfo.FullTableName;
tableName = tableName.Replace("[", @"""").Replace("]", @"""");
var columnsList = GetColumnList(tableInfo, operationType);
var commaSeparatedColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList).Replace("[", @"""").Replace("]", @"""");
var q = $"COPY {tableName} " +
$"({commaSeparatedColumns}) " +
$"FROM STDIN (FORMAT BINARY)";
return(q + ";");
}
// In Sqlite if table has AutoIncrement then InsertOrUpdate is not supported in one call,
// we can not simultaneously Insert without PK(being 0,0,...) and Update with PK(1,2,...), separate calls Insert, Update are required.
public static string InsertIntoTable(TableInfo tableInfo, OperationType operationType, string tableName = null)
{
tableName ??= tableInfo.InsertToTempTable ? tableInfo.TempTableName : tableInfo.TableName;
var tempDict = tableInfo.PropertyColumnNamesDict;
if (operationType == OperationType.Insert && tableInfo.PropertyColumnNamesDict.Any()) // Only OnInsert omit colums with Default values
{
tableInfo.PropertyColumnNamesDict = tableInfo.PropertyColumnNamesDict.Where(a => !tableInfo.DefaultValueProperties.Contains(a.Key)).ToDictionary(a => a.Key, a => a.Value);
}
List <string> columnsList = tableInfo.PropertyColumnNamesDict.Values.ToList();
List <string> propertiesList = tableInfo.PropertyColumnNamesDict.Keys.ToList();
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
if (operationType == OperationType.Insert && !keepIdentity && tableInfo.HasIdentity)
{
var identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
columnsList = columnsList.Where(a => a != tableInfo.IdentityColumnName).ToList();
propertiesList = propertiesList.Where(a => a != identityPropertyName).ToList();
}
var commaSeparatedColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList);
var commaSeparatedColumnsParams = SqlQueryBuilder.GetCommaSeparatedColumns(propertiesList, "@").Replace("[", "").Replace("]", "").Replace(".", "_");
var q = $"INSERT INTO [{tableName}] " +
$"({commaSeparatedColumns}) " +
$"VALUES ({commaSeparatedColumnsParams})";
if (operationType == OperationType.InsertOrUpdate)
{
List <string> primaryKeys = tableInfo.PrimaryKeysPropertyColumnNameDict.Select(k => tableInfo.PropertyColumnNamesDict[k.Key]).ToList();
var commaSeparatedPrimaryKeys = SqlQueryBuilder.GetCommaSeparatedColumns(primaryKeys);
var commaSeparatedColumnsEquals = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList, equalsTable: "", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = .[", "] = @").Replace(".", "_");
var commaANDSeparatedPrimaryKeys = SqlQueryBuilder.GetANDSeparatedColumns(primaryKeys, equalsTable: "@", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = @[", "] = @").Replace(".", "_");
q += $" ON CONFLICT({commaSeparatedPrimaryKeys}) DO UPDATE" +
$" SET {commaSeparatedColumnsEquals}" +
$" WHERE {commaANDSeparatedPrimaryKeys}";
}
tableInfo.PropertyColumnNamesDict = tempDict;
return(q + ";");
}
public static string MergeTable <T>(DbContext context, TableInfo tableInfo, OperationType operationType, IEnumerable <string> entityPropertyWithDefaultValue = default) where T : class
{
var columnsList = GetColumnList(tableInfo, operationType);
if (operationType == OperationType.InsertOrUpdateOrDelete)
{
throw new NotImplementedException($"For Postgres method {OperationType.InsertOrUpdateOrDelete} is not yet supported. Use combination of InsertOrUpdate with Read and Delete");
}
string q;
if (operationType == OperationType.Read)
{
var readByColumns = SqlQueryBuilder.GetCommaSeparatedColumns(tableInfo.PrimaryKeysPropertyColumnNameDict.Values.ToList()); //, tableInfo.FullTableName, tableInfo.FullTempTableName
q = $"SELECT {tableInfo.FullTableName}.* FROM {tableInfo.FullTableName} " +
$"JOIN {tableInfo.FullTempTableName} " +
$"USING ({readByColumns})"; //$"ON ({tableInfo.FullTableName}.readByColumns = {tableInfo.FullTempTableName}.readByColumns);";
}
else if (operationType == OperationType.Delete)
{
var deleteByColumns = SqlQueryBuilder.GetCommaSeparatedColumns(tableInfo.PrimaryKeysPropertyColumnNameDict.Values.ToList(), tableInfo.FullTableName, tableInfo.FullTempTableName);
deleteByColumns = deleteByColumns.Replace(",", " AND");
deleteByColumns = deleteByColumns.Replace("[", @"""").Replace("]", @"""");
q = $"DELETE FROM {tableInfo.FullTableName} " +
$"USING {tableInfo.FullTempTableName} " +
$@"WHERE {deleteByColumns}";
}
else
{
var commaSeparatedColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList).Replace("[", @"""").Replace("]", @"""");
var updateByColumns = SqlQueryBuilder.GetCommaSeparatedColumns(tableInfo.PrimaryKeysPropertyColumnNameDict.Values.ToList()).Replace("[", @"""").Replace("]", @"""");
var columnsListEquals = GetColumnList(tableInfo, OperationType.Insert);
var equalsColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsListEquals, equalsTable: "EXCLUDED").Replace("[", @"""").Replace("]", @"""");
q = $"INSERT INTO {tableInfo.FullTableName} ({commaSeparatedColumns}) " +
$"(SELECT {commaSeparatedColumns} FROM {tableInfo.FullTempTableName}) " +
$"ON CONFLICT ({updateByColumns}) " +
$"DO UPDATE SET {equalsColumns}";
if (tableInfo.CreatedOutputTable)
{
var allColumnsList = tableInfo.PropertyColumnNamesDict.Values.ToList();
string commaSeparatedColumnsNames = SqlQueryBuilder.GetCommaSeparatedColumns(allColumnsList).Replace("[", @"""").Replace("]", @"""");
q += $" RETURNING {commaSeparatedColumnsNames}";
}
}
q = q.Replace("[", @"""").Replace("]", @"""");
q += ";";
Dictionary <string, string> sourceDestinationMappings = tableInfo.BulkConfig.CustomSourceDestinationMappingColumns;
if (tableInfo.BulkConfig.CustomSourceTableName != null && sourceDestinationMappings != null && sourceDestinationMappings.Count > 0)
{
var textSelect = "SELECT ";
var textFrom = " FROM";
int startIndex = q.IndexOf(textSelect);
var qSegment = q.Substring(startIndex, q.IndexOf(textFrom) - startIndex);
var qSegmentUpdated = qSegment;
foreach (var mapping in sourceDestinationMappings)
{
var propertyFormated = $@"""{mapping.Value}""";
var sourceProperty = mapping.Key;
if (qSegment.Contains(propertyFormated))
{
qSegmentUpdated = qSegmentUpdated.Replace(propertyFormated, $@"""{sourceProperty}""");
}
}
if (qSegment != qSegmentUpdated)
{
q = q.Replace(qSegment, qSegmentUpdated);
}
}
return(q);
}