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

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
    {
    }
}