<update>A revised and extended version of this post also handles creating a customer domain object, which does have some behavior. It does also covers some methods to make the behavior of the datareader a little cleaner. The post can be found here</update>

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 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 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.

Getting started: what do I expect my code to do ?

In the example I want to read 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 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.

Getting it done: read 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 all 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.

After opening the connection data can be read. An open database connection is a very expensive resource. Open a connection as late as possible and always make sure it is closed as soon as possible and always closed when hitting an exception. The try finally block will ensure 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.

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 has an indexer to get straight to the raw data, use this to check for a DBNull.

if (sdr.Read())

{

    number = sdr.GetInt32(0);

    name = sdr.GetString(1);

    if (sdr[2] != DBNull.Value)

        phone = sdr.GetString(2);

}

Note that DBNull.Value is not the same as null. The test will no longer throw an exception, but still fails.

The indexer of the datareader can also take the column name as an indexer. A small refactoring of the reading part.

if (sdr.Read())

{

    number = sdr.GetInt32(0);

    name = sdr["Name"] == DBNull.Value ? "" : sdr.GetString(1);

    phone = sdr["Phone"] == DBNull.Value ? "" : sdr.GetString(2);

}

This code is a little smelly. In one statement I am using a column name as indexer and a column number as parameter to the method. Alas, the GetX methods do not have an overload (yet ?) which accepts a column name. But I really do like the typed GetX methods. Especially when it comes to data types which have a cryptic internal representation like a Date column.

Sometimes the GetX methods are stricter than desired. In the code I have assumed the customer number to be an int32. When the underlying DB column is an int64 or int16 the code will hit an exception again. You can do a little fiddling when using the raw data. Like this

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

Now the raw data is first converted to a string which is parsed into an int of the desired type. This is not going to hit an exception until an int returned from the DB is larger than MaxInt. This does work for MS sql server, I have no guarantees for any other database.

After this refactoring and filling the database with the expected data the test passes. I do hope I demonstrated how using some trivial tests has resulted in a straightforward development of some good code.

But what about DataAdapters and DataSets ?

In a lot of older projects I used to rely on DataAdapters and DataSets. In the 1.x days of the framework there used to be big discussion whether to use DataAdapters or DataReaders. In essence there is not really a big difference, internally a DataAdapter uses a DataReader. 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. What I did find out over the years was the problem of maintainability. Especially when the data underlying the adapter was 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 have demonstrated here. I am so much more in control.

I have bypassed Object Relation mappers here. Writing out all that sql is a repeating job. The next step is to fully automate it and push it away in a good OR tool. Doing my DB work by hand gives me a good feeling what the OR's job is. Far better than a DataAdapter. The next step will be be moving to a mapper.

Perhaps the main reason for using dataAdapters was that they were the way to fill and read (typed) datasets. In .NET 1.x it is very hard to work with anything else than datasets as that was the only thing the other parts of the framework understood. But since version 2 of the framework you can use other, quite simple, strongly typed objects for things like databinding. In case you are not going to kick me to the kindergarten I will delve into that in a later post.

Keep your feet on the ground.

<update>So you made it to the end. The story continues rigth here</update>