C# | exception

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

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

Tag: C#