Check out the Latest Articles:

My First Data Access Layer

By Saturday August 20, 2011

My First Data Access Layer

Mainly throughout the years I have used ORM’s (Object Relational Mappers) such as LINQ to SQL, Entity Framework Code First and nHibernate to handle my database access. I mean why recreate the wheel when these tools make accessing data and returning objects so simple? With all three of them now you can even write your queries in LINQ and will translate it to the relevant SQL for you! They also provide a nice abstraction from your underlying database vendor making it easy to switch from SQL Server to MySQL to Oracle. So you might ask, why am I even writing about creating a Data Access Layer from scratch? Why am I not just content with using nHibernate (My favourite from the 3 mentioned)? Well the answer is curiosity. I want to know how things work and I feel the best way to gain that understanding is to play around and write your own.

I also hear a lot of talk at work and from other more experienced developers about their love of just having a Data Access Object and just calling Stored Procedures. They feel you have a lot more control over the SQL you are running on the database and you experience better performance from this. From what I’ve read I’m aware that ORM’s generate SQL on the fly which does have it’s performance draw backs. Obviously when running a stored procedure no SQL needs to be generated meaning better performance. However you can cache the SQL generated by ORM’s to improve its performance and I know there are several features in nHibernate you can use to improve its performance. It’s just learning what they are! Which I am hoping to do at my new job where they claim to be hardcore nHibernate users!

Anyway my attempt at Data Access so far. I’ve only spent a couple days working on this so don’t be harsh! I have tried to take things I have learnt from using Generic Repository pattern using ORM’s and use it with what I have read about normal Data Access Layers. Perhaps one of the better article’s I read on the subject was this one here written by Matthew Cochran.

So I will start by explaining my Core.Abstract class library. Here I have the contracts which provide the Core of my data access. This is not specific to any project, implementation or database vendor. In this project I have a IDatabaseFactory which will simply provide an IDbConnection.

1
2
3
4
public interface IDatabaseFactory
{
     IDbConnection DbConnection { get; }
}

I also have an IMapper interface which will be used for classes which will map my domain objects to the relevant database tables. Implementers of this interface will be able to return single entities or collections of entities from a passed in IDataReader object.

1
2
3
4
5
6
public interface IMapper<T> where T : class
{
     T Map(IDataRecord record);
     T MapSingle(IDataReader reader);
     ICollection<T> MapCollection(IDataReader reader);
}

Next I have the first of three generic interfaces which will provide the main access for different types of domain objects. The first is IObjectReaderBase which will provide read access for a Object. The next IObjectWriterBase will provide create, update and delete operations for a domain object. The last IObjectKeyReaderBase provides a GetById method for Domain Objects which have a single primary key.

1
2
3
4
5
6
7
8
9
10
11
public interface IObjectReaderBase<T> where T : class
{
     IDatabaseFactory DatabaseFactory { get; }
     IDbConnection Connection { get; }
     IMapper<T> Mapper { get; }
     ICollection<T> GetAll();
     T ExecuteSingle(CommandType commandType, string commandText);
     T ExecuteSingle(CommandType commandType, string commandText, ICollection<IDataParameter> parameters);
     ICollection<T> ExecuteCollection(CommandType commandType, string commandText);
     ICollection<T> ExecuteCollection(CommandType commandType, string commandText, ICollection<IDataParameter> parameters);
}
1
2
3
4
5
6
7
8
9
10
11
12
public interface IObjectWriterBase<T> : IObjectReaderBase<T> where T : class
{
     bool Add(T item);
     bool Add(IEnumerable<T> items);
     bool Update(T item);
     bool Update(IEnumerable<T> items);
     bool Delete(T item);
     bool Delete(IEnumerable<T> items);
     IUnitOfWork UnitOfWork { get; }
     bool ExecuteWrite(CommandType commandType, string commandText);
     bool ExecuteWrite(CommandType commandType, string commandText, ICollection<IDataParameter> parameters);
}
1
2
3
4
public interface IObjectKeyReaderBase<T> : IObjectWriterBase<T> where T : class
{
     T GetById(int id);
}

Finally I have an IUnitOfWork Interface which will encapsulate my transactions.

1
2
3
4
5
6
7
8
public interface IUnitOfWork
{
     IDbTransaction Transaction { get; }
     bool IsInTransaction { get; }
     void BeginTransaction();
     void RollBackTransaction();
     bool CommitTransaction();
}

Ok on to the implementation of this which can be found in the Core.Concrete class library. The Database Factory is fairly simple and it just returns a new SqlConnection.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class DatabaseFactory : IDatabaseFactory
{
     private IDbConnection _dbConnection;

     public IDbConnection DbConnection
     {
          get
          {
               if (_dbConnection == null)
                    _dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

                return _dbConnection;
           }
    }
}

Object Reader Base contains the Unit of Work implementation, the IDbConnection which it gets from the Database Factory and the domain objects Mapper class. The class needs the Database Factory and Mapper class to be instantiated. The class contains two fairly similar methods. ExecuteSingle and ExecuteCollection which do exactly what they are called. ExecuteSingle is intended to be used for things such as GetById or a query that will only return one object. ExecuteCollection things such as GetAll or a method that will return many objects. As they are fairly similar and the only difference is which Mapping method is called I will only explain one. Fairly standard code it just creates a command from the passed in Command Type (E.g. Stored Procedure), Command Text (E.g. Stored Procedure Name) and Command Parameters (E.g. Stored Procedure Variables). This will all be specified in the Domain Objects repository class (Explained Later). If the process is in a transaction we assign the transaction to the command, we that add the parameters to the command. If the process is in a transaction we do not open the connection as it will already be open and will cause an exception (You need to keep the connection open for the entire transaction). If not in a transaction we open the connection ready to fire the command to the database. Once executed we call our Mapping method to return an object. We then close or not close our connection depending on the transaction status.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
public T ExecuteSingle(CommandType commandType, string commandText, ICollection<IDataParameter> parameters)
{
     IDbCommand command = Connection.CreateCommand();
     command.Connection = Connection;
     command.CommandText = commandText;
     command.CommandType = commandType;

     if (UnitOfWork.IsInTransaction)
          command.Transaction = UnitOfWork.Transaction;

     if (parameters != null)
     {
          foreach (var param in parameters)
          {
               command.Parameters.Add(param);
          }
     }

     try
     {
          if (!UnitOfWork.IsInTransaction)
               Connection.Open();

          using (IDataReader reader = command.ExecuteReader())
          {
               try
               {
                    return Mapper.MapSingle(reader);
               }
               catch (Exception)
               {
                    throw;
               }
               finally
               {
                    reader.Close();
               }
         }
     }
     catch (Exception)
     {
          throw;
     }
     finally
     {
          if (!UnitOfWork.IsInTransaction)
               Connection.Close();
     }
}

We also declare an abstract GetAll() method which will be implemented by the inheriting repository. This way we can provide the name of the stored procedure we want to execute.

1
public abstract ICollection<T> GetAll();

Next the ObjectWriterBase contains a method similar to ExecuteSingle and ExecuteCollection called ExecuteWrite. This method simply calls ExecuteNonQuery instead of executing the command and using IDataReader. This method will be used for our Create, Update and Delete operations. Again we declare abstract Add, Update and Delete methods so the inheriting class can provide the appropriate stored procedure name and parameters. We also have methods that allow a collection to be passed to them to Add, Update or Delete many records at once.

1
2
3
4
5
6
7
8
public virtual bool Add(IEnumerable<T> items)
{
     foreach (var item in items)
     {
          Add(item);
     }
     return true;
}

Finally our ObjectKeyReaderBase just declares an abstract GetById method for the same reasons as previously.

1
public abstract T GetById(int id);

The unit of work implementation is fairly simple allowing one to Begin a Transaction. Which will also open the connection for you.

1
2
3
4
5
public void BeginTransaction()
{
     _connection.Open();
     _transaction = _connection.BeginTransaction();
}

Rollback a transaction.

1
2
3
4
5
public void RollBackTransaction()
{
     _transaction.Rollback();
     _connection.Close();
}

Commit a transaction and run the relevant rollback if it doesn’t work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public bool CommitTransaction()
{
     if (_transaction == null)
     {
          throw new ApplicationException("Cannot roll back a transaction while there is no transaction running.");
     }

     try
     {
           _transaction.Commit();
           _connection.Close();
           return true;
     }
     catch (Exception ex)
     {
          _transaction.Rollback();
          return false;
     }
     finally
     {
          ReleaseCurrentTransaction();
     }
}

It also contains a flag to tell you whether you are currently in a transaction.

1
2
3
4
public bool IsInTransaction
{
     get { return _transaction != null; }
}

So now lets look at how we actually use this in relation to an object. I have a simple Person object in Entities.Conrete.

1
2
3
4
5
6
7
public class Person
{
     public int PersonId { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string Email { get; set; }
}

I then have a IPersonRepository interface in Repositories.Abstract. So far this interface just inherits from IObjectKeyReaderBase as I haven’t created any specific Person queries. I still like to create a new interface however just encase I do want to add additional methods in the future.

1
2
public interface IPersonRepository : IObjectKeyReaderBase<Person>
{ }

Finally PersonRepository is implemented in Repositories.Concrete. This class provides the Stored Procedure names and parameters to the underlying execute methods. It uses an IPersonParamCollectionRepository to get the collection of parameters. I have done this to try and create a reusable way of getting parameter collections for stored procedures which require the same parameters.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public class PersonRepository : ObjectKeyReaderBase<Person>, IPersonRepository
{
     private IPersonParamCollectionRepository _paramCollectionRepository;

     public PersonRepository(IDatabaseFactory databaseFactory, IMapper<Person> mapper, IPersonParamCollectionRepository paramCollectionRepository)
          : base(databaseFactory, mapper)
     {
          _paramCollectionRepository = paramCollectionRepository;
     }

     public override ICollection<Person> GetAll()
     {
          return ExecuteCollection(CommandType.StoredProcedure, "GetAllPeople");
     }

     public override Person GetById(int id)
     {
          ICollection<IDataParameter> collection = _paramCollectionRepository.GetPersonIdParamCollection(id);
          return ExecuteSingle(CommandType.StoredProcedure, "GetPersonById", collection);
     }

     public override bool Add(Person item)
     {
          ICollection<IDataParameter> collection =
               _paramCollectionRepository.GetFirstNameLastNameEmailParamCollection(item.FirstName,
                                                                                   item.LastName,
                                                                                   item.Email);

          return ExecuteWrite(CommandType.StoredProcedure, "AddPerson", collection);
     }

     public override bool Update(Person item)
     {
          ICollection<IDataParameter> collection =
               _paramCollectionRepository.GetPersonIdFirstNameLastNameEmailParamCollection(item.PersonId,
                                                                                           item.FirstName,
                                                                                           item.LastName,
                                                                                           item.Email);

          return ExecuteWrite(CommandType.StoredProcedure, "UpdatePerson", collection);
    }

    public override bool Delete(Person item)
    {
         ICollection<IDataParameter> collection = _paramCollectionRepository.GetPersonIdParamCollection(item.PersonId);
         return ExecuteWrite(CommandType.StoredProcedure, "DeletePerson", collection);
    }
}

Now lets see the Parameter Collection Repository. In a similar method to the Repository using this to group reusable parameters together. The creation of the parameters falls to a separate class. This way if anything with an individual parameter changes, such as its data type, it only needs to be changed in one place!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public class PersonParamCollectionRepository : IPersonParamCollectionRepository
{
     private IPersonParamRepository _paramRepository;

     public PersonParamCollectionRepository(IPersonParamRepository paramRepository)
     {
          _paramRepository = paramRepository;
     }

     public ICollection<IDataParameter> GetPersonIdParamCollection(int id)
     {
          ICollection<IDataParameter> collection = new Collection<IDataParameter>();
          collection.Add(_paramRepository.GetPersonIdParam(id));

          return collection;
     }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public class PersonParamRepository : IPersonParamRepository
{
     private readonly IPersonColumnProvider _columnProvider;
     private readonly IDataParamHelper _dataParamHelper;

     public PersonParamRepository(IPersonColumnProvider columnProvider, IDataParamHelper dataParamHelper)
     {
          _columnProvider = columnProvider;
          _dataParamHelper = dataParamHelper;
     }

     public IDataParameter GetPersonIdParam(int id)
     {
          return _dataParamHelper.BuildDataParameter(DbType.Int32, _columnProvider.PersonID, id);
     }
}

The BuildDataParameter method simply builds a parameter. Again if anything changes here it is interfaced so a new implementation can be provided and injected where it is used using Dependency Injection.

1
2
3
4
5
6
7
8
public IDataParameter BuildDataParameter(DbType dataType, string parameterName, object value)
{
     IDataParameter param = new SqlParameter();
     param.DbType = dataType;
     param.Value = value ?? DBNull.Value;
     param.ParameterName = parameterName;
     return param;
}

The Person Column Provider class simply encapsulates the names of the columns on the Person table. Again I’ve done this so if a column name changes it only needs to be changed in this one class. Whenever I use a column name such as when I create the Data Parameters it get the name from this class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class PersonColumnProvider : IPersonColumnProvider
{
     public string PersonID
     {
          get { return "PersonID"; }
     }

     public string FirstName
     {
          get { return "FirstName"; }
     }

     public string LastName
     {
          get { return "LastName"; }
     }

     public string Email
     {
          get { return "Email"; }
     }
}

Finally perhaps a bit out of context but lets have a look at the Person Mapper class. It inherits from MapperBase which can be located in Core.Concrete.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public abstract class MapperBase<T> : IMapper<T> where T : class, new()
{
     public abstract T Map(IDataRecord record);

     public T MapSingle(IDataReader reader)
     {
          T item = null;

          while (reader.Read())
          {
               item = Map(reader);
          }

          return item;
     }

     public virtual ICollection<T> MapCollection(IDataReader reader)
     {
          ICollection<T> collection = new Collection<T>();

          while (reader.Read())
          {
               try
               {
                    collection.Add(Map(reader));
               }
               catch (Exception)
               {    
                    throw;
               }
          }

          return collection;
     }
}

A mapper class then just implements Map allowing it to return a single object or a collection of objects. Below simply either populates an attribute from the record that is returned from the database or populates it to an empty value if there is no record returned.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public class PersonMapper : MapperBase<Person>
{
     private readonly IPersonColumnProvider _columnProvider;

     public PersonMapper(IPersonColumnProvider columnProvider)
     {
          _columnProvider = columnProvider;
     }

     public override Person Map(IDataRecord record)
     {
          try
          {
               Person p = new Person();

               p.PersonId = (DBNull.Value == record[_columnProvider.PersonID]) ? 0 : (int)record[_columnProvider.PersonID];
               p.FirstName = (DBNull.Value == record[_columnProvider.FirstName]) ? string.Empty : (string)record[_columnProvider.FirstName];
               p.LastName = (DBNull.Value == record[_columnProvider.LastName]) ? string.Empty : (string)record[_columnProvider.LastName];
               p.Email = (DBNull.Value == record[_columnProvider.Email]) ? string.Empty : (string)record[_columnProvider.Email];

               return p;
          }
          catch (Exception)
          {      
               throw;
          }
     }
}

I hope this helps someone who is just starting to look at data access layers. If anyone would like to provide comments below it would be much appreciated. Any suggestions on improvements or enhancements would be very welcome. I’m trying to learn how to do things the right way and the best way.

My next steps for this is to discover a way to map relationships and collections in an elegant way. Hopefully my research into this may lead me to enhancing this project to.

I provide the source code below. However it does contain a Caching Project and Web Project which I was just playing around with. Those two are for a later discussion! Enjoy!

Source Code: Here

PS: If anyone doesn’t have a version of Visual Studio where you can open Database Projects leave a comment and I will generate a SQL script.



0 Comments

  1. It‘s quite in here! Why not leave a response?



Leave a Response