Back to basics, just reading some data from a DB

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

This entry was posted in Coding, Data, Featured. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • JR

    Excellent post. Answered a question I was attempting to resolve. Thanks!

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    @Greg

    This customer does not have any behavior (yet), I’m just reading some data and am preparing myself to do more with it. That’s a bigger story

  • http://buddylindsey.com/blog/ Buddy LIndsey

    Thanks this was a great tutorial. I am still trying to learn Interfaces and this has helped me get a little bit more of an understanding. I know it wasn’t written for that, but it helped anyway.

  • Greg

    I’m curious how you then test your *behavior* in your customer object … Do you duplicate it all into your real and your fake customer objects? Testing that persistence works is great but I generally consider that to be integration testing as opposed to unit testing.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Okay, I admit that the story is incomplete… And to big for one post, step by step. I´ll write another one, based on this and do hope it will not be to disappointing…….

  • Dan

    @Peter

    “What you are saying is that there can be no dataaccess without domain object and IoC”

    What is Customer if it is not some kind of domain object? Some people create interfaces for their domain objects but there is no place for this in a simple example.

    I did not suggest using IoC. Your example used an interface, a mock implementation and a “real” implementation. I was just suggesting a better way of how you could use these three things in an example of data access code.

    My main point was that I thought your example scenario was too contrived and that some readers might get in a mess by basing real-world data access code around this.

  • Fen

    I dont understand why you feel that you need an ICustomer interface and then a mock on it? It doesnt seem to add any benefit over instansiating the concrete class in your example. I tend to develop using a guideline of interfaces are better for behaviour and shouldnt be applied on what is essentially an entity class without a good case for doing so. Do other people feel that way or is it just me?

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    @Dan,

    You are expecting far too much from the story. What you are saying is that there can be no dataaccess without domain object and IoC. That’s too much and will scare too many people away.

    The only thing I wanted to demonstrate is how to read data from a DB. Doing it in a basic way. A basic way DOES include TDD and a mock to formulate your expectations. These two things are basic to any development.

    The DO and the IoC have crept in in the comments. They could be in a next chapter but do not belong here yet.

  • Dan

    I am intrigued by your choice of example scenario. I can’t think of a real-world case where it would make sense to pass an integer id to the constructor of an object, which in turns queries the database and initialises it’s own properties.

    Surely the logical approach would have been:

    – Define a simple concrete Customer class (write a test if you want to check its constructor works)
    – Define an ICustomerDataAccess interface with a single FindById method
    – Write a MockCustomerDataAccess class that always returns a Customer object called Alfred (write a test if you want)
    – Implement a SqlCustomerDataAccess class that queries database for customer record based on id supplied and creates and returns a Customer object

    You’re in danger of misleading beginners here with this approach – I think even example code should observe at least some OO design principals. I’m sure I’m not being pedantic here, this really stands out against the excellent examples used in other codebetter articles.

    I know that you wanted to get “back to basics” – from the comments it is clear that some of your readers appreciate this. But if this was your intention, why have you confused things by bringing in mocking, TDD etc? Please don’t add IOC to this!

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Thanks everybody for the tips to make the datareader code better. I wasn’t aware yet of the IsDBNull and the GetOrdinal methods. They do resolve a lot of the smells. This deserves a roundup, I’ll make a new post on that.

    @sergei: Please read the the text as well. All your comments are covered here. When it comes to IDisposable, yes you are right but the essential things with needs to be disposed is the open DB connection. Which is taken care of, as you say yourself as well.

    I am very happy that the idea has come accross and is appreceated. All I wanted to do is read some data and keep things as simple as possible. Based on this I could do a sequel on turning the Customer object in a true domain object and using IoC to insert either dB or a mock dataprovider. OK, in the works.

  • http://blog.magenic.com/blogs/aarone Aaron Erickson

    Mike-

    While a big benefit (or downside, depending on POV w/respect to DDD) of CSLA to a lot of people is how it can give you objects that know how to save themselves, etc, I see no reason why one could not get a lot of the non-dataportal related features of CSLA (n-level undo, indexing (in the next version)) – by using it and simply not using the dataportal parts of it – and rather, using a repository pattern with it.

    That said, CSLA is never going to be the DDD and IoC purists favorite framework, nor should it try. That said, if used correctly, it does a reasonably good job of seperating concerns *within* the class by at least isolating the database functionality to certain parts.

    Furthermore, a colleague of mine, Nermin Dibek, has done a good deal of full on TDD using CSLA, and a post that details that here: http://www.nermins.net/post/2007/04/TDDUsing-Mock-objects-with-CSLANet.aspx .

    As a disclosure, I am currently doing work on the core framework with CSLA, so I am probably prone to defending it at the moment – just want to make sure my biases are known…

  • Jeff

    Nevermind, I read the answer above.

  • Jeff

    Sorry for beginner question, but what is wrong with

    int.Parse(sdr[“Number”].ToString())

  • http://mikehadlow.blogspot.com/ Mike Hadlow

    I agree with Aaron, I prefer to keep business Entities separate from Services such as database persistence. So I’d have a simple Customer class, with no need for an interface or mocks and a separate ICustomerRepository service that (de)persists Customers as required. You can mock ICustomerRepository to return real Customers for your unit tests. I’ve done several projects like this and it works pretty well.

    I know Rocky Lhotka used to (maybe still does) recommend putting the data access code in your business entities but most modern ORM frameworks (NHibernate, Linq to SQL) all follow the pattern of separating those concerns.

  • http://vidapractica.com.ar nicogranelli@gmail.com

    I don’t like the number = sdr.GetInt32(0),
    it’s more maintainable sdr.GetInt32(sdr.GetOrdinal(“Number”))

  • http://blog.magenic.com/blogs/aarone Aaron Erickson

    Why not use a DI approach – i.e. instead of having a DBCustomer that represents a customer by implementing ICustomer, why not have an approach where you have a simple Customer that implements ICustomer, and constructs with a parameter that supports ICustomerLoader.

    Then, if you mock, you might have MockCustomerLoader that supports ICustomerLoader, DBCustomerLoader, or whatever implementation of a customer loader you want. At least then, you seperate concerns by having the Customer class strictly for business logic, CustomerLoader strictly for persistence (usually, DB) logic.

    That said, it spirit, I agree that at some point, we need a series of updated posts that demonstrate the “in TDD, how do I get from here to there in the simplest way possible for the kind of thing I actually do” posts.

  • Sergei Almazov

    Sample code is a bad code indeed.
    1. Different methods are used to get data from SqlDataReader: SqlDataReader.Getxxx(int) methods are mixed with SqlDataReader[int] and SqlDataReader[string]. If you want to test column for null value – use SqlDataReader.IsDBNull(int) instead, if you want efficient code – use Getxxx(int) methods.
    2. int.Parse(sdr[“Number”].ToString()) – no comments, just a VERY bad and inefficient code sample.
    3. IDisposable objects are not disposed. SqlConnection, SqlCommand, SqlDataReader are all IDisposable, so they should be wrapped into “using” statements (and then try…finally block is no longer needed)

  • http://www.engagesoftware.com/Blog/tabid/297/BlogID/11/Default.aspx Brian Dukes

    Nice to see some information about the various ways to get this done. I’ll have to look into the CommandBehavior stuff.

    One thing that I notice (because I’ve been bitten by it before) is that you can run into potential globalization problems when you’re using ToString (since it’ll use the CurrentCulture to create the string representation from an object). You should be able to skip the ToString (i.e., number = int.Parse(sdr[“Number”]);). Not a real big deal with integers so long as they’re positive, but get into dates and it can cause lots of little, difficult to test problems.

  • http://www.tobinharris.com Tobin Harris

    Nice post showing a mocking, tdd, OR round trip.

    Despite being a big NHibernate fan, I occassionly find myself starting manually and then “backing into” tools later. I usually switch to an ORM tool as soon as I start feeling the pain of identity mapping & update tracking.

    If you want a simple tool then the NHibernate ActiveRecord can be a nice start.

  • Jeff

    Great post. I am just learning TDD and really, really need this type of information. The big wigs discuss community, patterns, and how hard it is to find coders on blogs and lists, but it seems the real coders are head down with too much work to give us the details of how this is best implemented. Thank you!