Getting the auto-incremental ID of a record in SQL Server

Time for another SQL Server aide-memoire…

I can never remember which command to use in T-SQL to get the ID of the most recently added record when using auto-incrementing IDs. There are three choices:

  • @@IDENTITY
  • SCOPE_IDENTITY()
  • IDENT_CURRENT(‘{TableName}’)

However, as described in great and excellent detail by the ‘SQL Authority’ in one of his blog posts, the one to use is SCOPE_IDENTITY(). This to restrict the ID to one generated in the current scope, rather than accidentally getting an ID generated by a trigger further down the line.

Geeky Google Analytics

Google Analytics is awesome, there is no doubt about it. It’s easy to integrate into websites and the UI to get metrics information out of is pretty cool too, especially the real time stuff which now seems to work better than ever. However, when I’m asked particularly complex metrics-based questions by guys I develop sites for I often find it difficult to get exactly the information I need from the standard Google Analytics UI.

Instead, I find the Google Analytics Query Explorer 2 tool useful. It’s not pretty, but it allows me to build up complex analytics queries in a single screen without having to search around for filters and options. It feels more like writing SQL against Google’s Analytics store rather than battling with the standard UI they wrap around the numbers. I wouldn’t be surprised if the default UI uses the Query Explorer behind the scenes to get at the numbers either.

Enjoy!

Essential FireFox add-ons for web-developers

Although Chrome seems to be winning the browser wars these days, I still like to develop against Firefox first and then tweak/hack CSS for compatibility with other browsers afterwards. I do this because I believe that Firefox most closely adheres to the HTML and CSS standards when it renders sites. I’m pretty sure that this belief used to be true, but I’m not certain if it’s technically correct today. However, as an approach it still seems to work pretty well. I’d be interested to hear your views on this.

I thus know more about Firefox add-ons than add-ons for other browsers. Here is my list of essential Firefox add-ons, which I’ll keep up-to-date:

Firebug

Totally essential for web-developers. Does pretty much everything you’ll ever need, from on-the-fly HTML and CSS tweaks, to JavaScript debugging, to monitoring network calls and cookies, and much, much more.

Web Developer by Chris Pederick

If you do find something that Firebug doesn’t do, there’s a fair chance that Chris Pederick has thought of it and included it in his web-developer toolbar. Worth getting alone because it makes it ridiculously easy to enable/disable JavaScript and CSS without having to remember where the options are hiding in the standard Firefox options. It does some other neat things too though, like highlighting elements of certain kind and showing outlines, which are invaluable if you want to get everything to line up nicely.

iMacros

Not really a developer tool, but a great tool for recording and replaying macros in Firefox. I tend to use it to log in to sites that have laborious access procedures or have auto-complete disabled.

User Agent Switcher

Another one from Chris Pederick, and very useful for testing out different user agents with your code. When used in conjunction with the re-size feature in the Web Developer toolbar mentioned above, you can get your sites in a pretty good state to work on mobile devices, before testing on the devices themselves.

SAML Tracer

Useful when working with SAML based single-sign on solutions, which I’ve been doing lately with ADFS.

That’s all for now folks!

Enabling developer options on Samsung Galaxy S4

Although it won’t be useful to most standard users, here’s how to enable Developer Options on your Samsung Galaxy S4:

Open the Settings menu. This is available by dragging down the menu at the top of the screen and clicking on the settings cog:

S4 Developer Options 1

Click on More at the top right of the screen:

S4 Developer Options 2

Then click About device:

S4 Developer Options 3

Then press the entry for Build number a number of times:

S4 Developer Options 4

A message will tell you that you are touching the right area. A countdown is also included so you know how many presses you need to make. You should get a success message when it’s worked.

This then activates a new menu labelled Developer options, which you can see is already activated on my phone in the screenshots above.

The most useful feature in this new menu for me is USB debugging, which is required by some apps, including MyPhoneExplorer.

Aside: My Phone Explorer is an awesome free piece of software which I use to connect my phone to my computer. With this software I can send texts from my computer, keep a back up of all my messages and perform various sync-tasks. I find that it works better than the Kies software that comes with the phone as standard.

Taking screenshots on Samsung Galaxy S4

It is sometimes useful to be able to take screenshots of what’s happening on your phone, particularly if you’re a developer/tester doing some work on mobiles. It’s also useful if you’re blogging about phones and want to include some screenshots, as I did in my last post on killing unused apps. On the Samsung Galaxy S4 you can take screenshots by holding down the power button and home key together until the screen flashes. If you’ve got the sound on, you’ll also hear a camera shutter sound.

An especially useful trick is to use the Dropbox app and configure it to sync images taken on the phone. If you do this, your screenshot will appear in the Dropbox on your local machine within seconds, assuming you’re on a good internet connection.

Killing apps on Samsung Galaxy S4

Now that I’m the (proud) owner of a Samsung Galaxy S4 I’ll blog about any cool features and tricks I pick up for this phone. As the Galaxy S4 runs on Android any such tips will probably work on other Android phones as well, although the buttons used will probably differ between different makes of phones.

First up, there’s a neat way to kill open apps that are running in the background. Killing unused apps could potentially save battery usage, and so is worth doing if you’re trying conserve power. To access this feature, first “long press” the Home key. On the screen that opens, click on the button at the bottom left hand corner of the screen showing a pie-chart:

S4 Kill Apps

Click the Applications button at the top left hand corner of the screen:

S4 Kill Apps 2

…end click End next to any applications you want to close. Simple!

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.

Encapsulated and strongly-typed access to .NET configuration files with dependency injection

This article builds on a previous post entitled Encapsulated and strongly-typed access to .NET configuration files. In this example I’ll be adding dependency injection using Castle Windsor.

As per my previous post, the code for this example is in GitHub, here:

https://github.com/stevebarker333/Injected-Encapsulated-Strongly-Typed-Configuration-Files

In this post I’ll assume that you’ve read the previous post, and that you have the existing code base as a starting point. The first step then, is to add Castle Windsor to the project using NuGet. This adds references to each project as follows:

ConfigurationDependencyInjection01

Since dependency injection works by resolving interfaces to types, each encapsulated ConfigurationFile will need to be given an interface by adding IConfigurationFile to each of the two UI projects:

ConfigurationDependencyInjection02

These new interfaces need contain only the properties that are unique to each UI. Access to core properties is achieved via the ICoreConfiguration, as follows:

UI1.Configuration.ConfigurationFile:

using System;
using API.Configuration;

namespace UI1.Configuration
{
    public interface IConfigurationFile : ICoreConfiguration
    {
        string UI1String { get; }
        int UI1Int { get; }
        Uri UI1Uri { get; }
    }
}

UI2.Configuration.ConfigurationFile

using System;
using API.Configuration;

namespace UI2.Configuration
{
    public interface IConfigurationFile : ICoreConfiguration
    {
        string UI2String { get; }
        int UI2Int { get; }
        Uri UI2Uri { get; }
    }
}

The definitions of the ConfigurationFile classes in each UI project need to be adjusted to include these new interfaces, as follows:

public class ConfigurationFile : 
    API.Configuration.ConfigurationFile, IConfigurationFile, 
    API.Configuration.ICoreConfiguration
{
    ...
}

Each UI then gets its own Injector class:

ConfigurationDependencyInjection03

…containing the following code:

UI1.DependencyInjection.Injector:

using API.Configuration;
using Castle.MicroKernel.Registration;
using Castle.Windsor;
using Castle.Windsor.Installer;
using UI1.Configuration;
using API;

namespace UI1.DependencyInjection
{
    public static class Injector
    {
        private static readonly object InstanceLock = new object();

        private static IWindsorContainer instance;

        public static IWindsorContainer Instance
        {
            get
            {
                lock (InstanceLock)
                {
                    return instance ?? (instance = GetInjector());
                }
            }
        }

        private static IWindsorContainer GetInjector()
        {
            var container = new WindsorContainer();

            container.Install(FromAssembly.This());

            RegisterInjector(container);
            RegisterConfiguration(container);
            RegisterWidget(container);

            return container;
        }

        private static void RegisterInjector(WindsorContainer container)
        {
            container.Register(
                Component.For<IWindsorContainer>()
                .Instance(container));
        }

        private static void RegisterConfiguration(WindsorContainer container)
        {
            container.Register(
                Component.For<IConfigurationFile, ICoreConfiguration>()
                .ImplementedBy(typeof(Configuration.ConfigurationFile))
                .LifeStyle.Singleton);
        }

        private static void RegisterWidget(WindsorContainer container)
        {
            container.Register(
                Component.For<IWidget>()
                .ImplementedBy(typeof(Widget))
                .LifeStyle.Singleton);
        }
    }
}

UI2.DependencyInjection.Injector:

using API.Configuration;
using Castle.MicroKernel.Registration;
using Castle.Windsor;
using Castle.Windsor.Installer;
using UI2.Configuration;
using API;

namespace UI2.DependencyInjection
{
    public static class Injector
    {
        private static readonly object InstanceLock = new object();

        private static IWindsorContainer instance;
        
        public static IWindsorContainer Instance
        {
            get
            {
                lock (InstanceLock)
                {
                    return instance ?? (instance = GetInjector());
                }
            }
        }

        private static IWindsorContainer GetInjector()
        {
            var container = new WindsorContainer();

            container.Install(FromAssembly.This());

            RegisterInjector(container);
            RegisterConfiguration(container);
            RegisterWidget(container);

            return container;
        }

        private static void RegisterInjector(WindsorContainer container)
        {
            container.Register(
                Component.For<IWindsorContainer>()
                .Instance(container));
        }

        private static void RegisterConfiguration(WindsorContainer container)
        {
            container.Register(
                Component.For<IConfigurationFile, ICoreConfiguration>()
                .ImplementedBy(typeof(Configuration.ConfigurationFile))
                .LifeStyle.Singleton);
        }

        private static void RegisterWidget(WindsorContainer container)
        {
            container.Register(
                Component.For<IWidget>()
                .ImplementedBy(typeof(Widget))
                .LifeStyle.Singleton);
        }
    }
}

Note that the technique described in my post entitled Allowing Castle Windsor to resolve two interfaces to the same instance allows the ConfigurationFile type to be accessed using both the API interface ICoreConfiguration and each of the UI interfaces, IConfigurationFile. Note also that the Widget is registered with the dependency injector. This requires some small changes to the Widget and the creation of an interface, IWidget:

using System;
using API.Configuration;

namespace API
{
    public class Widget : IWidget
    {
        public ICoreConfiguration Configuration { get; set; }

        public void DoSomething()
        {
            Console.WriteLine(Configuration.CoreString);
            Console.WriteLine(Configuration.CoreInt);
            Console.WriteLine(Configuration.CoreUri);
        }
    }
}
namespace API
{
    public interface IWidget
    {
        void DoSomething();
    }
}

Finally, we change the Program classes in the console applications to use the injector, as follows:

using System;
using UI1.Configuration;
using API;
using UI1.DependencyInjection;

namespace UI1
{
    public static class Program
    {
        public static void Main()
        {
            var configurationFile = Injector.Instance.Resolve<IConfigurationFile>();

            Console.WriteLine(configurationFile.UI1String);
            Console.WriteLine(configurationFile.UI1Int);
            Console.WriteLine(configurationFile.UI1Uri);

            var widget = Injector.Instance.Resolve<IWidget>();
            widget.DoSomething();

            Console.ReadLine();
        }
    }
}
using System;
using UI2.Configuration;
using API;
using UI2.DependencyInjection;

namespace UI2
{
    public static class Program
    {
        public static void Main()
        {
            var configurationFile = Injector.Instance.Resolve<IConfigurationFile>();

            Console.WriteLine(configurationFile.UI2String);
            Console.WriteLine(configurationFile.UI2Int);
            Console.WriteLine(configurationFile.UI2Uri);

            var widget = Injector.Instance.Resolve<IWidget>();
            widget.DoSomething();

            Console.ReadLine();
        }
    }
}

Since the Widget class contains a property called Configuration of type ICoreConfiguration, the dependency injection engine can simply load the correct configuration file object in at run-time, and everything “just works”. Running the application gives exactly the same outputs as the previous non-injected example which means the spirit of the original code has been kept the same:

ConfigurationDependencyInjection04

ConfigurationDependencyInjection05

So, as always, adding dependency injection involves a bit more work, but the benefits (non-brittle, highly testable code to name just two) far outweigh the extra time required.