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