HierarchyObjects

What is HierarchyObjects?

HierarchyObjects is a professional ORM (object relational mapper) written in C#. It uses native ADO.NET or OLEDB. It is capable of loading an entire hierarchy of unique objects, do joins, and other things you might expect from a good ORM. It was created out of frustration with NHibernate. When using a perfect databasemodel NHibernate is good. But with a difficult databasemodel NHibernate does not work.
HierarchyObjects is flexible and helps the programmer instead of taking over control. You can always access the ADO.NET or OLEDB objects used by HierarchyObjects when you need to perform a difficult task. The name HierarchyObjects is taken from the fact that it is capable of defining hierarchies of objects which can be loaded at once.

Supported databases at this moment: MySQL, PostgreSQL, Sybase ASE, SQLite.

Features of HierarchyObjects:

XML datamodel can be generated from the database.
The XML datamodel can be loaded to inspect the database, but also be used to generate code which can do intellisense at development-time.
A complete hierarchy of objects can be defined, that can be loaded at once. Object uniqueness in this hierarchy is possible.
Definition of joins and extra methods are possible in the databasemodel. See the codegenerator.

Code sample

Sample of all CRUD actions, Join and hierarchy actions:

	HoConnector conn = MySqlConnector();
	conn.Open();
	
	//cRUd: Read and update a row.
	Actor actor = Actor.GetSingle(conn, last_name: "hamill", first_name: "mark");
	actor.middle_name_parts = "jedi";
	actor.Persist(); /* the database is updated */
	
	//Crud : Create a new row.
	Actor wannabeJedi = new Actor(conn);
	wannabeJedi.last_name = "Dancer";
	wannabeJedi.first_name = "Fat";
	wannabeJedi.Persist();
	int newId = wannabeJedi.actor_id.Value;  //the new generated ID is available automatically
	
	//cruD: Delete a row.
	Actor.Delete(conn, actor_id: wannabeJedi.actor_id);
	
	//Make a join. See the code-generator for the definition of the join.
	HoSelect joinSelect = conn.NewSelectJoin();
	joinSelect.AddWhereAnd("actor.last_name = ?", "hamill");
	List rows = joinSelect.ExecuteJoin();
	
	//Load an entire hierarchy of objects.
	Movie movie = Movie.GetSingle(conn, title: "contact");
	movie.LoadHierarchy("movie_hierarchy_images");  //load all the objects that are defined
                                                        //in the hierarchy "movie_hierarchy_images".
	conn.Close();

Hierarchies

A table has relations. They are defined using foreign keys. The programmer often wants to load a complete set of related objects from the database.

For example: in our movie database, we have a couple of tables which are related.

Table movie contains movie information.
Table movie_image contains a picture for a movie, it has a N-1 relation with 'movie'.
Table actor contains actor information.
Table actor_image contains a picture for an actor, it has a N-1 relation with 'actor'.
Table movie_actor relates a movie with an actor. It has an N-M relation with movie and actor.

When the movie is loaded, HierarchyObjects can load related objects:

	Movie movie = Movie.GetSingle(conn, title: "contact");
	movie.LoadHierarchy("movie_hierarchy_images");

Ofcourse the developer wants to define which relations are loaded. This is done in HierarchyObjectsGenerator.

Start the HierarchyObjectsGenerator and load the movies project. Then select the movie table. Then select the Hierarchy tab. This is where the hierarchies are defined. Select the movie_hierarchy_images hierarchy element.

The black items in the tree will be loaded, the light-grey items will not be loaded.

After the statement movie.LoadHierarchy("movie_hierarchy_images"); you can use the related objects:
movie.MovieActorList[0].Actor.last_name

Setup HierarchyObjects

How to setup the samples for HierarchyObjects:

Download HierarchyObjects

Unzip the directory to c:\ (or other location)

Install a mysql databaseserver.

Use HeidiSQL or phpMyAdmin to import c:\HierarchyObjects\movies.sql

Start Visual Studio 2010 and open the HiearchyObjects.sln solution.

Make the project 'HierarchyObjectsTest' the startup project.

Check the connectionstring in HierarchyObjectsTest. It uses the anonymous mysql account. Maybe you want to use an other account.

Rebuild the solution.

Run 'HierarchyObjectsTest'.

Click the button 'Import Backup'. This imports the movie content.

Select a movie from the combobox, and press 'Movie Query 2' for pictures or 'Movie Query 1' for an other sample.

Important

Licenses: please respect the license of the products in de dll directory. They are not part of HierarchyObjects, but just included. No commercial dll's are included in this release.

Credits

HierarchObjects is programmed by Rene Olsthoorn (drachten,The Netherlands), who is a professional MCSD for several years and has used ORM's for almost 14 years. The code is nice and refactored.

License

License: See the sourceforge project. HierarchyObjects can be used in commercial applications. No costs. It would be nice to see bugfixes or additions. Mail me at: rene.olsthoorn@ g m a i l . c o m

More sample code

1) Typically, you first have a database and then want to access it from C#. So, first you have to get the model from the database:

        string MySqlConnStr = "Server=Localhost;Port=3306;Database=movies;";
	HoDatabase database = HoModel.GetModelFromMySql(MySqlConnStr, "movies"); //table schema "movies"
	database.SetInitialValuesAndFolder("movies"); //code generation subfolder "movies"
	database.ModelsPath = "C:\\HierarchyObjects\\Models";
	database.ModelBaseName = "movies";
	database.ModelProjectName = "project";
	database.ProjectModelOutputPath = "C:\\CodeGenerationOutput";
	HoModelWriters.WriteModelToDisk(database);

2) Then, you load the model in HierarchyObjectsGenerator and generate the data access layer (DAL).
Just start HierarchyObjectsGenerator, load the project, and press the 'Generate' button.

3) Then, you load the model in C#, and use your generated access layer:

	HoConnectorMySql conn = new HoConnectorMySql();
	conn.DatabaseModel = HoModel.LoadModelFromDisk("C:\\HierarchyObjects\\Models", "movies project");
	conn.Connection = conn.NewConnection(MySqlConnStr);
        conn.Open();
        List<Movie> movies = Movie.GetList(conn);
        conn.Close();

Notice that HierarchyObjects can also access the database without generated data-access code:

            conn.Open();

            //This is not the preferred way. Please use the generated data access code!

            //cRUd: Read and update a row.
            //WARNING: this is the ugly alternative, the nice way is above in part 1!
            HoTable sqlTable = conn.DatabaseModel.FindTableWithName("actor");
            HoSelect select = conn.NewSelect(sqlTable);
            //extra:  select.Limit = 1;
            select.AddWhereAnd("last_name", "hamill");
            select.AddWhereAnd("first_name", "mark");
            //extra:  int rowCount = select.ExecuteCount(conn.Connection);
            HoRow row = select.Execute(conn.Connection)[0];
            row["middle_name_parts"] = "the jedi";
            HoListAction action = new HoListAction(conn, row);
            action.Execute(conn.Connection); /* the database is updated */

            //Crud : Create a new row.
            //WARNING: this is the ugly alternative, the nice way is above in part 1!
            HoRow newRow = new HoRow(sqlTable);
            newRow["last_name"] = "Dancer";
            newRow["first_name"] = "Fat";
            HoListAction insertAction = new HoListAction(conn, newRow);
            insertAction.Execute(conn.Connection);

            //cruD: Delete a row.
            //WARNING: this is the ugly alternative, the nice way is above in part 1!
            newRow.SetToDelete();
            HoListAction deleteAction = new HoListAction(conn, row);
            deleteAction.Execute(conn.Connection);

            //Make a join.
            //WARNING: this is the ugly alternative, the nice way is above in part 1!
            HoTable joinTable = conn.DatabaseModel.CreateJoinTable("actor.actor_id", 
                                                                   "actor.last_name",
                                                                   "actor_image.actor_image");
            HoSelect theSelect = conn.NewSelect(joinTable);
            theSelect.Limit = 10; //max 10 rows returned this time.
            theSelect.From = "actor, actor_image";
            theSelect.AddWhereAnd("actor.actor_id = actor_image.actor_id");
            theSelect.AddWhereAnd("actor.last_name = ?", "hamill");
            //theSelect.AppendAtTheEnd = "order by actor.actor_id asc";
            HoRow theRow = theSelect.Execute(conn.Connection)[0];

            //Again, this is not the preferred way. Please use the generated data access code!

            conn.Close();