Today I was helping on a project which was using ADO.NET SQL Client. Wow, it looks so much scary, and old. I was asked to do some tweaks, so that data layer could be organized. Can I use Repository pattern here, one of the ways to add separation of responsibility these days.

So I started on creating a base class for these repositories. I thought of managing all connection related code in that base class. That was quite easy when I moved ExecuteNonQuery related calls here. Next came the hardest part ExecuteReader, how to send back DataReader without loosing control on my connection state. I can not move all reader related usage in base class, that would negate single responsibility rule.

Hmm, one way would be to create a virtual method in base class, and then each inherited class can override and implement it’s own mapping rules in it. I did small test with that, but soon was struck, what about mapping compositions. If I used this approach then I will have to add multiple conditional cases to map each item properly.

Then I thought of passing in mapper related methods as delegate, not bad. But why not use new concepts, Action<T, T …> / Func<T, …, TResult> / Predicate<T, …, bool>.

I wrote a small application to test this, here is it’s code.

Here is the model I will be using

 
    public class PostItem
    {
        public string Id { get; set; }
        public string Text { get; set; }
        public AuthorItem Author { get; set; }
    }

    public class AuthorItem
    {
        public string Id { get; set; }
        public string Name { get; set; }
    }

This is what base class will look

 
    public class RepositoryBase
    {
        protected virtual T ExecuteReader<T>(SqlCommand command, Func<SqlDataReader, T> Mapper)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                try
                {
                    connection.Open();
                    command.Connection = connection;
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        T results = Mapper(reader);
                        return results;
                    }
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
            }
        }
    }

How I used this to construct my concrete class

 
    public class BlogRepository : RepositoryBase
    {
        public List<PostItem> GetPosts()
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.CommandText = "SELECT * FROM Posts";
                List<PostItem> posts = ExecuteReader<List<PostItem>>(command, MapPosts);

                return posts;
            }
        }

        public PostItem GetPost(string id)
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.CommandText = string.Format("SELECT * FROM Posts WHERE postId = '{0}'", id);
                PostItem post = ExecuteReader<PostItem>(command, MapPost);

                return post;
            }
        }

        public AuthorItem MapAuthor(SqlDataReader reader)
        {
            AuthorItem author = new AuthorItem();

            author.Id = reader.GetString(reader.GetOrdinal("authorId"));
            author.Name = reader.GetString(reader.GetOrdinal("authorName"));

            return author;
        }

        public PostItem MapPost(SqlDataReader reader)
        {
            PostItem post = new PostItem();

            post.Id = reader.GetString(reader.GetOrdinal("postId"));
            post.Text = reader.GetString(reader.GetOrdinal("postText"));

            return post;
        }

        public List<PostItem> MapPosts(SqlDataReader reader)
        {
            List<PostItem> posts = new List<PostItem>();

            while (reader.Read())
            {
                PostItem post = MapPost(reader);

                posts.Add(post);
            }

            return posts;
        }
    }

Look how many mapping options I have using this technique. It looks quite amazing, ha.

Related posts:

1. Type.GetType()
2. Why XML request is not properly parsed by WCF REST service?
3. Google like auto complete suggestions

Related posts brought to you by Yet Another Related Posts Plugin.