Is wrapping the service layer worthwhile?

Question

I have been trying to design a good data layer that will eventually be generated. I am wondering if I have missed anything. The basic architecture contains a service class that handles connecting and eventually transactions. I wrap the IDbConnection in the service class to make sure it gets disposed properly so that the user will not need to worry about that. Are there any drawbacks to wrapping the IDbConnection in a using statement?

Sample code:

public abstract class SqlService
{
 private readonly string _connectionString;
 protected Service(string connectionString)
 {
 _connectionString = connectionString;
 }
 private IDbConnection CreateConnection()
 {
 var connection = new SqlConnection(_connectionString);
 connection.Open();
 return connection;
 }
 protected T Execute<IDbConnection, T>(Func<IDbConnection, T> query)
 {
 using(var connection = CreateConnection())
 {
 return query(connection);
 }
 }
 protected T ExecuteTransaction<T>(Func<IDbConnection, IDbTransaction, T> query, IsolationLevel level)
 {
 return Execute(c =>
 {
 using (var transaction = c.BeginTransaction(level))
 {
 try
 {
 var result = query(c, transaction);
 transaction.Commit();
 return result;
 }
 catch (SqlException)
 {
 transaction.Rollback();
 throw;
 }
 }
 });
 }
}
public interface ICanGetById<TEntity, TKey>
{
 TEntity GetById(TKey id)
}
public interface IOrderService : ICanGetById<OrderDTO, int>
{
}
public sealed partial class OrderService : SqlService, IOrderService
{
 public OrderService (string connectionString) : base(connectionString) { }
 public OrderDTO GetById(int id)
 {
 var dynamicParameters = new DynamicParameters();
 dynamicParameters.Add("@Id", id);
 return Execute(c => c.Query<OrderDTO>(
 "usp_OrderSelect",
 dynamicParameters,
 commandType: CommandType.StoredProcedure));
 }
}

Answer 1

The drawback is that you are limiting the functionality of your service to perform one query per connection and you have no "batch mode"

But maybe you can implement a query batching method!

For this reason, the user of the database service perhaps should have the power to open and close the connection. Your SQLService could expose Open, Close, and Dispose.

• Dustin Kingen: My vision of the batch insertion was using DataTable or maybe exposing a SqlBulkCopy. I might add query chaining if multiple queries per connection are necessary.

Answer 2

I have been trying to design a good data layer that will eventually be generated. I am wondering if I have missed anything

You have missed the Entity Framework and/or NHibernate :), both of them are good data layers that are mature enough. If you want to have a good data layer - the best you can do is start using one of them, and stop designing a wheel. Out of those two I would prefer NHibernate, but Entity Framework may be easier for beginner.

Concerning your code - properties should not represent factories (like you do in Connection), in other words repeating calls to the property getter is assumed to return the same value. To fix that replace Connection property with CreateConnection() method

Also you are missing the notion of transactions and unit-of-work here, and a whole object mappings story.

• Dustin Kingen: EF and Nhibernate don't take a data-centric approach to ORM and they want to force their conventions and idioms how you communicate with the database. In order to address those shortfalls I wanted to create a service layer that is mostly decoupled by utilizing stored procedures and views.
• almaz: Both ORMs allow you to use stored procedures (but you loose part of flexibility in this case). Obviously any framework forces you to use some kind of conventions. "I could do it better" approach is viable, but very unlikely. You may optimize it for a certain very specific scenario, but bigger issues will appear when you try to step out of those specifics, and it will be too late to switch.
• Dustin Kingen: It is true NH and EF have support for stored procedures, but they feel clunky and involve a lot intervention to use them. I would rather have my DTOs generated off stored procedures and views and have all the mapping done against the views and stored procedures.
• almaz: The fact that you're asking a question about using statement suggests that you don't have a lot of experience with .NET, thus most likely you don't realize all the complexity, drawbacks and support cost of building your own ORM.
• Dustin Kingen: The fact that you're calling out my inexperience as a reason for discouragement shows that's there's a lack of understanding that NH and EF don't meet my requirements which is why I'm using Dapper + Code Generation.