This post is an extended and revised version of "Back to basics, just reading some data from a DB". Feedback on that provided more than enough inspiration for this one.

Are you already drowning in the enormous amount of TLA's, tools, utilities, methodologies and whatever else ? Let's take a step back. Do you still know how to read and work with some basic data from a sql database ? Without using any (external) OR mapper or visual component ? Just by hand in a couple of lines of code. In this post I want to go back to the bare basics: some C# and a couple of core classes from the .NET framework. I will do it using Test Driven techniques end will up with a very simple domain object. On this journey we will meet several core buzzwords in "modern" software development. I am not going to present anything new or revolutionary, most of the code can be found scattered over MSDN. The only thing I want to reach is getting my feet back on the ground to get the overview again.

What about DataAdapters and DataSets ?

In a lot of older projects I used to rely on DataAdapters and DataSets when working with data. Perhaps the main reason was that that it was very hard to work with anything else than datasets in .NET 1. It was the only thing the other parts of the framework understood. In those days the data discussion used to focus whether to use DataAdapters or DataReaders. But in essence there is not really a big difference, internally a DataAdapter uses a DataReader and nobody ever found a clear performance difference between the two. Which is no wonder, over 99% of the performance is determined by the efficiency of you SQL statement. But what I did find out over the years was the problem of maintainability. Especially when the data underlying the dataadapter got split over several tables it is a hell of a job to update all the sql and keep the datadapter working. Right now I am replacing more and more datadapter code with the kind of stuff I am demonstrating here.

Getting started

I want to work with some customer data. The first step is to describe this data in an interface:

public interface ICustomer

{

    int Number { get; }

    string Name { get; }

    string Phone { get; }

}

A customer has a number, name and phone number, you can only read the data. Based on this interface I create a mock class.

public class MockedCustomer: ICustomer

{

    public MockedCustomer(int customerNumber)

    {

        number = customerNumber;

        name = "Alfred";

        phone = "+31505035737";

    }

    #region ICustomer Members

    private int number;

    public int Number

    {

        get { return number; }

    }

    private string name;

    public string Name

    {

        get

        {

            return name;

        }

    }

    private string phone;

    public string Phone

    {

        get

        {

            return phone;

        }

    }

    #endregion

}

This mocked customer looks, by definition, exactly what I expect a customer to look like. To confirm that the code really does what I expect I write a trivial test.

[Test]

public void CanCreateMockedCustomer()

{

ICustomer testCustomer = new MockedCustomer(12);

Assert.AreEqual(12, testCustomer.Number);

Assert.AreEqual("Alfred", testCustomer.Name);

Assert.AreEqual("+31505035737", testCustomer.Phone);

}

So far I have created absolutely trivial things. But what I did do is lay out what the data is going to look like and I provided a separation between the data in the database and the code which is going to work with it. For now that other code can work (and be tested against) the mock object. While we will start writing the code to read a customer from the database.

Reading data from the DB

A new class is going to read a customer from the DB. All it has to do is implement the ICustomer interface. The class is modeled on the mock customer. But instead of filling in the properties with hard coded values it will read them using some SQL.

public class DBcustomer : ICustomer

{

    public DBcustomer(int customerNumber)

    {

        // DB stuff here

    }

    #region ICustomer Members

    private int number;

    public int Number

    {

        get { return number; }

    }

    private string name;

    public string Name

    {

        get { return name; }

    }

    private string phone;

    public string Phone

    {

    get { return phone; }

    }

    #endregion

}

The database stuff is done in the constructor. To work with a database in .NET you need three things: a connection to the database, a placeholder for the sql and a way to read the resulting data into your code. The .NET framework classes to do this are SqlConnection, SqlCommand and SqlDataReader. This could be a first shot at the code.

public DBcustomer(int customerNumber)

{

    SqlConnection connection = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand();

    string sql = "SELECT Number, Name, Phone FROM Customers";

    sql += " WHERE number=@number";

    cmd.Parameters.AddWithValue("@number", customerNumber);

    cmd.CommandText = sql;

    cmd.Connection = connection;

    try

    {

        connection.Open();

        SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (sdr.Read())

        {

            number = sdr.GetInt32(0);

            name = sdr.GetString(1);

            phone = sdr.GetString(2);

        }

    }

    finally

    {

        connection.Close();

    }

}

All the SqlConnection needs is a connection string. I have implemented it as the connectionString property. The property will return something like Data Source=.;Initial Catalog=Back2Basics;Integrated Security=True".Where it gets that string from is another topic. It could be anything, for instance an entry in your web.config.

The SqlCommand needs a string containing the sql statement, a collection of parameters and a connection. Note how you can set these properties in any order you like. As long as the command's connection is closed that's not a problem at all.

An open database connection is a very expensive and also an unmanaged resource, which means it is not under control of the .NET runtime. The SqlConnection object wraps up the connection, the SqlCommand has a SqlConnection property and the SqlDatareader works on a SqlCommand object with an open connection. So all these three classes work with an unmanaged (database connection) resource and thus all three implement the IDisposable interface. Writing correct code would lead to call their Dispose method when finished or work with the using statement. Instead of writing political correct code here I am focusing on the connection resource itself. Open a connection as late as possible and always make sure it is closed as soon as possible and make sure it is always closed when hitting an exception. The try finally block will do that. Don't be afraid to lose performance when opening and closing connections often. The ado.net connection pooling will do the hard work, this is something which really works well. I doubted once, only to end up fully convinced.

After opening the connection data can be read. Executing the ExecuteReader method on the SqlCommand returns a SqlDataReader object to get the data. Note the (optional) CommandBehavior parameter to the method. This enumeration has a lot of useful members. As I expect only one result row I pass that to the reader. Check the other members for your needs.

A datareader reads a row at the time using its Read method. The method returns false when there is no (more) data present. The datareader has several methods, like GetInt32 and GetString, to read typed data from the current row straight into the customer properties. They take the index of the column, as determined in the sql statement, as a parameter.

Now, does this code work ? Again I write a test.

[Test]

public void CanReadCustomer()

{

    ICustomer testCustomer = new DBcustomer(1);

    Assert.AreEqual(1, testCustomer.Number);

    Assert.AreEqual("Peter", testCustomer.Name);

    Assert.AreEqual("0505035737", testCustomer.Phone);

}

I do have a row in the database for customer 1. Whether the data is complete is for the test to find out. A good test runner, like the one included in ReSharper, is that it also displays exception messages. And this test will fail, due to an exception.

It is only a pity the test runner does not wrap the output. The full message is to wide for a screenshot, but is very clear: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. The strongly typed GetString method looks quite nice but throws an exception when the underlying DB-columns does not contain any data.

A datareader does have the IsDBNull method to check for a DBnull value. So I can check before I read,

name = sdr.IsDBNull(1) ? null : sdr.GetString(1);

Note that DBNull.Value is not the same as null.

But this code is a little smelly. Twice in the line it assumes the name column having index 1. Which is only so when name was the second column in the select statement of the underlying SQL. Changing the sql will break the code. The get around this the datareader has the GetOrdinal method which will return the index of a column by name.

if (sdr.Read())

{

    int numberIndex = sdr.GetOrdinal("Number");

    int nameIndex = sdr.GetOrdinal("Name");

    int phoneindex = sdr.GetOrdinal("Phone");

    number = sdr.GetInt32(numberIndex);

    name = sdr.IsDBNull(nameIndex) ? null : sdr.GetString(nameIndex);

    phone = sdr.IsDBNull(phoneindex) ? null : sdr.GetString(phoneindex);

}

The test will no longer throw an exception, but still fail.

The problem now has to do with the content of the database. This is a problem on the customer level, not the database handling code. Before diving deeper into customers I want to show just one more thing of the datareader.

A datareader has an indexer to get straight to the raw data. As indexing expression it will accept the index of the column as well as the column name. Working with the raw data itself can be quite handy when you cannot rely on the type of the underlying DB data. There is a GetInt16, a GetInt32 and a GetInt64 method. Now what if you do not know which one to use ? This snippet will work for any type of integer.

long number = long.Parse(sdr["Number"].ToString());

The raw data is first converted to a string which is parsed into a long (int64). This does work for MS sql server, I have no guarantees for any other database.

Enter the domain object

All the code presented so far does it read data from the database to fill a customer data object. I have managed to hide the actual DB itself, whether it was a mock or sql server, the ICustomer object does not care. But a customer is more than just some data, it does have behavior as well. The most simple behavior would be validating it. My test just returned me a customer from the database without a phone number. That might be OK from the DB's point of view but it's not a customer my app can work with. The ICustomerDO interface does describe a customer which better fulfills the needs of my app.

public interface ICustomerDO

{

    int Number { get; }

    string Name { get;}

    string Phone { get; }

    bool Validate();

}

The Validate method of a customer domain object could do anything, for instance

public bool Validate()

{

    return Name != null && Phone != null && myCustomPhoneNumberValidation(Phone);

}

This can go far beyond anything expressible in database validations.

But how are we going to bring the Customer data object and the CustomerDO domain object together without getting them entangled ? There should be a separation of concerns. On one side there is persistence: a way to store (and retrieve) customer data. On the other side is a customer domain object, a customer as my app would like to work with. Perhaps a tempting approach might be to inherit every data object class from a base domain object class. But the moment I start doing that the mess starts as the database code will need the domain object code.

Enter the Repository

We can abstract the persistence of customers away in a repository. An interface describes such a repository.

public interface ICustomerRepository

{

ICustomer CustomerById(int customerNumber);

List<ICustomer> CustomerByName(string customerName);

}

A customer repository object of this kind has a method which will return a customer based on a customer number and a method which returns a list of customers with a given name. For now I will only go into the CustomerById member.

Let's write a test what the repository should do. To start with a mock.

[Test]

public void CanCreateMockedCustomerRepository()

{

    ICustomerRepository testRepository = new MockedCustomerRepository();

    ICustomer testCustomer = testRepository.CustomerById(12);

    Assert.AreEqual("Alfred", testCustomer.Name);

    Assert.AreEqual("+31505035737", testCustomer.Phone);

}

This test is almost the same as the one for the mocked customer. Instead of creating a mocked customer object the method of the repository is going to do that. To make the test pass requires one line of code.

public ICustomer CustomerById(int customerNumber)

{

    return new MockedCustomer(customerNumber);

}

Again this code is quite trivial. But it does give a clear idea how to write the "real" database repository. The test builds on the DBcustomer test

[Test]

public void CanReadCustomerFromRepository()

{

    ICustomerRepository testRepository = new CustomerRepository();

    ICustomer testCustomer = testRepository.CustomerById(1);

    Assert.AreEqual(1, testCustomer.Number);

    Assert.AreEqual("Peter", testCustomer.Name);

    Assert.AreEqual("0505035737", testCustomer.Phone);

}

And the code to make it pass builds on what we learned building the mockedrepository.

public class CustomerRepository : ICustomerRepository

{

    public ICustomer CustomerById(int customerNumber)

    {

        return new DBcustomer(customerNumber);

    }

… 

}

Having these repositories at hand makes it possible to limit the visibility of the data classes we started with (DBcustomer and MockedCustomer) to internal. Only the implemented ICustomer interface is exposed to other assemblies using the repository. The downside doing this that the test running directly against these data objects will no longer build. This is the only thing which sometimes bothers me (and some others) with TDD. To be able to write tests against a piece of code you have to expose it to the outer world with public visibility. You can do two things. Either rewrite the tests to target exposed members or keep your members public for the sake of testing. A third way might be to include the tests in the assembly they are testing but in that case your assembly while be exposing the test classes and test methods. (A class containing tests and the test methods themselves should always have public visibility.)

Putting it together

Now we have an interface describing a customer repository, a real and a mocked implementation for that and a customer domain object with some behavior dependent on data from a repository. The customer domain object needs a repository member; it is passed one in its constructor

public class Customer : ICustomerDO

{

    private ICustomerRepository repository;

    public Customer(ICustomerRepository repository)

    {

        this.repository = repository;

    }

    public void LoadByNumber(int customerNumber)

    {

        ICustomer customerData = repository.CustomerById(customerNumber);

        number = customerData.Number;

        name = customerData.Name;

        phone = customerData.Phone;

    }

    private bool myCustomPhoneNumberValidation(string number)

    {

        // Anything goes

        return true;

    }

    private int number;

    public int Number

    {

        get { return number; }

    }

    private string name;

    public string Name

    {

        get { return name; }

    }

    private string phone;

    public string Phone

    {

        get { return phone; }

    }

    public bool Validate()

    {

        return name != string.Empty && phone != string.Empty && myCustomPhoneNumberValidation(phone);

    }

}

Again a test is the best way to see how the CustomerDO works

[Test]

public void CanCreateMockedCustomerDomainObject()

{

    Customer testCustomer = new Customer(new MockedCustomerRepository());

    testCustomer.LoadByNumber(12);

    Assert.AreEqual("Alfred", testCustomer.Name);

    Assert.AreEqual("+31505035737", testCustomer.Phone);

}

The Customer class is the domain object When instantiating a new customer I pass in an object which does implement the ICustomerRepository interface. Like the MockedCustomRepository class.

But I can also use the "real" CustomerRepository class.

[Test]

public void CanCreateCustomerDomainObject()

{

    Customer testCustomer = new Customer(new CustomerRepository());

    testCustomer.LoadByNumber(1);

    Assert.AreEqual("Peter", testCustomer.Name);

    Assert.AreEqual("0505035737", testCustomer.Phone);

}

Which repository to use is injected into the customer via its constructor. This is what is called Dependency Injection. The customer domain object does not have any knowledge about repository implementations, it only has knowledge of the ICustomerRepository interface. The container which instantiates the customer domain object is in control of the repository which will be used. Another name for Dependancy Injection is Inversion of Control, or IoC for short.

Now the customer object does have behavior as well. Which can be tested. Let's return to the customer which made our first database tests fail because it's database record did not contain a phone number. A test to describe this, as well as the resulting behavior

[Test]

public void CanValidateCustomer()

{

    Customer testCustomer = new Customer(new CustomerRepository());

    testCustomer.LoadByNumber(1);

    Assert.AreEqual("Peter", testCustomer.Name);

    Assert.AreEqual("0505035737", testCustomer.Phone);

    Assert.IsTrue(testCustomer.Validate());

    testCustomer.LoadByNumber(2);

    Assert.AreEqual("Alfred", testCustomer.Name);

    Assert.AreEqual(string.Empty, testCustomer.Phone);

    Assert.IsFalse(testCustomer.Validate());

}

When the code is right and the DB contains the right data all tests will pass

Most of these test do rely on a database and its content. For several people a reason to name them integration tests and not unit test. For this story they are just tests. As I hope to have demonstrated building code (and keep it running !) based on tests is pretty straightforward and you can build on this code to further develop the app. When you want to get rid of the dependency on the database just use the mocked repository.

Winding down

Building this solution has lead to several projects. The good thing is that these project have very little interdependencies.

Only the Contracts project is referenced by all others. This assembly only contains the interface declarations, no executing code. The DomainObjects, the Mocks and the Persistence do not depend on any other project than the Contracts project. So they can be easily swapped out for another project as long as these implement the interfaces defined in the Contracts assembly. A next step might be to replace the Persistence with an OR mapper like nHibernate.

Everything comes together in the tests. Did you notice there is not even an app yet ? No choice yet whether it is going to be a web or winforms app. How you are going to present the customer data is open. Even the simplest approach will work. With .NET 2.0 and later you can (data-)bind to any public member of any object, you no longer need a dataset for that. As the customer domain object is a Plain Old CLR Object there will be no big hurdles.

Well, that should be it. But don't take this as the way to work. To get a feeling for the real stuff I invite you to write an implementation for the CustomerName method which returns a list of customers. That will require some refactorings. But as long as you keep working in a test driven way you can experiment around and stay sure you are not breaking any existing code. But do keep your concerns separated and your feet on the ground.