Dynamic SQL Merge statement builder through C#
Generating a merge statement dynamically can be a tedious job. This code help you create a SQL merge statement through C# dynamically at run-time.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 |
public class DynamicMergeStatementBuilder { private string SourceStatement { get; set; } private string TargetStatement { get; set; } private string MatchStatement { get; set; } private string InsertStatement { get; set; } private string UpdateStatement { get; set; } private string DeleteStatement { get; set; } public static string CreateTempTableStatement(string tempTableName, Dictionary<string, string> columnWithDataTypes) { if (string.IsNullOrWhiteSpace(tempTableName)) { throw new ArgumentNullException("Specified value for argument tempTableName is empty"); } ValidateArgument(columnWithDataTypes); StringBuilder builder = new StringBuilder(); string concatenatedCols = string.Join(",", columnWithDataTypes.Select(c => string.Format("[{0}] {1}", c.Key, GetSqlTypeFromFriendlyName(c.Value)))); builder.AppendFormat("CREATE TABLE {0} ({1})", tempTableName, concatenatedCols); return builder.ToString(); } public DynamicMergeStatementBuilder WithTables(string sourceTable, string targetTable) { this.SourceStatement = string.Format("Merge into {0} as t ", targetTable); this.TargetStatement = string.Format("Using {0} as s ", sourceTable); return this; } public DynamicMergeStatementBuilder MatchOn(List<string> columns) { return this.MatchOn(columns.ToDictionary(e => e, e => e)); } public DynamicMergeStatementBuilder MatchOn(Dictionary<string, string> sourceTargetMapping) { ValidateArgument(sourceTargetMapping); StringBuilder builder = new StringBuilder(); builder.AppendLine("on"); foreach (var sourceColumn in sourceTargetMapping.Keys) { builder.AppendFormat("t.[{0}] = s.[{1}] ", sourceColumn, sourceTargetMapping[sourceColumn]); builder.Append("AND "); } builder.Remove(builder.Length - 4, 4); this.MatchStatement = builder.ToString(); return this; } public DynamicMergeStatementBuilder AddInsertStatement(List<string> columns) { return this.AddInsertStatement(columns.ToDictionary(e => e, e => e)); } public DynamicMergeStatementBuilder AddInsertStatement(Dictionary<string, string> sourceTargetMapping) { ValidateArgument(sourceTargetMapping); StringBuilder builder = new StringBuilder(); builder.AppendLine("when not matched then insert"); builder.AppendFormat("({0}) ", string.Join(",", sourceTargetMapping.Values.Select(c => string.Format("[{0}]", c)).ToArray())); builder.Append(string.Format("values (")); foreach (var sourceColumn in sourceTargetMapping.Keys) { builder.AppendFormat("s.[{0}]", sourceColumn); builder.Append(","); } builder.Remove(builder.Length - 1, 1); builder.Append(")"); this.InsertStatement = builder.ToString(); return this; } public DynamicMergeStatementBuilder AddUpdateStatement(List<string> columns) { return this.AddUpdateStatement(columns.ToDictionary(e => e, e => e)); } public DynamicMergeStatementBuilder AddUpdateStatement(Dictionary<string, string> sourceTargetMapping, List<string> targetColumnsWithStaticValues = null) { ValidateArgument(sourceTargetMapping); StringBuilder builder = new StringBuilder(); builder.AppendLine("when matched then update set"); foreach (var sourceColumn in sourceTargetMapping.Keys) { if (targetColumnsWithStaticValues != null && targetColumnsWithStaticValues.Contains(sourceColumn)) { builder.AppendFormat("t.[{0}] = {1} ", sourceColumn, sourceTargetMapping[sourceColumn]); } else { builder.AppendFormat("t.[{0}] = s.[{1}] ", sourceTargetMapping[sourceColumn], sourceColumn); } builder.Append(","); } builder.Remove(builder.Length - 1, 1); this.UpdateStatement = builder.ToString(); return this; } public DynamicMergeStatementBuilder AddDeleteStatement() { this.DeleteStatement = "when not matched by source then delete"; return this; } public string Build() { StringBuilder builder = new StringBuilder(); builder.AppendLine(this.SourceStatement); builder.AppendLine(this.TargetStatement); builder.AppendLine(this.MatchStatement); builder.AppendLine(this.InsertStatement); builder.AppendLine(this.UpdateStatement); builder.AppendLine(this.DeleteStatement); builder.Append(";"); return builder.ToString(); } private static void ValidateArgument(Dictionary<string, string> sourceTargetMapping) { if (sourceTargetMapping == null || sourceTargetMapping.Count == 0) { throw new ArgumentNullException("Number of columns must be greater than zero"); } } private static string GetSqlTypeFromFriendlyName(string typeName) { if (typeName == null) { throw new ArgumentNullException("typeName"); } int minimumPrecision = 10; if (typeName.Contains("(")) { minimumPrecision = int.Parse(typeName.Substring(typeName.IndexOf("(") + 1, (typeName.IndexOf(")") - typeName.IndexOf("(")) - 1)); typeName = typeName.Remove(typeName.IndexOf("("), 3); } typeName = typeName.ToLower(); string parsedTypeName = null; switch (typeName) { case "bool": case "boolean": parsedTypeName = "[bit]"; break; case "datetime": parsedTypeName = "[datetime]"; break; case "float": parsedTypeName = string.Format("[decimal](18,{0})", minimumPrecision); break; case "int32": case "int": parsedTypeName = "[int]"; break; case "string": parsedTypeName = "[nvarchar](MAX)"; break; } return parsedTypeName; } } |
Usage
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
string SchemaName = "dbo"; string TableName = "SampleSource"; string PrimaryKey = "ID"; List<string> columns = new List<string> { "ID", "Name", "Phone" }; DynamicMergeStatementBuilder builder = new DynamicMergeStatementBuilder(); string targetTableName = string.Format("[{0}].[{1}]", SchemaName, TableName); string sourceTableName = string.Format("[{0}].[#{1}]", SchemaName, TableName); Dictionary<string, string> updateColumnMappings = columns.ToDictionary(e => e, e => e); updateColumnMappings.Add("ModifiedDate", "GETUTCDATE()"); builder.WithTables(sourceTableName, targetTableName) .MatchOn(PrimaryKey.Split(",".ToCharArray()).ToList()) .AddInsertStatement(columns) .AddUpdateStatement(updateColumnMappings, new List<string> { "ModifiedDate" }) .AddDeleteStatement(); string mergeStatement = builder.Build(); |
Output
|
1 2 3 4 5 6 7 8 9 |
Merge into [dbo].[SampleSource] as t Using [dbo].[#SampleSource] as s on t.[ID] = s.[ID] when not matched then insert ([ID],[Name],[Phone]) values (s.[ID],s.[Name],s.[Phone]) when matched then update set t.[ID] = s.[ID] ,t.[Name] = s.[Name] ,t.[Phone] = s.[Phone] ,t.[ModifiedDate] = GETUTCDATE() when not matched by source then delete; |