Skip to main content

Improving “Boiler Plate” Data-Reader Code – Part 3

In Part 1 of this series we started with a basic Data-Reader / SQL Connection/Command pattern and illustrated how it is possible to abstract the parsing of the Data Reader into a standalone object that can be fully unit tested in isolation of the calling code. In Part 2 of the series we made a very simple optimisation to the “DataReader” convertor and updated the tests to capture/verify the changes.

In part 3 of the series we put this all together into a repository pattern to create a reusable and testable data access layer. The first step is to create an interface for the repository.

namespace DataAccess.Example
{
using System.Collections.Generic;
using System.Data.BoilerPlate;
 
public interface IRespository
{
IEnumerable<TEntity> Get(IQuery query, IConvertDataReader<TEntity> dataReaderConvertor);
}
}

The intent implied by the interface is that "Get" will be responsible for returning an enumerable list of a generic. To do this we pass in an implementation of IQuery and an implementation of IConvertDataReader for the generic we are to return. We already have an implementation of IConvertDataReader that we can use from the previous post. In this example the implementation of IQuery just returns SQL text that can be executed and is shown below.

namespace DataAccess.Example
{
public interface IQuery
{
string Text { get; }
}
}
 
namespace DataAccess.Example
{
public class GetAllCustomersQuery : IQuery
{
public string Text
{
get
{
return "SELECT id, Firstname, Surname FROM Customer";
}
}
}
}

The final piece of the jigsaw is the implementation of the repository interface, in this instance for SQL but it could be any data provider that returns an implementation of IDataReader. A basic implementation of the SQL repository is shown below:

namespace DataAccess.Example
{
using System.Collections.Generic;
using System.Data.BoilerPlate;
using System.Data.SqlClient;
 
public class SqlRepository : IRespository
{
private readonly SqlConnectionStringBuilder config;
 
public SqlRepository(SqlConnectionStringBuilder config)
{
this.config = config;
}
 
public IEnumerable<TEntity> Get<TEntity>(IQuery query, IConvertDataReader<TEntity> dataReaderConvertor)
{
using (var connection = new SqlConnection(this.config.ConnectionString))
{
using (var command = connection.CreateCommand())
{
command.CommandText = query.Text;
connection.Open();
 
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
yield return dataReaderConvertor.Parse(dataReader);
}
}
}
}
}
}
}

We now have a repository object that can be used against any SQL database to return a list of any type of object that can be populated using a standard SQL SELECT statement; the code below shows how to put this together.

var customers = new SqlRepository(connectionString).Get(
new GetAllCustomersQuery(),
new CustomerDRConvertorPart2()).ToList();

To understand the power of this pattern, consider the following code that is all that is needed to update the previous code to return a different list of customers; in this case all those with the first name of "Paul".

namespace DataAccess.Example
{
public class GetAllCustomersCalledPaul : IQuery
{
public string Text
{
get
{
return "SELECT id, Firstname, Surname FROM Customer WHERE Firstname = 'Paul'";
}
}
}
}
 
var customers = new SqlRepository(connectionString).Get(
new GetAllCustomersCalledPaul(),
new CustomerDRConvertorPart2()).ToList();

Part 4 shows how the code can be modified so it can be extended to handle different "query types".

Comments

Popular posts from this blog

Why do my Android Notification only appear in the status bar?

I'm definitely getting back into Android development, I'm remembering that feeling of 'Surely this should be easier than this!'. All I wanted to do was to schedule a local notification which behaved similar to a push notification pop-up. That is, as well as showing the small icon in the status bar I wanted it to pop up on screen to notify the end user. All seems fairly easily, I found this code for how to schedule a notification. That all worked perfectly, apart from the notification would only appear in the status bar. Searching around I found loads of different answers / solutions, mostly all saying the same thing:It only worked if you used 'NotificationCompat.Builder' in place of 'Notification.Builder', orYou had to set the priority to 'NotificationCompat.PRIORITY_HIGH'As usually happens, none of these solutions worked for me until I added in the missing piece of the jigsaw:- '.setDefaults(Notification.DEFAULT_ALL)'. For me this…

Do "Task Hours" add anything in Scrum (Agile)?

What do task hours add to the overall process in scrum?This was a question that has arisen from all team members in both instances that I've helped teams switch over to scrum. The benefits of artifacts like the comparative story point estimation, the 2 week sprints, stand-ups and the end of sprint demo have been self evident to the team, but as one I think every team member has expressed dismay when it comes to task planning and estimating each task in hours. Left unchecked there is a natural tendency for people to actually begin to dread the start of each sprint purely due to the task planning session.In my current role we've been lucky to investigate this further as a team.The team sat down to discuss the problems it was experiencing with estimating tasks in hours and the following common themes appeared:It is hard: Maybe it shouldn't be, but time estimation is hard! Story points are comparative and abstracted making them easier to determine, but time estimate is gen…

IPhone hangs when running from XCode

I've had this happen a couple of times now and the first time was a little worrying that I'd bricked my iPhone. Basically I was running an application on my phone via XCode and when rebuilding an updated version it failed with a "busy" error message. Stopping XCode and unconnecting my phone had no effect, the phone was stuck displaying the loading screen of the application and wouldn't respond to any key commands. To fix you have to hard reboot, holding the power and home button until the phone reboots - doesn't lose any of the data you have on your phone (a concern the first time I did it).