private static string GenerateInsertStatement(ObjectTableMap map)
{
var properties = map.Columns.Where(c => !c.DatabaseGenerated).ToList();
var columnList = string.Join(", ", properties.Select(p => p.Column));
var valueList = string.Join(", ", properties.Select(p => "@" + p.Property.Name));
var insert = $"INSERT INTO [{map.Schema}].[{map.Table}] ({columnList}) OUTPUT inserted.* VALUES ({valueList})";
return(insert);
}
public static string GetFromStatement(this ObjectTableMap map, string alias = null)
{
if (alias == null)
{
alias = string.Empty;
}
else
{
alias = " AS " + alias;
}
var from = $"[{map.Schema}].[{map.Table}]" + alias;
return(from);
}
public static string GetSelectStatement(this ObjectTableMap map, string alias = null)
{
if (alias == null)
{
alias = string.Empty;
}
else
{
alias = alias + ".";
}
var select = string.Join(", ", map.Columns.Select(c => alias + $"[{c.Column}] as [{c.Property.Name}]"));
return(select);
}
public static string GenerateUpsertStatement(ObjectTableMap map)
{
var notGeneratedColumns = map.Columns.Where(m => !m.DatabaseGenerated).ToList();
var columns = map.Columns;
var uniqueColumns = map.Columns.Where(m => m.IsUnique).ToList();
var idColumns = map.Columns.Where(m => m.IsKey).ToList();
var builder = new StringBuilder();
builder.AppendLine($"MERGE INTO [{map.Schema}].[{map.Table}] AS tgt");
builder.AppendLine($"USING (SELECT {string.Join(", ", columns.Select(p => $"@{p.Property.Name} AS {p.Column}"))}) AS src");
builder.AppendLine($"ON {string.Join(" AND ", idColumns.Select(ic => $"src.{ic.Column} = tgt.{ic.Column}"))}");
if (uniqueColumns.Count > 0)
{
builder.AppendLine(
$"OR {string.Join(" AND ", uniqueColumns.Select(ic => $"src.{ic.Column} = tgt.{ic.Column}"))}");
}
builder.AppendLine("WHEN MATCHED ");
builder.AppendLine($"THEN UPDATE SET {string.Join(", ", notGeneratedColumns.Select(p => $"{p.Column} = src.{p.Column}"))}");
builder.AppendLine($"WHEN NOT MATCHED THEN INSERT ({string.Join(", ", notGeneratedColumns.Select(p => $"{p.Column}"))}) VALUES ({string.Join(", ", notGeneratedColumns.Select(p => $"src.{p.Column}"))})");
builder.AppendLine($"OUTPUT {string.Join(", ", columns.Select(p => $"inserted.{p.Column} AS {p.Property.Name}"))};");
return(builder.ToString());
}
private static QueryBuilder GenerateSelectByIdStatement(ObjectTableMap map, params object[] keys)
{
var keyColumns = map.Columns.Where(c => c.IsKey).ToList();
if (keys.Length != keyColumns.Count)
{
throw new ArgumentException(nameof(keys));
}
var builder = new QueryBuilder();
builder.AddSelectStatement(map.GetSelectStatement());
builder.AddFromStatement(map.GetFromStatement());
var keyIndex = 0;
foreach (var keyColumn in keyColumns)
{
builder.AddWhereStatement("AND", $"{keyColumn.Column} = @{keyColumn.Property.Name}");
builder.AddParameter($"@{keyColumn.Property.Name}", keys[keyIndex++]);
}
return(builder);
}
