Use Power Shell to execute SQL Server script files

Below is the snippet for using Power Shell to execute list of SQL scripts under a specific folder. I use this script in Octopus deployment to deploy database changes (* for this particular case, we don’t use Code First therefore we don’t use Migrate.exe)

# Connection String
[string] $ConnectionString = "server=MyDBServer;database=MyDatabase;user id=Myuser;password=Mypassword;trusted_connection=true;"

#The folder where all the sql scripts are located
[string] $ScriptPath= "C:\Octopus\Applications\SQL2014UAT\Powershell Deployment\Scripts"

# Go to every single SQL files under the folder
foreach ($sqlFile in Get-ChildItem -Path $ScriptPath -Filter "*.sql" | sort-object)
{
    $SQLQuery = Get-Content "$ScriptPath\$sqlFile" -Raw
    
    ExecuteSqlQuery $ConnectionString $SQLQuery 
}

# executes multiple lines of SQL query
function ExecuteSqlQuery ($ConnectionString, $SQLQuery) {
    # Use GO to separate between commands
    $queries = [System.Text.RegularExpressions.Regex]::Split($SQLQuery, "^\s*GO\s*`$", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)

    $queries | ForEach-Object {
        $q = $_
        
        if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go")) 
        {            
            $Connection = New-Object System.Data.SQLClient.SQLConnection

            Try
            {
                $Connection.ConnectionString = $ConnectionString
                $Connection.Open()

                $Command = New-Object System.Data.SQLClient.SQLCommand
                $Command.Connection = $Connection
                $Command.CommandText = $q
                $Command.ExecuteNonQuery() | Out-Null
            }
            Catch
            {
                echo $_.Exception.GetType().FullName, $_.Exception.Message
            }
            Finally
            {
                if ($Connection.State -eq 'Open')
                {
                    write-Host "Closing Connection..." 
                    $Command.Dispose()
                    $Connection.Close()        
                }  
            }
        }
    }
  }

Alternatively if you have SMO, PS extensions and the snap in then you can use simpler script below. For the pre-requisites for invoke-sqlcmd is here

Get-ChildItem -Path "C:\Octopus\Applications\SQL2014UAT\Powershell Deployment\Scripts" -Filter "*.sql" | % {invoke-sqlcmd -InputFile $_.FullName}
Advertisements

Reading multiple lines using Powershell

by default, Get-Content in powershell reading the file as a one long string. which means you might have an issue if you have multiple lines of SQL statements as it will read this

IF EXISTS(SELECT 1 FROM sys.procedures WHERE NAME = ‘PSTest’)
BEGIN
DROP PROCEDURE PsTest
END
GO

becoming

IF EXISTS(SELECT 1 FROM sys.procedures WHERE NAME = ‘PSTest’) BEGIN DROP PROCEDURE PsTest END GO

so how to read multiple lines using Power Shell?

Before Power Shell 3.0 you can use the code snippet below

(Get-Content $FilePath) -join "`r`n" 

Power Shell 3.0 and above, you can use -Raw parameter

Get-Content $FilePath -Raw

Logging in .NET – Elastic Search, Kibana and Serilog

I’ve been using log4net in the past and I found it quite useful as it is ready to use out of the box. In my last workplace, we are using SPLUNK and its amazing as I’m able to troubleshoot production issue by looking at the trend and activities. You can do query based and filtering the log and build a pretty dashboard. Downside of it is the cost for Splunk is expensive (i don’t think its for the mainstream user or small business)

So I’ve found another logging mechanism/storage/tool which is amazing!! It is called Elastic Search and its open source (well there are different subscriptions level for better supports). Pretty much Elastic Search is the engine for search and analytics

How about the GUI/Dashboard?Yes you can use Kibana. It is an open source data visualization platform that allows you to interact with data

Ok, so lets say if I have a .NET, how do I write my log to Elastic Search?You can use SeriLog. It will allow you to log structured event data to your log and you can use Serilog Elastic Search sink to integrate with Elastic Search

Serilog has different sink providers that allow you to store your log externally (beside file) including SPLUNK

I will talk more about Serilog separately in different post, stay tune!

Git – Prune your local branches to keep it in sync with remote branches

On your local branches normally you have a stale branch where it doesn’t have corresponding remote branch and you feel like you want to make it in sync with the remote branches

1. Lets start listing the remote branch first just to know what are the branches available remotely

$ git remote show origin

2. Lets see our local stale branches “–dry-run” command will just display the stale branch but without deleting it

$ git remote prune origin --dry-run

3. Alternatively if you want to really delete the stale branches you can run it without “–dry-run” command

$ git remote prune origin

*just make sure you already committed your feature branch to the remote before doing this

Dynamic Deserialization using JsonConverter

This post is the continuation from the previous post. The previous post was in regard to casting the object dynamically. This post will explain how to deserialize dynamically from Json object

I have a json that I want to deserialize dynamically based on a specific property that defines what object it is. We can do it easily and elegantly by using JsonConverter

1. Create a custom JsonConverter

public class MessageConverter : JsonConverter
    {
        static readonly JsonSerializerSettings SpecifiedSubclassConversion = new JsonSerializerSettings() { ContractResolver = new CamelCasePropertyNamesContractResolver() };

        public override bool CanConvert(Type objectType)
        {
            return (objectType == typeof(WhafflMetadata));
        }

        public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
        {
            var jObject = JObject.Load(reader);

            switch (jObject["messageType"].Value<string>())
            {
                case "STAFF_CREATED":
                    return JsonConvert.DeserializeObject<Message<StaffDetail>>(jObject.ToString(), SpecifiedSubclassConversion);

                case "CITY_CREATED":
                    return JsonConvert.DeserializeObject<Message<City>>(jObject.ToString(), SpecifiedSubclassConversion);

                default:
                    throw new Exception(string.Format("messageType {0} cannot be handled", jObject["messageType"].Value<string>()));
            }
        }

        public override bool CanWrite
        {
            get { return false; }
        }

        public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
        {
            throw new NotImplementedException();
        }
    }

2. Cast it using your converter

JsonConvert.DeserializeObject<Message>(
                jsonValue,
                new JsonSerializerSettings { Converters = new JsonConverter[] { new MessageConverter() } });

Automapper – Dynamic and Generic Mapping

In Automapper, we normally have 1 to 1 mapping defined but I have a case whereas the incoming stream as a json payload which then I cast it as a dynamic (using JObject parse) and in one of the property within the payload it defined which object that it needs to cast into. Lets take a look at the sample below

Input
Json payload to create a city

{
    "requestId": "C4910016-C30D-415C-89D3-D08D724429A6",
    "messageType": "CITY_CREATED",
    "categoryName": "categoryA",
    "metadata": {
        "city": "sydney",
        "state": "NSW",
        "postcode": "2000",
        "country": "australia"
    }
}

at the same time we can also have a Json payload to create a staff

{
  "requestId":"C4910016-C30D-415C-89D3-D08D724429A6",
  "messageType": "STAFF_CREATED",
  "categoryName": "categoryB",
  "staffDetail": {
    "name": "fransiscus",
    "dateOfBirth": "01/01/1950"
  },
  "location" : {
    "cityId" : "1"
  }
}

So what we are doing in here, all the message will go into payload property (it can contain any object) and we add some extra information/header/metadata on the parent level
Desired Outputs

{
    "messageType": "CITY_CREATED",
    "payload": {
        "city": "sydney",
        "state": "NSW",
        "postcode": "2000",
        "country": "australia"
    },
    "provider": "abc",
    "providerRequestId": "C4910016-C30D-415C-89D3-D08D724429A6",
    "receivedAt": "2015-09-30T23:53:58.6118521Z",
    "lastUpdated": "2015-09-30T23:53:58.6128283Z",
    "lastUpdater": "Transformer",
    "attempt": 0
}
{
    "messageType": "STAFF_CREATED",
    "payload": {
        "staffName": "fransiscus",
        "dateOfBirth": "01/01/1950",
        "cityId": "1"
    },
    "provider": "abc",
    "providerRequestId": "C4910016-C30D-415C-89D3-D08D724429A6",
    "receivedAt": "2015-09-30T23:53:58.6118521Z",
    "lastUpdated": "2015-09-30T23:53:58.6128283Z",
    "lastUpdater": "Transformer",
    "attempt": 0
}

To map this to a concrete class 1:1 mapping is straight forward and easy. The problem here is that the “messageType” is the one that decided which object that it should be

Automapper Configuration:

1. POCO object

abstract class that stores all the metadata

public abstract class Metadata
    {
        public string MessageType { get; set; }

        public string Provider { get; set; }

        public string ProviderRequestId { get; set; }

        public DateTime ReceivedAt { get; set; }

        public DateTime LastUpdated { get; set; }

        public string LastUpdater { get; set; }

        public int Attempt { get; set; }

        public List<string> Errors { get; set; } 
    }
 public class City
    {
        public string CityName { get; set; }
        public string State { get; set; }
        public string PostCode { get; set; }
        public string Country { get; set; }
    }
public class StaffDetail 
    {
        public string Name { get; set; }
        public string DateOfBirth { get; set; }
        public int CityId { get; set; }
    }
public class Message<T> : Metadata where T : class
    {
        public T Payload { get; set; }
    }

2. Lets create a TypeConverter for the base class which is Metadata and from this converter it will return the derived class

public class MetadataTypeConverter : TypeConverter<dynamic, Metadata>
    {
protected override Metadata ConvertCore(dynamic source)
        {
            Metadata metadata;

            var type = (string)source.messageType.Value;

            switch (type)
            {
                case "STAFF_CREATED":
                    metadata = new Message<StaffDetail> { Payload = Mapper.Map<dynamic, StaffDetail>(source) };
                    break;
                case "CITY_CREATED":
                    metadata = new Message<City> { Payload = Mapper.Map<dynamic, City>(source) };
                    break;

                default: throw new Exception(string.Format("no mapping defined for {0}", source.messageType.Value));
            }

            metadata.ProviderRequestId = source.requestId;
            metadata.Topic = string.Format("{0}.{1}.pregame", 
                producerTopicName,
                source.categoryName ?? source.competition.categoryName);
            metadata.Provider = "My Provider";
            metadata.MessageType = source.messageType;
            metadata.ReceivedAt = DateTime.UtcNow;
            metadata.LastUpdated = DateTime.UtcNow;
            metadata.LastUpdater = "Transformer";
            metadata.Attempt = 0;

            return metadata;
        }
    }

3. Lets create a TypeConverter for the derived class which are Staff and City

public class CityTypeConverter : TypeConverter<dynamic, City>
    {
        protected override City ConvertCore(dynamic source)
        {
            City city = new City();
            city.CityName = source.metadata.city;
            city.State = source.metadata.state;
            city.Postcode = source.metadata.postcode;
            city.Country = source.metadata.country;

            return city;
        }
    }
 public class StaffDetailTypeConverter : TypeConverter<dynamic, StaffDetail>
    {
        protected override StaffDetail ConvertCore(dynamic source)
        {
            StaffDetail staffdetail = new StaffDetail();
            staffdetail.Name = source.staffDetail.name;
            staffdetail.DateOfBirth = source.staffDetail.dateOfBirth;
            staffdetail.CityId = source.location.cityId;

            return staffdetail;
        }
    }

3. Define the Automapper mapping in the configuration

public class WhafflMessageMapping : Profile
    {
        public override string ProfileName
        {
            get
            {
                return this.GetType().Name;
            }
        }

        protected override void Configure()
        {
            this.CreateMap()
                .ConvertUsing(new MetadataTypeConverter());

            this.CreateMap()
                .ConvertUsing(new StaffDetailTypeConverter());

            this.CreateMap()
                .ConvertUsing(new CityTypeConverter());
        }

        private Metadata BuildWhafflMessage(dynamic context)
        {
            var type = ((string)context.messageType.Value);

            switch (type)
            {
                case "STAFF_CREATED":
                    return new Message { Payload = Mapper.Map(context) };
                case "CITY_CREATED:
                    return new Message { Payload = Mapper.Map(context) };

                default: throw new Exception(string.Format("no mapping defined for {0}", context.messageType.Value));
            }

        }
    }

401 Unauthorized – WebRequest

I got this nasty 401 unauthorized error from my code all of sudden, I don’t really know why and what’s causing it. I used Fiddler as a proxy to see the request header and all of sudden it works but then removing the proxy again brings back the 401!!!

So after googling for a while i found something interesting about browser was requesting for authentication level etc therefore even if you pass the basic security header then it will just simply ignore it. So I played around with the code below and it fixes my issue

WebRequest request = WebRequest.Create(source);
request.AuthenticationLevel = System.Net.Security.AuthenticationLevel.MutualAuthRequested;