Pre conditions:

=> Database has been installed according to https://www.hemelix.com/sql/sql-server-installation/

=> Database has been created with the following connection string information:

"Server=(local);initial catalog=MyDatabase; user=my_user_id;password=my_password;"

We shall add several complete samples that will help us to learn data model. We shall shows how data from sensor can be stored to database. We shall create reports based on the data accumulated in the SQL database.  

We shall use Fluent NHibernate in all of our projects.  Fluent NHibernate is a DLL that can be linked to our projects and all SQL data can be saved or updated or even deleted without knowing the different SQL syntaxes. Of course, we can write our SQL syntaxes but those are error-prone and our focus will be in another direction than the automation.

Fluent NHibernate offers an alternative to NHibernate’s standard XML mapping files. Rather than writing XML documents, you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability, and more concise code.

Sample 1

Conventional store and worker (adapted from https://github.com/nhibernate/fluent-nhibernate/wiki/Getting-started) 

For this example, we’re going to be mapping a simple domain for a retail company. The company has a couple of stores, each with products in (some products are in both stores, some are exclusive), and each with employees. In database terms, that’s a Store, Employee, and Product table, with a many-to-many table between Store and Product.

First, we need the necessary libraries that support Windows, normally we can add those by Visual Studio Nuget Packages Management or we can download the libraries and use it for our project. Normally, I create a folder called Lib in my projects and I store it there. We shall store those libraries which come as part of Nuget Packages in the Lib folder.

In this example, we shall display the data from the SQL server to our TwinCAT HMI and give the user options to add, delete, and update the SQL database.

Main UI of the sample application is like this:

The application reads 4 tables (Employee, product, store and  store product).

Load From Database => Loads all the contents from SQL Server

Edit From Database => Update the selected data based on store ID from the SQL Server

Delete From Database => Delete the data from the database. If store is selected then the store is deleted as well

Save To Database => Save all data from the edit box to SQL Server.

(No error checking or status of operation is shown)

 

How data grid is managed:

We shall show how TcHmiDatagrid for employee table is managed and linked to the Database. If we press F4 while selected the grid then we find columns and srcData as seen in the following image. Columns indicate how the data will be formatted in the table where as srcData is the actual data in JSON format.

using (var session = NHibernateHelper.OpenSession())
{
using (var tx = session.BeginTransaction())
     {
      productList = (List<Product>)session.QueryOver<Product>().List<Product>();
         employeeList = (List<Employee>)session.QueryOver<Employee>().List<Employee>();
         storeList = (List<Store>)session.QueryOver<Store>().List<Store>();
         numberOfStore = storeList.Count;
         var employeeResult = from p in employeeList
          select new { emplid = p.Id, emplfirstname = p.FirstName, empllastname = p.LastName, emplstoreid = p.Store.Id };
//Anonymous object with properties as it matches with the srcData and columns used as JSON.
             employeeListJson = JsonConvert.SerializeObject(employeeResult);
//employeeListJson will be used srcData for the grid passed by extension framework

How data can be inserted from the grid to edit box:

There are several data grid in the UI. Those have an id for JavaScript method. We need to configure the grid for calling method as shown in the following picture. Grid identifier, row index and column index are passed to the JavaScript method.

Java Script function for retrieving data from the grid and inserting it to the text editor.


(function (TcHmi) {
    var FunctionJS_GridItemClicked = function (GridIdentifier, SelectedRow, SelectedColumn) {        
        switch (GridIdentifier) {
            case 0:
                {
                    var myControl = TcHmi.Controls.get('TcHmiDatagrid_Employee');
                    if (myControl) {
                        var dataa = myControl.getSrcData();
                        var emplid = dataa[SelectedRow].emplid;
                        var emplfirstname = dataa[SelectedRow].emplfirstname;
                        var empllastname = dataa[SelectedRow].empllastname;
                        var emplstoreid = dataa[SelectedRow].emplstoreid;                        
                        var firstNameControl = TcHmi.Controls.get('TcHmiTextbox_FirstName');
                        if (firstNameControl) {
                            firstNameControl.setText(emplfirstname);
                        }
                        var lastNameControl = TcHmi.Controls.get('TcHmiTextbox_LastName');
                        if (lastNameControl) {
                            lastNameControl.setText(empllastname);
                        }
                        var storeControl = TcHmi.Controls.get('TcHmiCombobox_Store');
                        if (storeControl) {
                            storeControl.setSelectedIndex(emplstoreid);
                        }
                    }
                }
                break;
            case 1:
                {
                    var myControl = TcHmi.Controls.get('TcHmiDatagrid_Product');
                    if (myControl) {
                        var dataa = myControl.getSrcData();
                        var prdid = dataa[SelectedRow].prdid;
                        var prdname = dataa[SelectedRow].prdname;
                        var prdprice = dataa[SelectedRow].prdprice;
                        //console.log(prdid);
                        //console.log(prdname);
                        //console.log(prdprice);
                        var productNameControl = TcHmi.Controls.get('TcHmiTextbox_ProductName');
                        if (productNameControl) {
                            productNameControl.setText(prdname);
                        }
                        var productPriceControl = TcHmi.Controls.get('TcHmiTextbox_ProductPrice');
                        if (productPriceControl) {
                            productPriceControl.setText(prdprice);
                        }
                    }
                }
                break;
            case 2:
                {
                    var myControl = TcHmi.Controls.get('TcHmiDatagrid_Store');
                    if (myControl) {
                        var dataa = myControl.getSrcData(); 
                        var strid = dataa[SelectedRow].strid;
                        var strname = dataa[SelectedRow].strname;
                        //console.log(strid);
                        //console.log(strname);
                        var storeControl = TcHmi.Controls.get('TcHmiTextbox_Storeid_Insert');
                        if (storeControl) {
                            storeControl.setText(strname);
                        }
                        var storeControl = TcHmi.Controls.get('TcHmiCombobox_Store');
                        if (storeControl) {
                            storeControl.setSelectedIndex(strid);
                        }                        
                    }
                }
                break;
            case 3:
                {
                    var myControl = TcHmi.Controls.get('TcHmiDatagrid_StoreProduct');
                    if (myControl) {
                        var dataa = myControl.getSrcData();
                        var productid = dataa[SelectedRow].productid;
                        var storeid = dataa[SelectedRow].storeid;
                        //console.log(productid);
                        //console.log(storeid);
                    }
                }
                break;
            default:
                break;
        }        
    };    
    TcHmi.Functions.registerFunction('FunctionJS_GridItemClicked', FunctionJS_GridItemClicked);
})(TcHmi);

Download the source code (couple of functionalities removed from the original thinking due to lack of time).

The following samples are not related to TwinCAT HMI, it is more like windows application. If you want you could take a look in it. This is more like for understanding database and fluent nhibernate

Sample 2 this almost same as in this example, but it was original example found in wiki, but it is written as EXE and DLL


Sample 3 this almost same as in this example, but it was original example found in wiki, but it is written as EXE only


Sample 4 this  example has different dataset as compared to previous 3 example. I found a YouTube (https://www.youtube.com/watch?v=HQcnLP0iYQo&t=8s) video, I watched and wrote myself the code as shown in the video. 

Tips

=> Fluent NHibernate uses different log4net file. If you are using log4net in the extension then, you can’t open the session in the NHibernate. One way to disable the log4net in the extension DLL.