Planning Poker

During agile projects it is common for teams to estimate the “size” of each user-story being considered for inclusion in the next development sprint by committee. This involves each team member assigning a number of points to each user-story. It is usual to restrict number choices to the Fibonacci numbers. Many teams do this using special “planning poker” card decks, allowing each team member to select a card from their own deck in private, prior to everyone revealing their cards at the same time.

However, with the increase in popularity of home working it is not always possible to have all team members in the same room to facilitate an estimation session.

PlanningPoker.com is a great (and free!) website that allows teams to play planning poker online. User stories are loaded in to the tool in advance, and then iterated through one at a time. Participants are allowed some time for discussion, during which each should choose a card. Once everyone has chosen the cards are revealed allowing a consensus to be reached.

NHibernate NullableDateTime custom type

NHibernate doesn’t support mappings for nullable DateTime types by default. Fortunately, this is easy to implement using the IUserType interface. To demonstrate this, create a new Console Application project and add FluentNHibernate using NuGet. Then add the following class:

using System;
using System.Data;
using NHibernate;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;

namespace UserTypes
{
    /// <summary>
    /// Type to allow NHibernate to persist DateTime? objects
    /// </summary>
    public class NullableDateTimeType : IUserType
    {
        /// <summary>
        /// Gets a value indicating whether the value is mutable
        /// </summary>
        public bool IsMutable
        {
            get
            {
                // This item is immutable:
                return false;
            }
        }

        /// <summary>
        /// Gets the type returned by NullSafeGet()
        /// </summary>
        public Type ReturnedType
        {
            get
            {
                return typeof(DateTime?);
            }
        }

        /// <summary>
        /// Gets the SQL types for the columns mapped by this type. 
        /// </summary>
        public SqlType[] SqlTypes
        {
            get
            {
                return new[]
                {
                    new SqlType(DbType.DateTime)
                };
            }
        }

        /// <summary>
        /// Reconstruct an object from the cacheable representation. At the very least this method should perform a deep copy if the type is mutable.
        /// </summary>
        /// <param name="cached">The cached object</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The assemled object</returns>
        public object Assemble(object cached, object owner)
        {
            // Used for caching. As our object is immutable we can return as is:
            return cached;
        }

        /// <summary>
        /// Return a deep copy of the persistent state, stopping at entities and at collections. 
        /// </summary>
        /// <param name="value">The item to copy</param>
        /// <returns>The copied item</returns>
        public object DeepCopy(object value)
        {
            // We deep copy the item by creating a new instance with the same contents.
            // Note that this happens for free with value types because of the way 
            // that method parameters work:
            if (value == null)
            {
                return null;
            }

            return value as DateTime?;
        }

        /// <summary>
        /// Transform the object into its cacheable representation. At the very least this method should perform a deep copy 
        /// if the type is mutable. That may not be enough for some implementations, however; for example, associations must 
        /// be cached as identifier values.
        /// </summary>
        /// <param name="value">The cached object</param>
        /// <returns>The dassassemled object</returns>
        public object Disassemble(object value)
        {
            // Used for caching. As our object is immutable we can return as is:
            return value;
        }

        /// <summary>
        /// Compare two instances of the class mapped by this type for persistent "equality" ie. equality of persistent state 
        /// </summary>
        /// <param name="x">The first item</param>
        /// <param name="y">The second item</param>
        /// <returns>A value indicating whether the items are equal</returns>
        public new bool Equals(object x, object y)
        {
            if (x == null && y == null)
            {
                return true;
            }

            if (x == null)
            {
                return false;
            }

            return x.Equals(y);
        }

        /// <summary>
        /// Get a hashcode for the instance, consistent with persistence "equality" 
        /// </summary>
        /// <param name="x">The value to get the hash code for</param>
        /// <returns>The hash code</returns>
        public int GetHashCode(object x)
        {
            if (x == null)
            {
                return 0;
            }

            return x.GetHashCode();
        }

        /// <summary>
        /// Retrieve an instance of the mapped class from a resultset. Implementors should handle possibility of null values. 
        /// </summary>
        /// <param name="rs">The reader</param>
        /// <param name="names">The item names</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The object requested</returns>
        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            // We get the DateTime from the database using the NullSafeGet used to get strings from NHibernateUtil:
            return NHibernateUtil.DateTime.NullSafeGet(rs, names[0]) as DateTime?;
        }

        /// <summary>
        /// Write an instance of the mapped class to a prepared statement. Implementors should handle possibility of null values. A multi-column type should be written to parameters starting from index. 
        /// </summary>
        /// <param name="cmd">The command</param>
        /// <param name="value">The value to use</param>
        /// <param name="index">The index to set</param>
        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            // Convert to the correct type and set:
            var dateTimeValue = value as DateTime?;

            if (dateTimeValue == null)
            {
                NHibernateUtil.DateTime.NullSafeSet(cmd, null, index);
            }
            else
            {
                NHibernateUtil.DateTime.NullSafeSet(cmd, dateTimeValue.Value, index);
            }
        }

        /// <summary>
        /// During merge, replace the existing (target) value in the entity we are merging to with a new (original) 
        /// value from the detached entity we are merging. For immutable objects, or null values, it is safe to 
        /// simply return the first parameter. For mutable objects, it is safe to return a copy of the first parameter. 
        /// For objects with component values, it might make sense to recursively replace component values. 
        /// </summary>
        /// <param name="original">The original value</param>
        /// <param name="target">The target value</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The replacement object</returns>
        public object Replace(object original, object target, object owner)
        {
            // As our object is immutable we can just return the original  
            return original;
        }
    }
}

This class is responsible for telling NHibernate how to save and retrieve nullable DateTime values. The following demonstrates how this class can be used.

Given a class with a nullable DateTime property:

using System;

namespace UserTypes
{
    public class Student
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime? GraduationDate { get; set; }
    }
}

…the mapping would be as follows:

using FluentNHibernate.Mapping;
using UserTypes;

public class StudentMapping : ClassMap<Student>
{
    public StudentMapping()
    {
        Not.LazyLoad();
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.FirstName).Length(20).Not.Nullable();
        Map(x => x.LastName).Length(20).Not.Nullable();
        Map(x => x.GraduationDate).Nullable().CustomType(x => typeof(NullableDateTimeType));
    }
}

This can be tested by adding the following code to the Program class:

using System;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace UserTypes
{
    public static class Program
    {
        public static void Main()
        {
            var connectionString = "Data Source=(local);Initial Catalog=StudentTest;Integrated Security=True;Pooling=False";

            var sessionFactory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString).ShowSql())
                .ExposeConfiguration(BuildSchema)
                .Mappings(x => x.FluentMappings.AddFromAssemblyOf<Student>())
                .BuildSessionFactory();

            // Create the session:
            using (var session = sessionFactory.OpenSession())
            {
                session.SaveOrUpdate(new Student { FirstName = "Not", LastName = "Graduated" });
                session.SaveOrUpdate(new Student { FirstName = "Already", LastName = "Graduated", GraduationDate = new DateTime(2000, 1, 1) });
            }

            using (var session = sessionFactory.OpenSession())
            {
                var students = session.QueryOver<Student>().List();
                foreach (var student in students)
                {
                    Console.WriteLine("{0} {1} {2}", student.FirstName, student.LastName, student.GraduationDate);
                }
            }

            Console.ReadLine();
        }

        private static void BuildSchema(Configuration configuration)
        {
            new SchemaExport(configuration).Create(false, true);
        }
    }
}

Note that the code above assumes there is a local instance of SQL Server running, with a database called StudentTest accessible using integrated authentication.

Running the code creates the following table, which includes a DateTime field that accepts null values as required:

NHibernateNullableDateTime01

…inserts the following data:

NHibernateNullableDateTime02

…and outputs the following:

NHibernateNullableDateTime03

This proves that the mapping can be used to store and retrieve null and non-null DateTime values as required.

Enabling ‘xp_cmdshell’ in SQL Server

I was doing some work with SQL Server today and came across a solution I’ve used a lot over the years. This is a perfect candidate for my “Aides-Memoires” category.

Part of the task involved copy a file from one location to another using T-SQL. This is simple to achieve:

DECLARE @CommandText varchar(1000)
SET @CommandText = 'copy [source] [destination]'
EXEC master..xp_cmdshell @CommandText

However, when I executed the code above I got the following error:

“SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.”

The solution to this is simple. Run the following command to enable the use of xp_cmdshell:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

After running the T-SQL above, the code to copy the file worked as expected.