Singleton Pattern

If you don’t want to use static methods for whatever reason, you can still gain the benefits of static methods by using the singleton pattern. The resultant behavior is that the entire object acts like it’s static.

using System.Diagnostics;

namespace Company.Product
    internal class Singleton
        // We make the constructor private so the class
        // can't be externally instantiated.
        private Singleton(){}

        static private Singleton _localStaticInstance;

        // We add a static method that will return
        // the instance object that's stored internally.
        static public Singleton Instance
                if (_localStaticInstance == null)
                    _localStaticInstance = new Singleton();
                return _localStaticInstance;

        public string GetWorld()
            return "Hello!";

    internal class Test
        public void Exercise()
            // The static instance method actually returns an instantiated object.
            // It's instantiated internally and every call to the static method returns
            // the same instance every time.  Caching is fun!

            // Or if you don't want to type 'Singleton.Instance' every time,
            // you can store a reference locally for less typing.
            var singleton = Singleton.Instance;

Comparing Dates

Prior to SQL Server 2008, there’s no such thing as a date. There is only datetime. Because of the time, you can’t simply use a WHERE clause like this:

WHERE (@MyFirstDate = getdate())

They’ll never match! Unless it’s actually exactly midnight.

It gets even more unpredictable if you’re making relative comparisions:

WHERE (@MyFirstDate > '3/2/2015')

If @MyFirstDate is ‘3/2/2015 3:27 PM’, that WHERE clause will actually return True. If the date parts are the same, you’re actually asking if 3:27 PM > 12:00 AM.

To reduce the uncertainty, you should always use DATEDIFF. If you’re comparing just dates, you can specify to compare based on days where the difference in days is zero.

WHERE (DATEDIFF(d, @MyFirstDate, getdate()) = 0)

Luckily, in 2008 they introduced the date and time data types. Now you can store just the part you need and avoid all this silliness.

Here’s some light reading for other date and time functions: Date and Time Data Types and Functions

Filtered Unique Constraints

If you primarily employ logical deletes and very rarely actually remove a row from a table, a traditional unique constraint often results in a bad user experience. Consider the following scenario:

Let’s say I have a table called [Color] with a unique constraint on the [Name] column.
User1 adds the color “Red”.
User2 decides “Red” is a dumb color and deletes it (which merely sets the [IsActive] flag to False).
User1 then sees “Red” is missing and attempts to add it in again.
The system tells him he can’t add “Red” because it already exists.
User1 says, “!&#^@%”, because “Red” clearly doesn’t exist.

The solution is a filtered unique index.

       [Name] ASC
WHERE ([IsActive] = 1)

The key here is the WHERE clause. Now when User1 attempts to add “Red”, the system will only consider the active records when checking for uniqueness. The result is that I can have multiple inactive records for a given value, but only one active record.

To handle this in the calling code, enclose your insert statement in a Try…Catch (SqlException ex). When the unique filtered index is violated, SqlException.Number will be 2601. Check for that number in your Catch and craft a friendly message accordingly.