Database-independent Data Access Layer

We all know that well-written applications separate the various areas of responsibility into multiple layers, right? Generally speaking, the less each of those layers depend on specific functionality of the others the better. One of those areas of responsibility that warrants separation is data access. There are numerous tools about to generate data access code for you these days but, even if you're more of a roll-your-own kinda person, there's still help at hand. ADO.NET provides the tools to write data access code that is independent of the underlying data source and it has done since version 2.0.

The lynchpin of this functionality is the DbProviderFactory class. It provides methods to generate instances of all the usual ADO.NET objects, e.g. connections, commands and data adapters, for any specific ADO.NET provider.

Let’s start with a whirlwind tour of the System.Data and System.Data.Common namespaces. Amongst other things, the System.Data namespace contains the interfaces that declare the set of functionality required by ADO.NET data access objects, e.g. IDataReader for all data readers and IDbConnection for all connections. The System.Data.Common namespace contains classes that provide a basic concrete implementation of these interfaces, e.g. DbDataReader implements IDataReader and DbConnection implements IDbConnection. Each data source-specific ADO.NET provider will then declare a set of classes that inherit those from System.Data.Common to provide a data source-specific set of functionality, e.g. SqlClient.SqlDataReader inherits DbDataReader and OleDb.OleDbConnection inherits DbConnection.

The System.Data.Common namespace also includes the DbProviderFactory class, which provides an abstract implementation of an ADO.NET object factory. Each data source-specific ADO.NET provider should inherit this class and provide its own data source-specific implementation, e.g. SqlClientFactory and OleDbFactory. Third party ADO.NET providers can and should do the same thing, e.g. MySql.Data.MySqlClient.MySqlClientFactory.

Now, ADO.NET factory classes are singletons. Each concrete factory implementation will have a static/Shared Instance field that you can use to get the one and only instance of that type. Doing so directly defeats the purpose somewhat though. You will normally get a DbProviderFactory instance using the DbProviderFactories helper class, which is also a member of the System.Data.Common namespace. The DbProviderFactories class includes two static/Shared methods: GetFactoryClasses will return a DataTable containing information about all ADO.NET factories available and GetFactory will return an instance of a specific ADO.NET factory class.

To see how this works, try creating a new Windows Forms application, adding a DataGridView to the default form and then binding that grid to the result of the DbProviderFactories.GetFactoryClasses method:

C#

this.dataGridView1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.DataGridView1.DataSource = DbProviderFactories.GetFactoryClasses()

If you run that application you will see a row each for factories named “Odbc Data Provider”, “OleDb Data Provider”, “OracleClient Data Provider” and “SqlClient Data Provider”. If you’ve installed Microsoft SQL Server CE then you’ll also see a row for a factory named “Microsoft SQL Server Compact Data Provider”. If you’ve installed some third party data provider then you’ll see a row for that too. For instance, if you’ve installed MySQL’s Connector/Net then you’ll see a row for a factory named “MySQL Data Provider”. If you’ve installed Oracle’s ODAC then you’ll see a row for a factory named “Oracle Data Provider for .NET”.

To get an appropriate instance of the DbProviderFactory class for your data source you would normally call the DbProviderFactories.GetFactory method. You can pass that method either a DataRow from the DataTable returned by the GetFactoryClasses method or else one of the values from the InvariantName column of that table. For instance, you could pass the string “System.Data.SqlClient” to the GetFactory method and it would return the value of the SqlClientFactory.Instance field.

This provides us with two simple and logical ways to create an ADO.NET factory. Firstly, we can call GetFactories and display the results to the user, e.g. in a ComboBox, for them to choose a provider:

C#

this.comboBox1.DisplayMember = "Name";

this.comboBox1.DataSource = DbProviderFactories.GetFactoryClasses();

VB

Me.ComboBox1.DisplayMember = "Name"

Me.ComboBox1.DataSource = DbProviderFactories.GetFactoryClasses()

We can then call GetFactory and pass the row they selected as an argument:

C#

DataRow providerRow = ((DataRowView)this.comboBox1.SelectedItem).Row;

DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow);

VB

Dim providerRow As DataRow = DirectCast(Me.ComboBox1.SelectedItem, DataRowView).Row

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(providerRow)

Alternatively, we can store the invariant name of our desired provider somewhere, e.g. in the config file, and pass that to GetFactory as an argument:

C#

string invariantName = Properties.Settings.Default.ProviderFactoryInvariantName;

DbProviderFactory factory = DbProviderFactories.GetFactory(invariantName);

VB

Dim invariantName As String = My.Settings.ProviderFactoryInvariantName

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(invariantName)

So, once we have our factory object, what do we do with it? Well, we use it to create all our data access objects, including connections, commands, parameters, data adapters and more. For instance, the following code might form part of your repository layer:

C#

private readonly DbProviderFactory _factory;

public string ConnectionString { get; set; }

public Repository(string factoryName)

{

    this._factory = DbProviderFactories.GetFactory(factoryName);

}

public Repository(DataRow factoryRow)

{

    this._factory = DbProviderFactories.GetFactory(factoryRow);

}

public DbConnection GetConnection()

{

    return this.GetConnection(this.ConnectionString);

}

public DbConnection GetConnection(string connectionString)

{

    DbConnection connection = this._factory.CreateConnection();

    connection.ConnectionString = connectionString;

    return connection;

}

public DbParameter GetParameter()

{

    return this._factory.CreateParameter();

}

public DbParameter GetParameter(string parameterName,

                                object value)

{

    DbParameter parameter = this.GetParameter();

    parameter.ParameterName = parameterName;

    parameter.Value = value;

    return parameter;

}

public DbCommand GetCommand()

{

    return this._factory.CreateCommand();

}

public DbCommand GetCommand(string commandText)

{

    DbCommand command = this.GetCommand();

    command.CommandText = commandText;

    return command;

}

public DbCommand GetCommand(string commandText,

                            IDictionary parameters)

{

    DbCommand command = this.GetCommand(commandText);

    foreach (string parameterName in parameters.Keys)

    {

        command.Parameters.Add(this.GetParameter(parameterName,

                                                 parameters[parameterName]));

    }

    return command;

}

public DataTable GetDataTable(string procName,

                              IDictionary parameters)

{

    DbCommand command = this.GetCommand(procName,

                                        parameters);

    DbConnection connection = this.GetConnection();

    command.Connection = connection;

    command.CommandType = CommandType.StoredProcedure;

    connection.Open();

    DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo |

                                                CommandBehavior.CloseConnection);

    DataTable table = new DataTable();

    table.Load(reader);

    reader.Close();

    return table;

}

VB

Private ReadOnly _factory As DbProviderFactory

Private _connectionString As String

Public Property ConnectionString() As String

    Get

        Return Me._connectionString

    End Get

    Set(ByVal value As String)

        Me._connectionString = value

    End Set

End Property

Public Sub New(ByVal factoryName As String)

    Me._factory = DbProviderFactories.GetFactory(factoryName)

End Sub

Public Sub New(ByVal factoryRow As DataRow)

    Me._factory = DbProviderFactories.GetFactory(factoryRow)

End Sub

Public Function GetConnection() As DbConnection

    Return Me.GetConnection(Me.ConnectionString)

End Function

Public Function GetConnection(ByVal connectionString As String) As DbConnection

    Dim connection As DbConnection = Me._factory.CreateConnection()

    connection.ConnectionString = connectionString

    Return connection

End Function

Public Function GetParameter() As DbParameter

    Return Me._factory.CreateParameter()

End Function

Public Function GetParameter(ByVal parameterName As String, _

                             ByVal value As Object) As DbParameter

    Dim parameter As DbParameter = Me.GetParameter()

    parameter.ParameterName = parameterName

    parameter.Value = value

    Return parameter

End Function

Public Function GetCommand() As DbCommand

    Return Me._factory.CreateCommand()

End Function

Public Function GetCommand(ByVal commandText As String) As DbCommand

    Dim command As DbCommand = Me.GetCommand()

    command.CommandText = commandText

    Return command

End Function

Public Function GetCommand( _

ByVal commandText As String, _

ByVal parameters As IDictionary(Of String, Object)) As DbCommand

    Dim command As DbCommand = Me.GetCommand(commandText)

    For Each parameterName As String In parameters.Keys

        command.Parameters.Add(Me.GetParameter(parameterName, _

                                               parameters(parameterName)))

    Next parameterName

    Return command

End Function

Public Function GetDataTable( _

ByVal procedureName As String, _

ByVal parameters As IDictionary(Of String, Object)) As DataTable

    Dim command As DbCommand = Me.GetCommand(procedureName, parameters)

    Dim connection As DbConnection = Me.GetConnection()

    command.Connection = connection

    command.CommandType = CommandType.StoredProcedure

    connection.Open()

    Dim reader As DbDataReader = command.ExecuteReader(CommandBehavior.KeyInfo Or _

                                                       CommandBehavior.CloseConnection)

    Dim table As New DataTable()

    table.Load(reader)

    reader.Close()

    Return table

End Function

The GetDateTable method takes the name of a stored procedure and a dictionary of parameter values keyed on name as arguments. It then invokes other methods that use the factory to create a connection and a command with parameters. The DbProviderFactory class can also generate connection string builders, data adapters and command builders, so you can implement many other scenarios in a similar fashion. In this way you can build up a complete data access layer with nary a reference to any specific data source.

Sorting Arrays and Collections (Part 3)

Part 1 here
Part 2 here

In part 1 of this series we looked at using the IComparable interface to provide sorting by allowing objects to compare themselves to each other. In part 2 we looked at using the IComparer interface to provide custom sorting through the external comparison of objects that may or may not be inherently comparable. In this third and final part we look at using the Comparison(T) delegate to provide similar functionality to the IComparer interface but without the need to define a whole class. The Comparison delegate allows us to use a method declared anywhere to perform the comparisons. Most often that would be in the same code file as we’re performing a one-off sort.

The signature of the Comparison delegate should put you in mind of the methods we’ve been using for comparisons already. It takes two arguments of type T and returns an Int32 value that indicates their relative order. That’s exactly the same as the IComparer.Compare method. Not surprisingly, the implementation of the method referred to by this delegate should be exactly the same as the implementation of an equivalent IComparer.Compare method.

Going back to our example from part 2, where we were able to sort a list of Person objects by both LastName and FirstName, we can basically extract the implementations of the CompareByFirstName and CompareByLastName methods. Instead of declaring them in a separate class that implements the IComparer interface we can declare them right there in the same class that contains the sorting code:

C#

private int ComparePeopleByFirstName(Person x, Person y)

{

    // Compare by FirstName by default.

    int result = x.FirstName.CompareTo(y.FirstName);

    // If FirstNames are the same...

    if (result == 0)

    {

        // ...compare by LastName.

        result = x.LastName.CompareTo(y.LastName);

    }

    return result;

}

private int ComparePeopleByLastName(Person x, Person y)

{

    // Compare by LastName by default.

    int result = x.LastName.CompareTo(y.LastName);

    // If LastNames are the same...

    if (result == 0)

    {

        // ...compare by FirstName.

        result = x.FirstName.CompareTo(y.FirstName);

    }

    return result;

}

VB

Private Function ComparePeopleByFirstName(ByVal x As Person, _

                                          ByVal y As Person) As Integer

    'Compare by FirstName by default.

    Dim result As Integer = x.FirstName.CompareTo(y.FirstName)

    'If FirstNames are the same...

    If result = 0 Then

        '...compare by LastName.

        result = x.LastName.CompareTo(y.LastName)

    End If

    Return result

End Function

Private Function ComparePeopleByLastName(ByVal x As Person, _

                                         ByVal y As Person) As Integer

    'Compare by LastName by default.

    Dim result As Integer = x.LastName.CompareTo(y.LastName)

    'If LastNames are the same...

    If result = 0 Then

        '...compare by FirstName.

        result = x.FirstName.CompareTo(y.FirstName)

    End If

    Return result

End Function

We can then create an instance of the Comparison delegate that references one or other of those methods to sort by the appropriate properties:

C#

List<Person> people = new List<Person>();

people.Add(new Person("Mary", "Smith"));

people.Add(new Person("John", "Williams"));

people.Add(new Person("John", "Smith"));

people.Add(new Person("Andrew", "Baxter"));

Console.WriteLine("Before sorting:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0}, {1}",

                                    person.LastName,

                                    person.FirstName));

}

people.Sort(new Comparison<Person>(ComparePeopleByLastName));

Console.WriteLine("After sorting by LastName:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0}, {1}",

                                    person.LastName,

                                    person.FirstName));

}

people.Sort(new Comparison<Person>(ComparePeopleByFirstName));

Console.WriteLine("After sorting by FirstName:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0} {1}",

                                    person.FirstName,

                                    person.LastName));

}

VB

Dim people As New List(Of Person)

people.Add(New Person("Mary", "Smith"))

people.Add(New Person("John", "Williams"))

people.Add(New Person("John", "Smith"))

people.Add(New Person("Andrew", "Baxter"))

Console.WriteLine("Before sorting:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0}, {1}", _

                                    person.LastName, _

                                    person.FirstName))

Next

people.Sort(New Comparison(Of Person)(AddressOf ComparePeopleByLastName))

Console.WriteLine("After sorting by LastName:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0}, {1}", _

                                    person.LastName, _

                                    person.FirstName))

Next

people.Sort(New Comparison(Of Person)(AddressOf ComparePeopleByFirstName))

Console.WriteLine("After sorting by FirstName:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0} {1}", _

                                    person.FirstName, _

                                    person.LastName))

Next

Removing the need to declare a whole new class makes the Comparison delegate more convenient than the IComparer interface in many cases, but it can be more convenient still if we employ anonymous methods or lambda expressions.

C# 2.0 introduced the notion of anonymous methods. Anonymous methods can be used to initialise a delegate just as a named method can. If the code to perform our comparisons is relatively simple then using an anonymous method to create our Comparison delegate is simpler than writing a separate named method. Going back to our example from part 2, where we sorted strings by length instead of alphabetically, we could rewrite that code as follows:

C#

List<string> strings = new List<string>();

strings.Add("The longest of all");

strings.Add("Short");

strings.Add("Even longer");

strings.Add("Longer");

Console.WriteLine("Before sorting:");

foreach (string str in strings)

{

    Console.WriteLine(str);

}

strings.Sort(delegate(string x,

                      string y)

{

    return x.Length.CompareTo(y.Length);

});

Console.WriteLine("After sorting:");

foreach (string str in strings)

{

    Console.WriteLine(str);

}

Our anonymous method has the appropriate signature to initialise a Comparison delegate, i.e. it has two parameters of type string and a return type of int, so the C# compiler accepts it and invokes the appropriate overload of the Sort method.

VB 8 has no equivalent to C# anonymous methods but both VB 9 and C# 3.0 introduced lambda expressions as a supporting feature for LINQ. A lambda expression can be used to initialise a delegate in essentially the same way as an anonymous method:

C#

var strings = new List<string> {"The longest of all",

                                "Short",

                                "Even longer",

                                "Longer"};

Console.WriteLine("Before sorting:");

foreach (var str in strings)

{

    Console.WriteLine(str);

}

strings.Sort((x, y) => x.Length.CompareTo(y.Length));

Console.WriteLine("After sorting:");

foreach (var str in strings)

{

    Console.WriteLine(str);

}

VB

Dim strings As New List(Of String)

strings.Add("The longest of all")

strings.Add("Short")

strings.Add("Even longer")

strings.Add("Longer")

Console.WriteLine("Before sorting:")

For Each s In strings

    Console.WriteLine(s)

Next

strings.Sort(Function(x, y) x.Length.CompareTo(y.Length))

Console.WriteLine("After sorting:")

For Each s In strings

    Console.WriteLine(s)

Next

In each case the compiler infers the types of the lambda parameters based on the generic type of the list being sorted.

Just note that anonymous methods and lambdas make your code simpler if they themselves are simple but your code can quickly become difficult to read if you try to perform complex comparisons using an inline method.

So, let’s sum up the different ways we’ve discussed for sorting arrays and collections in the three parts of this series. First, we looked at performing automatic sorts on lists of objects that could compare themselves, thanks to their implementing the IComparable interface. Next, we looked at using the IComparer interface to create a class that could perform complex comparisons on objects that might not be inherently comparable. That class could also be reused in multiple code files or even multiple projects. Finally, we looked at using the generic Comparison delegate to invoke standard methods, anonymous methods and lambda expressions for simple custom sorts.

Happy sorting!

Sorting Arrays and Collections (Part 2)

Part 1 here

In the previous instalment we discussed how to use the IComparable interface to facilitate automatic sorting of arrays and collections. This time we will look at the IComparer interface and how it can be used to sort objects that may or may not be inherently comparable.

Like IComparable,the IComparer interface comes in both standard and generic flavours. Normally you would implement the generic version, although there may be instances where you need to implement the standard version. Sorting the items in a ListView is one such instance.

Again like IComparable, the IComparer interface declares only a single method. Where the IComparable.CompareTo method takes a single object and compares it to the current instance, the IComparer.Compare method takes two objects and compares them to each other. As an example, let's consider the case where you have a collection of strings that you want to sort by length instead of alphabetically. In this case we cannot rely on the IComparable implementation of the String class itself so we must define our own comparison, which we can do by implementing the IComparer interface:

C#

public class StringLengthComparer : IComparer<string>

{

    public int Compare(string x, string y)

     {

         return x.Length.CompareTo(y.Length);

     }

}

VB

Public Class StringLengthComparer

    Implements IComparer(Of String)

    Public Function Compare(ByVal x As String, _

                            ByVal y As String) As Integer _

    Implements IComparer(Of String).Compare

        Return x.Length.CompareTo(y.Length)

    End Function

End Class

Our StringLengthComparer.Compare method will take two Strings and return a result that indicates their relative order based on their Lengths. Notice that we are still making use of the IComparable.CompareTo method, which we know is a good convention to follow. In this case we are comparing the two Length properties, which are type Int32. The Int32 structure implements the IComparable interface so we should make use of it.

We can now perform a custom sort of a list of strings like so:

C#

List<string> strings = new List<string>();

strings.Add("The longest of all");

strings.Add("Short");

strings.Add("Even longer");

strings.Add("Longer");

Console.WriteLine("Before sorting:");

foreach (string str in strings)

{

     Console.WriteLine(str);

}

strings.Sort(new StringLengthComparer());

Console.WriteLine("After sorting:");

foreach (string str in strings)

{

     Console.WriteLine(str);

}

VB

Dim strings As New List(Of String)

strings.Add("The longest of all")

strings.Add("Short")

strings.Add("Even longer")

strings.Add("Longer")

Console.WriteLine("Before sorting:")

For Each str As String In strings

    Console.WriteLine(str)

Next

strings.Sort(New StringLengthComparer)

Console.WriteLine("After sorting:")

For Each str As String In strings

    Console.WriteLine(str)

Next

In this case we pass an instance of our StringLengthComparer class as an argument when we call Sort and all comparisons will be done using the Compare method of our class instead of the CompareTo methods of the objects in the list. Sorting arrays is much the same except, again, the Array.Sort method is static/Shared:

C#

string[] strings = {"The longest of all",

                    "Short",

                    "Even longer",

                    "Longer"};

Console.WriteLine("Before sorting:");

foreach (string str in strings)

{

    Console.WriteLine(str);

}

Array.Sort(strings, new StringLengthComparer());

Console.WriteLine("After sorting:");

foreach (string str in strings)

{

    Console.WriteLine(str);

}

VB

Dim strings As String() = {"The longest of all", _

                           "Short", _

                           "Even longer", _

                           "Longer"}

Console.WriteLine("Before sorting:")

For Each str As String In strings

    Console.WriteLine(str)

Next

Array.Sort(strings, New StringLengthComparer)

Console.WriteLine("After sorting:")

For Each str As String In strings

    Console.WriteLine(str)

Next

Now, our StringLengthComparer class is relatively simple. It only knows how to compare strings in one way. What if we want to be able compare objects in various ways depending on the circumstances? We can certainly do that with a class that implements IComparer. There’s no limit to the complexity of the class, as long as it provides the functionality defined by the interface. For example, let’s consider our Person class from the previous instalment:

C#

public class Person : IComparable, IComparable<Person>

{

     private string _lastName;

     private string _firstName;

     public string LastName

     {

        get { return this._lastName; }

         set { this._lastName = value; }

     }

     public string FirstName

     {

         get { return this._firstName; }

         set { this._firstName = value; }

     }

     public Person(string firstName, string lastName)

     {

         this._firstName = firstName;

         this._lastName = lastName;

     }

     public int CompareTo(object obj)

     {

         return this.CompareTo((Person) obj);

     }

     public int CompareTo(Person other)

     {

        // Compare by LastName by default.

        int result = this.LastName.CompareTo(other.LastName);

         // If LastNames are the same...

         if (result == 0)

        {

            // ...compare by FirstName.

            result = this.FirstName.CompareTo(this.FirstName);

        }

        return result;

     }

}

VB

Public Class Person

    Implements IComparable, IComparable(Of Person)

    Private _lastName As String

    Private _firstName As String

    Public Property FirstName() As String

        Get

            Return Me._firstName

        End Get

        Set(ByVal value As String)

            Me._firstName = value

        End Set

    End Property

    Public Property LastName() As String

        Get

            Return Me._lastName

        End Get

        Set(ByVal value As String)

            Me._lastName = value

        End Set

    End Property

    Public Sub New(ByVal firstName As String, _

                   ByVal lastName As String)

        Me._firstName = firstName

        Me._lastName = lastName

    End Sub

    Public Function CompareTo(ByVal obj As Object) As Integer _

    Implements System.IComparable.CompareTo

        Return Me.CompareTo(DirectCast(obj, Person))

    End Function

    Public Function CompareTo(ByVal other As Person) As Integer _

    Implements System.IComparable(Of Person).CompareTo

        'Compare by LastName by default.

        Dim result As Integer = Me.LastName.CompareTo(other.LastName)

        'If LastNames are the same...

        If result = 0 Then

            '...compare by FirstName.

            result = Me.FirstName.CompareTo(other.FirstName)

        End If

        Return result

    End Function

End Class

What if we want to be able to sort a list of Person objects by either FirstName or LastName? The Person class already implements the IComparable interface, which implementation compares by LastName then FirstName. As such, we could just provide an implementation of IComparer that compares by FirstName then LastName. That way we could either accept the default comparison or use our IComparer implementation, depending on the circumstances. For consistency though, a better idea might be to provide an IComparer implementation that can compare in either way and then use that all the time:

C#

public class PersonComparer : IComparer<Person>

{

    public enum ComparisonProperty

    {

        FirstName,

        LastName

    }

    private readonly ComparisonProperty _comparisonProperty;

    public PersonComparer(ComparisonProperty comparisonProperty)

    {

        this._comparisonProperty = comparisonProperty;

    }

    public int Compare(Person x, Person y)

    {

        int result = 0;

        switch (this._comparisonProperty)

        {

            case ComparisonProperty.FirstName:

                result = this.CompareByFirstName(x, y);

                break;

            case ComparisonProperty.LastName:

                result = this.CompareByLastName(x, y);

                break;

        }

        return result;

    }

    private int CompareByFirstName(Person x, Person y)

    {

        // Compare by FirstName by default.

        int result = x.FirstName.CompareTo(y.FirstName);

        // If FirstNames are the same...

        if (result == 0)

        {

            // ...compare by LastName.

            result = x.LastName.CompareTo(y.LastName);

        }

        return result;

    }

    private int CompareByLastName(Person x, Person y)

    {

        // Compare by LastName by default.

        int result = x.LastName.CompareTo(y.LastName);

        // If LastNames are the same...

        if (result == 0)

        {

            // ...compare by FirstName.

            result = x.FirstName.CompareTo(y.FirstName);

        }

        return result;

    }

}

VB

Public Class PersonComparer

    Implements IComparer(Of Person)

    Public Enum ComparisonProperty

        FirstName

        LastName

    End Enum

    Private ReadOnly _comparisonProperty As ComparisonProperty

    Public Sub New(ByVal comparisonProperty As ComparisonProperty)

        Me._comparisonProperty = comparisonProperty

    End Sub

    Public Function Compare(ByVal x As Person, _

                            ByVal y As Person) As Integer _

    Implements IComparer(Of Person).Compare

        Dim result As Integer

        Select Case Me._comparisonProperty

            Case ComparisonProperty.FirstName

                result = Me.CompareByFirstName(x, y)

            Case ComparisonProperty.LastName

                result = Me.CompareByLastName(x, y)

        End Select

        Return result

    End Function

    Private Function CompareByFirstName(ByVal x As Person, _

                                        ByVal y As Person) As Integer

        'Compare by FirstName by default.

        Dim result As Integer = x.FirstName.CompareTo(y.FirstName)

        'If FirstNames are the same...

        If result = 0 Then

            '...compare by LastName.

            result = x.LastName.CompareTo(y.LastName)

        End If

        Return result

    End Function

    Private Function CompareByLastName(ByVal x As Person, _

                                       ByVal y As Person) As Integer

        'Compare by LastName by default.

        Dim result As Integer = x.LastName.CompareTo(y.LastName)

        'If LastNames are the same...

        If result = 0 Then

            '...compare by FirstName.

            result = x.FirstName.CompareTo(y.FirstName)

        End If

        Return result

    End Function

End Class

Note that the CompareByLastName method compares in exactly the same way as the Person.CompareTo method. As such, we could have made use of that existing functionality in our PersonComparer class:

C#

private int CompareByLastName(Person x, Person y)

{

    return x.CompareTo(y);

}

VB

Private Function CompareByLastName(ByVal x As Person, _

                                   ByVal y As Person) As Integer

    Return x.CompareTo(y)

End Function

This does save us duplicating some code but it also makes the implementation of the PersonComparer class more reliant on the implementation of the Person class. By implementing our CompareByLastName method exactly as we want it, we allow the IComparable implementation of the Person class to change without changing the behaviour of our PersonComparer class.

We can now put our PersonComparer class to work in sorting a list of Person objects by either LastName or by FirstName:

C#

List<Person> people = new List<Person>();

people.Add(new Person("Mary", "Smith"));

people.Add(new Person("John", "Williams"));

people.Add(new Person("John", "Smith"));

people.Add(new Person("Andrew", "Baxter"));

Console.WriteLine("Before sorting:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0}, {1}",

                                    person.LastName,

                                    person.FirstName));

}

people.Sort(new PersonComparer(PersonComparer.ComparisonProperty.LastName));

Console.WriteLine("After sorting by LastName:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0}, {1}",

                                    person.LastName,

                                    person.FirstName));

}

people.Sort(new PersonComparer(PersonComparer.ComparisonProperty.FirstName));

Console.WriteLine("After sorting by FirstName:");

foreach (Person person in people)

{

    Console.WriteLine(string.Format("{0} {1}",

                                    person.FirstName,

                                    person.LastName));

}

VB

Dim people As New List(Of Person)

people.Add(New Person("Mary", "Smith"))

people.Add(New Person("John", "Williams"))

people.Add(New Person("John", "Smith"))

people.Add(New Person("Andrew", "Baxter"))

Console.WriteLine("Before sorting:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0}, {1}", _

                                    person.LastName, _

                                    person.FirstName))

Next

people.Sort(New PersonComparer(PersonComparer.ComparisonProperty.LastName))

Console.WriteLine("After sorting by LastName:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0}, {1}", _

                                    person.LastName, _

                                    person.FirstName))

Next

people.Sort(New PersonComparer(PersonComparer.ComparisonProperty.FirstName))

Console.WriteLine("After sorting by FirstName:")

For Each person As Person In people

    Console.WriteLine(String.Format("{0} {1}", _

                                    person.FirstName, _

                                    person.LastName))

Next

As I said earlier, you can make your class as complex as you like, comparing objects in numerous different ways involving as many properties as you want.

Now, implementing the IComparer interface in a new class is a good option if you want to be able to compare instances of a type in multiple different ways and/or in multiple different places. If you only need to sort in one place, or at least only in one code file, then there is a slightly easier way. We’ll look at that in the next instalment.

Part 3 here