It took me some time, but now it’s raining outside and I have the time necessary to finish the little series about unit tests and it’s related concepts, namely dependency injection and mocking.

Here is the table of contents of the series:

We’ve seen what unit tests are and why dependency injection is a usefull method helping you to isolate the unit to test from it’s dependencies. So, everything seems fine and the series could have ended there. Well, in theory, yes. But reality shows that a class to test may have so many dependencies that it may seem to much work to write all the fake classes.

Consider the following example where you want to unit test a person object implementing the IPerson interface:

    public interface IPerson
    {
        string Name { get; set; }
        DateTime Birthday { get; set; }
        IIdentityCard IdentityCard { get; set; }

        Boolean CheckIdentity(int number);
    }

    //Implementation of IPerson to unit test
    public class Person: IPerson
    {
        private Person(){}

        // A person instance can only be created by passing an IIdentity object
        public Person(IIdentityCard identity)
        {
            this.IdentityCard = identity;
        }

        public string Name { get; set; }
        public DateTime Birthday { get; set; }
        public IIdentityCard IdentityCard { get; set; }
    }

Take a look at the IdentityCard property of type IIdentityCard. A person instance can only be instanciated if you pass an IIdentityCard instance into the constructor. This means, that you must create an IIdentityCard object to unit test the Person class, even if don’t need it because you just want to test the Name-property.

To make things worse, have a look at the IIdentityCard interface:

    public interface IIdentityCard
    {
        int Number { get; set; }
        ICertificate Certificate { get; set; }
        bool Validate();
    }

Note the Certificate-property of type ICertificate. This means, that to unit test your person you may also have to create an object implementing ICertificate, if you need the Certificate-property for your unit test. In that case it may be easier to mock the dependencies instead of writing fake classes.

Mocking means that you have a mocking framework allowing you to create a kind of faked implementation of an interface or base class without having to write a real fake class. This allows you to just implement the members of the mocked class needed for your unit test and to ignore the other members. In this article, I use the moq framework.

Let’s have a look how it works when unit testing the person class shown above (as usual, I use Nunit as unit testing framework):

    [TestFixture]
    class UnitTests
    {
        [Test]
        public void MoqIPerson()
        {
            //Create mock of IIdentityCard needed to instanciate a Person
            var identity = new Moq.Mock<IIdentityCard>();
            //Make the properties of the IIdentityCard mock work like real properties
            identity.SetupAllProperties();
            //Assign a value to the number property
            identity.Object.Number = 1234;

            //Create a real person based on a mocked IIdentityCard instance
            Person person = new Person(identity.Object);

            //Test the Number property of the persons identity
            Assert.AreEqual(1234, person.IdentityCard.Number);
        }
    }

You see that there is no fake class implementing the IIdentityCard-interface. Instead the mock framework does it for you. Even better, with one line of code you can instruct the mocked object to let it’s properties behave like real properties, being able to read and write them.

You can also implement methods of a mocked object by using Lambda expressions. For example, the IIdentity-interface has a validate-method. If we want to make use of it in our unit test, we can configure it using the Setup/Return pair to define the behaviour of the method like this:

identity.Setup(ic => ic.Validate()).Returns(() => identity.Object.Number > 0);
Assert.IsTrue(identity.Object.Validate());

Now the Validate-method returns true if the number-property of the IIdentity-mock is greater than zero. Have a look at the moq framework to find more information about how mock objects work and what else you can do with them. Also consult this article explaining very well how to configure the Setup/Return pair of a method to implement.

Now, having an idea about what mocks are, you may ask you-self what is better: defining and using fake classes or writing mocks?

To me, it depends on the degree of dependencies of the class you want to unit test. The more dependencies it has, the easier it is to use mocks instead of writing the necessary fake classes by hand.

On the other side, mocks are always defined and created at run-time, meaning that you can not make use of them in the design-time configuration of your dependency injection framework, where a fake class implementing an interface is interchangeable with every other fake or production class implementing the same interface. This interchangeablility may come handy, because it allows you to switch between unit testing (making use of fake classes) and integration testing (making use of production classes) by referencing different sections of the unit testing configuration section.

I also consider to many dependencies as a code smell, telling me that the class design of the application may be to complex. Remember, that object design and database design are different things. You don’t have to reuse a complex object (like the Person object above) for every use case in the application but instead you can create a light-wight Person object if you just need the person’s name to display it in a combobox. Using light-wight objects naturally reduces the dependency when unit-testing later.

date5 May


As you may know, Microsoft released the SQL Server Data Tools on 6th of March, which means that your beloved software company wants you to make database development inside Visual Studio rather than in SQL Server Management Studio.

Click on the picture to read my test of SSDT’s on Codeproject.:

CodeProject
.

date5 Apr

In the last post we covered what dependency injection is. Now we take a look how to make it’s use as efficient as possible by using a dependency injection framework, namely the Microsoft Unity framework in it’s current version 2.0.

To start with, you can either use NuGet to import Unity into your project automatically or you do manually download the necessary assemblies and reference them into your project.

In the downloadable sample project I also reference NUnit to execute our samples as we used to when doing unit testing. Having said this, our additional usings look like this:

using NUnit.Framework;
// Core namespace of Unity
using Microsoft.Practices.Unity;
// Needed for desgin time configuration of Unity
using Microsoft.Practices.Unity.Configuration;

With Unity, you have two options how to inject a concrete implementation for your interface (or base class):

  • Run-time configuration: this means you make use of Unity by typing code in a class file
  • Design-time configuration: this means you type XML into a configuration file (e.g. the app.config)

To give you an idea about how unity works, I will cover some of the basic scenarios you will probably need both using run-time and design-time configuration. Note, that everything covered here is documented in much more detail in the outstanding Microsoft documentation  of the framework.

Here are the basic scenarios being covered:

  • Create instance of class having empty constructor only
  • Create instance of class having constructor with parameters
  • Create instance of class as a singleton
  • Create a named singleton instance

First we create an interface and two implementations of it:

namespace DependencyInjectionUnity
{
    public interface IPerson
    {
        string Name { get; set; }
        DateTime Birthday { get; set; }
    }
}

namespace DependencyInjectionUnity
{
    ///
    /// A simple class implementing an interface
    ///
    /// This class has no constructor taking parameters.
    public class SimplePerson : IPerson
    {
        public SimplePerson()
        {
        }

        private string _Name;
        public string Name
        {
            get
            {
                return this._Name;
            }
            set
            {
                _Name = value;
            }
        }

        public DateTime Birthday { get; set; }
    }
}

namespace DependencyInjectionUnity
{
    ///
    /// A complex class implementing an interface
    ///
    /// This class has a constructor taking a parameter.
    public class ComplexPerson : IPerson
    {
        public ComplexPerson()
        {
        }

        public ComplexPerson(string name)
        {
            this._Name = name;
        }

        private string _Name;
        public string Name
        {
            get
            {
                return this._Name;
            }
            set
            {
                _Name = value;
            }
        }

        public DateTime Birthday { get; set; }
    }
}

Second we create a NUnit test class and declare some variables used to test if our  injection works as expected:

namespace DependencyInjectionUnity
{
    [TestFixture]
    class InjectionTests
    {
        // Some values we use in unit tests to verify that the implementation
        // of IPerson works properly.
        private readonly string _Name = "Billy G.";
        private readonly DateTime _Birthday = new DateTime(2000, 12, 31);

       //Unity samples come here ...
    }
}

And here is the code using run-time injection for all four scenarios. The basic approach taken here is always the same:

  1. Create an instance of  a Unity container
  2. Tell unity about the mapping between the interface and it’s implementation
  3. Create an implementation of the interface by using Unity which makes use of the mapping defined in step 2

Please refere to the comments inside the code:

        #region RunTime Dependency Injection
        /// <summary>
        /// Create instance of class having empty constructor only
        /// </summary>
        /// <remarks>RunTime dependency injection.</remarks>
        [Test]
        public void Rt_SimplePerson()
        {
            UnityContainer uc = new UnityContainer();

            // Use of standard method
            uc.RegisterType(typeof(IPerson), typeof(SimplePerson));
            // Use of extension method - my prefered way
            uc.RegisterType<IPerson, SimplePerson>();

            IPerson person = uc.Resolve<IPerson>();
            person.Name = _Name;

            Assert.AreEqual(_Name, person.Name);
        }

        /// <summary>
        /// Create instance of class having constructor with parameters
        ///
        /// </summary>
        /// <remarks>RunTime dependency injection.</remarks>
        [Test]
        public void Rt_ComplexPerson()
        {
            UnityContainer uc = new UnityContainer();
            uc.RegisterType<IPerson, ComplexPerson>(new InjectionConstructor(_Name));

            IPerson person = uc.Resolve<IPerson>();
            Assert.AreEqual(_Name, person.Name);
        }

        /// <summary>
        /// Create singleton instance.
        /// </summary>
        /// <remarks>RunTime dependency injection.</remarks>
        [Test]
        public void Rt_Singleton()
        {
            UnityContainer uc = new UnityContainer();

            // Register class SimplePerson as singleton
            uc.RegisterType<IPerson, SimplePerson>(new ContainerControlledLifetimeManager());

            IPerson person1 = uc.Resolve<IPerson>();
            person1.Name = _Name;

            IPerson person2 = uc.Resolve<IPerson>();

            // Test that person1 and person2 represent same instance
            Assert.AreEqual(person1.Name, person2.Name);
            Assert.True(object.ReferenceEquals(person1,person2));
        }

        /// <summary>
        /// Create a named singleton instance.
        /// </summary>
        /// <remarks>RunTime dependency injection.</remarks>
        [Test]
        public void Rt_NamedSingleton()
        {
            UnityContainer uc = new UnityContainer();

            // Register the president of the United Stats as singleton
            uc.RegisterType<IPerson, SimplePerson>("PresidentOfUnitedStates", new ContainerControlledLifetimeManager());
            // Register other persons
            uc.RegisterType<IPerson, SimplePerson>();

            IPerson president = uc.Resolve<IPerson>("PresidentOfUnitedStates");
            president.Name = "Barack";

            IPerson justYouOrMe = uc.Resolve<IPerson>();
            justYouOrMe.Name = "Joe";

            // Test that person1 and person2 represent same instance
            Assert.AreNotEqual(president.Name, justYouOrMe.Name);
            Assert.False(object.ReferenceEquals(president, justYouOrMe));
        }
        #endregion


To make use of design-time configuration we create an App.config-file and add unity-specific information to it to declare the mapping. This results in exactly the same as we did during runtime-configuration. By doing so, that amount of code needed is significantly reduced. In a code-file all we have to do is to instanciate a Unity-container and tell this container to load it’s mappings from the config-file like this:

UnityContainer uc = new UnityContainer();
// Loads default configuration from App.Config using default container
uc.LoadConfiguration();

Unity allows you to have several configurations, called containers – inside your config-file. When using the LoadConfiguration()-method, you can use an overload to enter a string representing the name of a named conitainer in the config-file.

In our sample config-file we have declared three containers cointaining different mappings:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<!--Unity configuration-->
	<!-- To enable intellisense the schema must be on your local harddrive: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas\UnityConfiguration20.xsd -->
	<configSections>
		<section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration"/>
	</configSections>

	<!--Documentation: http://msdn.microsoft.com/en-us/library/ff660914%28v=pandp.20%29.aspx -->
	<unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
		<namespace name="DependencyInjectionUnity" />
		<assembly name="DependencyInjectionUnity" />

		<!--Container resolving IPerson to type SimplePerson-->
		<container>
			<!--Register type pattern: <namespace>.<typename>, <assembly>-->
			<register type="DependencyInjectionUnity.IPerson, DependencyInjectionUnity"
					  mapTo="DependencyInjectionUnity.SimplePerson, DependencyInjectionUnity" />
		</container>

		<!--Container resolving IPerson to type ComplexPerson having constructor with parameters -->
		<container name="ComplexPerson">
			<register type="DependencyInjectionUnity.IPerson, DependencyInjectionUnity"
					  mapTo="DependencyInjectionUnity.ComplexPerson, DependencyInjectionUnity">
				<constructor>
					<param name="name" value="Billy G."></param>
				</constructor>
			</register>
		</container>

		<!--Container resolving IPerson to a singleton type of SimplePerson-->
		<container name="SingletionSimplePerson">
			<register type="DependencyInjectionUnity.IPerson, DependencyInjectionUnity"
					  mapTo="DependencyInjectionUnity.SimplePerson, DependencyInjectionUnity" >
				<lifetime type="singleton" />
			</register>
		</container>
	</unity>
</configuration>

And here is how you design-time configuration is used with Unity. Again, please take a look at the comments for better understanding what is happening:

        #region DesignTimeDependencyInjection
        /// <summary>
        /// Create instance of class having empty constructor only
        /// </summary>
        /// <remarks>DesignTime dependency injection.</remarks>
        [Test]
        public void Dt_SimplePerson()
        {
            UnityContainer uc = new UnityContainer();
            // Loads default configuration from App.Config using default container
            uc.LoadConfiguration();

            IPerson person = uc.Resolve<IPerson>();
            person.Name = _Name;

            Assert.AreEqual(_Name, person.Name);
        }

        /// <summary>
        /// Create instance of class having constructor with parameters
        ///
        /// </summary>
        /// <remarks>DesignTime dependency injection.</remarks>
        [Test]
        public void Dt_ComplexPerson()
        {
            UnityContainer uc = new UnityContainer();
            // Loads default configuration from App.Config using named container
            uc.LoadConfiguration("ComplexPerson");

            IPerson person = uc.Resolve<IPerson>();
            Assert.AreEqual(_Name, person.Name);
        }

        /// <summary>
        /// Create singleton instance.
        /// </summary>
        /// <remarks>DesignTime dependency injection.</remarks>
        [Test]
        public void Dt_Singleton()
        {
            UnityContainer uc = new UnityContainer();
            uc.LoadConfiguration("SingletionSimplePerson");

            IPerson person1 = uc.Resolve<IPerson>();
            person1.Name = _Name;

            IPerson person2 = uc.Resolve<IPerson>();

            // Test that person1 and person2 represent same instance
            Assert.AreEqual(person1.Name, person2.Name);
            Assert.True(object.ReferenceEquals(person1, person2));
        }

        /// <summary>
        /// Create a named singleton instance.
        /// </summary>
        /// <remarks>DesignTime dependency injection.</remarks>
        [Test]
        public void Dt_NamedSingleton()
        {
            UnityContainer ucPresident = new UnityContainer();
            ucPresident.LoadConfiguration("SingletionSimplePerson");

            UnityContainer ucJustYouOrMe = new UnityContainer();
            ucJustYouOrMe.LoadConfiguration();

            IPerson president = ucPresident.Resolve<IPerson>();
            president.Name = "Barack";

            IPerson justYouOrMe = ucJustYouOrMe.Resolve<IPerson>();
            justYouOrMe.Name = "Joe";

            // Test that person1 and person2 represent same instance
            Assert.AreNotEqual(president.Name, justYouOrMe.Name);
            Assert.False(object.ReferenceEquals(president, justYouOrMe));
        }
        #endregion

Using unity, your can easily switch between different implementations of your interfaces or base classes. Doing testing, you could switch between a fake class for unit testing and the production class for integration tests. When doing design-time configuration, all you needed to do is to change the name of the cointainer defining the mappings.

date14 Mar

No, I this is not David Hasselhoff but your unit tests crying for freedom, because they want to be independent. Independent from other classes your class to test must collaborate with.

In my last post I gave a definition of a useful unit test. Linked to this definition is the concept of dependency injection which we are going to explain now.

So, what is dependency injection?

Let’s have a look what each word means:

  • Dependency
    This descripes the fact that every class you have needs to collaborate with other classes to get it’s job done. Are there any exceptions to this rule? The answer is no. To give you an example, the second you declare a variable of  type string, you create a dependency to the string-class. Does this dependency create any possible problems to your unit tests? Again, the answer is no. Because this dependency can be considered as being stable and secure. Stable means, that in all likelihood the string type’s existing members won’t change in the long term. Secure means, that the chance that a member of the string class will produce an error because of bad implementation is very unlikely.In constrast, if your class to test makes use of of a class being subject to change and possible implementation errors, the dependency creates a problem to your unit test because we don’t comply anymore with our definition of a useful unit test.
  • Injection
    This relates to the method how you construct the dependency between the class to test and the class it need’s to collaborate. Consider the following dependency between the Customer class and the Repository class the Customer class is dependent on. Does this dependency includes injection?

    public class Customer
    {
        Repository _Repository;
    
        public Customer()
        {
            this._Repository = new Repository();
        }
    
        public void Load()
        {
            return this._Repository.Load()
        }
    }
    

    The answer is no. The dependency is hard coded, static.

    Take a look at the next version of the Customer class. Does this dependency include injection?:

    public class Customer
    {
        Repository _Repository;
    
        private Customer()
        { }
    
        public Customer(Repository repository)
        {
            this._Repository = repository;
        }
    
        public void Load()
        {
            return this._Repository.Load();
        }
    }
    

    The answer is yes. Because this customer implementation get’s it’s dependency injected from outside; from the code creating an instance of the Customer class.

    In practice, you won’t see concrete types (Repository in this example) being instanciated by injection but interfaces or base classes instead. Like this:

    public class Customer
    {
        IRepository _Repository;
    
        private Customer()
        {}
    
        public Customer(IRepository repository)
        {
            this._Repository = repository;
        }
    
        public void Load()
        {
            return this._Repository.Load();
        }
    }
    

    Why instanciating Interfaces or base classes and no concrete types? This is because Interfaces and base classes are exchangeable. Many concrete classes may exist implementing a specific interface or base class. The concrete implementation may be changed on demand. E.g. a customer in production environment may collaborate with a class class implementing the Interface IRepository in such a way that the repository class loads data from the production database. On the other hand, a customer in a test environment may collaborate with a class implementing the Interface IRepository in such a way that the repository does not connect to the production database but to a text-file containing static data only.

By the way: often, the term “Dependency Injection” is used as a synonym to “Inversion of Control”. I guess, someone more intelligent than me wanted to stress the point, that the class collaborating with another class no longer defines the relationship by it-self, but that it is defined by the client-code, instead.

Anyway, understanding what dependency injection means opens the gateway to the next step: the use of dependency injection frameworks, which will be covered in the next post.

See you!

Nerdy

date9 Mar

In a former post I wrote how to make use of experimental unit tests as a kind of first step to get accustomed with unit testing. These first steps were still easy to implement as everything needed was to reference your unit-testing-framework of choice, get a unit-test-runner and write your tests. Unfortunately, it get’s a lot more complicated when you seriously try to use unit-testing.

Why?

The answer lies in the definition of a useful unit-test itself, which means that you just test the member (mostly a method) you want to test. By doing so:

  • you know that if your unit-test fails, the reason for that resides inside the member it-self, not in some code called by the tested member hidden deep inside the stack
  • your results are repeatable
  • your unit tests execute fast

Unfortunately, it won’t take a long time for you to notice that in order to test some methods, you must instantiate other classes colaborating with the class whose member you’re going to test. And without knowing your code, I am hazardous enough to say that most of your non-trivial method relies on a another class to function. In object-oriented programming this fact is known as coupling.

Now the question is, if your code is loosely coupled or not, because only loosely coupled code allows you to make use of efficient unit testing. But what does loosely coupled mean?

It means, that first you will never get rid of your classes collaborating with each-other and thus being dependent from each-other. The real difference lies in how you construct the collaboration. The point is, that when ClassA needs to call a method from ClassB, it should never do this by holding a reference to ClassB as ClassB itself, but by using an interface which encapsulates the set of functionality ClassA needs.

This way, the implementation of the set of functionality needed by ClassA becomes exchangeable. Now you can implement your own light-weight version of a class implementing the interface defining the functionaliy ClassA needs. When writing unit-tests, you will always reference the light-weight implementation of the interface. This ensures, that the tests will hold the defenition of a good unit-test as explained above.

But doesn’t this create a whole bunch of overhead? Not only writing unit tests but to define interfaces en masse and implementing them not only once, but at least twice (one real implementation and a second light-weight implementation). The answer is yes and so a new question arises: how to keep this overhead as small and efficient as possible?

Luckily, we are not the first persons to ask this question and answers exist. Unluckily, the answers are not for free, because you new concepts arise you need to understand. Namely:

  • understanding dependendy injection and how to use dependency injection frameworks
  • mocking and how to use mocking frameworks

I will cover both of them in a later post. Right now, be glad to know what makes a unit-test a good one and to see the problem in making it good.

date5 Mar

Access
I just published my first article in CodeProject. It’s about avoiding event spaghetti code when business logic becomes complex and this is also my first article about Microsoft Access VBA.

Click on the picture to read the article:

CodeProject
.

date7 Feb

I guess that every programmer needs to have a look at some feature he’s not accustomed to from time to time. Normaly this happens in the course of a running project, mostly you only need to type some lines of code to see how an unfamiliar method works, and sometimes you may need some more code to test a concept.

Now the question is: where to type this code? For most of us the days of the office-VBA editor enabling you to execute any not private method which was currently selected are gone. If you work on an executable assembly, you can place your code inside a method being called right from the start after the assembly is executed, like a loaded-event of the mainform in a windows project.

Doing so has two drawbacks:

  1. at best you temporarily pollute your production code with experimental code, at worst you deploy your project still containing your test code running on the user’s machine
  2. in case you think about cleaning the experimental code, you will probable delete it, because this lines have no right to live in production code and only to comment these lines out is not a good coding style

Even worse: in case you do not have an executable assembly, you need to create a new executable project or even a brand new solution and in both cases you need to reference all the assemblies you need for your experimental coding. This is cumbersome and creates the overhead the choosen executable project type (command line, winforms, WPF, …).

For theses reasons my prefered way is to expand my unit tests project with a new namespace containing all my experimental code. By doing so, you gain the following benefits:

  1. experimental and production code are seperated by project/assembly
  2. because the unit tests project already references the development projects, the need to reference additional assemblies is lowered
  3. using a refactoring tool like ReSharper, JustCode or CodeRush provides you with unit test runners, allowing you to select any of you experimental methods and execute it directly. Doing this in the test runner’s debug mode enables you to set break-points and make use to edit and continue during runtime.

Of course, for this to work, you first need to mark your experimental method as a regular testing method using the attribute of the  the testing framework you second make use of a refactoring tool including a unit test runner supporting the debug mode.

Below you see an example how it looks like using NUnit as testing framewok combined with the JustCode test runner from Telerik:

ExperimentalCode

And if you still have no refactoring tool, get one. You won’t regret!

date29 Jan

Ok, maybe not a hundred, but still a lot. But first let me explain the problem I encountered when I first wanted to remove an item according to a certain criteria from a generic list this way:

Module Start
    Sub Main()

        Dim myList As New List(Of String)

        myList.Add("A")
        myList.Add("BB")
        myList.Add("CCC")
        myList.Add("DDDD")

        ' Run-Time error: InvalidOperationException
        For Each s In myList
            If s.Length > 2 Then
                myList.Remove(s)
            End If
        Next

        Console.WriteLine(String.Format("MyList still contains {0}", myList.Count.ToString))
        Console.ReadKey()
    End Sub

Yes, you are correct: an InvalidOperationException was thrown. As you may think, I was motivated enough to find a solution – or better quick-fix – for this problem. I simply let the loop restart any time an item was removed from the list:

Restart:
        For Each s In myList
            If s.Length > 2 Then
                myList.Remove(s)
                GoTo Restart
            End If
        Next

Going back to the GoTo-statement – shame on me! And indeed, I felt quilty from the first second I ran this code. Because of this I posted an entry in the Microsoft forum and people came up with several clever (at least more clever than mine) solutions. And here they are:

This one iterates from behind to avoid the InvalidOperation-Exception:

        For i = myList.Count - 1 To 0 Step -1
            If myList(i).Length > 2 Then
                myList.RemoveAt(i)
            End If
        Next

This one uses a seperate function to pass a predicate delegate as parameter for the RemoveAll-method:

    myList.RemoveAll(AddressOf RemovePredicate)

    Public Function RemovePredicate(s As String) As Boolean
        If s.Length > 2 Then
            Return True
        Else
            Return False
        End If
    End Function

And this one uses a lamda function (with multiple lines as valid since VB2010):

        myList.RemoveAll(Function(s)
                             If s.Length > 2 Then
                                 Return True
                             Else
                                 Return False
                             End If
                         End Function)

Choose any solution which appears the most sexy to you, except the one with the GoTo, because theses days are over now – even for me ;)

date4 Jan


Recently a college showed me a blog entry of Brad Schulz – a SQL Server MVP – where he demonstrates what else you can do with the apply operator except using it to call your user defined functions. This blog entry was that cool to me that I am doing an own entry out of it. Contrary to his entry, I concentrate on the most important aspects he demonstrates and use a very simple all-in-one sample.

But before writing SQL, let’s philosoph about what the apply operator does:

when writing your ordinary SQL in the from part (this is the left side) you can introduce the apply operator and by doing so your statement following the apply operator can access all the columns already defined in the left side of the query. This possibility enables the calling of user defined functions (UDF) with changing parameter values taken from the left side of the query. Prior to SQL Server 2005 your UDF could only be called with static parameter values.

Other than calling UDFs in a dynamic fashion, I imagine myself making use of apply to do the following:

  • Execute a subquery using values of the left side as criteria
  • Create a new column based on values of the left side
  • Perform calculations based on values of the left side

Ok, let’s move on to SQL. First create two tables with some data to feed our query:

CREATE TABLE dbo.Continent
(
ID int PRIMARY KEY
,Continent varchar(50) NOT NULL
)
GO

CREATE TABLE dbo.Country
(
ID int PRIMARY KEY IDENTITY(1,1)
,ContinentID int NOT NULL REFERENCES Continent(ID)
,Country varchar(50) NOT NULL
,Populace int NOT NULL
)
GO

-- Insert records
INSERT INTO Continent (ID, Continent) VALUES (1, 'Europe')
INSERT INTO Continent (ID, Continent) VALUES (2, 'Asia')
INSERT INTO Continent (ID, Continent) VALUES (3, 'North america')
INSERT INTO Continent (ID, Continent) VALUES (4, 'Africa')

INSERT INTO Country (ContinentID, Country, Populace) VALUES (1, 'Germany',80)
INSERT INTO Country (ContinentID, Country, Populace) VALUES (2, 'China', 1500)
INSERT INTO Country (ContinentID, Country, Populace) VALUES (3, 'USA', 250)
INSERT INTO Country (ContinentID, Country, Populace) VALUES (3, 'Canada', 35 )
INSERT INTO Country (ContinentID, Country, Populace) VALUES (4, 'Algeria', 15)
GO

And now the fun part. A single query demonstrating the three points listed above.

SELECT
   Country
   ,Continent
   ,Hemisphere
   ,SkinColor
   ,Populace
   ,PopulaceChina
   ,PopulaceLessThanChina
FROM
	Country
	-- 1.How to: Execute subquery
	CROSS APPLY (
				SELECT
					Continent
				FROM
					Continent
				WHERE
					Country.ContinentID = Continent.ID
				) Continent
	-- 2.How to: Create new column (based on data of first apply)
	CROSS APPLY (
				SELECT
					CASE
                         WHEN Continent.Continent IN ('Europe', 'North america') THEN 'The west'
                         WHEN Continent.Continent = 'Asia' THEN 'The east'
						 ELSE 'The rest'
                    END AS Hemisphere
				) AS Hemisphere
	-- 3.How to: Create new column (based on data of second apply)
	CROSS APPLY (
				SELECT
					CASE
                         WHEN Hemisphere = 'The west' THEN 'White'
                         WHEN Hemisphere = 'The east' THEN 'Yellow'
						 ELSE 'Black maybe'
                    END AS SkinColor
				) AS SkinColor
	-- 4. How to: Calculate how many people china has more than the other countries
	CROSS APPLY (
				SELECT
					Populace AS PopulaceChina
				FROM
					Country
				WHERE
					Country = 'China'
				) PopulaceChina
	CROSS APPLY (
				SELECT
					PopulaceChina - Populace  AS PopulaceLessThanChina
				) PopulaceLessThanChina

And this is the result:

Apply

Note, that although packing all aspects above in a single query, the query itself is still easy to read. That’s the big advantage of the apply operator: it makes your code more transparent and less error prone!

If you are interested in even more aspects of what you can do with apply, I recommand you to read Brad’s article mentioned above.

date29 Dec

This article will be salvation for everybody who already implemented the INotifyPropertyChanged interface, refactored some names of his properties and than asked himself why the UI does not update correctly anymore. And if you did not gain this experience, you will be glad to read that this will never happen to you if you implement INotifyPropertyChanged by using the Deluxe version, because this implementation does not need to pass the property name as string.

And this is where the magic happens:

    ''' <summary>
    ''' Raises the PropertyChanged event by determining property
    ''' name using stack trace.
    ''' </summary>
    Protected Overridable Overloads Sub OnPropertyChanged()
        Dim strackTrace As New System.Diagnostics.StackTrace
        Dim propertyName As String = strackTrace.GetFrame(1).GetMethod.Name

#If DEBUG Then
        If Not strackTrace.GetFrame(1).GetMethod.Name.StartsWith("set_") Then
            Debug.Fail("OnPropertyChanged-method without parameter was not called from a property setter.")
        End If
#End If

        Me.OnPropertyChanged(propertyName.Remove(0, 4))
    End Sub

We make us of stack walking to identify the name of the property whose value changed. Note that this assumes that you called this method directly within the property setter, which should be the only place where you change the value of the variable backed by the property. In case you did not, there is a security check used in the debugging version of a project popping up a window telling you there is something wrong.

The following code snippet gives you a complete implemetation of INotifyPropertyChanged containing also the classic implementation of the PropertyChanged-method where you need to pass in the property name for the sake of flexibility:

#Region "INotifyPropertyChanged"
    ''' <summary>
    ''' Warns the developer if this object does not have
    ''' a public property with the specified name. This
    ''' method does not exist in a Release build.
    ''' </summary>
    <Conditional("DEBUG"), DebuggerStepThrough()> _
    Public Sub VerifyPropertyName(ByVal propertyName As String)
        ' Verify that the property name matches a real,
        ' public, instance property on this object.
        If TypeDescriptor.GetProperties(Me)(propertyName) Is Nothing Then
            Dim msg As String = "Invalid property name: " & propertyName

            If Me.ThrowOnInvalidPropertyName Then
                Throw New Exception(msg)
            Else
                Debug.Fail(msg)
            End If
        End If
    End Sub

    ''' <summary>
    ''' Returns whether an exception is thrown, or if a Debug.Fail() is used
    ''' when an invalid property name is passed to the VerifyPropertyName method.
    ''' The default value is false, but subclasses used by unit tests might
    ''' override this property's getter to return true.
    ''' </summary>
    Private privateThrowOnInvalidPropertyName As Boolean
    Protected Overridable Property ThrowOnInvalidPropertyName() As Boolean
        Get
            Return privateThrowOnInvalidPropertyName
        End Get
        Set(ByVal value As Boolean)
            privateThrowOnInvalidPropertyName = value
        End Set
    End Property

    ''' <summary>
    ''' Raises the PropertyChanged event by determining property
    ''' name using stack trace.
    ''' </summary>
    Protected Overridable Overloads Sub OnPropertyChanged()
        Dim strackTrace As New System.Diagnostics.StackTrace
        Dim propertyName As String = strackTrace.GetFrame(1).GetMethod.Name

#If DEBUG Then
        If Not strackTrace.GetFrame(1).GetMethod.Name.StartsWith("set_") Then
            Debug.Fail("OnPropertyChanged-method without parameter was not called from a property setter.")
        End If
#End If

        Me.OnPropertyChanged(propertyName.Remove(0, 4))
    End Sub

    ''' <summary>
    ''' Raised when a property on this object has a new value.
    ''' </summary>
    Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged

    ''' <summary>
    ''' Raises this object's PropertyChanged event.
    ''' </summary>
    ''' <param name="propertyName">The property that has a new value.</param>
    Protected Overridable Overloads Sub OnPropertyChanged(ByVal propertyName As String)
        Me.VerifyPropertyName(propertyName)

        RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))

        Dim handler As PropertyChangedEventHandler = Me.PropertyChangedEvent
        If Not handler Is Nothing Then
            handler.Invoke(Me, New PropertyChangedEventArgs(propertyName))
        End If
    End Sub
#End Region

Finally, refactoring got a bit more easier.

date18 Dec