Implement ORM using C#

• Download source - 988.3 KB

Introduction

This article intends to describe the way of writing Object Relation Mapping in .NET Framework, for those who don't know anything about it. Object Relation Mapping is a way to map your relational database to logical objects in the programming language which comes to an advantage to reduce the lines of code to implement a business process and provides some sort of persistency to the objects. There are a lot of ORM implementations in the market, but why not do it yourself to fit your needs instead of using out of the box software, with extra things that you don’t need which comes with a cost of memory foot print and performance. So I was experimenting with ORM and I created my own but it misses one thing “objects persistency”, which I will implement at a later stage and I will post it once I’m done.

Background

How can we present the database tables, and table’s relations in a way that it is presentable for Application Objects, or how can we map the Logical objects to Database tables in a way by creating a logical view for the database represented in application objects. ORM is all about that.

Using the Code

There are a couple of classes which are related to the end result of my ORM implementation including Database layer, DataAccess, DataMapper, and dataStorage or Repository and the most important part is Data Models. I will not be able to describe the whole code but I will go through the most important parts of it to give the complete picture.

First, I created custom attributes that their only purpose to describe the model in similar way that the related table has been described in the database, so every table has column name and primary key and 1 or more relation to different tables, and the custom attributes which have been created described like below:

[DataSource(Name = "Sites_Departments", 
Type = GLOBALS.DataSource.Type.DBTable, AccessMethod = GLOBALS.DataSource.AccessMethod.SingleSource)]
public class SiteDepartment : DataModel
{
     [IsIDField]
     [DbColumn("ID")]
      public int ID { get; set; }
 
     [DbColumn("SiteID")]
      public int SiteID { get; set; }
 
     [DbColumn("DepartmentID")]
      public int DepartmentID { get; set; }
 
     [DataRelation(WithDataModel = typeof(Site), 
     OnDataModelKey = "ID", ThisKey = "SiteID")]
      public Site Site { get; set; }
 
     [DataRelation(WithDataModel = typeof(Department), 
     OnDataModelKey = "ID", ThisKey = "DepartmentID")]
      public Department Department { get; set; }
}
...

As shown above, the class is being described with datasource which has a name that reflects the table name and a type if it is table or whatever your source was and access type which refers that this table is single table in the database or there is more table like this table.

The data source name could refer to web services URL or CSV file but I didn’t implement this part yet.

• IsIDField: describes if the field is a primary key or not.
• DBColumn: describes the Name of the column in the database table
• DataRelation: describes the foreign key relation, with one small thing to take into consideration that WithDataModel part should refer to an existing class/Model.

In case data access type was distributed, there will be a need to set in the name the mapping table which holds the list of tables that share the table structure and usage, like below:

[DataSource(Name = "MonitoringServersInfo", Type = GLOBALS.DataSource.Type.DBTable, 
    AccessMethod = GLOBALS.DataSource.AccessMethod.SingleSource)]

So after this part of describing the decorators, there should be a layer that understands those decorators which is DataAccess.

DataAccess is a Layer that deals with abstracting the data source routines to the upper layer by creating a wrapper around Data source Routines and present it to DataMapper, while also being able to understand the Class decorators, but before that in order to make everything look more organized, DataAccess implements IDataAccess interface, and the interface looks like below:

public interface IDataAccess<T> where T : class, new()
{
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataObject"></param>
        /// <returns></returns>
         int Insert(T dataObject, string dataSourceName = null, 
             GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// Update the data based on a predict expression 
        /// </summary>
        /// <param name="dataObject">Object to be updated</param>
        /// <param name="predicate">Expression<Func<T, 
        /// bool>> predicate specify the expression that should be evaluated</param>
        /// <returns></returns>
         bool Update(T dataObject, string dataSourceName = null, 
              GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// Delete Data from the repository
        /// </summary>
        /// <param name="dataObject">the object you wish to delete</param>
        /// <param name="where">Dictionary<string,object> 
        /// Represents the where part that should be executed</param>
        /// <returns>bool status</returns>
        bool Delete(T dataObject, string dataSourceName = null, 
             GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        T GetById(long id, string dataSourceName = null, 
          GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// Gets the data from repository 
        /// </summary>
        /// <param name="fields">List<string> 
        /// represents the fields that should be set</param>
        /// <param name="where">Dictionary<string,object> 
        /// Represents the where part that should be executed</param>
        /// <param name="limit">Number of T objects to be populated</param>
        /// <returns>IQueryable<T>  Results</returns>
        IEnumerable<T> Get(Dictionary<string, object>  where, int limit = 25, 
                    string dataSourceName = null, 
                    GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// Gets the data from the repository and filter 
        /// it based on the specified predicate expression
        /// </summary>
        /// <param name="predicate">Expression<Func<T, 
        /// bool>> predicate specify the expression that should be evaluated</param>
        /// <returns>IQueryable<T>  Results</returns>
        IEnumerable<T> Get(Expression<Func<T, bool>> predicate, 
                    string dataSourceName = null, 
                    GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
        /// <summary>
        /// Get all the data from the Repo
        /// </summary>
        /// <returns></returns>
        IEnumerable<T> GetAll(string dataSourceName = null, 
                    GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default);
 
         #region Native SQL Execute Commands
 
        IEnumerable<T> GetAll(string sql);
        int Insert(string sql); 
        bool Update(string sql); 
        bool Delete(string sql); 
        #endregion
}

As you can see, IDataAccess is a typed class that describes the functions to be implemented by DataAccess such as insert, update, delete, get, getall, and getbyid.

This interface can be implemented to DB-DataAccess, CSV-DataAccess, and WS-DataAccess, etc. but since it is typed and it holds the type of the model that uses it at run time, there should be a way to extract model information, so I created another class which will be able to do so, and this class will be called from DataAccess Class constructor, the class will parse all the attributes of the model and put it in a schema object which will be read and understood from the DataAccess.

public class DataSourceSchema<T> where T: DataModel, new()
{
        public string DataSourceName { get; set; }
        public Enums.DataSourceType DataSourceType { set; get; }
        public Enums.DataSourceAccessType DataSourceAccessType { get; set; }
 
        public string IDFieldName { set; get; }
 
        public List<DataField> DataFields { get; set; }
  
        /***
        * Private functions.
        */
        /// <summary>
        /// Tries to read the TableName attribute value if it exists; 
        /// if it doesn't it throws and exception
        /// </summary>
        /// <returns>TableName attribute value (string), if exists.</returns>
        private void tryReadDataSourceAttributeValue()
        {
            //Get the table name attribute
            IEnumerable<attribute> dataSourceAtt = 
    typeof(T).GetCustomAttributes(typeof(DataSourceAttribute));

            // This mean that the Class is unstructured Class and 
            // it could be related to table/function or procedure or not.
            if (dataSourceAtt.Count() > 0)
            {
                var dsAttr = ((DataSourceAttribute)dataSourceAtt.First());

                if (dsAttr != null)
                {
                    DataSourceType = dsAttr.Type;
                    DataSourceAccessMethod = dsAttr.AccessMethod;

                    if (false == string.IsNullOrEmpty(dsAttr.Name))
                    {
                        DataSourceName = dsAttr.Name;
                    }
                }
            }
        }
 
        /// <summary>
        /// Tries to read the Class Db Properties, which are the properties marked 
        /// with DbColumn Attribute. It tries to resolve the other attribute values, if they exist, 
        /// otherwise, it assigns the default values.
        /// Write the results to the inner List of DataFields
        /// </summary>
         private void tryReadClassDataFields()
        {
            this.DataFields = new List<DataField>();

            var tableFields = typeof(T)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(property => 
                property.GetCustomAttribute<DbColumnAttribute>() != null)
                .ToList();

            var relationFields = typeof(T)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Where(property => 
                property.GetCustomAttribute<DataRelationAttribute>() != null)
                .ToList();

            var allClassFields = tableFields.Concat(relationFields).ToList();

            //If no exception was thrown, proceed to processing the class fields
            foreach (var field in allClassFields)
            {
                var newDataField = new DataField();

                newDataField.Name = field.Name;

                if (field.GetCustomAttribute<DbColumnAttribute>() != null)
                {
                    newDataField.TableField = new DbTableField()
                    {
                        ColumnName = field.GetCustomAttribute<DbColumnAttribute>().Name,
                        IsIDField = field.GetCustomAttribute<IsIDFieldAttribute>() 
                        != null ? field.GetCustomAttribute<IsIDFieldattribute>().Status : false,
                        AllowNull = field.GetCustomAttribute<AllowNullAttribute>() 
                        != null ? field.GetCustomAttribute<AllowNullAttribute>().Status : false,
                        AllowIDInsert = field.GetCustomAttribute<AllowIDInsertAttribute>() 
                        != null ? field.GetCustomAttribute<AllowIDInsertAttribute>().Status : false,
                        IsKey = field.GetCustomAttribute<IsKeyAttribute<() 
                        != null ? field.GetCustomAttribute<IsKeyAttribute<().Status : false,
                        FieldType = field.PropertyType
                    };
                }

                if (field.GetCustomAttribute<DataRelationAttribute>() != null)
                {
                    var dataRelationAttribute = field.GetCustomAttribute<DataRelationAttribute>();

                    newDataField.Relation = new DbRelation()
                    {
                        DataField = field.Name,
                        RelationName = dataRelationAttribute.Name,
                        WithDataModel = dataRelationAttribute.WithDataModel,
                        OnDataModelKey = dataRelationAttribute.OnDataModelKey,
                        ThisKey = dataRelationAttribute.ThisKey,
                        RelationType = dataRelationAttribute.RelationType
                    };
                }

                this.DataFields.Add(newDataField);
            }

            //Set the IDFieldName variable to the DbColumn name of the ID.
            if (this.DataFields.Count > 0)
            {
                var field = this.DataFields.Find(item => item.TableField 
                != null && item.TableField.IsIDField == true);

                if (field != null)
                {
                    this.IDFieldName = field.TableField.ColumnName;
                }
            }
        }
 
        public DataSourceSchema()
        {
            try
            {
                tryReadDataSourceAttributeValue();
                tryReadClassDataFields();
            }
            catch (Exception ex)
            {
                throw ex.InnerException;
            }
        }
 
        /***
         * Getters.
         * They support accessing a dynamic version of this object's data
         */
        public string GetDataSourceName()
        {
               return this.DataSourceName;
        }
 
        public GLOBALS.DataSource.Type GetDataSourceType()
        {
               return this.DataSourceType;
        }
 
        public Enums.DataSourceAccessType GetDataSourceAccessType()
        {
               return this.DataSourceAccessType;
        }

        public GLOBALS.DataSource.AccessMethod GetDataSourceAccessMethod()
        {
            return this.DataSourceAccessMethod;
        }
 
        public string GetIDFieldName()
        {
               return this.IDFieldName;
        }
 
        public List<DataField> GetDataFields()
        {
               return this.DataFields;
        } 
}

Since DataSourceSchema Class is typed, it will be able to extract Model Info/Attributes at run time and the type will be passed from DataAccess Class.

No jumping to DataAccess, as it has been mentioned before DataAccess implementation will act as wrapper for another library that I created before which deals with database Generic Select, Insert, Update and Delete, you can write your own it doesn’t matter but this library that I wrote evolved to cater to all sorts of Microsoft SQL routines such as selecting from functions, execute store procedures, update where and insert where, select where, and what columns to select and how many rows to be returned and so on so forth. You will find this library in the project zip file. It needs some refactoring because it was incremental development for this library but as far as you are concerned, it works like charm, anyway since data access is a logical representation related with how the model should get, modify, delete the data. You could hook to it any non SQL data repository such as Web services, NoSQL, CSV file, etc.

public class DataAccess<T> : IDataAccess<T> where T : DataModel, new()
    {
        private DataSourceSchema<T> Schema;

        private static DBLib DBRoutines = new DBLib();
        private static readonly List<Type> NumericTypes = new List<Type>() 
        { typeof(int), typeof(long), typeof(Int16), typeof(Int32), typeof(Int64) };


        /// <summary>
        /// This is a private function. It is responsible for returning a list of 
        /// the data relations on this data model translated to a list of SqlJoinRelation objects.
        /// </summary>
        /// <returns>List of SqlJoinRelation objects</returns>
        private List<SqlJoinRelation> GetDataRelations()
        {
            //Table Relations Map
            //To be sent to the DB Lib for SQL Query generation
            List<SqlJoinRelation> TableRelationsMap = new List<SqlJoinRelation>();

            //TableRelationsList
            //To be used to looking up the relations and extracting information 
            //from them and copying them into the TableRelationsMap
            List<DbRelation> DbRelationsList = Schema.DataFields.Where
            (field => field.Relation != null).Select<DataField, 
            DbRelation>(field => field.Relation).ToList<DbRelation>();

            //Start processing the list of table relations
            if (DbRelationsList != null && DbRelationsList.Count() > 0)
            {
                //Foreach relation in the relations list, 
                //process it and construct the big TablesRelationsMap
                foreach (var relation in DbRelationsList)
                {
                    //Create a temporary map for this target table relation
                    var joinedTableInfo = new SqlJoinRelation();

                    //Get the data model we're in relation with.
                    Type relationType = relation.WithDataModel;

                    //Build a data source schema for the data model we're in relation with.
                    var generalModelSchemaType = typeof(DataSourceSchema<>);
                    var specialModelSchemaType = generalModelSchemaType.MakeGenericType(relationType);
                    dynamic joinedModelSchema = Activator.CreateInstance(specialModelSchemaType);

                    //Get it's Data Fields.
                    List<DataField> joinedModelFields = joinedModelSchema.GetDataFields();

                    //Get the table column names - exclude the ID field name.
                    List<string> joinedModelTableColumns = joinedModelFields
                        .Where(field => field.TableField != null)
                        .Select<datafield, string="">
                        (field => field.TableField.ColumnName)
                        .ToList<string>();

                    //Get the field that describes the relation key from the target model schema
                    DataField joinedModelKey = joinedModelFields.Find
                    (item => item.TableField != null && item.Name == relation.OnDataModelKey);

                    //Get the field that describes our key on which we are in relation with the target model
                    DataField thisKey = Schema.DataFields.Find
                    (item => item.TableField != null && item.Name == relation.ThisKey);

                    if (thisKey != null && joinedModelKey != null)
                    {
                        //Initialize the temporary map and add it to the original relations map
                        joinedTableInfo.RelationName = relation.RelationName;
                        joinedTableInfo.RelationType = relation.RelationType;
                        joinedTableInfo.MasterTableName = Schema.DataSourceName;
                        joinedTableInfo.MasterTableKey = thisKey.TableField.ColumnName;
                        joinedTableInfo.JoinedTableName = joinedModelSchema.GetDataSourceName();
                        joinedTableInfo.JoinedTableKey = joinedModelKey.TableField.ColumnName;
                        joinedTableInfo.JoinedTableColumns = joinedModelTableColumns;

                        //Add the relation keys to the TableRelationsMap
                        TableRelationsMap.Add(joinedTableInfo);
                    }

                }//end-foreach

            }//end-outer-if

            return TableRelationsMap;
        }

        /**
         * Repository Constructor
         */
        public DataAccess() 
        {
            //Get the Table Name and List of Class Attributes
            try
            {
                //Initialize the schema for the class T
                this.Schema = new DataSourceSchema<T>();
                
                //Check for absent or invalid DataModel 
                //attributes and throw the respective exception if they exist.
                if(string.IsNullOrEmpty(Schema.DataSourceName))
                {
                    throw new NoDataSourceNameException(typeof(T).Name);
                }
                else if(Schema.DataFields.Where
                (item => item.TableField != null).ToList().Count() == 0)
                {
                    throw new NoTableFieldsException(typeof(T).Name);
                }
                else if(string.IsNullOrEmpty(Schema.IDFieldName))
                {
                    throw new NoTableIDFieldException(typeof(T).Name);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public virtual int Insert(T dataObject, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            int rowID = 0;
            string finalDataSourceName = string.Empty;
            Dictionary<string,object> columnsValues = new Dictionary<string,object>();
            

            //
            // Decide the DataSource Name
            if(false == string.IsNullOrEmpty(dataSourceName))
            {
                finalDataSourceName = dataSourceName;
            }
            else if(false == string.IsNullOrEmpty(Schema.DataSourceName))
            {
                finalDataSourceName = Schema.DataSourceName;
            }
            else
            {
                throw new Exception("Insert Error: No Data Source was provided in the " + 
                dataObject.GetType().Name + 
                ". Kindly review the class definition or the data mapper definition.");
            }

            //
            // Process the data object and attempt to insert it into the data source
            if (dataObject != null)
            {
                // Get only the Data Fields from Schema which have TableFields objects
                var objectSchemaFields = Schema.DataFields
                    .Where(field => field.TableField != null)
                    .ToList<DataField>();

                foreach (var field in objectSchemaFields)
                {
                    // Don't insert the ID Field in the Data Source, 
                    // unless it's marked as AllowIDInsert
                    if (field.TableField.IsIDField == true 
                    && field.TableField.AllowIDInsert == false)
                    {
                        continue;
                    }

                    // Get the property value
                    var dataObjectAttr = dataObject.GetType().GetProperty(field.Name);

                    //Continue handling the properties
                    if (field.TableField.AllowNull == false && dataObjectAttr != null)
                    {
                        var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

                        if (dataObjectAttrValue != null)
                        {
                            //
                            // Only update the int/long values to zeros if they are not foreign keys
                            if (true == NumericTypes.Contains(field.TableField.FieldType))
                            {
                                var value = Convert.ChangeType
                        (dataObjectAttrValue, field.TableField.FieldType);

                                if (Convert.ToInt64(value) <= 0 && field.TableField.IsKey == true)
                                {
                                    //continue;
                                    throw new Exception("The Property " + 
                                    field.TableField.ColumnName + " in the " + 
                                    dataObject.GetType().Name + " Table is a foreign key and 
                                    it is not allowed to be null. Kindly set the property value.");
                                }
                            }
                            
                            columnsValues.Add(field.TableField.ColumnName, 
                            Convert.ChangeType(dataObjectAttrValue, field.TableField.FieldType));
                        }
                        else
                        {
                            throw new Exception("The Property " + 
                            field.TableField.ColumnName + " in the " + 
                            dataObject.GetType().Name + 
                            " Table is not allowed to be null kindly annotate 
                the property with [IsAllowNull]");
                        }
                    }
                    else
                    {
                        var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

                        if (dataObjectAttrValue != null)
                        {
                            //
                            // Only update the int/long values to zeros if they are not foreign keys
                            if (true == NumericTypes.Contains(field.TableField.FieldType))
                            {
                                var value = Convert.ChangeType
                (dataObjectAttrValue, field.TableField.FieldType);

                                if (Convert.ToInt64(value) <= 0 && field.TableField.IsKey == true)
                                {
                                    continue;
                                }
                            }
                            
                            columnsValues.Add(field.TableField.ColumnName, 
                            Convert.ChangeType(dataObjectAttrValue, field.TableField.FieldType));
                        }
                    }
                    //end-inner-if

                }//end-foreach

                try
                {
                    rowID = DBRoutines.INSERT(tableName: finalDataSourceName, 
                    columnsValues: columnsValues, idFieldName: Schema.IDFieldName);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }//end-outer-if

            return rowID;  
        }
        
        public virtual bool Update(T dataObject, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            bool status = false;
            string finalDataSourceName = string.Empty;
            Dictionary<string,object> columnsValues = new Dictionary<string,object>();
            Dictionary<string,object> whereConditions = new Dictionary<string,object>();

            //
            // Decide the DataSource Name 
            if (false == string.IsNullOrEmpty(dataSourceName))
            {
                finalDataSourceName = dataSourceName;
            }
            else if (false == string.IsNullOrEmpty(Schema.DataSourceName))
            {
                finalDataSourceName = Schema.DataSourceName;
            }
            else
            {
                throw new Exception("Insert Error: 
                No Data Source was provided in the " + dataObject.GetType().Name + 
                ". Kindly review the class definition or the data mapper definition.");
            }

            //
            // Process the data object and attempt to insert it into the data source
            if (dataObject != null)
            {
                // Get only the Data Fields from Schema which have TableFields objects
                var objectSchemaFields = Schema.DataFields
                    .Where(field => field.TableField != null)
                    .ToList<DataField>();

                foreach (var field in objectSchemaFields)
                {
                    // Get the property value
                    var dataObjectAttr = dataObject.GetType().GetProperty(field.Name);

                    //
                    // Don't update the ID Field in the Data Source, unless it's marked as AllowIDInsert
                    // Add the data object ID into the WHERE CONDITIONS
                    if (field.TableField.IsIDField == true)
                    {
                        var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

                        //
                        // Put the ID Field in the WHERE CONDITIONS
                        if (dataObjectAttrValue != null)
                        {
                            //
                            // Add the ID Field and Value to the 
                            // Where Conditions if it was not added already!
                            if (false == whereConditions.Keys.Contains(field.TableField.ColumnName))
                            { 
                                whereConditions.Add(field.TableField.ColumnName, 
                                Convert.ChangeType(dataObjectAttrValue, field.TableField.FieldType));
                            }
                        }
                        else
                        {
                            throw new Exception("The Property " + 
                            field.TableField.ColumnName + " in the " + 
                            dataObject.GetType().Name + " Table is not SET! 
                            Kindly please set it to it's original value in order 
                            to decide what data to update accordingly.");
                        }

                        //
                        // DON'T CONTINUE EXECUTION IF THE ID FIELD IS NOT ALLOWED TO BE CHANGED
                        if(false == field.TableField.AllowIDInsert)
                        { 
                            continue;
                        }
                    }

                    // 
                    // Add the data object fields into the COLUMNS-VALUES dictionary
                    // This dictionary contains the values to be updated
                    if (field.TableField.AllowNull == false && dataObjectAttr != null)
                    {
                        var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

                        if (dataObjectAttrValue != null)
                        {
                            //
                            // Only update the int/long values to zeros if they are not foreign keys
                            if (true == NumericTypes.Contains(field.TableField.FieldType))
                            {
                                var value = Convert.ChangeType
                                    (dataObjectAttrValue, field.TableField.FieldType);

                                if (Convert.ToInt64(value) <= 0 && field.TableField.IsKey == true)
                                {
                                    //continue;
                                    throw new Exception("The Property " + 
                                    field.TableField.ColumnName + " in the " + 
                                    dataObject.GetType().Name + " Table is a foreign key 
                                    and it is not allowed to be null. Kindly set the property value.");
                                }
                            }

                            columnsValues.Add(field.TableField.ColumnName, 
                            Convert.ChangeType(dataObjectAttrValue, field.TableField.FieldType));
                        }
                    }
                    else
                    {
                        var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

                        if (dataObjectAttrValue != null)
                        {
                            //
                            // Only update the int/long values to zeros if they are not foreign keys
                            if (true == NumericTypes.Contains(field.TableField.FieldType))
                            {
                                var value = Convert.ChangeType
                                 (dataObjectAttrValue, field.TableField.FieldType);

                                if (Convert.ToInt64(value) <= 0 && field.TableField.IsKey == true)
                                {
                                    continue;
                                }
                            }

                            columnsValues.Add(field.TableField.ColumnName, 
                            Convert.ChangeType(dataObjectAttrValue, field.TableField.FieldType));
                        }
                    }
                    //end-inner-if

                }//end-foreach

                try
                {
                    if (0 == whereConditions.Count)
                    {
                        throw new Exception("Update Error: 
                        Cannot update data object unless there is at least one WHERE CONDITION. 
                        Please revise the update procedures on " + dataObject.GetType().Name);
                    }
                    else
                    {
                        status = DBRoutines.UPDATE(tableName: finalDataSourceName, 
                        columnsValues: columnsValues, wherePart: whereConditions);
                    }
                }
                catch (Exception ex)
                {
                    throw ex.InnerException;
                }

            }//end-outer-if

            return status;
        }


        public virtual bool Delete(T dataObject, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            long ID = 0;
            string finalDataSourceName = string.Empty;
            Dictionary<string,object> whereConditions = new Dictionary<string,object>();

            DataField IDField;
            string ObjectFieldNameWithIDAttribute = string.Empty;

            //
            // Decide the DataSource Name 
            if (false == string.IsNullOrEmpty(dataSourceName))
            {
                finalDataSourceName = dataSourceName;
            }
            else if (false == string.IsNullOrEmpty(Schema.DataSourceName))
            {
                finalDataSourceName = Schema.DataSourceName;
            }
            else
            {
                throw new Exception("Insert Error: 
                No Data Source was provided in the " + dataObject.GetType().Name + 
                ". Kindly review the class definition or the data mapper definition.");
            }
            
            //
            // Decide the IDField value
            IDField = Schema.DataFields.Find(field => field.TableField != null 
            && field.TableField.IsIDField == true);
            
            if(null == IDField)
            {
                throw new Exception("Delete Error: 
                The Data Model does not have IDField property. 
                Kindly mark the properties of " + typeof(T).Name + " with [IsIDField].");
            }

            //
            // Get the object field that is marked with the IsIDField attribute
            var dataObjectAttr = dataObject.GetType().GetProperty(IDField.Name);

            var dataObjectAttrValue = dataObjectAttr.GetValue(dataObject, null);

            if(dataObjectAttrValue == null)
            {
                throw new Exception("The ID Field's value is to NULL. 
                Kindly set the value of the ID Field for the object of type: " + typeof(T).Name);
            }
            else
            {
                whereConditions.Add(IDField.TableField.ColumnName, 
                Convert.ChangeType(dataObjectAttrValue, IDField.TableField.FieldType));
                return DBRoutines.DELETE
                (tableName: finalDataSourceName, wherePart: whereConditions);

            }//end-inner-if-else
        }


        public virtual T GetById(long id, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            DataTable dt = new DataTable();
            string finalDataSourceName = string.Empty;

            int maximumLimit = 1;
            List<string> thisModelTableColumns;
            List<SqlJoinRelation> dataRelations;
            Dictionary<string,object> condition;

            string errorMessage = string.Empty;

            //Get our table columns from the schema
            thisModelTableColumns = Schema.DataFields
                .Where(field => field.TableField != null)
                .Select<DataField, string>(field => field.TableField.ColumnName)
                .ToList<string>();

            //Decide on the Data Source Name
            finalDataSourceName = (string.IsNullOrEmpty(dataSourceName) ? 
            Schema.DataSourceName : dataSourceName);

            //Validate the presence of the ID
            if (id <= 0)
            {
                errorMessage = String.Format("The ID Field is either null or zero. 
                Kindly pass a valid ID. Class name: \"{0}\".", typeof(T).Name);
                throw new Exception(errorMessage);
            }

            //Construct the record ID condition
            condition = new Dictionary<string,object>();
            condition.Add(Schema.IDFieldName, id);

            //Proceed with getting the data
            if (Schema.DataSourceType == GLOBALS.DataSource.Type.DBTable)
            {
                dt = DBRoutines.SELECT(finalDataSourceName, 
                thisModelTableColumns, condition, maximumLimit);
            }

            //It will either return a data table with one row or zero rows
            if (dt.Rows.Count == 0)
            {
                return (T)Activator.CreateInstance(typeof(T));
            }
            else
            {
                return dt.ConvertToList<T>().FirstOrDefault<T>() ?? null;
            }
        }

        public virtual IEnumerable<T> Get(Expression<func<T, 
        bool>> predicate, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            DataTable dt = new DataTable();

            if (predicate == null) 
            {
                var errorMessage = string.Format
                ("There is no defined Predicate. {0} ",typeof(T).Name);
                
                throw new Exception(errorMessage);
            }
            else 
            {
                CustomExpressionVisitor ev = new CustomExpressionVisitor();
                
                string whereClause = ev.Translate(predicate);

                if (string.IsNullOrEmpty(dataSourceName))
                {

                    if (string.IsNullOrEmpty(whereClause))
                    {
                        dt = DBRoutines.SELECT(Schema.DataSourceName);
                    }
                    else
                    {
                        dt = DBRoutines.SELECT(Schema.DataSourceName, whereClause);
                    }
                }
                else 
                {
                    if (string.IsNullOrEmpty(whereClause))
                    {
                        dt = DBRoutines.SELECT(dataSourceName);
                    }
                    else
                    {
                        dt = DBRoutines.SELECT(dataSourceName, whereClause);
                    }
                }
            }

            return dt.ConvertToList<T>();
        }


        public virtual IEnumerablet<T> Get(Dictionary<string,object> 
        whereConditions, int limit = 25, string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            DataTable dt = new DataTable();
            string finalDataSourceName = string.Empty;
            
            List<string> thisModelTableColumns;
            List<SqlJoinRelation> dataRelations;

            string errorMessage = string.Empty;

            //Get our table columns from the schema
            thisModelTableColumns = Schema.DataFields
                .Where(field => field.TableField != null)
                .Select<Datafield, string>(field => field.TableField.ColumnName)
                .ToList<string>();

            //Decide on the Data Source Name
            finalDataSourceName = (string.IsNullOrEmpty(dataSourceName) ? 
            Schema.DataSourceName : dataSourceName);
            
            //Validate the presence of the where conditions
            if (whereConditions == null || whereConditions.Count  == 0)
            {
                errorMessage = String.Format("The \"whereConditions\" 
                parameter is either null or empty. 
                Kindly pass a valid \"whereConditions\" parameter. 
                Class name: \"{0}\".", typeof(T).Name);
                throw new Exception(errorMessage);
            }

            //Proceed with getting the data
            if (Schema.DataSourceType == GLOBALS.DataSource.Type.DBTable)
            {
                dt = DBRoutines.SELECT(finalDataSourceName, 
                thisModelTableColumns, whereConditions, limit);
            }

            return dt.ConvertToList<T>();
        }

        public virtual IEnumerable<T> GetAll(string dataSourceName = null, 
        GLOBALS.DataSource.Type dataSourceType = GLOBALS.DataSource.Type.Default)
        {
            DataTable dt = new DataTable();
            string finalDataSourceName = string.Empty;

            int maximumLimit = 0;
            List<string> thisModelTableColumns;
            Dictionary<string,object> whereConditions = null;
            List<SqlJoinRelation> dataRelations;

            //Get our table columns from the schema
            thisModelTableColumns = Schema.DataFields
                .Where(field => field.TableField != null)
                .Select<DataField, string>(field => field.TableField.ColumnName)
                .ToList<string>();

            //Decide on the Data Source Name
            finalDataSourceName = (string.IsNullOrEmpty(dataSourceName) ? 
            Schema.DataSourceName : dataSourceName);
            
            //Proceed with getting the data
            if (Schema.DataSourceType == GLOBALS.DataSource.Type.DBTable)
            {

                dt = DBRoutines.SELECT
                (finalDataSourceName, thisModelTableColumns, whereConditions, maximumLimit);
            }

            return dt.ConvertToList<T>();
        }

        public virtual IEnumerablet<T> GetAll(string SQL_QUERY)
        {
            DataTable dt = DBRoutines.SELECTFROMSQL(SQL_QUERY);

            return dt.ConvertToList<T>();
        }

        public virtual int Insert(string sql)
        {
            int id = DBRoutines.INSERT(sql);
            
            return id;
        }

        public virtual bool Update(string sql)
        {
            bool status = DBRoutines.UPDATE(sql);

            return status;
        }

        public virtual bool Delete(string sql)
        {
            bool status = DBRoutines.DELETE(sql);

            return status;
        }
    }

As shown above from the class definition, it extends DataModel, DataModel is a class that is supposed to deal with model persistence at a later stage, and due to the implementation of this class is incomplete. I will not include or discuss it for the time being.

DBRoutines is the library that I previously discussed which deals with all SQL Inserts/Update/Delete and Select.

Using DataAccess, you have the option to send Native SQL Command or send expression Predicate.

In the last stage, there is DataMapper that extends DataAccess<T> to give you the possibility to add your own functionality along with the DataAccess functionality, or overriding DataAccess functions behavior, such as below example:

public class SitesDepartmentsDataMapper : DataAccess<SiteDepartment>
{
        /// <summary>
        /// Given a Site's ID, return the list of its Departments.
        /// </summary>
        /// <param name="SiteID">Site.ID (int)</param>
        /// <returns>List of SiteDepartment objects</returns>
        public List<SiteDepartment> GetDepartmentsForSite(long SiteID)
        {
               Dictionary<string, object> 
               condition = new Dictionary<string,object>();
               condition.Add("SiteID", SiteID);
 
               try
               {
                       return Get(whereConditions: condition, 
                       limit: 0).ToList<SiteDepartment>();
               }
               catch(Exception ex)
               {
                       throw ex.InnerException;
               }
        } 
}

The Code above will only get the SiteDepratments Object without getting the relations objects from Sites/Departmnets

To get the relations object with the object you need to call it like below.

Extension Method Include which takes an Array of parameters as a parameter and it will look like below. Kindly note that Include is an extentionMethod you can find under Helpers/DataAccessExtensions under the Project.

 return Get(whereConditions: condition, limit: 0).Include
(item=>item.Site,Item=>item.Department).ToList<SitesDepartment>

Or if you want to include only sites, the call will look like this:

 return Get(whereConditions: condition, limit: 0).Include
(item=>item.Site).ToList<SitesDepartment>

Finally, add all your DataMapper Objects to DBStorage which acts as thread safe singleton class to facilitate the access of your objects using a centralized location.

public sealed class DataStorage
{
        /***
         * DataStorage Repositories
         */
        
        public SitesDepartmentsDataMapper Sites = new SitesDepartmentsDataMapper();
        
        /***
         * Singleton implementation with an 
         * attempted thread-safety using double-check locking
         * @source: http://csharpindepth.com/articles/general/singleton.aspx
         */
        // internal datastorage singleton container
        private static DataStorage _instance = null;
 
        // lock for thread-safety laziness
        private static readonly object _mutex = new object();
 
        // empty constructor
        private DataStorage() { }
 
        //The only public method, used to obtain an instance of DataStorage
        public static DataStorage Instance
        {
               get
               {
                       if (_instance == null)
                       {
                               lock(_mutex)
                               {
                                      if (_instance == null)
                                      {
                                              _instance = new DataStorage();
                                      }
                               }
                       }
 
                       return _instance;
               }
        }
}

Points of Interest

The funny thing about implementing my own ORM that I divided in .NET Reflections and generic types was fun to a certain degree and annoying to some degree since C# is a strongly typed language.

History

• Version 1.0 - > 2014-12-02