Author: Ankit Sharma

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;

Abstract Factory Pattern with Examples

September 2, 2016 2 Comments in Design Patterns Tags abstract, ADO.NET, C#, Design Pattern, examples, real by Ankit Sharma

Today, I will talk about highly popular Abstract Factory Pattern. Please note I am not going to take any Animal or Shape example as they don’t fit in the real-world development. Rather, I would take two different examples, one from .NET framework itself and another from my own work. To start with let’s see the

How to move messages from dead-letter queue to its associated queue (Sequentially)

October 15, 2015 No Comments in Azure,ServiceBus Tags Azure, deadletter, queue, Servicebus by Ankit Sharma

private static void MoveMessages()
{
const string Queue_Name = "<Your_Queue_Name>";
const string ConnectionString ="<Your_Service_Bus_Endpoint>";

string deadLetterQueue = QueueClient.FormatDeadLetterPath(Queue_Name);

QueueClient clientDeadLetter = QueueClient.CreateFromConnectionString(ConnectionString, deadLetterQueue);
QueueClient client = QueueClient.CreateFromConnectionString(ConnectionString, Queue_Name);

int counter = 1;
while (true)
{
BrokeredMessage deadMsg = clientDeadLetter.Receive();

if (deadMsg == null)
break;

BrokeredMessage newMsg = deadMsg.Clone();
client.Send(newMsg);
deadMsg.Complete();
Console.Write("\r {0}", counter);
counter++;
}
}

private static void MoveMessages()

{

const string Queue_Name = "<Your_Queue_Name>";

const string ConnectionString ="<Your_Service_Bus_Endpoint>";

string deadLetterQueue = QueueClient.FormatDeadLetterPath(Queue_Name);

QueueClient clientDeadLetter = QueueClient.CreateFromConnectionString(ConnectionString, deadLetterQueue);

QueueClient client = QueueClient.CreateFromConnectionString(ConnectionString, Queue_Name);

int counter = 1;

while (true)

{

BrokeredMessage deadMsg = clientDeadLetter.Receive();

if (deadMsg == null)

break;

BrokeredMessage newMsg = deadMsg.Clone();

client.Send(newMsg);

deadMsg.Complete();

Console.Write("\r {0}", counter);

counter++;

}

How to move pending code changes from one branch to another in TFS.

October 12, 2015 No Comments in TFS Tags Shelveset, Team Foundation Server, TFS by Ankit Sharma

Pre-requisites:- Make sure you’ve TFS power tools installed in your system. If not, you may download it from below links according to your TFS version TFS 2013 TFS 2010 Scenario:- Ever wondered how to move your pending code changes from one branch to another without manually replicating the changes? Let’s say you have a team

How to prevent accidental double clicks using Javascript

October 12, 2015 No Comments in Javascript Tags ASP.NET, Javascript by Ankit Sharma

function PleaseWait(arg) {
if (Page_ClientValidate())
{
$('[id$=btnDownload]').attr('disabled', 'disabled');
$('[id$=btnDownload]').val('Please Wait...');
$('[id$=btnSave]').attr('disabled', 'disabled');
$('[id$=btnSave]').val('Please Wait...');
}
}

function PleaseWait(arg) {

if (Page_ClientValidate())

{

$('[id$=btnDownload]').attr('disabled', 'disabled');

$('[id$=btnDownload]').val('Please Wait...');

$('[id$=btnSave]').attr('disabled', 'disabled');

$('[id$=btnSave]').val('Please Wait...');

}

<asp:Button ID="btnSave" runat="server" Text="Save &#187;" onclick="btnSave_Click" OnClientClick="PleaseWait(this)" UseSubmitBehavior="false" />

1	<asp:Button ID="btnSave" runat="server" Text="Save »" onclick="btnSave_Click" OnClientClick="PleaseWait(this)" UseSubmitBehavior="false" />

How to grant admin access in Visual Studio Online

October 12, 2015 No Comments in TFS Tags Team Foundation Server, TFS, Visual Studio Online by Ankit Sharma

Sign in to Visual Studio Online (youraccount.visualstudio.com) as the account owner. On dashboard, select the appropriate team project as highlighted in the screenshot below On team project dashboard, click on settings icon located at the top right bar. Click on Default collection. Navigate to Security tab and click on Users link followed by Member of

Cannot close stream until all bytes are written – C#

April 12, 2014 1 Comment in AWS,Exceptions,S3 Tags AWS, C#, S3, Stream by Ankit Sharma

This error is raised when the position of stream or memory stream object is set at the end of stream. To resolve the issue either you can set the position to “o” as shown below.

MemoryStream ms = new MemoryStream(); // Your memory stream
ms.Position=0;

1 2	MemoryStream ms = new MemoryStream(); // Your memory stream ms.Position=0;

If this doesn’t work, you can re-create the memory stream as shown below

MemoryStream content =new MemoryStream(); // Your memory stream.
MemoryStream ms = new MemoryStream(ReadFully(content)); // New memory stream
public static byte[] ReadFully(Stream input)
{
        byte[] buffer = new byte[16 * 1024];
        input.Seek(0, SeekOrigin.Begin);
        using (MemoryStream ms = new MemoryStream())
       {
             int read;
             while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
             {
                    ms.Write(buffer, 0, read);
              }
         return ms.ToArray();
       }
}

MemoryStream content =new MemoryStream(); // Your memory stream.

MemoryStream ms = new MemoryStream(ReadFully(content)); // New memory stream

public static byte[] ReadFully(Stream input)

{

byte[] buffer = new byte[16 * 1024];

input.Seek(0, SeekOrigin.Begin);

using (MemoryStream ms = new MemoryStream())

{

int read;

while ((read = input.Read(buffer, 0, buffer.Length)) > 0)

{

ms.Write(buffer, 0, read);

}

return ms.ToArray();

}

How to create remote site settings through apex.

May 9, 2013 13 Comments in Salesforce Tags Apex, Remote site settings, Salesforce by Ankit Sharma

Step 1: Create a new class for Metadata Service, and copy the contents of attached Metadata Service file. MetadataService Step 2: Add the following endpoints to your remote site settings, It is required to callout the web service. https://c.ap1.visual.force.com https://ap1.salesforce.com Note:- Change sub domains of the endpoints according to your org. Step 3: Create a visual

How to implement Parlay X in .NET

May 9, 2013 6 Comments in Parlay X API Tags C#, Parlay X, SOAP by Ankit Sharma

In this tutorial, I will explain how to implement Send SMS Interface of Parlay X 2.1 APIs using C#. Before proceeding, I would like to tell you that Parlay X is a set of standard low-level SOAP based Web API’s used by many telecom service providers. The implementation of interfaces might differ from provider to