4
\$\begingroup\$

Designing a micro ORM inspired by Dapper. I'm building this as an exercise to learn. I would appreciate if you guys can review my code.

I would also appreciate any suggestions regarding the features that can/should be added to this ORM. The source is available here.

ConnectionStringProvider.cs 

   public interface IDbConnectionStringProvider {
      ConnectionStringEntity GetConnectionString(string connectionStringKey);
   }

   public class ConnectionStringProvider : IDbConnectionStringProvider {
      public ConnectionStringEntity GetConnectionString(string connectionStringKey) {
         Require.NotNullOrEmptyString(connectionStringKey, @"Connection string cannot be empty.");
         Require.NotNull(ConfigurationManager.ConnectionStrings, "Please provide a config file with a connection strings entry");

         ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[connectionStringKey];

         if (settings == null)
            throw new ConfigurationErrorsException(string.Format("Cannot find the connection string: [{0}] in the config file.", connectionStringKey));

         if (string.IsNullOrWhiteSpace(settings.ConnectionString) || string.IsNullOrWhiteSpace(settings.ProviderName))
            throw new ConfigurationErrorsException(string.Format("Require connection string as well as provider name to instantiate connection for Key [{0}].", connectionStringKey));

         return new ConnectionStringEntity() {ConnectionString = settings.ConnectionString, ProviderName = settings.ProviderName};
      }
   }

ConnectionProvider.cs 

   public interface IConnectionProvider : IDisposable {
      DbConnection CreateConnection(string connectionStringKey);
      DbDataAdapter Adapter { get; }
      IDbDataParameter CreateParameter();
   }

   public class ConnectionProvider : IConnectionProvider {

      IDbConnectionStringProvider _connectionStringProvider;
      DbProviderFactory _dbProviderFactory;
      DbConnection _dbConnection;
      DbDataAdapter _adapter;

      public ConnectionProvider(IDbConnectionStringProvider connectionStringProvider) {
         _connectionStringProvider = connectionStringProvider;
      }

      public DbDataAdapter Adapter {
         get {
            if (_dbConnection == null)
               throw new ObjectDisposedException("Connection object does not exist. Cannot return adapter object.");
            if (_adapter == null)
               throw new ObjectDisposedException("Adapter not initialized.");
            return _adapter;
         }
      }

      public DbConnection CreateConnection(string connectionStringKey) {
         Require.NotNullOrEmptyString(connectionStringKey, "Connection string is empty.");

         ConnectionStringEntity connectionStringEntity = _connectionStringProvider.GetConnectionString(connectionStringKey);
         _dbProviderFactory = DbProviderFactories.GetFactory(connectionStringEntity.ProviderName);

         Require.NotNull(_dbProviderFactory, "Provider factory cannot be null. Please provide a valid provider name.");

         _dbConnection = _dbProviderFactory.CreateConnection();
         _dbConnection.ConnectionString = connectionStringEntity.ConnectionString;
         _adapter = _dbProviderFactory.CreateDataAdapter();

         return _dbConnection;
      }

      public IDbDataParameter CreateParameter() {
         Require.NotNull(_dbProviderFactory, "Provider factory cannot be null. Please access this method with a valid connection.");
         return _dbProviderFactory.CreateParameter();
      }

      public void Dispose() {
         CloseConnection();
         GC.SuppressFinalize(this);
      }

      protected virtual void CloseConnection() {
         try {
            if (_dbConnection != null) {
               _dbConnection.Close();
               _dbConnection.Dispose();
               _adapter.Dispose();
            }
         } finally {
            _dbConnection = null;
            _adapter = null;
         }
      }

      ~ConnectionProvider() {
         CloseConnection();
      }
   }

DbContext.cs

public interface IDbContext {
   List<T> Query<T>(string connectionStringKey, string query, List<Tuple<string, object>> parameters) where T : new();
   List<T> Procedure<T>(string connectionStringKey, string procedureName, List<Tuple<string, object, DbType, ParameterDirection>> parameters) where T : new();
   int ExecuteQuery(string connectionStringKey, string query, List<Tuple<string, object>> parameters);
}

public class DbContext : IDbContext {
   IConnectionProvider _connectionProvider;

   public DbContext(IConnectionProvider connectionProvider) {
      _connectionProvider = connectionProvider;
   }

   public List<T> Query<T>(string connectionStringKey, string query, List<Tuple<string, object>> parameters) where T : new () {
      IDbDataParameter[] dbParameters = null;
      if (parameters != null) {
         dbParameters = new IDbDataParameter[parameters.Count];
         for (int i = 0; i < parameters.Count; i++) {
            dbParameters[i] = CreateParameter(parameters[i].Item1, parameters[i].Item2);
         }
      }
      DataSet dataset = ExecuteDataSet(connectionStringKey, query, CommandType.Text, dbParameters);
      return Mapper.GetList<T>(dataset.Tables[0]);
   }

   public List<T> Procedure<T>(string connectionStringKey, string procedureName, List<Tuple<string, object, DbType, ParameterDirection>> parameters) where T : new() {
      IDbDataParameter[] dbParameters = null;
      if (parameters != null) {
         dbParameters = new IDbDataParameter[parameters.Count];
         for (int i = 0; i < parameters.Count; i++) {
            dbParameters[i] = CreateParameter(parameters[i].Item1, parameters[i].Item2, parameters[i].Item3, parameters[i].Item4);
         }
      }
      DataSet dataset = ExecuteDataSet(connectionStringKey, procedureName, CommandType.StoredProcedure, dbParameters);
      return Mapper.GetList<T>(dataset.Tables[0]);
   }

   public int ExecuteQuery(string connectionStringKey, string query, List<Tuple<string, object>> parameters) {
      int result = -1;
      IDbDataParameter[] dbParameters = null;
      if (parameters != null) {
         dbParameters = new IDbDataParameter[parameters.Count];
         for (int i = 0; i < parameters.Count; i++) {
            dbParameters[i] = CreateParameter(parameters[i].Item1, parameters[i].Item2);
         }
      }

      using(_connectionProvider) {
         using (DbConnection connection = _connectionProvider.CreateConnection(connectionStringKey)) {
            connection.Open();
            using (DbCommand command = connection.CreateCommand()) {
               command.CommandText = query;
               if (parameters != null) {
                  command.Parameters.AddRange(dbParameters);
               }
               result = command.ExecuteNonQuery();
            }
         }
      }

      return result;
   }

   private DataSet ExecuteDataSet(string connectionStringKey, string commandText, CommandType commandType, IDbDataParameter[] parameters) {
      DataSet dataset = new DataSet();
      using (_connectionProvider) {
         using (DbConnection connection = _connectionProvider.CreateConnection(connectionStringKey)) {
            connection.Open();
            using (DbCommand command = connection.CreateCommand()) {
               command.CommandText = commandText;
               command.CommandType = commandType;
               if (parameters != null) {
                  command.Parameters.AddRange(parameters);
               }
               DbDataAdapter adapter = _connectionProvider.Adapter;
               adapter.SelectCommand = command;
               adapter.Fill(dataset);
            }
         }
      }
      return dataset;
   }

   private IDbDataParameter CreateParameter(string parameterName, object value, DbType dbType, ParameterDirection direction) {
      Require.NotNullOrEmptyString(parameterName, string.Format("Invalid arguments passed to the method. Parameter name: [{0}]", "(empty)"));

      IDbDataParameter param = this._connectionProvider.CreateParameter();
      param.ParameterName = parameterName.GetParameterName();
      param.Value = value;
      param.DbType = dbType;
      param.Direction = direction;
      return param;
   }

   private IDbDataParameter CreateParameter(string parameterName, object value) {
      Require.NotNullOrEmptyString(parameterName, string.Format("Invalid arguments passed to the method. Parameter name: [{0}]", "(empty)"));

      IDbDataParameter param = this._connectionProvider.CreateParameter();
      param.ParameterName = parameterName.GetParameterName();
      param.Value = value;
      return param;
   }
}

Mapper.cs

public class Mapper {
   public static List<T> GetList<T>(DataTable dataTable) where T : new() {
      List<T> entities = new List<T>();

      foreach (DataRow row in dataTable.Rows) {
         T obj = new T();
         foreach (PropertyInfo propertyInfo in new T().GetType().GetProperties()) {
            var column = (Column)propertyInfo.GetCustomAttribute(typeof(Column));
            if (column == null)
               continue;
            propertyInfo.SetValue(obj, MassageValue(row[column.Name], propertyInfo.PropertyType));
         }
         entities.Add(obj);
      }

      return entities;
   }

   static object MassageValue(object o, Type t) {
      if (t == typeof(int)) {
         return o == DBNull.Value ? default(int) : o;
      }

      if(t == typeof(decimal)) {
         return o == DBNull.Value ? default(decimal) : o;
      }

      if (t == typeof(DateTime)) {
         return o == DBNull.Value ? default(DateTime) : o;
      }

      if (t == typeof(double)) {
         return o == DBNull.Value ? default(double) : o;
      }

      if (t == typeof(string)) {
         return o == DBNull.Value ? default(string) : o;
      }

      return o;
   }
}

Example:

public class Customer {
   [Column(Name = "CustomerID")]
   public string CustomerId { get; set; }
   [Column(Name = "CompanyName")]
   public string CompanyName { get; set; }
   [Column(Name = "ContactTitle")]
   public string Title { get; set; }
   [Column(Name = "Region")]
   public string Region { get; set; }
}

public class CustomerOrderDetail {
   [Column(Name = "UnitPrice")]
   public decimal UnitPrice { get; set; }
   [Column(Name = "ProductName")]
   public string ProductName { get; set; }
   [Column(Name = "Quantity")]
   public int Quantity { get; set; }
   [Column(Name = "Discount")]
   public int Discount { get; set; }
   [Column(Name = "ExtendedPrice")]
   public decimal ExtendedPrice { get; set; }
}

DbContext context = new DbContext(new ConnectionProvider(new ConnectionStringProvider()));
string connectionStringKey = @"northwind";

#region Simple select query
string query = @"SELECT TOP 10 * FROM customers";
List<Customer> customers = context.Query<Customer>(connectionStringKey, query, null);
#endregion

#region Parametrized SQL query
string parameterizedQuery = @"SELECT * FROM Customers WHERE CustomerID = @customerId";
string paramName = "customerId";
string customerId = @"ALFKI";
Tuple<string, object> t = new Tuple<string, object>(paramName, customerId);
customers = context.Query<Customer>(connectionStringKey, parameterizedQuery, new List<Tuple<string, object>>() { t });
#endregion

#region execute proc
Tuple<string, object, DbType, ParameterDirection> procedureParam = new Tuple<string, object, DbType, ParameterDirection>("OrderID", 10248, DbType.Int32, ParameterDirection.Input);
List<CustomerOrderDetail> details = context.Procedure<CustomerOrderDetail>(connectionStringKey, "dbo.CustOrdersDetail", new List<Tuple<string, object, DbType, ParameterDirection>>() { procedureParam });
#endregion

#region insert/update/delete data
string insertQuery = @"insert into region (RegionID, RegionDescription)values (@regionId, @description)";
var valueTupleList = new List<Tuple<string, object>>();
Tuple<string, object> t1 = new Tuple<string, object>("regionId", 5);
Tuple<string, object> t2 = new Tuple<string, object>("description", "MidWestern");

int result = context.ExecuteQuery(connectionStringKey, insertQuery, new List<Tuple<string, object>>() { t1, t2 });

string updateQuery = @"UPDATE region SET RegionDescription = @description WHERE RegionID = @regionId";
t2 = new Tuple<string, object>("description", "Mid-Western");
result = context.ExecuteQuery(connectionStringKey, updateQuery, new List<Tuple<string, object>>() { t1, t2 });

string deleteQuery = @"DELETE FROM region WHERE RegionID = @regionId";
result = context.ExecuteQuery(connectionStringKey, deleteQuery, new List<Tuple<string, object>>() { t1 });
#endregion
\$\endgroup\$

1 Answer 1

Reset to default
2
\$\begingroup\$

Some notes.

  1. This piece of code propertyInfo.SetValue(obj, MassageValue(row[column.Name], propertyInfo.PropertyType)); will trow exception for nullable types. Did you test it with classes with int? or double? properties ?
  2. Client code with your Tuples are very hard to read ... Look here. As for me, using Tuple for public methods is usually bad idea. I would like to have something like SqlParameter class there which encapsulates all parameter properties
  3. I would recommend you to use Dispose according to official recommendations .. . Look here. Section "Basic Dispose pattern". It seems that your Dispose will work but at least it looks strange ...
\$\endgroup\$
2
  • \$\begingroup\$ I modified the SetValue to something like this, propertyInfo.SetValue(obj, row[column.Name] == DBNull.Value ? null : row[column.Name]); This change works with nullable value types. \$\endgroup\$
    – LearnCode
    Commented Aug 18, 2016 at 20:02
  • \$\begingroup\$ @LearnCode Glad for you :) \$\endgroup\$
    – Disappointed
    Commented Aug 18, 2016 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.