.Net Code Monkey RSS 2.0
 Friday, July 29, 2016

The Stored Procedure Framework Now Supports A Stored Procedure With Dynamic Fields

Until recently when using the Stored Procedure Framework you needed to know the exactly *FieldName* and *DataType* of each column returned from a stored procedure so this could be accurately represented with the corresponding .Net CLR types in the class you needed to define that represents the row returned by the stored procedure. Recently however following a request a change has been made to support dynamic fields in stored procedure results. this allows supporting of stored procedures which contain pivoting of rows to columns or dynamically executed SQL statements.

Take for instance the basic stored procedure below:

CREATE PROCEDURE [app].[GetPossibleDynamicStoredProcedure]
    SELECT  'Dave'      [Firstname],
            'Smith'     [Surname],
            32          [Age],
            GETDATE()   [DateOfBirth]

    SELECT  'Peter'     [Firstname],
            'Pan'       [Surname],
            134         [Age],
            GETDATE()   [DateOfBirth];

Previously we would have needed a class that defines each field to be returned, which would be too restrictive to call stored procedures with dynamic field names or *DataTypes*.

 internal class GetPossibleDynamicStoredProcedure
     : NoParametersStoredProcedureBase<List<GetPossibleDynamicStoredProcedure.Return>>
     internal class Return
         public string Firstname { get; set; }
         public string Surname { get; set; }
         public int Age { get; set; }
         public DateTime DateOfBirth { get; set; }
However, now the *Stored Procedure Framework* has been updated to include support of dynamic fields using the .Net *ExpandoObject* as the type parameter for the return type list.
 internal class GetDynamicColumnStoredProcedure
     : NoParametersStoredProcedureBase<List<ExpandoObject>>
The *Stored Procedure Framework* will return a list of *ExpandoObjects* which can then be cast to the .Net *dynamic* object as required. An example of this is shown in the unit test below.

public class DynamicColumnStoredProcedure
     : SqlConnectionExampleTestBase
     public void GetDynamicColumnStoredProcedure()
         // ARRANGE
         var procedure = new GetDynamicColumnStoredProcedure();

         // ACT
         var results = Connection.ExecuteStoredProcedure(procedure);
         var result = results.First();

         // ASSERT
         Assert.IsTrue(DynamicObjectHelper.HasProperty(result, "Firstname"));
         Assert.IsTrue(DynamicObjectHelper.HasProperty(result, "Surname"));
         Assert.IsTrue(DynamicObjectHelper.HasProperty(result, "Age"));
         Assert.IsTrue(DynamicObjectHelper.HasProperty(result, "DateOfBirth"));
         Assert.IsFalse(DynamicObjectHelper.HasProperty(result, "MiddleName"));

         var dynamicResult = (dynamic) result;
         Assert.AreEqual("Dave", dynamicResult.Firstname);
         Assert.AreEqual("Smith", dynamicResult.Surname);
         Assert.AreEqual(32, dynamicResult.Age);
Note: Currently dynamic fields in stored procedures are only supported with stored procedures having single recordsets. Support for multiple recordsets with dynamic columns is on the roadmap.

GitHub Source

The updated source code can be found on GitHub here.

NuGet Package

The Stored Procedure Framework is also available here on NuGet, with its EF counterpart here.


I am the author of the *Stored Procedure Framework*.

Friday, July 29, 2016 4:29:30 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
.Net | C# | CodeProject | Dynamic | ExpandoObject | Sql Server | Stored Procedure Framework | Stored Procedures
 Thursday, May 05, 2016

Volkswagen 2007 Transporter 2.5 TDi BNZ Engine Alternator Auxiliary Shaft Failure

Today I want to discuss a couple of failures. The first failure is the root cause which caused my recently purchased second hand Volkswagen Transporter Shuttle to leak a lot of oil when the engine is running. The second failure is VW’s inadequate level of service when it comes to keeping the part in stock that was the root cause of the issue, meaning their only option was to suggest a full short or long block at circa £3.5k or £6.5k respectively!

So before I go full rant let’s step back a bit to when I purchased the bus. I purchased the Transporter Shuttle off a “trader” who advertised the van on AutoTrader as having good history and only 91k miles. Long story short I was seen off and the “trader” walked off with the documented history and on my way home with the van the various issues started to show their head, like various engine warning lights! Further issues have indicated this is unlikely to be a 91k mileage van!

One of the issues that was not immediately apparent until the day after I parked it on the drive was the pool of oil under it. This was traced by a mechanic to a seal on the alternator auxiliary drive shaft. This seal was ordered from VW and replaced, not an easy job as half the front end and radiator need to be removed for access. The mechanic noticed the shaft had a bit of play, but hopefully not too much to cause any problems. They ran the engine up and the leak seemed to have abated. Thank goodness.

However 30 miles later the oil was once again running out past the new seal. Now this shaft is driven from the crank via a set of gears which drive other auxiliary shafts. So this time to get to the bottom of the issue its gearbox off time to get full access to the shaft. What we found was although the shaft a little wear at the bearing points it had 0.4mm wear at the point where the rubber seal touched the shaft. I can only assume due to the suspected VERY high mileage of the engine the original seal must have worn right out and the metal “spring” which holds the seal lip against the shaft from inside the seal must have burst through the rubber lip and started to wear a groove in the shaft.

This shaft has a VW part number 070 903 325 C. So I contacted both  my local TPS and Volkswagen dealer, but both advised that this part is not available and I’d require either a short block or a long block at circa £3.5k or £6.5k (+VAT I assume!). Well I had only just bought the van for £7k so was not willing to spend £4k plus labour on a new engine. I cannot believe that VW do not keep any of these parts available for purchase. They must have made enough for spare engines, so why not run a few hundred off to keep as spares to sell. Do they really think it is acceptable to demand that an owner whose shaft has failed purchases a brand new engine from them? This to me seems a bit of a failure in customer service!

So my next port of call was the internet. Trawling the internet did not turn up much either except for this thread on a VW forum. It seems this guys does not have the same problem but did need a new shaft and bolt. I also found this thread where the guy also had an oil leak.

I could not find much evidence anywhere else except a Chec site which I could not read. Using information from one of the guys in the first thread i did speak to a guy called Paul at Carneil Precision in Staveley, Chesterfield S43 3PE. He was very helpful and said he would be able to make one if I sent him the part. If i had been based up near chesterfield I would have paid him a visit with the offending part. As it is I am on the south coast so took the part to a local engineer, Matt who is the proprietor of White Engineering, Portland, Dorset. He turned up the part out of a very hard material and it is now with the my mechanic waiting to be refitted.

So what is my gripe? Well mainly that i’m sure it would not have hurt VW to have made an extra number and kept some back for after sales service. I’m sure in the batches they must have had made for this engine it would have been pounds only or a couple of tens of pounds at least! I think it is a failure of customer service for VW to believe that the failure of such a small piece should result in a sale of half an engine, or a full engine.

So to sum up

Do I still love my VW transporter? Just about.
Will I be keeping it much longer? Possibly.

Will i buy another Transporter in the future? Unlikely.

Will i buy another VW in the future? Unlikely.

Part Reference

Some of these may not be correct. Suggest you check first!

  • Bolt VN 909 797 05 £1.54 + VAT
  • Alternator drive Hub V070 903 115 C £23.33 + VAT
  • Hub (Not sure if this is the shaft or the Gates coupling pulley!) 070 903 115 E
  • Could this be the part no shaft?   070 903 325b
  • Believed to be part number for seal 070 903 107

Thursday, May 05, 2016 7:43:10 PM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
070903325 | transporter | Volkswagen
 Friday, February 19, 2016
# Centralised Event Dispatcher in C# - Part 4
This is part four of a series of posts where I am going to share with you the solution I have used in a recent project for a centralised event dispatcher. All of the source code will be available in my public GitHub repositories. The concept I have tried to realise is a central class that can raise events for any listener to subscribe to.

In part one we created the event dispatcher class library.
In part two we consumed it from a windows forms application.
In part three we look at adding pooling for commonly raised events and implement an 'ApplicationEventPool'.

In this part we will were going to be looking at applying a restriction on what events can be safely pooled. However further investigation following feedback on the articles has indicated that there may be little to gain from pooling the events, so until a better reason presents itself to continue investigating pooling I am going to bring this series to a close here.

Friday, February 19, 2016 6:43:15 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
.Net | C# | Event Dispatcher | Event Handling | Events
 Friday, February 12, 2016

Using ReSharper Tool to Create an Immutable Class

If you have the JetBrains ReSharper tool for Visual Studio it is a trivial task to create a whole immutable class from a single line constructing the object before a class representing the object even exists in your solution.


Given the following line, and assuming that no class called `MessageEvent` already exists anywhere in your Visual Studio project solution.

var @event = new MessageEvent(sender, CreateMessageTextBox.Text);

Create a new class from the constructor call

Click on the `MessageEvent` constructor word. Observe the red reSharper light bulb in the left margin and mouse-over it. You will see a tool tip "Create type 'MessageEvent' (Alt + Enter)". Click the "Create type 'MessageEvent'" option and this will create a new class of this type in the current file. Select "class" or "struct", then tab through the constructor argument types and names, changing as required. Clear out the `throw new System.NotImplementedException();` from the constructor.

internal class MessageEvent
     public MessageEvent(object sender, string text)

Move the class to its own file

Click on the class type name, in this case `MessageEvent`, observer the reSharpoer hammer in the left margin and use the "Move to 'MessageEvent.cs'" to create the class in its own file. Using Visual Studio Solution Explorer, move the `MessageEvent.cs` to your preferred location.

Correct the class namespace

Click on the class namespace and use the ReSharper light bulb in the left margin to adjust the namespace to suit the location or correct it manually.

Introduce Fields for the Constructor Arguments

Click on each field and use the ReSharper light bulb in the left margin to "Introduce and initialize field"

internal class MessageEvent
     private readonly object _sender;
     private readonly string _text;

     public MessageEvent(object sender, string text)
         _sender = sender;
         _text = text;

Encapsulate field

Click on the field name and use the ReSharper light bulb in the left margin to "Encapsulate Field". Select the appropriate options, scope usage, etc, in this case I will just use "Read Usages".

internal class MessageEvent
     private readonly object _sender;
     private readonly string _text;

     public MessageEvent(object sender, string text)
         _sender = sender;
         _text = text;

     public object Sender
         get { return _sender; }

     public string Text
         get { return _text; }
And there you have it. An immutable class created with ease from a single constructor statement using ReSharper. Enjoy your new found productivity!

ReSharper Download

You can download a free 30 day trial of JetBrains ReSharper here.
Friday, February 12, 2016 5:35:10 AM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
.Net | C# | CodeProject | Visual Studio 2012 | JetBrains ReSharper | Visual Studio | Productivity
<July 2016>
Creator of ruby on Rails
 Harry Pierson
Passion * Technology * Ruthless Competence
 Joshua Flanagan
A .NET Software Developer
 Martin Ffowler
Author, speaker, and loud-mouth on the design of enterprise software
 Michael Schwarz's Blog
Developing applications on the Microsoft platform since Windows 3.1!
 Scot GU
Scott Guthrie lives in Seattle and builds a few products for Microsoft
 Scott Hanselman
Programming Life and the Zen of Computers
About the author/Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2016
Duane Wingett
Sign In
Total Posts: 85
This Year: 7
This Month: 1
This Week: 1
Comments: 62
Pick a theme:
All Content © 2016, Duane Wingett
DasBlog theme 'Business' created by Christoph De Baene (delarou)