public virtual ActionResult Create() { try { var table = new TableStruct(this); var fields = from f in table.Fields where f.Key != table.Id.Key select (string.Format("`{0}`", f.Key)); var values = from f in table.Fields where f.Key != table.Id.Key select ("?" + f.Key); var sqlBuilder = new StringBuilder(); sqlBuilder.AppendFormat("INSERT INTO `{0}` ({1}) VALUES ({2})", table.Name, string.Join(", ", fields), string.Join(", ", values)); var sqlParameters = table.Fields.Select(field => new SqlParameter(field.Key, field.Value)).ToList(); Id = DatabaseController.ExecuteInsertQuery(sqlBuilder.ToString(), sqlParameters.ToArray()); return new ActionResult(true, string.Format("{0} successfully created.", table.RealName)); } catch (Exception e) { return new ActionResult(false, e.Message); } }
public virtual ActionResult Destroy() { try { var table = new TableStruct(this); var sqlBuilder = new StringBuilder(); sqlBuilder.AppendFormat("DELETE FROM `{0}` WHERE `{1}` = {2}", table.Name, table.Id.Key, table.Id.Value); var result = DatabaseController.ExecuteNonQuery(sqlBuilder.ToString()); if (result > 0) { return new ActionResult(true, string.Format("{0} successfully destroyed.", table.RealName)); } return new ActionResult(false, string.Format("{0} destroyed unsuccessful.", table.RealName)); } catch (Exception e) { return new ActionResult(false, e.Message); } }
public void Inspect() { TableStruct ts = new TableStruct(this); Console.WriteLine(ts.ToString()); }
public virtual ActionResult Update() { try { var table = new TableStruct(this); var fields = from f in table.Fields where f.Key != table.Id.Key select (string.Format("`{0}` = ?{0}", f.Key.ToColumnName())); var sqlBuilder = new StringBuilder(); sqlBuilder.AppendFormat("UPDATE `{0}` SET {1} WHERE `{2}` = {3}", table.Name, string.Join(", ", fields), table.Id.Key, table.Id.Value); var sqlParameters = table.Fields.Where(field => field.Key != table.Id.Key).Select( field => new SqlParameter(field.Key, field.Value)).ToArray(); DatabaseController.ExecuteNonQuery(sqlBuilder.ToString(), sqlParameters); return new ActionResult(true, string.Format("{0} successfully updated.", table.RealName)); } catch (Exception e) { return new ActionResult(false, e.Message); } }
public virtual void Read(int id) { Id = id; Type type = GetType(); PropertyInfo[] properties = type.GetProperties(); var table = new TableStruct(this); var sqlBuilder = new StringBuilder(); sqlBuilder.AppendFormat("SELECT * FROM `{0}` WHERE `{1}` = {2}", table.Name, table.Id.Key, table.Id.Value); var dataTable = DatabaseController.ExecuteSelectQuery(sqlBuilder.ToString()); if(dataTable.Rows.Count == 0) { #region --- Reset Property Values to Default --- foreach (PropertyInfo property in properties) { var name = property.Name; if (name.Contains("Number")) { property.SetValue(this, 0, null); continue; } if (name.Contains("Amount")) { property.SetValue(this, 0m, null); continue; } if (name.Contains("Date")) { property.SetValue(this, new DateTime(), null); continue; } if (name.Contains("Id")) { property.SetValue(this, 0, null); continue; } if (name.Contains("Is")) { property.SetValue(this, false, null); continue; } property.SetValue(this, "", null); } #endregion return; } // Iterate through class properties using Reflection foreach (PropertyInfo property in properties) { var columnName = property.Name.ToColumnName(); if (dataTable.Rows[0][columnName] != null) { if (property.Name == "Id") continue; property.SetValue(this, dataTable.Rows[0][columnName], null); } } }