Henooh Database Model

What is Henooh Database Model?

Henooh Database Model is Henooh's way of implementing Model portion of Model-View-Controller(MVC) architectural pattern. The primary focus of Henooh Database Model was to apply a set a standard design and model - that is designed to be compatable in Henooh Desktop Applications, Henooh Web Services and Henooh Universal Services.

Henooh Database Model design specify that two separate projects to be created and maintained.

Steps of implementing Henooh Database Model.

Henooh Database Portable Project

[ProjectName] Database Portable Project contain the interface of the database model and data tables.

Data tables are represented in each separate classes, and columns are represented as properties within a class. Without specific design specifications and rules, database tables and columns were created with total disregard for portability, reuseability or adaptability. Henooh Database Model separates Model portion of Model-View-Controller architectural pattern, and implements specificiations on how Model portion should be coded for Henooh Framework.

Henooh Database Interface

[ProjectName] Database Interface is an abstraction of core functionalities of CRUD, with addition of Clear and Save methods. (CRUD+CS)

All applications and services that implemented Henooh Database Model will interact with data entities through abstractions implemented in Henooh Database Interface. Henooh Database Model provides two different implementation to access database entities. Data can be accessed through Entity Framework Object Relational Mapping, or XML files with use of Windows.Runtime.Serialization.

Henooh Database Interface abstract core functionalities of CRUD+CS the following ways.

Keep in mind, Henooh Database Interface distinguish Create and Update.

Henooh Database Entities

Henooh Database Entities is a set of classes that provides the data columns for a table used within the database.

Henooh Database Administrator Project.

[ProjectName] Database Administrator is a Windows Desktop Application which is part of a Model Implementation of Henooh Database Model. Provides database adminstrative functions of shared database in Henooh Database Model.

Before implementing Henooh Database Model, each project was created with individual database tailored to the scope of the project. There was a separate database for applications in Windows 10 Universal App, Windows Desktop Applications and Website databases. Maintaining three different databases for same data became redundent. If the application is for Desktop and Website use only, having the entire scope of the project using Entity Framework Object Relational Mapping is sufficient. However, with introduction to UWP, data need to be serializeable to XML files. Another challenge is giving the project adminstrator role group ability to make modifications to the data. This project provide a method for Database adminstrators ability to edit and modify data in a text file format. It is important to note that Henooh Desktop Adminstrator is designed for managing and syncing between different Database management systems, and serializeable XML file. This project is not a database management system. Primary features of the applications are listed below.

Henooh Desktop Applications Flow of Control applies for GUI, RtuiData, Engine and Assets.

Henooh Database Adminstration Project is a desktop application, and it is no exception - and follows Henooh Desktop Applications Flow of Control.

DatabaseModel Namespace.

DatabaseModel Namespace is where Henooh Database Interface is implemented.

DatabaseModel provides implementation of CRUD+CS, through two

Context Class.

Provides coordinating functionality for [DatabaseName] Database with Entity Framework.

This is a main class that coordinates Entity Framework functionality for a given data model. This class derives from the System.Data.Entity.DbContext class, which specifies which entities are included in the data model. In addition, there are few customized certain Entity Framework behaviors, determined to be used by Henooh Framework. This class exposes DbSet properties that represent collection of the specified entities in the context.

Entity Framework Class.

Provide Entity Framework implementation of [InterfaceName] interface.

Follows Henooh Database Interface abstraction, CRUD+CS. Create, Read, Update, Delete, Clear and Save.

XML Serialization Class.

Provide XML System.Runtime.Serialization implementation of [InterfaceName] interface.

Follows Henooh Database Interface abstraction, CRUD+CS. Create, Read, Update, Delete, Clear and Save. Unlike Entity Framework, PrimaryId is for each table is another property, does not enforce rules that relational databases require. Additional code has to be written to manage PrimaryId.

Changes to Schema and Data Migrations.

Before making modifications to the Data classes, ensure migrations is enabled.

Go to Nuget Package Manager Console. Choose the Database Administrator Project, and type > enable-migrations

Make updates to the Data classes.

Go to Nuget Package Manager Console. Choose the Database Administrator Project, and type > add-migration [MigrationName] -verbose

After scaffolding migration has completed, in order to update database, type > update-database -verbose

Create one-to-many relationship between two separate tables.

  1. Identify which table will contain collection of the entities in the other table. In the example of one-to-many relationship, the table that contains one entity in one-to-many relationship will be referred as a parent. The table that contains many entities in one-to-many relationship will be referred as a child.
  2. First, ensure that database is properly setup to be migrated after modifications to the data model has been made.
  3. In [Parent].cs (Class file that describes [Parent] table)
    • Create a collection property in [Parent] class with virtual [Child] entities.
                          public virtual List Children { get; set; }
                      
    • Ensure default constructor initializes the value.
                          Parent()
                          {
                              Children = new List();
                          }
                      
    • Modify the Update method to also copy [Child] entities.
                          public void Update(Parent aParent)
                          {
                              Children = aContent.Children;
                          }
                      
  4. In [Child].cs (Class file that describes [Child] table)
    • Create a navigational relationship. Two values you need are Unique Identifier and Virtual property.
                          public int ParentId { get; set; }
                          public virutal Parent Parent { get; set; }
                      
  5. In interface of database, [IFamily.cs] (Interface file that describes the database. For details, Look at Henooh Database Model.) Changes to interface must be handled in the implementation that derives from the interface.
    • We want to modify existing CreateChild method, so that when existing Child gets created, it must have a parent.
                          void CreateChild(Child aChild, int aParentId)
                      
  6. Implementation to [Family]DbEntityFramework.cs file in [Family]DbAdmin.DataModel need to be modified.
    • Modify Create[Child] method to establish one-to-many relationship on creation.
                          public void CreateChild(Child aChild, int aParentId)
                          {
                              aChild.ParentId = aParentId;
                              context.ChildDbSet.Add(aChild);
                              Save();
                          }
                      
    • Modify Clear method, to add and drop constraints.
                          public void Clear()
                          {
                              // Truncate tables without constraints.
                              context.Database.ExecuteSqlCommand("TRUNCATE TABLE Child");
                              // Drop constraints from tables with constraint followed by truncate table commands.
                              context.Database.ExecuteSqlCommand("ALTER TABLE Child DROP CONSTRAINT [FK_dbo.Child_dbo.Parent_ParentId] "
                                  + "FOREIGN KEY ([ParentId]) REFERENCES Parent(ParentId) ON DELETE CASCADE");
                              context.Database.ExecuteSqlCommand("TRUNCATE TABLE Parent");
                              
                              // Add back the constraints that were dropped.
                              context.Database.ExecuteSqlCommand(
                                  "ALTER TABLE Child ADD CONSTRAINT [FK_dbo.Child_dbo.Parent_ParentId] "
                                  + "FOREIGN KEY ([ParentId]) REFERENCES Parent(ParentId) ON DELETE CASCADE");
                          }
                      
  7. Implementation to [Family]DbXml.cs file in [Family]DbAdmin.DataModel need to be modified.
    • Modify Create[Child] method to establish one-to-many relationship on creation.
                          public void CreateChild(Child aChild, int aParentId)
                          {
                              // Get the index of a parent relational data entity.
                              Parent currentParent = ParentCollection.Find(x => x.ParentId == aParentId);
                              int currentParentIndex = ParentCollection.IndexOf(currentParent);
                              Parent entry;
                              // Database identifier is 1 based index, ensure that search for a unique identifier starts from 1.
                              if (aChild.ChildId == 0)
                              {
                                  aChild.ChildId++;
                              }
                              // Find a unique identifier in an incremental order.
                              do
                              {
                                  entry = ChildCollection.Find(x => x.ChildId == aChild.ChildId);
                                  if (entry != null)
                                  {
                                      aChild.ChildId++;
                                  }
                              } while (entry != null);
      
                              // Add the child entity to the XML collection.
                              aChild.ParentId = currentParent.ParentId;
                              ChildCollection.Add(aChild);
      
                              // Modify the parent entity to add an empty child entity with a navigational property set.
                              Child aNavigationalChild = new Child();
                              aNavigationalChild.ParentId = aChild.ParentId;
                              ParentCollection[currentParentIndex].Children.Add(aNavigationalChild);
                          }
                      
  8. Implement new one-to-many relationship in DatabaseManagementController.cs
    • Look at examples of existing code - for CopyEntityFrameworkToXml();
    • Look at examples of existing code - for CopyXmlToEntityFramework();