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!
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. using System.Data;
  7. using System.Reflection;
  8. using System.Data.SqlClient;
  10. namespace MyEntityFramework
  11. {
  12. //Base class, definiates the basic database methods
  13. public abstract class DBase
  14. {
  15. public SqlConnection con { get; private set; }
  16. public Type ReflectionType { get; set; }
  17. public ICollection<PropertyInfo> Props { get; set; }
  18. public DBase(string connectionstring)
  19. {
  20. con = new SqlConnection();
  21. con.ConnectionString = connectionstring;
  23. try
  24. {
  25. con.Open();
  26. }
  27. catch (Exception ex)
  28. {
  29. throw new Exception(ex.Message);
  30. }
  31. finally
  32. {
  33. if (con != null) con.Close();
  34. }
  35. }
  37. abstract public void Delete(ICollection<object> Objects);
  38. abstract public void Delete(object Object);
  39. abstract public void Insert(ICollection<object> Objects);
  40. abstract public void Insert(object Object);
  41. abstract public void Update(ICollection<object> Objects);
  42. abstract public void Update(object Object);
  43. abstract protected void LoadClassFromSQLReader(object Object, IDataRecord row, object[] dataMappingAttributes);
  44. abstract protected void FillProperties(object Object);
  45. }
  48. //Simple Entity FrameWork for Select, Insert, Delete, Update
  49. public class MyEntityFrameWork : DBase
  50. {
  51. public MyEntityFrameWork(string connectionstring)
  52. : base(connectionstring)
  53. {
  54. }
  56. /// <summary>
  57. /// Delete a record from the database table
  58. /// </summary>
  59. /// <param name="Objects">A class object</param>
  60. public override void Delete(object Object)
  61. {
  62. ICollection<object> collection = new List<object>();
  63. collection.Add(Object);
  64. try
  65. {
  66. Delete(collection);
  67. }
  68. catch
  69. {
  70. throw;
  71. }
  72. }
  73. /// <summary>
  74. /// Delete records from the database table
  75. /// </summary>
  76. /// <param name="Objects">Collection of class objects</param>
  77. public override void Delete(ICollection<object> Objects)
  78. {
  79. foreach (object o in Objects)
  80. {
  81. FillProperties(o);
  82. object property = null;
  83. string colname = "";
  84. foreach (PropertyInfo pi in Props)
  85. {
  86. object[] attributes = pi.GetCustomAttributes(true);
  87. foreach (Attribute attr in attributes)
  88. {
  89. Pkey pkeyattr = attr as Pkey;
  90. if (pkeyattr != null)
  91. {
  92. property = pi.GetValue(o);
  93. colname = pi.Name;
  94. break;
  95. }
  96. }
  97. }
  99. if (colname == "") throw new KeyNotFoundException(String.Format("Couldn't find property with primary key attribute in the class {0}", ReflectionType.Name));
  101. string sqlCommandLine = String.Format("DELETE FROM {0} WHERE({1}={2})", ReflectionType.Name, colname, property);
  102. SqlCommand cmd = new SqlCommand(sqlCommandLine, con);
  104. try
  105. {
  106. 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));
  107. }
  108. catch
  109. {
  110. throw;
  111. }
  112. }
  113. }
  115. /// <summary>
  116. /// Insert a record into the database table
  117. /// </summary>
  118. /// <param name="Objects">A class object</param>
  119. public override void Insert(object Object)
  120. {
  121. ICollection<object> collection = new List<object>();
  122. collection.Add(Object);
  124. try
  125. {
  126. Insert(collection);
  127. }
  128. catch
  129. {
  130. throw;
  131. }
  132. }
  134. /// <summary>
  135. /// Insert records into the database table
  136. /// </summary>
  137. /// <param name="Objects">Collection of class objects</param
  138. public override void Insert(ICollection<object> Objects)
  139. {
  140. foreach (object o in Objects)
  141. {
  142. FillProperties(o);
  144. IList<string> columnList = new List<string>();
  145. IList<object> valueList = new List<object>();
  147. foreach (PropertyInfo pi in Props)
  148. {
  149. object property = pi.GetValue(o);
  150. valueList.Add(property);
  151. columnList.Add(pi.Name);
  152. }
  153. string columns = string.Join(",", columnList);
  154. string values = string.Join(",@", columnList);
  156. string sqlCommandLine = String.Format("INSERT INTO {0} ({1}) VALUES(@{2})", ReflectionType.Name, columns, values);
  157. SqlCommand cmd = new SqlCommand(sqlCommandLine, con);
  159. for (int i = 0; i < columnList.Count; i++)
  160. {
  161. cmd.Parameters.AddWithValue(columnList[i], string.IsNullOrEmpty(valueList[i].ToString())?DBNull.Value:valueList[i]);
  162. }
  164. try
  165. {
  166. RunSqlCommand(cmd);
  167. }
  168. catch
  169. {
  170. throw;
  171. }
  172. }
  173. }
  175. /// <summary>
  176. /// Update a record in the database table
  177. /// </summary>
  178. /// <param name="Objects">A class object</param
  179. public override void Update(object Object)
  180. {
  181. ICollection<object> collection = new List<object>();
  182. collection.Add(Object);
  183. try
  184. {
  185. Update(collection);
  186. }
  187. catch
  188. {
  189. throw;
  190. }
  191. }
  193. /// <summary>
  194. /// Update records in the database table
  195. /// </summary>
  196. /// <param name="Objects">Collection of class objects</param
  197. public override void Update(ICollection<object> Objects)
  198. {
  199. foreach (object o in Objects)
  200. {
  201. FillProperties(o);
  203. PropertyInfo pkeypi = getPrimaryKey(Props);
  205. object pkeyproperty = null;
  206. string pkeycolname = "";
  207. if (pkeypi != null)
  208. {
  209. pkeyproperty = pkeypi.GetValue(o);
  210. pkeycolname = pkeypi.Name;
  211. }
  213. if (pkeycolname == "") throw new KeyNotFoundException(String.Format("Couldn't find property with primary key attribute in the class {0}", ReflectionType.Name));
  215. Dictionary<string, object> updateitems = new Dictionary<string, object>();
  216. foreach (PropertyInfo pi in Props)
  217. {
  218. //Must skip the primary key
  219. if (pi.Name != pkeypi.Name)
  220. {
  221. object property = pi.GetValue(o);
  222. updateitems.Add(pi.Name, property);
  223. }
  224. }
  226. string updatelinepart = string.Join(",", updateitems.Select(x => x.Key + "=@" + x.Key).ToArray());
  228. string sqlCommandLine = String.Format("UPDATE {0} SET {1} WHERE({2}={3})", ReflectionType.Name, updatelinepart, pkeycolname, pkeyproperty);
  230. SqlCommand cmd = new SqlCommand(sqlCommandLine, con);
  232. foreach (KeyValuePair<string, object> item in updateitems)
  233. {
  234. cmd.Parameters.AddWithValue("@" + item.Key, string.IsNullOrEmpty(item.Value.ToString()) ? DBNull.Value : item.Value);
  235. }
  237. try
  238. {
  239. 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));
  240. }
  241. catch
  242. {
  243. throw;
  244. }
  245. }
  246. }
  248. /// <summary>
  249. /// Returns with a collection of the required objects
  250. /// </summary>
  251. /// <typeparam name="T">Generic type</typeparam>
  252. /// <returns></returns>
  253. public ICollection<T> GetList<T>()
  254. {
  255. object item = null;
  256. try
  257. {
  258. item = Activator.CreateInstance<T>();
  259. FillProperties(item);
  260. }
  261. catch (Exception ex)
  262. {
  263. throw new Exception(ex.Message);
  264. }
  266. string sqlSelect = String.Format("SELECT * FROM {0}", ReflectionType.Name);
  268. SqlCommand cmd = new SqlCommand();
  269. cmd.Connection = con;
  270. cmd.CommandType = CommandType.Text;
  271. cmd.CommandText = sqlSelect;
  273. SqlDataReader dr = null;
  274. try
  275. {
  276. con.Open();
  277. dr = cmd.ExecuteReader();
  278. }
  279. catch (Exception ex)
  280. {
  281. throw new Exception(ex.Message);
  282. }
  284. if (dr.HasRows)
  285. {
  286. ICollection<T> list = new List<T>();
  287. object[] dataMappingAttributes = null;
  288. while (dr.Read())
  289. {
  290. try
  291. {
  292. item = Activator.CreateInstance<T>();
  293. Type ClassType = item.GetType();
  294. if (dataMappingAttributes == null) dataMappingAttributes = ClassType.GetCustomAttributes(typeof(DataMappingAttribute), false);
  295. if (dataMappingAttributes.Count() < 1) throw new KeyNotFoundException(String.Format("Couldn't find DataMapping attribute(s) for the class {0}!", ClassType.Name));
  297. LoadClassFromSQLReader(item, (IDataRecord)dr, dataMappingAttributes);
  299. list.Add((T)item);
  300. }
  301. catch
  302. {
  303. dr.Close();
  304. con.Close();
  305. throw;
  306. }
  307. }
  308. dr.Close();
  309. con.Close();
  310. return list;
  311. }
  312. return null;
  313. }
  315. /// <summary>
  316. /// Fills a class from an IDataRecord of SqlDataReader matching columns with public properties
  317. /// </summary>
  318. /// <param name="Object">class</param>
  319. /// <param name="dr">IDataRecord</param>
  320. protected override void LoadClassFromSQLReader(object Object, IDataRecord row, object[] dataMappingAttributes)
  321. {
  322. Type ClassType = Object.GetType();
  324. for (int colIndex = 0; colIndex < row.FieldCount; colIndex++)
  325. {
  326. string colName = row.GetName(colIndex);
  327. PropertyInfo propertyInfo = null;
  330. foreach (DataMappingAttribute dma in dataMappingAttributes)
  331. {
  332. if (dma.ColumnName == colName)
  333. {
  334. propertyInfo = ClassType.GetProperty(dma.PropertyName);
  335. break;
  336. }
  337. }
  339. if (propertyInfo != null)
  340. {
  341. try
  342. {
  343. propertyInfo.SetValue(Object, string.IsNullOrEmpty(row.GetValue(colIndex).ToString())?null:row.GetValue(colIndex));
  344. }
  345. catch (ArgumentException ex)
  346. {
  347. throw new ArgumentException(String.Format("Type mismatch error at the {0} property! {1}", colName, ex.Message));
  348. }
  349. }
  350. else
  351. {
  352. 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));
  353. }
  354. }
  355. }
  357. /// <summary>
  358. /// Gets the type of the object and it's public properties by reflection
  359. /// </summary>
  360. /// <param name="SomeClass"></param>
  361. protected override void FillProperties(object SomeClass)
  362. {
  363. try
  364. {
  365. ReflectionType = SomeClass.GetType();
  367. Props = ReflectionType.GetProperties(BindingFlags.Public |
  368. BindingFlags.Instance);
  369. }
  370. catch (Exception ex)
  371. {
  372. throw new Exception(ex.Message);
  373. }
  374. }
  376. /// <summary>
  377. /// Runs an SqlCommand on the database
  378. /// </summary>
  379. /// <param name="cmd">SqlCommand</param>
  380. /// <returns>Counts of affected rows</returns>
  381. private int RunSqlCommand(SqlCommand cmd)
  382. {
  383. int rowCount = 0;
  385. try
  386. {
  387. con.Open();
  388. rowCount = cmd.ExecuteNonQuery();
  389. }
  390. catch (Exception ex)
  391. {
  392. throw new Exception(ex.Message);
  393. }
  394. finally
  395. {
  396. if (con.State == ConnectionState.Open) con.Close();
  397. }
  398. return rowCount;
  399. }
  401. /// <summary>
  402. /// Returns with a Pkey PropertyInfo
  403. /// </summary>
  404. /// <param name="pis">Collection of PropertyInfos</param>
  405. /// <returns></returns>
  406. private PropertyInfo getPrimaryKey(ICollection<PropertyInfo> pis)
  407. {
  408. foreach (PropertyInfo pi in pis)
  409. {
  410. object[] attributes = pi.GetCustomAttributes(true);
  411. foreach (Attribute attr in attributes)
  412. {
  413. Pkey pkeyattr = attr as Pkey;
  414. if (pkeyattr != null)
  415. {
  416. return pi;
  417. }
  418. }
  419. }
  420. return null;
  421. }
  422. }
  424. //DataMapping for ColumnName=PropertyName
  425. [System.AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
  426. public class DataMappingAttribute : System.Attribute
  427. {
  428. public string ColumnName { get; set; }
  429. public string PropertyName { get; set; }
  431. public DataMappingAttribute(string columname, string propertyname)
  432. {
  433. ColumnName = columname;
  434. PropertyName = propertyname;
  435. }
  436. }
  438. //DataMapping for primary key property
  439. [System.AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
  440. public class Pkey : System.Attribute
  441. {
  442. }
  443. }