Tiny SQL Entity Framework Library to Present Some Reflection and Custom Attributes

The framework usable for select (whole table), insert, delete and update on an SQL database, using classes.

The point is to present the use of reflection in an SQL framework. The developer can create his/her own entity with following requirements:

• Constructor of MyEntityFramework class requires a valid connection string!  
• Each class name must mach with the table name in the database!
• Each class must contain a parameter less constructor!
• Every property names must must be public with accessible get,set!
• These properties must have valid DataMapping: [DataMapping(columnName,propertyName)]!
• For Insert, Delete or Update must mark the primary key property with Pkey attribute!
• The framework doesn't maintain the auto-increment fields! To avoid any issue don't define them!
• Must use LINQ for compound selection!
• Please pay attention to nullable fields when define the properties!

The framework doesn't contain methods for stored procedures and any complicated SQL transactions!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;

namespace MyEntityFramework
{
    //Base class, definiates the basic database methods
    public abstract class DBase
    {
        public SqlConnection con { get; private set; }
        public Type ReflectionType { get; set; }
        public ICollection<PropertyInfo> Props { get; set; }
        public DBase(string connectionstring)
        {
            con = new SqlConnection();
            con.ConnectionString = connectionstring;

            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (con != null) con.Close();
            }
        }

        abstract public void Delete(ICollection<object> Objects);
        abstract public void Delete(object Object);
        abstract public void Insert(ICollection<object> Objects);
        abstract public void Insert(object Object);
        abstract public void Update(ICollection<object> Objects);
        abstract public void Update(object Object);
        abstract protected void LoadClassFromSQLReader(object Object, IDataRecord row, object[] dataMappingAttributes);
        abstract protected void FillProperties(object Object);
    }


    //Simple Entity FrameWork for Select, Insert, Delete, Update
    public class MyEntityFrameWork : DBase
    {
        public MyEntityFrameWork(string connectionstring)
            : base(connectionstring)
        {
        }

        /// <summary>
        /// Delete a record from the database table
        /// </summary>
        /// <param name="Objects">A class object</param>
        public override void Delete(object Object)
        {
            ICollection<object> collection = new List<object>();
            collection.Add(Object);
            try
            {
                Delete(collection);
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// Delete records from the database table
        /// </summary>
        /// <param name="Objects">Collection of class objects</param>
        public override void Delete(ICollection<object> Objects)
        {
            foreach (object o in Objects)
            {
                FillProperties(o);
                object property = null;
                string colname = "";
                foreach (PropertyInfo pi in Props)
                {
                    object[] attributes = pi.GetCustomAttributes(true);
                    foreach (Attribute attr in attributes)
                    {
                        Pkey pkeyattr = attr as Pkey;
                        if (pkeyattr != null)
                        {
                            property = pi.GetValue(o);
                            colname = pi.Name;
                            break;
                        }
                    }
                }

                if (colname == "") throw new KeyNotFoundException(String.Format("Couldn't find property with primary key attribute in the class {0}", ReflectionType.Name));

                string sqlCommandLine = String.Format("DELETE FROM {0} WHERE({1}={2})", ReflectionType.Name, colname, property);
                SqlCommand cmd = new SqlCommand(sqlCommandLine, con);

                try
                {
                    if (RunSqlCommand(cmd) == 0) throw new KeyNotFoundException(String.Format("Couldn't find any columns or values with name of {0} and value {1}!", colname, property));
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Insert a record into the database table
        /// </summary>
        /// <param name="Objects">A class object</param>
        public override void Insert(object Object)
        {
            ICollection<object> collection = new List<object>();
            collection.Add(Object);

            try
            {
                Insert(collection);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Insert records into the database table
        /// </summary>
        /// <param name="Objects">Collection of class objects</param
        public override void Insert(ICollection<object> Objects)
        {
            foreach (object o in Objects)
            {
                FillProperties(o);

                IList<string> columnList = new List<string>();
                IList<object> valueList = new List<object>();

                foreach (PropertyInfo pi in Props)
                {
                    object property = pi.GetValue(o);
                    valueList.Add(property);
                    columnList.Add(pi.Name);
                }
                string columns = string.Join(",", columnList);
                string values = string.Join(",@", columnList);

                string sqlCommandLine = String.Format("INSERT INTO {0} ({1}) VALUES(@{2})", ReflectionType.Name, columns, values);
                SqlCommand cmd = new SqlCommand(sqlCommandLine, con);

                for (int i = 0; i < columnList.Count; i++)
                {
                    cmd.Parameters.AddWithValue(columnList[i], string.IsNullOrEmpty(valueList[i].ToString())?DBNull.Value:valueList[i]);
                }

                try
                {
                    RunSqlCommand(cmd);
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Update a record in the database table
        /// </summary>
        /// <param name="Objects">A class object</param
        public override void Update(object Object)
        {
            ICollection<object> collection = new List<object>();
            collection.Add(Object);
            try
            {
                Update(collection);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Update records in the database table
        /// </summary>
        /// <param name="Objects">Collection of class objects</param
        public override void Update(ICollection<object> Objects)
        {
            foreach (object o in Objects)
            {
                FillProperties(o);

                PropertyInfo pkeypi = getPrimaryKey(Props);

                object pkeyproperty = null;
                string pkeycolname = "";
                if (pkeypi != null)
                {
                    pkeyproperty = pkeypi.GetValue(o);
                    pkeycolname = pkeypi.Name;
                }

                if (pkeycolname == "") throw new KeyNotFoundException(String.Format("Couldn't find property with primary key attribute in the class {0}", ReflectionType.Name));

                Dictionary<string, object> updateitems = new Dictionary<string, object>();
                foreach (PropertyInfo pi in Props)
                {
                    //Must skip the primary key
                    if (pi.Name != pkeypi.Name)
                    {
                        object property = pi.GetValue(o);
                        updateitems.Add(pi.Name, property);
                    }
                }

                string updatelinepart = string.Join(",", updateitems.Select(x => x.Key + "=@" + x.Key).ToArray());

                string sqlCommandLine = String.Format("UPDATE {0} SET {1} WHERE({2}={3})", ReflectionType.Name, updatelinepart, pkeycolname, pkeyproperty);

                SqlCommand cmd = new SqlCommand(sqlCommandLine, con);

                foreach (KeyValuePair<string, object> item in updateitems)
                {
                    cmd.Parameters.AddWithValue("@" + item.Key, string.IsNullOrEmpty(item.Value.ToString()) ? DBNull.Value : item.Value);
                }

                try
                {
                    if (RunSqlCommand(cmd) == 0) throw new KeyNotFoundException(String.Format("Couldn't find any columns or values with name of {0} and value {1}!", pkeycolname, pkeyproperty));
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Returns with a collection of the required objects
        /// </summary>
        /// <typeparam name="T">Generic type</typeparam>
        /// <returns></returns>
        public ICollection<T> GetList<T>()
        {
            object item = null;
            try
            {
                item = Activator.CreateInstance<T>();
                FillProperties(item);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            string sqlSelect = String.Format("SELECT * FROM {0}", ReflectionType.Name);

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlSelect;

            SqlDataReader dr = null;
            try
            {
                con.Open();
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            if (dr.HasRows)
            {
                ICollection<T> list = new List<T>();
                object[] dataMappingAttributes = null;
                while (dr.Read())
                {
                    try
                    {
                        item = Activator.CreateInstance<T>();
                        Type ClassType = item.GetType();
                        if (dataMappingAttributes == null) dataMappingAttributes = ClassType.GetCustomAttributes(typeof(DataMappingAttribute), false);
                        if (dataMappingAttributes.Count() < 1) throw new KeyNotFoundException(String.Format("Couldn't find DataMapping attribute(s) for the class {0}!", ClassType.Name));

                        LoadClassFromSQLReader(item, (IDataRecord)dr, dataMappingAttributes);

                        list.Add((T)item);
                    }
                    catch
                    {
                        dr.Close();
                        con.Close();
                        throw;
                    }
                }
                dr.Close();
                con.Close();
                return list;
            }
            return null;
        }

        /// <summary>
        /// Fills a class from an IDataRecord of SqlDataReader matching columns with public properties
        /// </summary>
        /// <param name="Object">class</param>
        /// <param name="dr">IDataRecord</param>
        protected override void LoadClassFromSQLReader(object Object, IDataRecord row, object[] dataMappingAttributes)
        {
            Type ClassType = Object.GetType();

            for (int colIndex = 0; colIndex < row.FieldCount; colIndex++)
            {
                string colName = row.GetName(colIndex);
                PropertyInfo propertyInfo = null;


                foreach (DataMappingAttribute dma in dataMappingAttributes)
                {
                    if (dma.ColumnName == colName)
                    {
                        propertyInfo = ClassType.GetProperty(dma.PropertyName);
                        break;
                    }
                }

                if (propertyInfo != null)
                {
                    try
                    {
                        propertyInfo.SetValue(Object, string.IsNullOrEmpty(row.GetValue(colIndex).ToString())?null:row.GetValue(colIndex));
                    }
                    catch (ArgumentException ex)
                    {
                        throw new ArgumentException(String.Format("Type mismatch error at the {0} property! {1}", colName, ex.Message));
                    }
                }
                else
                {
                    throw new KeyNotFoundException(String.Format("Couldn't find any property with the name {0}! It can be caused by: Invalid mapping attribute or Invalid property. A property should be public with proper accessors and valid DataMapping attribute!", colName));
                }
            }
        }

        /// <summary>
        /// Gets the type of the object and it's public properties by reflection
        /// </summary>
        /// <param name="SomeClass"></param>
        protected override void FillProperties(object SomeClass)
        {
            try
            {
                ReflectionType = SomeClass.GetType();

                Props = ReflectionType.GetProperties(BindingFlags.Public |
                                                     BindingFlags.Instance);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Runs an SqlCommand on the database
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <returns>Counts of affected rows</returns>
        private int RunSqlCommand(SqlCommand cmd)
        {
            int rowCount = 0;

            try
            {
                con.Open();
                rowCount = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open) con.Close();
            }
            return rowCount;
        }

        /// <summary>
        /// Returns with a Pkey PropertyInfo
        /// </summary>
        /// <param name="pis">Collection of PropertyInfos</param>
        /// <returns></returns>
        private PropertyInfo getPrimaryKey(ICollection<PropertyInfo> pis)
        {
            foreach (PropertyInfo pi in pis)
            {
                object[] attributes = pi.GetCustomAttributes(true);
                foreach (Attribute attr in attributes)
                {
                    Pkey pkeyattr = attr as Pkey;
                    if (pkeyattr != null)
                    {
                        return pi;
                    }
                }
            }
            return null;
        }
    }

    //DataMapping for ColumnName=PropertyName
    [System.AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
    public class DataMappingAttribute : System.Attribute
    {
        public string ColumnName { get; set; }
        public string PropertyName { get; set; }

        public DataMappingAttribute(string columname, string propertyname)
        {
            ColumnName = columname;
            PropertyName = propertyname;
        }
    }

    //DataMapping for primary key property
    [System.AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class Pkey : System.Attribute
    {
    }
}