Click here to Skip to main content
Click here to Skip to main content
Go to top

Replace SqlDataReader with a DynamicSqlDataReader

, 31 May 2009
Rate this:
Please Sign up or sign in to vote.
Remove those strings from your .NET 4.0 applications using the dynamic feature.

Introduction

This is a very simple example of how the dynamic feature of .NET 4.0 can help you on the borders between typed and untyped. It wraps a SqlDataReader in a DynamcSqlDataReader which allows you to access the fields as properties instead of passing string values with the column names to methods.

Using the code

Some code reads like poetry, and doesn't need a lot of explaining. By looking at the test fixture, you should be able to get an idea of the intended use. The database used to prove the concept contains a table Person with the following fields:

  • Id: uniqueidentifier (PK)
  • Name: nvarchar(50) non null
  • Country: nvarchar(50) null
  • Age: smallint null

The test fixture:

[TestClass]
public class PersonDataAccessTests
{
    public void Theory_Read_Person_Table(Action<dynamic> assertion)
    {
        using (var connection = new SqlConnection(@"CONNECTION STRING HERE"))
        {
            using (var command = new SqlCommand("SELECT TOP 1 Id," + 
                   "Name,Country,Age FROM Person", connection))
            {
                connection.Open();

                var x = new DynamicSqlDataReader(command.ExecuteReader(
                            System.Data.CommandBehavior.CloseConnection));
                while (x.Read())
                {
                    assertion(x);
                }
            }
        }
    }

    [TestMethod]
    [ExpectedException(typeof(ColumnIsNotInResultSetException))]
    public void Test_Read_Person_Table_Failure()
    {
        Theory_Read_Person_Table((x) =>
        {
            string s = x.NonExistingColumn;
        });
    }

    [TestMethod]
    public void Test_Read_Person_Table_Success()
    {
        Theory_Read_Person_Table((x) =>
        {
            Guid id = x.Id;
            string name = x.Name;
            string country = x.Country;
            int? age = x.Age;

            Assert.AreNotEqual(Guid.Empty, id);
            Assert.AreEqual("Tim", name);
            Assert.AreEqual("Belgium", country);
            Assert.AreEqual(null, age);
        });
    }
}

The actual code:

public class DynamicSqlDataReader : DynamicObject
{
    private SqlDataReader reader;

    public DynamicSqlDataReader(SqlDataReader reader)
    {
        this.reader = reader;
    }

    public bool Read()
    {
        return reader != null && (!reader.IsClosed) && reader.Read();
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        try
        {
            var rawResult = reader.GetValue(reader.GetOrdinal(binder.Name));
            result = rawResult == DBNull.Value ? null : rawResult;
            return true;
        }
        catch (IndexOutOfRangeException)
        {
            throw new ColumnIsNotInResultSetException(binder.Name);
        }
    }
}

Points of interest

Please provide some feedback and perhaps a discussion on the use of the new dynamic feature. I also know that it would be cleaner to wrap the DbDataReader instead of the concrete SqlDataReader, but hey it's just to show the use of the new dynamic feature Smile | :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

TimMerksem

Belgium Belgium
No Biography provided

Comments and Discussions

 
GeneralI do not understand how tow use this yet. PinmemberJollyMansArt15-Aug-10 15:40 
Question[My vote of 2] Why??? PinmemberMR_SAM_PIPER1-Jun-09 15:51 
QuestionPoetry? PinmemberDewey1-Jun-09 9:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 31 May 2009
Article Copyright 2009 by TimMerksem
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid