Converting a Vertical Table to an Horizontal Table in SQL Server

Today I’ve encountered a vertical table in an SQL Database and I wanted to transform it to an horizontal one. A vertical table is described as an EAV model.

Imagine you have this table


CREATE TABLE VerticalTable
(
Id int,
Att_Id varchar(50),
Att_Value varchar(50)
)

INSERT INTO VerticalTable
SELECT 1, 'FirstName', 'John' UNION ALL
SELECT 1, 'LastName', 'Smith' UNION ALL
SELECT 1, 'Email', 'john.smith@dummy.com' UNION ALL
SELECT 2, 'FirstName', 'Jack' UNION ALL
SELECT 2, 'LastName', 'Daniels' UNION ALL
SELECT 2, 'Email', 'jack.daniels@dummy.com'

If you run


SELECT * FROM VerticalTable

you get this

Id Att_Id Att_Value
1 1 FirstName John
2 1 LastName Smith
3 1 Email john.smith@dummy.com
4 2 FirstName Jack
5 2 LastName Daniels
6 2 Email jack.daniels@dummy.com

 

To convert this into an horizontal table I’m going to use PIVOT.


SELECT [Id], [FirstName], [LastName], [Email] 
FROM
(
 SELECT Id, Att_Id, Att_Value FROM VerticalTable
) as source
PIVOT
(
 MAX(Att_Value) FOR Att_Id IN ([FirstName], [LastName], [Email])
) as target

And I will get this

Id FirstName LastName Email
1 1 John Smith john.smith@dummy.com
2 2 Jack Daniels jack.daniels@dummy.com

 

You can find the code here.

Speaker at Collab 365

Hi,

I’m proud to announce that I was selected to present a session at Collab 365 Global Conference 2016. This is a global online conference delivering sessions about SharePoint, Office 365 and Azure on the 19th-20th October.  And it’s free. Register here.

In my session I will talk about event driven applications with Azure Functions, it will cover triggers and webhooks that Azure Functions provide. It will also have a look at various integrations and at last, it will check the scaling and costs.

See you there

log4net PatternLayout and PatternConverter

I have the following scenario:

I’m using Unity dependency injection container and interception techniques to log some WCF operation calls. I need to log the input parameters and also the result of these operations so I’ve implemented a CallHandler that I can apply to whatever methods that I need to intercept.

In this CallHandler I’ve defined a class called LogEntry and I’m logging with log4net. Something like this:


LogEntry logEntry = new LogEntry();
....
// set some base properties
logEntry.MethodName = input.MethodBase.Name;
logEntry.TypeName = input.Target.GetType().FullName;
...
// set input parameters
logEntry.Input = ....;
...
// set return value
logEntry.ReturnValue = result.ReturnValue;
.....
// set total method duration
logEntry.CallTime = stopwatch.Elapsed.TotalMilliseconds;
.....

// use log4net logger to log this information
logger.Debug(logEntry);

So this LogEntry class has some properties that will hold the information that I want to log. Now I need to capture these properties and configure the log4net appenders properly.

I’m using log4net PatternLayout and PatternConverter features. Let’s start with PatternLayout


///<summary>
/// Extended version of <see cref="PatternLayout"/>
/// </summary>


public class ExtendedPatternLayout : PatternLayout
{
 ///<summary>
 /// Initializes a new instance of the <see cref="ExtendedPatternLayout"/> class.
 /// </summary>;


 public ExtendedPatternLayout()
 {
   this.AddConverter(new ConverterInfo()
   {
     Name = "logEntry",
     Type = typeof(LogEntryConverter),
   });
 }
}

This layout just adds a new Converter named “logEntry” implemented by the class LogEntryConverter. There is no reference to LogEntry class here. And the LogEntryConverter class is here:


 /// <summary>
 /// Custom <see cref="PatternConverter"/> to handle <see cref="LogEntry"/>
 /// </summary>
 public class LogEntryConverter : PatternConverter
 {
   /// <summary>
   /// Converts the specified writer.
   /// </summary>
   /// <param name="writer">The writer.</param>
   /// <param name="state">The state.</param>
   protected override void Convert(System.IO.TextWriter writer, object state)
   {
       var loggingEvent = state as LoggingEvent;
       var logEntry = loggingEvent.MessageObject as LogEntry;

       if (logEntry != null)
       {
          switch (this.Option.ToLower())
          {
             case "calltime":
               writer.Write(logEntry.CallTime.HasValue ? logEntry.CallTime.Value : 0);
               break;
             case "methodname":
               writer.Write(logEntry.MethodName);
               break;
             case "returnvalue":
               writer.Write(SerializationService.SerializeToJSON(logEntry.ReturnValue));
               break;
             ..........
             ..........
             default:
               writer.Write(string.Empty);
               break;
          }
       }
    }
 }

I’ve made the code smaller but you need to check for null references!

I’m using a JSON serialization technique to grab the representation of the method’s return value.

I need to override the Convert method and access state object that is a log4net LoggingEvent object. This object has my logged object from the CallHandler which is a LogEntry object. Based on the Option property of the converter I can choose which property to write.

And finally I can configure my log4net like this


<layout type="ExtendedPatternLayout">
  <conversionPattern value="%logEntry{CallTime}" />
</layout>

You can see the conversionPattern follows this syntax: %convertername{option}

nettiers – GetBy Index problem with StoredProcedure long name

Today I’ve discovered a problem with CodeSmith nettiers templates because it was possible to generate two SPs with the same name, for different entities.

This happens when there are two indexes for two different tables that have the same column names and the column names reach the maximum lenght (128 for SQL). In this case the generated SP name doesn’t contain the table name as a prefix.

Here is the original nettiers code for the GetProcNameForGetByIX method in CommonSqlCode.cs:

// get the key names one at a time until we run out of space
stringbuilder names = new stringbuilder(maxlen);
 
string keyname;
for(int x = 0; x < keys.length; x++)
{
  keyname = getpropertyname(keys[x]);
  if (names.length + keyname.length <= maxlen)
    names.append(keyname);
  else
    break;
}
return names.tostring();

I’ve upgraded the method to receive the index name and the new code is:

if (string.IsNullOrEmpty(indexName))
 {
   throw new Exception("Need index name");
 }
 
 string name = string.Concat(prefix, indexName);
 
 if (name.Length > maxLen)
 {
   throw new Exception("SP name continues to be greater than maxlen");
 }
 
 return name;

Then I needed to pass the index name on all the places that this method was call. Just use find in files and search for that method.

Got my first IoT device

Hi,

yesterday I got my first Arduino. Here it is my first sketch… the blink sketch 🙂

IMG_20160430_115827

And the Arduino IDE blink sketch


// the setup function runs once when you press reset or power the board
void setup() {
 // initialize digital pin 13 as an output.
 pinMode(13, OUTPUT);
}

// the loop function runs over and over again forever
void loop() {
 digitalWrite(13, HIGH); // turn the LED on (HIGH is the voltage level)
 delay(150); // wait
 digitalWrite(13, LOW); // turn the LED off by making the voltage LOW
 delay(150); // wait
}

SQL Persisted Computed Column ANSI_NULLS

I had to recreate a table in SQL Server just because it was created with ANSI_NULLS ON

Here you can check the requirements to create a persisted  computed column on SQL Server.

“…the SET options in the following table must be set to the values shown in the Required value column…”

SET options Required value Default server value Default

OLE DB and ODBC value

Default

DB-Library value

ANSI_NULLS ON OFF ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS* ON OFF ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON OFF ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON OFF ON OFF

 

Add WCF Message Id in every log4net message

I needed to have the WCF message identifier in every log message because I needed to correlate the log messages from a particular WCF call.

The best way to achieve this with log4net is to use context properties.

Since I’m already using Unity and Interception on my WCF Services, I’ve decided to implement a custom Call Hander


namespace CustomCallHandlers
{
 using System;
 using Microsoft.Practices.Unity.InterceptionExtension;

 /// <summary>
 /// Call Handler to setup wcf message id in log4net context
 /// </summary>
 public class ServiceCallHandler : ICallHandler
 {
 /// <summary>
 /// The message identifier
 /// </summary>
 private const string MessageIdKey = "MessageId";

 /// <summary>
 /// Initializes a new instance of the <see cref="ServiceCallHandler"/> class.
 /// </summary>
 /// <param name="order">The call handler's execution order.</param>
 public ServiceCallHandler(int order)
 {
  this.Order = order;
 }

 /// <summary>
 /// Gets or sets the order.
 /// </summary>
 public int Order { get; set; }

 /// <summary>
 /// Invokes the specified input.
 /// </summary>
 /// <param name="input">The input.</param>
 /// <param name="getNext">The get next.</param>
 /// <returns>The method return for the nexte invocation</returns>
 public IMethodReturn Invoke(IMethodInvocation input, GetNextHandlerDelegate getNext)
 {
  if ((System.ServiceModel.OperationContext.Current != null) &&
      (System.ServiceModel.OperationContext.Current.IncomingMessageHeaders != null))
  {
   if (System.ServiceModel.OperationContext.Current.IncomingMessageHeaders.MessageId != null)
   {
    string messageId = System.ServiceModel.OperationContext.Current.IncomingMessageHeaders.MessageId.ToString();
    log4net.ThreadContext.Properties[MessageIdKey] = messageId == "(null)" ? Guid.NewGuid().ToString() : messageId;
   }
  }

  IMethodReturn resultado = getNext()(input, getNext);

  // Cleaning
  log4net.ThreadContext.Properties[MessageIdKey] = null;

  return resultado;
  }
 }
}

If you want to see how to configure log4net appenders to write custom properties check this post.

 

 

Custom properties log4net

I had the need to add some custom properties in my log4net messages.

I used the log4net contexts like this:


log4net.ThreadContext.Properties[MessageIdKey] = messageId;

And then updated my rolling file appender:


<!-- Rolling File Appender -->
<appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="log.txt" />
<appendToFile value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="5" />
<maximumFileSize value="10MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date{dd-MM-yyyy HH:mm:ss,fff} (%logger) [%2thread]     [%property{MessageId}] %5level %message%newline" />
</layout>
</appender>

and my AdoNetAppender:


<!-- ADO Appender -->
 <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
 <bufferSize value="5" />
 <lossy value="false" />
 <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
 <connectionString value="data source=localhost;initial catalog=MyDb;integrated security=true;" />
 <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception],[MessageId]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception, @messageId)" />

 ......

 <parameterName value="@messageId" />
 <dbType value="String" />
 <size value="255" />
 <layout type="log4net.Layout.PatternLayout">
 <conversionPattern value="%property{MessageId}" />
 </layout>
 </parameter>

 </appender>

Azure Mobile Services News – Issue#4

Logging in with Google, Microsoft and Facebook SDKs to Azure Mobile Services

“One of the values that Azure Mobile Services provides is an easy way to implement authentication for mobile applications, via a very simple API – call a login function (or equivalent) on the client object in any of the supported platforms, and your user gets presented with a simple web-based interface that allows them to log in to your mobile service. This is what we call a server-side authentication flow, where the service guides the client to the provider (via redirections in the web page) and then back to itself.”


 

Azure Service Bus – ‘NamespaceType’ default value change

“Recently we announced that we are splitting the user experience between Service Bus and Notification Hubs to enable a better experience for Notification Hubs.”


 

Rich Push with Notification Hubs in iOS 8

“In order to engage users with instant rich content, applications often push notifications beyond just plain text. These notifications can contain urls, images, sounds, and more. “


 

Better support for paging with Table Storage in Azure Mobile Services .NET backend

“When we released the .NET backend for Azure Mobile Services, we provided support to store data in Azure Table Storage and MongoDB databases (in addition to the SQL Azure storage which has been used since the first version of the service).”

 

How to Access the Previous Row and Next Row value in SELECT statement?

LAG – http://msdn.microsoft.com/en-us/library/hh231256.aspx

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

LEAD – http://msdn.microsoft.com/en-us/library/hh213125.aspx

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

SQL SERVER 2012 and 2014