@F | exception

Azure AD – Working across tenants using PowerShell

August 25, 2017 No Comments in @F,Active Directory,Azure Tags Active Directory, AD, Azure, Powershell, Tenants by Ankit Sharma

Working across various tenants programmatically in Azure AD could be relatively complex. Before even writing a script to do such operations, one must be very clear about a few concepts about Azure AD. Azure AD application and service principal. An Azure AD application is defined by its one and only application object which resides in

Dynamic SQL Merge statement builder through C#

September 21, 2016 No Comments in @F,C# Tags C#, Dynamically, Merge, SQL by Ankit Sharma

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.

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

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

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

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

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;

Merge into [dbo].[SampleSource] as t

Using [dbo].[#SampleSource] as s

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;

Category: @F